PostgreSQL SQL编写规范

核心概念

SQL编写规范是数据库开发的重要组成部分，良好的SQL编写规范有助于：

提高代码的可读性和可维护性
确保SQL语句的执行效率
避免常见的SQL错误
便于团队协作开发
降低后期维护成本

SQL语句格式规范

1. 基本格式规则

关键字大写：SQL关键字（SELECT, FROM, WHERE等）统一使用大写字母
缩进：使用4个空格进行缩进，不使用制表符
换行：每个主要子句（SELECT, FROM, WHERE, JOIN等）单独占一行
逗号位置：逗号放在列名或表名的后面，便于注释和修改
空格使用：在关键字和标识符之间、运算符两侧添加空格

sql

-- 推荐
SELECT 
    u.id, 
    u.username, 
    o.order_no, 
    o.total_amount
FROM 
    users u
JOIN 
    orders o ON u.id = o.user_id
WHERE 
    u.status = 1
    AND o.created_at > '2023-01-01'
ORDER BY 
    o.created_at DESC;

-- 不推荐
SELECT u.id,u.username,o.order_no,o.total_amount FROM users u JOIN orders o ON u.id=o.user_id WHERE u.status=1 AND o.created_at>'2023-01-01' ORDER BY o.created_at DESC;

2. SELECT语句规范

**避免使用SELECT ***：只查询需要的列，减少网络传输和I/O开销
使用表别名：特别是在多表连接时，使用简洁的表别名
列名顺序：将主键列放在前面，然后是常用列，最后是不常用列
使用DISTINCT时要谨慎：DISTINCT会增加查询开销，确保必要时才使用

sql

-- 推荐
SELECT 
    id, 
    username, 
    email, 
    created_at
FROM 
    users
WHERE 
    status = 1;

-- 不推荐
SELECT * FROM users WHERE status=1;

3. INSERT语句规范

指定列名：明确指定插入的列名，避免因表结构变更导致的错误
批量插入：使用批量INSERT语句，减少网络往返次数
使用DEFAULT关键字：对于有默认值的列，使用DEFAULT关键字

sql

-- 推荐：指定列名
INSERT INTO users (
    username, 
    email, 
    password_hash, 
    created_at
) VALUES (
    'john_doe', 
    'john@example.com', 
    'hashed_password', 
    DEFAULT
);

-- 推荐：批量插入
INSERT INTO users (
    username, 
    email, 
    password_hash
) VALUES 
    ('user1', 'user1@example.com', 'hash1'),
    ('user2', 'user2@example.com', 'hash2'),
    ('user3', 'user3@example.com', 'hash3');

-- 不推荐：不指定列名
INSERT INTO users VALUES ('john_doe', 'john@example.com', 'hashed_password', NOW());

4. UPDATE语句规范

指定WHERE条件：避免无WHERE条件的UPDATE，防止误更新所有行
使用LIMIT：在必要时使用LIMIT限制更新的行数
明确更新的列：只更新需要修改的列，减少I/O开销
使用RETURNING子句：获取更新后的结果，便于验证和后续处理

sql

-- 推荐
UPDATE users 
SET 
    status = 0, 
    updated_at = NOW() 
WHERE 
    id = 1 
RETURNING 
    id, 
    status, 
    updated_at;

-- 推荐：使用LIMIT
UPDATE users 
SET 
    status = 0 
WHERE 
    last_login < '2022-01-01' 
LIMIT 100;

-- 不推荐：无WHERE条件
UPDATE users SET status = 0;

5. DELETE语句规范

指定WHERE条件：避免无WHERE条件的DELETE，防止误删除所有行
使用LIMIT：在必要时使用LIMIT限制删除的行数
考虑使用软删除：对于重要数据，考虑使用标记删除（is_deleted = true）而非物理删除
使用RETURNING子句：获取删除前的结果，便于备份和恢复

sql

-- 推荐
DELETE FROM users 
WHERE 
    id = 1 
RETURNING 
    *;

-- 推荐：软删除
UPDATE users 
SET 
    is_deleted = true, 
    updated_at = NOW() 
WHERE 
    id = 1;

-- 不推荐：无WHERE条件
DELETE FROM users;

JOIN操作规范

1. JOIN类型选择

优先使用INNER JOIN：明确指定JOIN类型，避免隐式JOIN
避免过多的JOIN：尽量减少JOIN表的数量，建议不超过5个
合理使用LEFT/RIGHT JOIN：根据业务需求选择合适的外连接类型
使用ON子句指定连接条件：将连接条件放在ON子句中，而非WHERE子句

sql

