印刻数据库

外观

PostgreSQL SQL书写规范 ​

1. 命名规范 ​

1.1 数据库对象命名 ​

对象类型命名规则示例
数据库小写字母+下划线，业务相关ecommerce_db
模式小写字母+下划线，功能模块相关sales_schema
表小写字母+下划线，复数形式orders, users
视图小写字母+下划线，v_前缀v_active_orders
索引小写字母+下划线，idx_表名_字段名idx_orders_customer_id
函数小写字母+下划线，动词开头get_user_by_id()
存储过程小写字母+下划线，sp_前缀sp_process_order()
序列小写字母+下划线，表名_id_seqorders_order_id_seq
触发器小写字母+下划线，tr_表名_事件tr_orders_after_insert

1.2 列命名 ​

  • 使用小写字母+下划线
  • 避免使用保留字
  • 保持命名简洁明确
  • 使用有意义的名称，避免缩写

推荐：

sql
user_id, order_date, total_amount

不推荐：

sql
uid, od, tamt  -- 缩写不明确

2. 格式规范 ​

2.1 基本格式 ​

  • 关键字大写
  • 缩进使用4个空格（避免使用制表符）
  • 逗号放在行尾
  • 每行长度不超过120个字符

示例：

sql
SELECT
    o.order_id,
    o.customer_id,
    u.username,
    o.total_amount,
    o.order_date
FROM
    orders o
JOIN
    users u ON o.customer_id = u.user_id
WHERE
    o.order_date >= '2024-01-01'
    AND o.status = 'completed'
ORDER BY
    o.order_date DESC
LIMIT 100;

2.2 复杂查询格式 ​

  • 子查询、CTE单独成行
  • JOIN条件每行一个
  • WHERE条件按逻辑分组
  • 函数调用参数换行对齐

示例：

sql
WITH recent_orders AS (
    SELECT
        order_id,
        customer_id,
        total_amount
    FROM
        orders
    WHERE
        order_date >= NOW() - INTERVAL '30 days'
)
SELECT
    c.customer_id,
    c.customer_name,
    COUNT(ro.order_id) AS order_count,
    SUM(ro.total_amount) AS total_spent
FROM
    customers c
LEFT JOIN
    recent_orders ro ON c.customer_id = ro.customer_id
GROUP BY
    c.customer_id,
    c.customer_name
HAVING
    COUNT(ro.order_id) > 0
ORDER BY
    total_spent DESC
LIMIT 20;

2.3 DDL语句格式 ​

sql
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
    last_login TIMESTAMP,
    is_active BOOLEAN NOT NULL DEFAULT true,
    role VARCHAR(20) NOT NULL DEFAULT 'user'
);

CREATE INDEX idx_users_email ON users (email);

3. 性能优化规范 ​

3.1 SELECT语句优化 ​

  1. 只查询需要的列：避免使用SELECT *

    sql
    -- 推荐
SELECT user_id, username FROM users;

-- 不推荐
SELECT * FROM users;

  2. 使用LIMIT限制返回行数：特别是在开发和调试阶段

    sql
    SELECT * FROM large_table LIMIT 100;

  3. 避免在WHERE子句中使用函数：会导致索引失效

    sql
    -- 推荐
SELECT * FROM orders WHERE order_date >= '2024-01-01';

-- 不推荐
SELECT * FROM orders WHERE DATE(order_date) = '2024-01-01';

  4. 使用JOIN替代子查询：在大多数情况下JOIN性能更好

    sql
    -- 推荐
SELECT o.* FROM orders o JOIN users u ON o.customer_id = u.user_id WHERE u.username = 'john';

-- 不推荐
SELECT * FROM orders WHERE customer_id IN (SELECT user_id FROM users WHERE username = 'john');

3.2 索引使用规范 ​

  1. 为WHERE子句中的列创建索引
  2. 为JOIN条件中的列创建索引
  3. 为ORDER BY和GROUP BY列创建索引
  4. 避免创建过多索引：每个表建议不超过5个索引
  5. 使用复合索引时，将高选择性列放在前面

3.3 事务处理规范 ​

  1. 保持事务简短：减少锁持有时间
  2. 明确指定事务隔离级别：根据业务需求选择合适的隔离级别
  3. 避免在事务中执行DDL语句：会导致隐式提交
  4. 使用SAVEPOINT进行部分回滚：在复杂事务中使用
