外观
MySQL SQL书写规范
SQL书写规范概述
为什么需要SQL书写规范
SQL书写规范是确保SQL语句具有良好可读性、高性能和可维护性的一组规则。良好的SQL书写规范可以：
- 提高可读性：便于团队成员理解和维护
- 减少错误：标准化的书写方式减少语法错误
- 提高性能：规范的SQL更容易被优化器识别和优化
- 便于调试：结构清晰的SQL更容易定位问题
- 促进团队协作：统一的书写风格便于团队成员之间的交流
书写规范的基本原则
- 可读性优先：清晰的结构比简洁更重要
- 性能优化：书写时考虑查询性能
- 一致性：团队内保持一致的书写风格
- 安全性：避免SQL注入等安全问题
- 可维护性：便于后续修改和扩展
- 标准化：遵循行业标准和最佳实践
SQL基础书写规范
命名规范
数据库对象命名
| 对象类型 | 命名规则 | 示例 |
|---|---|---|
| 数据库 | 小写字母，下划线分隔，简短 | user_center |
| 表 | 小写字母，下划线分隔，单数形式 | user |
| 视图 | 前缀v_，小写字母，下划线分隔 | v_active_user |
| 索引 | 前缀idx_或uk_，小写字母，下划线分隔 | idx_username，uk_email |
| 存储过程 | 前缀proc_，小写字母，下划线分隔 | proc_generate_report |
| 函数 | 前缀func_，小写字母，下划线分隔 | func_calculate_score |
| 触发器 | 前缀trig_，小写字母，下划线分隔 | trig_update_user |
| 临时表 | 前缀tmp_，小写字母，下划线分隔 | tmp_order_statistics |
字段命名
| 规则 | 示例 |
|---|---|
| 小写字母，下划线分隔 | user_id，created_at |
| 避免使用保留字 | 不使用select，from，where等 |
| 语义明确 | 不使用col1，data等模糊命名 |
| 保持一致性 | 相同含义的字段在不同表中使用相同名称 |
| 使用标准后缀 | _id（主键/外键），_at（时间），_flag（布尔值），_count（计数） |
格式规范
大小写规范
| 元素 | 规范 | 示例 |
|---|---|---|
| SQL关键字 | 大写 | SELECT，FROM，WHERE |
| 数据库对象 | 小写 | users，user_id |
| 函数名 | 大写 | SUM()，COUNT()，DATE_FORMAT() |
| 变量 | 小写 | @user_id |
| 常量 | 大写 | 'ACTIVE'，100 |
缩进和换行
SELECT语句格式：
sqlSELECT u.user_id, u.username, u.email, o.order_count, o.total_amount FROM users u LEFT JOIN ( SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total_amount FROM orders GROUP BY user_id ) o ON u.user_id = o.user_id WHERE u.status = 'ACTIVE' AND u.created_at >= '2023-01-01' AND u.age > 18 ORDER BY u.created_at DESC LIMIT 10 OFFSET 20;UPDATE语句格式：
sqlUPDATE users SET status = 'INACTIVE', updated_at = NOW(), last_login = NULL WHERE user_id = 123 AND status = 'ACTIVE';INSERT语句格式：
sqlINSERT INTO users (username, email, status, created_at) VALUES ('john_doe', 'john@example.com', 'ACTIVE', NOW()), ('jane_smith', 'jane@example.com', 'ACTIVE', NOW());DELETE语句格式：
sqlDELETE FROM users WHERE user_id IN (1, 2, 3) AND created_at < '2022-01-01';
空格和对齐
逗号后加空格：
sql-- 正确 SELECT user_id, username, email FROM users; -- 错误 SELECT user_id,username,email FROM users;操作符两侧加空格：
sql-- 正确 SELECT * FROM users WHERE age > 18; -- 错误 SELECT * FROM users WHERE age>18;括号内侧不加空格：
sql-- 正确 SELECT COUNT(*) FROM users WHERE status IN ('ACTIVE', 'PENDING'); -- 错误 SELECT COUNT( * ) FROM users WHERE status IN ( 'ACTIVE' , 'PENDING' );关键字对齐：
sql-- 正确 SELECT column1, column2 FROM table1 WHERE condition1 AND condition2 ORDER BY column1; -- 错误 SELECT column1, column2 FROM table1 WHERE condition1 AND condition2 ORDER BY column1;
注释规范
注释风格
单行注释：使用
--（注意空格），用于单行注释sql-- 这是单行注释 SELECT * FROM users; -- 查询所有用户多行注释：使用
/* */，用于多行注释或复杂注释sql/* * 这是多行注释 * 用于详细说明SQL的用途 * 和实现逻辑 */ SELECT * FROM users;
注释的使用场景
| 场景 | 示例 |
|---|---|
| 复杂查询 | 解释查询逻辑和意图 |
| 特殊处理 | 说明为什么使用特定的实现方式 |
| 性能优化 | 记录优化的原因和效果 |
| 业务规则 | 说明SQL实现的业务规则 |
| 临时修改 | 标记临时修改，便于后续恢复 |
SQL性能书写规范
查询优化规范
SELECT语句优化
**避免SELECT ***：
sql-- 正确：只查询需要的字段 SELECT user_id, username, email FROM users; -- 错误：查询所有字段，包括不需要的 SELECT * FROM users;使用LIMIT限制结果集：
sql-- 正确：限制返回行数 SELECT user_id, username FROM users LIMIT 10; -- 错误：不限制返回行数，可能导致性能问题 SELECT user_id, username FROM users;使用JOIN代替子查询：
sql-- 正确：使用JOIN SELECT u.user_id, u.username, o.order_id FROM users u JOIN orders o ON u.user_id = o.user_id; -- 错误：使用子查询，性能较差 SELECT user_id, username, (SELECT order_id FROM orders WHERE user_id = u.user_id LIMIT 1) AS order_id FROM users u;使用EXISTS代替IN：
sql-- 正确：使用EXISTS SELECT user_id, username FROM users u WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.user_id = u.user_id ); -- 错误：使用IN，当orders表大时性能差 SELECT user_id, username FROM users WHERE user_id IN (SELECT user_id FROM orders);使用UNION ALL代替UNION：
sql-- 正确：使用UNION ALL，不需要去重 SELECT user_id FROM users WHERE status = 'ACTIVE' UNION ALL SELECT user_id FROM users WHERE age > 60; -- 错误：使用UNION，需要去重，性能差 SELECT user_id FROM users WHERE status = 'ACTIVE' UNION SELECT user_id FROM users WHERE age > 60;
WHERE子句优化
避免在WHERE子句中使用函数：
sql-- 正确：直接比较 SELECT * FROM users WHERE created_at >= '2023-01-01'; -- 错误：在字段上使用函数，无法使用索引 SELECT * FROM users WHERE DATE(created_at) = '2023-01-01';避免使用!=或<>：
sql-- 正确：使用范围查询 SELECT * FROM users WHERE status = 'ACTIVE'; -- 错误：使用!=，无法使用索引 SELECT * FROM users WHERE status != 'INACTIVE';避免使用IS NULL/IS NOT NULL：
sql-- 正确：使用默认值代替NULL SELECT * FROM users WHERE status = 'INACTIVE'; -- 错误：使用IS NULL，索引效率低 SELECT * FROM users WHERE status IS NULL;使用BETWEEN代替>和<：
sql-- 正确：使用BETWEEN SELECT * FROM orders WHERE amount BETWEEN 100 AND 200; -- 错误：使用>和<，可读性差 SELECT * FROM orders WHERE amount > 100 AND amount < 200;使用IN代替多个OR：
sql-- 正确：使用IN SELECT * FROM users WHERE user_id IN (1, 2, 3, 4, 5); -- 错误：使用多个OR，性能差 SELECT * FROM users WHERE user_id = 1 OR user_id = 2 OR user_id = 3 OR user_id = 4 OR user_id = 5;合理使用LIKE：
sql-- 正确：前缀匹配，可以使用索引 SELECT * FROM users WHERE username LIKE 'admin%'; -- 错误：后缀匹配，无法使用索引 SELECT * FROM users WHERE username LIKE '%admin'; -- 错误：中间匹配，无法使用索引 SELECT * FROM users WHERE username LIKE '%admin%';
JOIN操作优化
JOIN顺序：将小表放在前面，大表放在后面
sql-- 正确：小表users在前，大表orders在后 SELECT u.username, o.order_id FROM users u JOIN orders o ON u.user_id = o.user_id;JOIN条件：使用索引字段作为JOIN条件
sql-- 正确：使用索引字段user_id作为JOIN条件 SELECT u.username, o.order_id FROM users u JOIN orders o ON u.user_id = o.user_id;避免笛卡尔积：确保JOIN条件完整
sql-- 正确：有完整的JOIN条件 SELECT u.username, o.order_id FROM users u JOIN orders o ON u.user_id = o.user_id; -- 错误：缺少JOIN条件，导致笛卡尔积 SELECT u.username, o.order_id FROM users u, orders o;使用STRAIGHT_JOIN：强制MySQL按照指定顺序JOIN表
sql-- 当优化器选择的JOIN顺序不合理时使用 SELECT STRAIGHT_JOIN u.username, o.order_id FROM users u JOIN orders o ON u.user_id = o.user_id;
插入、更新、删除优化
INSERT语句优化
使用批量插入：
sql-- 正确：批量插入，减少网络开销 INSERT INTO users (username, email) VALUES ('user1', 'user1@example.com'), ('user2', 'user2@example.com'), ('user3', 'user3@example.com'); -- 错误：单条插入，性能差 INSERT INTO users (username, email) VALUES ('user1', 'user1@example.com'); INSERT INTO users (username, email) VALUES ('user2', 'user2@example.com'); INSERT INTO users (username, email) VALUES ('user3', 'user3@example.com');指定字段名：
sql-- 正确：指定字段名，提高可读性和兼容性 INSERT INTO users (username, email) VALUES ('user1', 'user1@example.com'); -- 错误：不指定字段名，依赖表结构，容易出错 INSERT INTO users VALUES ('user1', 'user1@example.com');使用INSERT IGNORE或ON DUPLICATE KEY UPDATE：
sql-- 忽略重复键错误 INSERT IGNORE INTO users (username, email) VALUES ('user1', 'user1@example.com'); -- 遇到重复键时更新 INSERT INTO users (username, email) VALUES ('user1', 'user1@example.com') ON DUPLICATE KEY UPDATE email = VALUES(email);
UPDATE语句优化
使用WHERE条件：
sql-- 正确：使用WHERE条件，只更新需要的行 UPDATE users SET status = 'INACTIVE' WHERE user_id = 123; -- 错误：没有WHERE条件，更新所有行 UPDATE users SET status = 'INACTIVE';更新必要的字段：
sql-- 正确：只更新需要的字段 UPDATE users SET last_login = NOW() WHERE user_id = 123; -- 错误：更新所有字段，包括不需要的 UPDATE users SET username = 'new_name', email = 'new_email', last_login = NOW() WHERE user_id = 123;避免更新主键：
sql-- 错误：更新主键，会导致索引重建 UPDATE users SET user_id = 456 WHERE user_id = 123;
DELETE语句优化
使用WHERE条件：
sql-- 正确：使用WHERE条件，只删除需要的行 DELETE FROM users WHERE user_id = 123; -- 错误：没有WHERE条件，删除所有行 DELETE FROM users;使用LIMIT限制删除数量：
sql-- 正确：限制删除数量，避免长时间锁表 DELETE FROM users WHERE status = 'INACTIVE' LIMIT 1000; -- 错误：不限制删除数量，可能导致锁表 DELETE FROM users WHERE status = 'INACTIVE';使用TRUNCATE代替DELETE：
sql-- 正确：清空表，性能更好 TRUNCATE TABLE users; -- 错误：删除所有行，性能差 DELETE FROM users;
事务和锁优化
事务优化
保持事务短小：减少事务持有锁的时间
sql-- 正确：事务只包含必要的操作 START TRANSACTION; UPDATE users SET balance = balance - 100 WHERE user_id = 1; UPDATE orders SET status = 'PAID' WHERE order_id = 100; COMMIT; -- 错误：事务包含不必要的操作 START TRANSACTION; UPDATE users SET balance = balance - 100 WHERE user_id = 1; -- 执行耗时操作 DO SLEEP(10); UPDATE orders SET status = 'PAID' WHERE order_id = 100; COMMIT;避免在事务中执行DDL操作：DDL操作会隐式提交事务
sql-- 错误：事务中执行DDL操作 START TRANSACTION; UPDATE users SET balance = balance - 100 WHERE user_id = 1; ALTER TABLE orders ADD COLUMN new_column INT; -- 隐式提交事务 UPDATE orders SET status = 'PAID' WHERE order_id = 100; COMMIT; -- 事务已经被隐式提交合理使用隔离级别：根据业务需求选择合适的隔离级别
sql-- 正确：根据业务需求选择隔离级别 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; START TRANSACTION; -- 事务操作 COMMIT;
锁优化
使用行级锁：避免使用表级锁
sql-- 正确：使用行级锁 SELECT * FROM users WHERE user_id = 1 FOR UPDATE; -- 错误：使用表级锁 LOCK TABLES users WRITE; SELECT * FROM users WHERE user_id = 1; UNLOCK TABLES;避免锁定不必要的数据：只锁定需要的行
sql-- 正确：只锁定需要的行 SELECT * FROM users WHERE user_id IN (1, 2, 3) FOR UPDATE; -- 错误：锁定所有行 SELECT * FROM users FOR UPDATE;使用共享锁代替排他锁：如果只需要读取数据
sql-- 正确：使用共享锁 SELECT * FROM users WHERE user_id = 1 LOCK IN SHARE MODE; -- 错误：使用排他锁 SELECT * FROM users WHERE user_id = 1 FOR UPDATE;
SQL安全书写规范
防止SQL注入
使用参数化查询：
sql-- 正确：使用参数化查询 PREPARE stmt FROM 'SELECT * FROM users WHERE username = ?'; SET @username = 'admin'; EXECUTE stmt USING @username; DEALLOCATE PREPARE stmt; -- 错误：直接拼接SQL，存在SQL注入风险 SELECT * FROM users WHERE username = 'admin';过滤和转义输入：
sql-- 正确：转义特殊字符 SELECT * FROM users WHERE username = 'admin\' OR '1'='1'; -- 错误：不转义特殊字符，导致SQL注入 SELECT * FROM users WHERE username = 'admin' OR '1'='1';使用最小权限原则：
sql-- 正确：只授予必要的权限 GRANT SELECT, INSERT, UPDATE ON users TO 'app_user'@'%'; -- 错误：授予所有权限 GRANT ALL PRIVILEGES ON *.* TO 'app_user'@'%';
防止敏感信息泄露
避免在日志中记录敏感信息：
sql-- 正确：不记录密码等敏感信息 UPDATE users SET password = 'new_password' WHERE user_id = 1; -- 错误：在注释中记录敏感信息 UPDATE users SET password = 'new_password' WHERE user_id = 1; -- 密码：new_password使用加密函数处理敏感数据：
sql-- 正确：使用加密函数 INSERT INTO users (username, password) VALUES ('admin', PASSWORD('password')); -- 错误：明文存储密码 INSERT INTO users (username, password) VALUES ('admin', 'password');限制返回的敏感数据：
sql-- 正确：只返回需要的字段，不返回敏感信息 SELECT user_id, username, email FROM users; -- 错误：返回所有字段，包括敏感信息 SELECT * FROM users;
SQL可维护性书写规范
模块化设计
使用视图封装复杂查询：
sql-- 创建视图封装复杂查询 CREATE VIEW v_active_user_orders AS SELECT u.user_id, u.username, COUNT(o.order_id) AS order_count, SUM(o.amount) AS total_amount FROM users u JOIN orders o ON u.user_id = o.user_id WHERE u.status = 'ACTIVE' GROUP BY u.user_id; -- 使用视图简化查询 SELECT * FROM v_active_user_orders WHERE order_count > 10;使用存储过程封装业务逻辑：
sql-- 创建存储过程封装业务逻辑 DELIMITER // CREATE PROCEDURE proc_update_user_status(IN p_user_id INT, IN p_status VARCHAR(20)) BEGIN UPDATE users SET status = p_status, updated_at = NOW() WHERE user_id = p_user_id; INSERT INTO user_status_history (user_id, status, changed_at) VALUES (p_user_id, p_status, NOW()); END // DELIMITER ; -- 使用存储过程简化调用 CALL proc_update_user_status(123, 'INACTIVE');
注释和文档
为复杂SQL添加注释：
sql/* * 统计活跃用户的订单信息 * 1. 筛选状态为ACTIVE的用户 * 2. 关联订单表统计订单数量和金额 * 3. 按用户ID分组 * 4. 只返回订单数量大于10的用户 */ SELECT u.user_id, u.username, COUNT(o.order_id) AS order_count, SUM(o.amount) AS total_amount FROM users u JOIN orders o ON u.user_id = o.user_id WHERE u.status = 'ACTIVE' GROUP BY u.user_id HAVING order_count > 10;记录SQL的业务用途：
sql-- 业务需求：统计每个用户的订单数量，用于用户等级评定 SELECT user_id, COUNT(*) AS order_count FROM orders GROUP BY user_id;记录优化历史：
sql-- 优化前：执行时间10秒，扫描100万行 -- 优化后：执行时间0.1秒，使用索引idx_user_id，扫描100行 SELECT user_id, username, email FROM users WHERE user_id IN (SELECT user_id FROM orders WHERE amount > 100);
测试和验证
编写测试用例：
sql-- 测试用例1：正常情况 SELECT * FROM users WHERE user_id = 1; -- 测试用例2：边界情况 SELECT * FROM users WHERE user_id = 0; -- 测试用例3：异常情况 SELECT * FROM users WHERE user_id = NULL;使用EXPLAIN分析执行计划：
sql-- 分析查询执行计划 EXPLAIN SELECT * FROM users WHERE username = 'admin'; -- 查看详细执行计划 EXPLAIN EXTENDED SELECT * FROM users WHERE username = 'admin'; SHOW WARNINGS;验证结果的正确性：
sql-- 验证查询结果是否符合预期 SELECT COUNT(*) FROM users WHERE status = 'ACTIVE'; -- 预期结果：100
常见SQL错误和规范示例
常见错误示例
不规范的命名：
sql-- 错误：使用大写表名 SELECT * FROM Users; -- 错误：使用保留字作为字段名 SELECT * FROM users WHERE select = 1;不良的格式：
sql-- 错误：没有缩进和换行 SELECT user_id,username,email FROM users WHERE status='ACTIVE' AND created_at>='2023-01-01' ORDER BY created_at DESC;性能问题：
sql-- 错误：使用SELECT * SELECT * FROM users; -- 错误：在WHERE子句中使用函数 SELECT * FROM users WHERE DATE(created_at) = '2023-01-01'; -- 错误：使用LIKE '%admin%' SELECT * FROM users WHERE username LIKE '%admin%';安全问题：
sql-- 错误：直接拼接SQL SELECT * FROM users WHERE username = 'admin' OR '1'='1'; -- 错误：明文存储密码 INSERT INTO users (username, password) VALUES ('admin', 'password');
规范示例
SELECT语句：
sqlSELECT u.user_id, u.username, u.email, COUNT(o.order_id) AS order_count, SUM(o.amount) AS total_amount FROM users u LEFT JOIN orders o ON u.user_id = o.user_id WHERE u.status = 'ACTIVE' AND u.created_at >= '2023-01-01' AND u.age > 18 GROUP BY u.user_id, u.username, u.email HAVING order_count > 5 ORDER BY total_amount DESC LIMIT 10 OFFSET 0;INSERT语句：
sqlINSERT INTO users ( username, email, password, status, created_at, updated_at ) VALUES ( 'john_doe', 'john@example.com', PASSWORD('secure_password'), 'ACTIVE', NOW(), NOW() );UPDATE语句：
sqlUPDATE users SET email = 'new_email@example.com', updated_at = NOW() WHERE user_id = 123 AND status = 'ACTIVE';DELETE语句：
sqlDELETE FROM users WHERE user_id = 123 AND status = 'INACTIVE' AND created_at < '2022-01-01';事务处理：
sqlSTART TRANSACTION; -- 1. 检查余额 SELECT balance INTO @balance FROM users WHERE user_id = 1 FOR UPDATE; -- 2. 检查余额是否足够 IF @balance >= 100 THEN -- 3. 扣除余额 UPDATE users SET balance = balance - 100 WHERE user_id = 1; -- 4. 创建订单 INSERT INTO orders (user_id, amount, status, created_at) VALUES (1, 100, 'PAID', NOW()); -- 5. 提交事务 COMMIT; ELSE -- 6. 回滚事务 ROLLBACK; SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient balance'; END IF;
SQL书写规范检查表
基础规范检查
- [ ] 命名是否符合规范？
- [ ] 格式是否规范（缩进、换行、大小写）？
- [ ] 是否添加了必要的注释？
- [ ] 是否避免了使用保留字？
- [ ] 是否使用了标准的后缀？
性能规范检查
- [ ] 是否避免了SELECT *？
- [ ] 是否使用了LIMIT限制结果集？
- [ ] WHERE子句是否使用了索引字段？
- [ ] 是否避免了在WHERE子句中使用函数？
- [ ] JOIN操作是否使用了索引字段？
- [ ] 是否避免了笛卡尔积？
- [ ] 事务是否保持短小？
安全规范检查
- [ ] 是否使用了参数化查询？
- [ ] 是否过滤和转义了输入？
- [ ] 是否使用了最小权限原则？
- [ ] 是否避免了在日志中记录敏感信息？
- [ ] 是否使用了加密函数处理敏感数据？
可维护性检查
- [ ] 是否使用了视图或存储过程封装复杂逻辑？
- [ ] 是否添加了详细的注释和文档？
- [ ] 是否编写了测试用例？
- [ ] 是否使用了EXPLAIN分析执行计划？
- [ ] 是否验证了结果的正确性？
总结
SQL书写规范是数据库开发和运维的重要组成部分，良好的SQL书写规范可以提高SQL语句的可读性、性能、安全性和可维护性。
作为数据库开发人员或DBA，应该：
- 遵循统一的SQL书写规范
- 不断学习和更新SQL优化知识
- 定期审查和优化现有SQL
- 分享和推广良好的SQL书写习惯
- 结合业务需求和系统特点调整规范
通过严格遵循SQL书写规范，可以减少SQL错误，提高数据库性能，增强系统的可靠性和可维护性，为业务提供更好的支持。