-- 推荐：明确JOIN类型
SELECT 
    u.username, 
    o.order_no 
FROM 
    users u
INNER JOIN 
    orders o ON u.id = o.user_id
WHERE 
    u.status = 1;

-- 推荐：左连接
SELECT 
    u.username, 
    COALESCE(SUM(o.total_amount), 0) AS total_spent
FROM 
    users u
LEFT JOIN 
    orders o ON u.id = o.user_id
GROUP BY 
    u.username;

-- 不推荐：隐式JOIN
SELECT u.username, o.order_no FROM users u, orders o WHERE u.id=o.user_id AND u.status=1;

2. 连接条件规范

使用主键-外键连接：优先使用主键和外键进行连接，提高查询效率
避免在连接条件中使用函数：会导致索引失效
连接条件顺序：将选择性高的连接条件放在前面

子查询和CTE规范

1. 子查询规范

使用相关子查询时要谨慎：相关子查询可能导致性能问题
考虑使用JOIN替代子查询：在某些情况下，JOIN的性能优于子查询
为子查询添加别名：提高可读性

sql

-- 推荐：使用JOIN替代子查询
SELECT 
    u.username
FROM 
    users u
JOIN 
    (SELECT DISTINCT user_id FROM orders WHERE total_amount > 1000) o 
    ON u.id = o.user_id;

-- 不推荐：相关子查询
SELECT 
    username
FROM 
    users u
WHERE 
    EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.total_amount > 1000);

2. CTE（Common Table Expressions）规范

使用CTE简化复杂查询：将复杂查询拆分为多个CTE，提高可读性和可维护性
为CTE添加有意义的名称：便于理解CTE的用途
避免递归CTE的无限循环：确保递归CTE有明确的终止条件

sql

-- 推荐：使用CTE简化复杂查询
WITH 
    high_value_orders AS (
        SELECT 
            user_id, 
            SUM(total_amount) AS total_spent
        FROM 
            orders
        WHERE 
            created_at > '2023-01-01'
        GROUP BY 
            user_id
        HAVING 
            SUM(total_amount) > 1000
    )
SELECT 
    u.username, 
    h.total_spent
FROM 
    users u
JOIN 
    high_value_orders h ON u.id = h.user_id
ORDER BY 
    h.total_spent DESC;

事务处理规范

1. 事务设计原则

保持事务短小：减少锁持有时间，降低死锁风险
避免在事务中执行耗时操作：如网络请求、文件I/O等
使用合适的隔离级别：根据业务需求选择合适的隔离级别
显式提交或回滚：避免隐式事务导致的问题

2. 事务编写规范

使用BEGIN/COMMIT/ROLLBACK：明确标记事务边界
在异常情况下回滚事务：使用TRY-CATCH或其他异常处理机制
避免嵌套事务：PostgreSQL不支持真正的嵌套事务

sql

-- 推荐
BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 验证操作结果
SELECT 
    (SELECT balance FROM accounts WHERE id = 1) AS account1_balance,
    (SELECT balance FROM accounts WHERE id = 2) AS account2_balance;

COMMIT;

-- 错误处理示例
BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
IF NOT FOUND THEN
    ROLLBACK;
    RAISE EXCEPTION 'Account 1 not found';
END IF;

UPDATE accounts SET balance = balance + 100 WHERE id = 2;
IF NOT FOUND THEN
    ROLLBACK;
    RAISE EXCEPTION 'Account 2 not found';
END IF;

COMMIT;

注释规范

1. 注释类型

单行注释：使用--进行单行注释
多行注释：使用/* */进行多行注释
文档注释：为函数、存储过程添加文档注释

2. 注释最佳实践

注释要简洁明了：避免冗余注释
注释要准确：确保注释与代码一致
注释复杂逻辑：对复杂的SQL语句添加注释，说明其用途和逻辑
注释特殊处理：对特殊的业务规则或边界情况添加注释

sql

-- 推荐：单行注释
SELECT 
    id, 
    username, 
    email
FROM 
    users
WHERE 
    status = 1; -- 只查询活跃用户

-- 推荐：多行注释
/*
 * 查询2023年第一季度的订单统计
 * 按用户分组，计算总订单金额和订单数量
 */
SELECT 
    user_id, 
    COUNT(*) AS order_count, 
    SUM(total_amount) AS total_amount
FROM 
    orders
WHERE 
    created_at BETWEEN '2023-01-01' AND '2023-03-31'
GROUP BY 
    user_id;

-- 推荐：函数文档注释
/**
 * 计算用户的总消费金额
 * @param user_id 用户ID
 * @return 总消费金额
 */
