PostgreSQL SQL编写规范

命名规范

数据库对象命名

• 使用小写字母：所有数据库对象名称（表、列、索引、函数等）使用小写字母
• 使用下划线分隔：使用下划线作为单词分隔符，避免使用驼峰命名法
• 避免保留字：避免使用 PostgreSQL 保留字作为对象名称
• 限制长度：名称长度不宜过长，建议不超过30个字符
• 有意义的名称：使用描述性强、易于理解的名称

具体对象命名规则

对象类型	命名规则	示例
数据库	项目名称或业务名称	ecommerce, blog_db
模式	功能模块名称	public, sales, warehouse
表	名词复数形式	users, orders, products
列	名词或形容词+名词	user_id, order_date, product_name
主键	表名_id 或 id	user_id, id
外键	关联表名_id	user_id, product_id
索引	idx_表名_列名	idx_users_email, idx_orders_status_created_at
唯一索引	uk_表名_列名	uk_users_email, uk_products_sku
函数	动词+名词	get_user_by_id, calculate_total_amount
存储过程	动词+名词	process_order, send_notification
视图	v_表名 或 v_业务名称	v_active_users, v_sales_report
物化视图	mv_表名 或 mv_业务名称	mv_daily_sales, mv_product_inventory
序列	表名_列名_seq	users_id_seq, orders_order_no_seq

示例

-- 推荐
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_email ON users(email);
CREATE UNIQUE INDEX uk_users_email ON users(email);