sql
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 业务逻辑
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

COMMIT;

4. 安全规范 ​

4.1 防止SQL注入 ​

  1. 使用参数化查询：避免直接拼接SQL语句
  2. 验证输入数据：对用户输入进行严格验证
  3. 使用最小权限原则：为应用程序分配最小必要权限

4.2 敏感数据处理 ​

  1. 避免明文存储敏感数据：使用加密函数存储密码等敏感信息
  2. 使用列级权限：限制对敏感列的访问
  3. 审计敏感操作：记录对敏感数据的访问和修改
sql
-- 推荐
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    password_hash VARCHAR(255) NOT NULL  -- 存储哈希值
);

-- 不推荐
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    password VARCHAR(50) NOT NULL  -- 明文存储
);

5. 最佳实践 ​

5.1 代码复用 ​

  • 使用视图封装复杂查询
  • 使用函数和存储过程封装业务逻辑
  • 使用CTE简化复杂查询

5.2 注释规范 ​

  • 为复杂查询添加注释
  • 为函数和存储过程添加文档注释
  • 说明业务逻辑和特殊处理
sql
-- 查询最近30天的订单统计数据
SELECT
    DATE(order_date) AS order_day,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_sales
FROM
    orders
WHERE
    order_date >= NOW() - INTERVAL '30 days'
GROUP BY
    DATE(order_date)
ORDER BY
    order_day;

5.3 版本兼容性 ​

  • 避免使用特定版本的功能：如需要使用，添加版本检查
  • 使用标准SQL语法：提高代码可移植性
  • 测试不同版本的兼容性

5.4 测试规范 ​

  • 为复杂查询编写测试用例
  • 测试边界条件
  • 测试性能和并发情况

6. 常见错误与规避 ​

6.1 空值处理 ​

  • 正确使用IS NULL和IS NOT NULL
  • 避免使用=或!=比较NULL值
  • 考虑使用COALESCE()函数处理NULL值
sql
-- 推荐
SELECT * FROM users WHERE last_login IS NULL;

-- 不推荐
SELECT * FROM users WHERE last_login = NULL;

6.2 数据类型匹配 ​

  • 确保比较的数据类型一致
  • 避免隐式类型转换
  • 使用显式类型转换函数
sql
-- 推荐
SELECT * FROM orders WHERE order_id = 123;

-- 不推荐
SELECT * FROM orders WHERE order_id = '123';  -- 隐式转换

6.3 锁竞争 ​

  • 避免长时间持有锁
  • 减少事务中的操作数量
  • 使用合适的锁级别

7. 版本差异注意事项 ​

功能PostgreSQL版本注意事项
JSONB支持9.4+9.4之前使用JSON类型
并行查询9.6+9.6之前不支持并行执行
分区表支持10+10之前使用继承表实现分区
物化视图自动刷新13+13之前需要手动刷新
原生表压缩14+14之前使用外部工具压缩

8. 工具推荐 ​

8.1 SQL格式化工具 ​

  • pgFormatter：PostgreSQL专用SQL格式化工具
  • SQLFluff：支持多种数据库的SQL lint工具
  • dbeaver：集成开发环境，支持SQL格式化

8.2 性能分析工具 ​

  • EXPLAIN ANALYZE：内置查询计划分析工具
  • pg_stat_statements：查询统计扩展
  • pgBadger：日志分析工具

9. 代码审查要点 ​

  1. 命名规范：检查数据库对象和列命名是否符合规范
  2. 格式规范：检查SQL格式是否统一
  3. 性能问题：检查是否存在性能瓶颈
  4. 安全问题：检查是否存在SQL注入风险
  5. 可维护性：检查代码是否易于理解和维护
  6. 版本兼容性：检查是否兼容目标PostgreSQL版本

10. 总结 ​

良好的SQL书写规范对于数据库的性能、可维护性和安全性至关重要。通过遵循本规范，可以：

  • 提高SQL代码的可读性和可维护性
  • 减少性能问题和安全漏洞
  • 提高团队协作效率
  • 确保数据库的长期稳定运行

建议将本规范作为团队SQL开发的标准，并定期进行培训和代码审查，持续改进SQL开发质量。