CREATE FUNCTION get_user_total_spent(user_id INT) 
RETURNS NUMERIC AS $$
BEGIN
    RETURN (
        SELECT COALESCE(SUM(total_amount), 0) 
        FROM orders 
        WHERE orders.user_id = get_user_total_spent.user_id
    );
END;
$$ LANGUAGE plpgsql;

其他最佳实践

1. 避免在WHERE子句中使用函数

函数操作会导致索引失效，降低查询效率
考虑使用计算列或索引表达式

sql

-- 不推荐：WHERE子句中使用函数
SELECT * FROM orders WHERE DATE(created_at) = '2023-01-01';

-- 推荐：使用范围查询
SELECT * FROM orders WHERE created_at >= '2023-01-01' AND created_at < '2023-01-02';

-- 推荐：使用索引表达式
CREATE INDEX idx_orders_created_date ON orders ((DATE(created_at)));

2. 使用EXPLAIN分析查询

定期使用EXPLAIN或EXPLAIN ANALYZE分析复杂查询的执行计划
根据执行计划优化查询和索引

3. 避免使用ORDER BY RANDOM()

ORDER BY RANDOM()会导致全表扫描，效率低下
考虑使用其他方法实现随机排序

sql

-- 不推荐
SELECT * FROM users ORDER BY RANDOM() LIMIT 10;

-- 推荐：使用TABLESAMPLE
SELECT * FROM users TABLESAMPLE SYSTEM(10) LIMIT 10;

常见问题（FAQ）

Q1：如何处理长SQL语句？

A1：可以采取以下方法：

将长SQL语句拆分为多个CTE
使用合适的换行和缩进
为表和列添加别名
将复杂逻辑封装为函数或视图

Q2：如何优化复杂查询？

A2：优化复杂查询的方法包括：

分析执行计划，查看是否存在全表扫描或低效的JOIN
添加合适的索引
减少JOIN表的数量
避免使用子查询和复杂函数
考虑使用物化视图

Q3：如何避免SQL注入？

A3：避免SQL注入的方法包括：

使用参数化查询
对输入进行验证和过滤
限制数据库用户的权限
使用ORM框架

Q4：何时使用存储过程？

A4：考虑使用存储过程的情况：

复杂的业务逻辑需要在数据库端执行
需要多次执行的复杂查询
涉及多个表的事务操作
需要提高查询性能

Q5：如何编写可维护的SQL代码？

A5：编写可维护SQL代码的建议：

遵循一致的命名规范和格式规范
添加必要的注释
避免使用复杂的嵌套查询
封装复杂逻辑为函数或视图
定期重构和优化SQL代码

PostgreSQL SQL编写规范 ​

核心概念 ​

SQL语句格式规范 ​

1. 基本格式规则 ​

2. SELECT语句规范 ​

3. INSERT语句规范 ​

4. UPDATE语句规范 ​

5. DELETE语句规范 ​

JOIN操作规范 ​

1. JOIN类型选择 ​

2. 连接条件规范 ​

子查询和CTE规范 ​

1. 子查询规范 ​

2. CTE（Common Table Expressions）规范 ​

事务处理规范 ​

1. 事务设计原则 ​

2. 事务编写规范 ​

注释规范 ​

1. 注释类型 ​

2. 注释最佳实践 ​

其他最佳实践 ​

1. 避免在WHERE子句中使用函数 ​

2. 使用EXPLAIN分析查询 ​

3. 避免使用ORDER BY RANDOM() ​

常见问题（FAQ） ​

Q1：如何处理长SQL语句？ ​

Q2：如何优化复杂查询？ ​

Q3：如何避免SQL注入？ ​

Q4：何时使用存储过程？ ​

Q5：如何编写可维护的SQL代码？ ​

PostgreSQL SQL编写规范

核心概念

SQL语句格式规范

1. 基本格式规则

2. SELECT语句规范

3. INSERT语句规范

4. UPDATE语句规范

5. DELETE语句规范

JOIN操作规范

1. JOIN类型选择

2. 连接条件规范

子查询和CTE规范

1. 子查询规范

2. CTE（Common Table Expressions）规范

事务处理规范

1. 事务设计原则

2. 事务编写规范

注释规范

1. 注释类型

2. 注释最佳实践

其他最佳实践

1. 避免在WHERE子句中使用函数

2. 使用EXPLAIN分析查询

3. 避免使用ORDER BY RANDOM()

常见问题（FAQ）

Q1：如何处理长SQL语句？

Q2：如何优化复杂查询？

Q3：如何避免SQL注入？

Q4：何时使用存储过程？

Q5：如何编写可维护的SQL代码？