-- 不推荐
CREATE TABLE Users (
    UserID SERIAL PRIMARY KEY,
    Email VARCHAR(255) NOT NULL,
    PasswordHash VARCHAR(255) NOT NULL,
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UpdatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

语法规范

大小写规则

• 关键字大写：SQL 关键字（SELECT、FROM、WHERE、JOIN等）使用大写
• 函数名大写：内置函数名（CURRENT_TIMESTAMP、COUNT、SUM等）使用大写
• 对象名小写：数据库对象名称（表、列、索引等）使用小写
• 值和变量小写：字符串值、数值、变量等使用小写（除非有特殊要求）

缩进和换行

• 使用4个空格缩进：避免使用制表符
• 关键字后换行：SELECT、FROM、WHERE、JOIN等关键字后换行
• 逗号前置：逗号放在列名或表达式的前面，便于添加、删除或注释列
• 合理使用空格：在操作符两侧、逗号后添加空格
• 长查询分行：长查询语句应适当分行，提高可读性

示例

-- 推荐
SELECT
    u.user_id
    ,u.email
    ,u.created_at
    ,COUNT(o.order_id) AS order_count
FROM users u
JOIN orders o ON u.user_id = o.user_id
WHERE u.created_at >= '2023-01-01'
GROUP BY
    u.user_id
    ,u.email
    ,u.created_at
HAVING COUNT(o.order_id) > 5
ORDER BY
    u.created_at DESC
    ,order_count DESC
LIMIT 10;

-- 不推荐
select u.user_id, u.email, u.created_at, count(o.order_id) as order_count from users u join orders o on u.user_id = o.user_id where u.created_at >= '2023-01-01' group by u.user_id, u.email, u.created_at having count(o.order_id) > 5 order by u.created_at desc, order_count desc limit 10;

注释规范

• 单行注释：使用 -- 进行单行注释
• 多行注释：使用 /* */ 进行多行注释
• 注释位置：在复杂查询、函数、存储过程前添加注释
• 清晰的注释内容：注释应简洁明了，说明代码的功能和用途

示例

-- 单行注释：查询活跃用户数量
SELECT COUNT(*) FROM users WHERE is_active = true;

/*
 * 多行注释：
 * 计算每月销售总额
 * 按月份分组并排序
 */
SELECT
    DATE_TRUNC('month', order_date) AS month
    ,SUM(total_amount) AS total_sales
FROM orders
GROUP BY month
ORDER BY month;

查询编写最佳实践

SELECT 语句

• 只选择需要的列：避免使用 SELECT *，只选择实际需要的列
• 使用表别名：在多表查询中使用简洁的表别名
• 避免重复计算：使用 CTE 或子查询避免重复计算
• 使用 LIMIT 限制结果集：在测试或分页查询中使用 LIMIT 限制返回行数

WHERE 子句

• 优先使用索引列：将索引列放在 WHERE 子句的前面
• 避免在索引列上使用函数：如 DATE(created_at) = '2023-01-01' 应改为 created_at BETWEEN '2023-01-01' AND '2023-01-01 23:59:59'
• 使用 IN 替代 OR：当条件较多时，使用 IN 替代多个 OR 条件
• 避免使用 NOT IN：NOT IN 可能导致全表扫描，考虑使用 LEFT JOIN 或 NOT EXISTS 替代

JOIN 语句

• 使用明确的 JOIN 类型：优先使用 INNER JOIN、LEFT JOIN 等明确的 JOIN 类型，避免使用隐式 JOIN
• 限制 JOIN 表的数量：JOIN 表的数量不宜过多，建议不超过5个
• 使用 ON 子句指定连接条件：在 JOIN 子句中明确指定连接条件
• 避免笛卡尔积：确保每个 JOIN 都有适当的连接条件

GROUP BY 和 HAVING

• GROUP BY 列完整：GROUP BY 子句应包含 SELECT 列表中所有非聚合列
• 使用 HAVING 过滤聚合结果：使用 HAVING 过滤聚合结果，而不是 WHERE
• 避免在 HAVING 中使用非聚合列：HAVING 子句应只包含聚合函数或 GROUP BY 列

ORDER BY

• 使用索引列排序：ORDER BY 子句应优先使用索引列
• 避免使用 SELECT * 并 ORDER BY：当使用 ORDER BY 时，避免使用 SELECT *
• 考虑 NULL 值排序：明确指定 NULL 值的排序方式（NULLS FIRST 或 NULLS LAST）

示例

-- 推荐：只选择需要的列，使用表别名，使用明确的 JOIN 类型
SELECT
    u.user_id
    ,u.email
    ,o.order_id
    ,o.order_date
    ,o.total_amount
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id
WHERE u.created_at >= '2023-01-01'
ORDER BY o.order_date DESC
LIMIT 10;

-- 不推荐：使用 SELECT *，隐式 JOIN，在索引列上使用函数
SELECT *
FROM users, orders
WHERE users.user_id = orders.user_id
AND DATE(users.created_at) = '2023-01-01'
ORDER BY orders.order_date;

数据修改语句

INSERT 语句

• 指定列名：明确指定插入的列名，避免依赖列顺序
• 使用批量插入：对于大量数据插入，使用批量插入提高性能
• 使用 RETURNING 子句：需要返回插入结果时，使用 RETURNING 子句
• 考虑默认值：合理使用默认值，减少插入语句的长度

UPDATE 语句

• 使用 WHERE 子句：UPDATE 语句必须包含 WHERE 子句，避免误更新全表
• 使用 LIMIT 限制更新行数：在测试或特定场景下，使用 LIMIT 限制更新行数
• 使用 RETURNING 子句：需要返回更新结果时，使用 RETURNING 子句
• 避免更新主键：尽量避免更新主键值

DELETE 语句

• 使用 WHERE 子句：DELETE 语句必须包含 WHERE 子句，避免误删除全表
• 使用 LIMIT 限制删除行数：在测试或特定场景下，使用 LIMIT 限制删除行数
• 考虑使用 TRUNCATE：对于清空表数据，考虑使用 TRUNCATE 替代 DELETE
• 注意外键约束：删除数据时，注意外键约束的影响

示例

-- 推荐：指定列名，使用 RETURNING 子句
INSERT INTO users (email, password_hash) 
VALUES ('test@example.com', 'hashed_password')
RETURNING user_id;

-- 推荐：使用 WHERE 子句，明确指定条件
UPDATE users 
SET email = 'new_email@example.com', updated_at = CURRENT_TIMESTAMP
WHERE user_id = 1
RETURNING user_id, email;

-- 推荐：使用 WHERE 子句，限制删除行数
DELETE FROM users 
WHERE created_at < '2022-01-01' AND is_active = false
LIMIT 1000;

-- 不推荐：不指定列名，依赖列顺序
INSERT INTO users VALUES (DEFAULT, 'test@example.com', 'hashed_password', DEFAULT, DEFAULT);

-- 不推荐：没有 WHERE 子句，可能误更新全表
UPDATE users SET is_active = false;

性能考虑

索引使用

• 创建合适的索引：根据查询模式创建合适的索引
• 避免过多索引：每个表的索引数量不宜过多，建议不超过5个
• 考虑复合索引：对于经常一起查询的列，考虑创建复合索引
• 定期重建索引：对于频繁更新的表，定期重建索引以提高性能

查询优化

• 使用 EXPLAIN ANALYZE：分析查询执行计划，优化慢查询
• 避免全表扫描：通过创建索引或优化查询条件，避免全表扫描
• 使用 CTE 或子查询：将复杂查询拆分为简单的 CTE 或子查询
• 避免递归查询：除非必要，避免使用递归查询

事务管理

• 保持事务简短：事务应尽可能简短，减少锁持有时间
• 避免长事务：长事务会导致锁争用和 MVCC 膨胀
• 使用合适的隔离级别：根据业务需求选择合适的隔离级别
• 使用 SAVEPOINT：在复杂事务中使用 SAVEPOINT，便于回滚部分操作

示例

-- 推荐：使用 EXPLAIN ANALYZE 分析查询
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

-- 推荐：使用 CTE 拆分复杂查询
WITH recent_orders AS (
    SELECT order_id, user_id, total_amount
    FROM orders
    WHERE order_date >= '2023-01-01'
)
SELECT
    u.user_id
    ,u.email
    ,ro.total_amount
FROM users u
JOIN recent_orders ro ON u.user_id = ro.user_id;

安全考虑

防止 SQL 注入

• 使用参数化查询：使用参数化查询或预处理语句，避免直接拼接 SQL
• 验证输入：对用户输入进行严格验证和过滤
• 使用最小权限原则：数据库用户应只拥有必要的权限
• 避免使用动态 SQL：除非必要，避免使用动态 SQL

数据保护

• 加密敏感数据：对敏感数据（如密码、信用卡号）进行加密存储
• 使用视图限制数据访问：使用视图限制用户对敏感数据的访问
• 审计重要操作：对重要操作（如用户登录、数据修改）进行审计
• 定期备份数据：定期备份数据库，确保数据安全

示例

-- 推荐：使用参数化查询
PREPARE stmt1 (VARCHAR) AS
SELECT * FROM users WHERE email = $1;
EXECUTE stmt1('test@example.com');
DEALLOCATE stmt1;

-- 推荐：使用视图限制数据访问
CREATE VIEW user_public_info AS
SELECT user_id, email, created_at
FROM users
WHERE is_active = true;

-- 不推荐：直接拼接 SQL，容易导致 SQL 注入
SELECT * FROM users WHERE email = '' || $email || '';

版本特定语法

PostgreSQL 12+ 特性

• 表分区增强：支持声明式分区，简化分区表管理
• JSONB 增强：支持 JSONB 路径查询
• 索引增强：支持覆盖索引

PostgreSQL 13+ 特性

• 并行查询增强：支持更多并行查询场景
• 逻辑复制增强：支持发布-订阅模式
• 真空增强：支持并行真空

PostgreSQL 14+ 特性

• JSONB 增强：支持 JSONB 子脚本语法
• 索引增强：支持多值索引
• 事务增强：支持事务级别的临时表

PostgreSQL 15+ 特性

• 权限增强：支持行级安全策略
• 查询增强：支持 MERGE 语句
• 性能增强：支持增量排序

PostgreSQL 16+ 特性

• 并行查询增强：支持更多并行执行场景
• 索引增强：支持表达式索引的统计信息
• 安全性增强：支持更细粒度的权限控制

代码审查要点

语法检查

• 检查 SQL 语法是否正确
• 检查命名规范是否符合要求
• 检查缩进和换行是否规范
• 检查注释是否清晰完整

性能检查

• 检查是否存在慢查询
• 检查索引使用是否合理
• 检查 JOIN 表的数量是否过多
• 检查是否存在全表扫描

安全性检查

• 检查是否存在 SQL 注入风险
• 检查是否使用了最小权限原则
• 检查敏感数据是否加密
• 检查是否存在审计机制

可读性检查

• 检查代码是否易于理解
• 检查命名是否清晰易懂
• 检查注释是否充分
• 检查代码结构是否合理

常见问题 (FAQ)

为什么不推荐使用 SELECT *？

• 性能问题：SELECT * 会返回所有列，包括不需要的列，增加网络传输和内存消耗
• 维护问题：当表结构发生变化时，SELECT * 可能导致应用程序出现问题
• 可读性问题：SELECT * 使查询结果的列不明确，降低代码可读性

如何处理 NULL 值？

• 使用 COALESCE 函数：将 NULL 值转换为默认值
• 使用 IS NULL 或 IS NOT NULL：明确检查 NULL 值
• 在 ORDER BY 中指定 NULLS FIRST 或 NULLS LAST：控制 NULL 值的排序方式
• 考虑使用 NOT NULL 约束：对于不应该为 NULL 的列，添加 NOT NULL 约束

什么时候使用索引？

• 经常用于 WHERE 条件的列
• 经常用于 JOIN 条件的列
• 经常用于 ORDER BY 或 GROUP BY 的列
• 区分度高的列（如 email、ID 等）

如何优化慢查询？

• 使用 EXPLAIN ANALYZE 分析执行计划
• 创建合适的索引
• 优化查询条件，避免全表扫描
• 减少 JOIN 表的数量
• 使用 CTE 或子查询拆分复杂查询

如何防止 SQL 注入？

• 使用参数化查询或预处理语句
• 对用户输入进行严格验证和过滤
• 避免直接拼接 SQL
• 使用最小权限原则

什么时候使用事务？

• 需要保证多个操作的原子性时
• 需要保证数据一致性时
• 需要回滚操作时
• 涉及多个表的修改时

如何选择合适的隔离级别？

• READ COMMITTED：默认隔离级别，适用于大多数场景
• REPEATABLE READ：适用于需要一致读取的场景
• SERIALIZABLE：最高隔离级别，适用于需要严格一致性的场景
• READ UNCOMMITTED：最低隔离级别，可能读取到未提交的数据，不推荐使用

如何管理数据库对象权限？

• 使用角色管理权限
• 遵循最小权限原则
• 定期审查权限设置
• 为不同用户设置不同的权限

总结

SQL 编写规范是保证数据库代码质量和可维护性的重要手段。通过遵循统一的命名规范、语法规范和最佳实践，可以提高 SQL 代码的可读性、性能和安全性。

在编写 SQL 代码时，需要考虑性能、安全性和可维护性等方面，同时关注 PostgreSQL 不同版本的特性和语法差异。定期进行代码审查，及时发现和修复问题，也是保证 SQL 代码质量的重要环节。

通过不断学习和实践 SQL 编写规范，可以提高数据库开发的效率和质量，为业务发展提供可靠的数据支持。