SQL Formatter
格式化 SQL 查询以提高可读性并压缩它们的工具。整理复杂的查询并统一团队内的 SQL 编码风格!
Examples:
Formatted SQL will appear here...
💡 Tips
- 복잡한 쿼리를 읽기 쉽게 포맷팅하세요
- 팀 내 SQL 코딩 스타일을 통일하세요
- Compress 기능으로 쿼리를 한 줄로 만들 수 있습니 다
- 여러 SQL 문을 한 번에 포맷팅할 수 있습니다
主要功能
1. SQL 格式化
- 为关键字、列和表名进行适当的缩进
- 换行以提高可读性
- 应用一致的编码风格
2. 支持多种 SQL 方言
- 标准 SQL
- MySQL
- PostgreSQL
- MariaDB
- PL/SQL (Oracle)
- T-SQL (SQL Server)
- SQLite
3. 自定义选项
- 缩进样式(2 个空格、4 个空格、Tab)
- 关键字大写
- 行间距调整
4. 压缩功能
- 删除不必要的空白
- 压缩为单行
使用示例
格式化前
SELECT u.id,u.name,u.email,o.order_id,o.total FROM users u LEFT JOIN orders o ON u.id=o.user_id WHERE u.active=1 AND o.total>100 ORDER BY o.total DESC LIMIT 10;
格式化后
SELECT
u.id,
u.name,
u.email,
o.order_id,
o.total
FROM
users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE
u.active = 1
AND o.total > 100
ORDER BY
o.total DESC
LIMIT
10;
使用场景
1. 代码审查
-- 格式化复杂查询以便于审查
WITH monthly_sales AS (
SELECT
DATE_TRUNC('month', order_date) AS month,
SUM(total) AS revenue
FROM
orders
GROUP BY
DATE_TRUNC('month', order_date)
)
SELECT
month,
revenue,
LAG(revenue) OVER (
ORDER BY
month
) AS prev_month_revenue
FROM
monthly_sales;
2. 文档编写
-- 整理 README 或文档中的 SQL
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_users_email ON users (email);
3. 调试
-- 使日志中的单行查询可读
SELECT
p.id,
p.name,
c.name AS category,
COUNT(o.id) AS order_count
FROM
products p
INNER JOIN categories c ON p.category_id = c.id
LEFT JOIN order_items o ON p.id = o.product_id
WHERE
p.active = TRUE
GROUP BY
p.id,
p.name,
c.name
HAVING
COUNT(o.id) > 10;
4. 复杂的 CTE(公用表表达式)
WITH RECURSIVE subordinates AS (
SELECT
employee_id,
manager_id,
full_name,
1 AS level
FROM
employees
WHERE
manager_id IS NULL
UNION ALL
SELECT
e.employee_id,
e.manager_id,
e.full_name,
s.level + 1
FROM
employees e
INNER JOIN subordinates s ON s.employee_id = e.manager_id
)
SELECT
employee_id,
full_name,
level
FROM
subordinates
ORDER BY
level,
full_name;
使用压缩功能
何时使用?
- 日志分析: 将多行查询转换为单行以便 grep 搜索
- URL 参数: 在 URL 中包含查询时
- 节省空间: 在代码中内联使用简单查询
示例
-- 压缩前(多行)
SELECT
id,
name
FROM
users
WHERE
active = TRUE;
-- 压缩后(单行)
SELECT id,name FROM users WHERE active=TRUE;
最佳实践
1. 一致的风格
在团队内使用相同的格式设置:
- 缩进: 2 个空格
- 关键字: 大写
- 行间距: 1
2. 对复杂查询使用 CTE
-- 好: 用 CTE 分离
WITH active_users AS (
SELECT * FROM users WHERE active = TRUE
),
recent_orders AS (
SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '30 days'
)
SELECT
u.*,
COUNT(o.id) AS order_count
FROM
active_users u
LEFT JOIN recent_orders o ON u.id = o.user_id
GROUP BY
u.id;
3. 使用 JOIN 而不是子查询
-- 不好: 子查询
SELECT
*
FROM
orders
WHERE
user_id IN (
SELECT
id
FROM
users
WHERE
active = TRUE
);
-- 好: 使用 JOIN
SELECT
o.*
FROM
orders o
INNER JOIN users u ON o.user_id = u.id
WHERE
u.active = TRUE;
4. 显式列出列
-- 不好: SELECT *
SELECT * FROM users;
-- 好: 显式列
SELECT
id,
name,
email,
created_at
FROM
users;
支持的 SQL 语法
DDL(数据定义语言)
CREATE TABLE, ALTER TABLE, DROP TABLE
CREATE INDEX, DROP INDEX
CREATE VIEW, DROP VIEW
DML(数据操作语言)
SELECT, INSERT, UPDATE, DELETE
MERGE(某些方言)
高级功能
-- CTE(公用表表达式)
WITH cte_name AS (...)
-- 窗口函数
ROW_NUMBER(), RANK(), LAG(), LEAD()
-- 连接
INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, CROSS JOIN
-- 子查询
WHERE id IN (SELECT ...), FROM (SELECT ...) AS subquery
提示和技巧
1. 逐步编写长查询
-- 步骤 1: 基本 SELECT
SELECT * FROM users;
-- 步骤 2: 添加 JOIN
SELECT * FROM users u
JOIN orders o ON u.id = o.user_id;
-- 步骤 3: WHERE 条件
SELECT * FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.active = TRUE;
-- 步骤 4: 格式化
2. 使用注释
-- 查询用户订单统计
SELECT
u.id,
u.name,
COUNT(o.id) AS total_orders, -- 总订单数
SUM(o.total) AS total_revenue -- 总收入
FROM
users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY
u.id,
u.name;
3. 一致的表别名
-- 使用首字母
FROM users u, orders o
-- 使用有意义的缩写
FROM users usr, orders ord
注意事项
- 格式化程序不验证 SQL 语法
- 方言之间可能存在细微差异
- 压缩功能会牺牲可读性(仅在需要时使用)
- 格式化时保留注释
相关工具
- JSON Path Tester - JSON 查询
- Regex Tester - 正则表达式测试
- Text Diff - 查询比较
键盘快捷键
许多 SQL 编辑器提供格式化快捷键:
- VS Code:
Shift + Alt + F - JetBrains:
Ctrl + Alt + L - DBeaver:
Ctrl + Shift + F