MySQL数据库SQL使用规范

DML语句规范

【推荐】写完SQL先explain查看执行计划

日常开发写 SQL 的时候，尽量养成这个好习惯呀：写完 SQL 后，用 explain 分析一下，尤其注意走不走索引。

【强制】用IN代替OR

SQL语句中IN包含的值不应过多，应少于1000个

【强制】禁止隐式转换

数值类型禁止加引号；字符串类型必须加引号，必要时推荐用JOIN代替子查询。

【强制】Sql中存在嵌套查询时，所有内部嵌套语句中禁止出现排序语句

【强制】Where 语句后的条件字段，不允许对条件加函数

如 data（datatime）=‘2018-10-19’

【强制】用UNION ALL代替UNION

• Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
• Union All：对两个结果集进行并集操作，包括重复行，不进行排序；

【强制】SELECT只获取必要的字段，禁止使用SELECT *

【强制】SQL中避免出现now()、rand()、sysdate()、current_user()等不确定结果的函数。

【强制】INSERT语句必须指定字段列表，禁止使用 INSERT INTO TABLE()。

【强制】禁止单条SQL语句同时更新多个表

【强制】禁止出现没有WHERE条件的SQL

【强制】重要SQL必须被索引

update、delete的where条件列、order by、group by、distinct字段、多表join 字段。

【强制】禁止使用%前导查询

例如：like “%abc”，无法利用到索引。

【强制】禁止使用负向查询

例如，not in、!=、not like。

【强制】禁止使用order by rand()，不能在不同数据类型的字段上进行比较，避免字段类型转换损失性能

【强制】SQL语句在程序中传入的参数值类型必须与字段在数据库中的类型相同

【强制】不能使用SELECT … FOR UPDATE语法，它会扩大意向锁范围，影响数据库的并发效率

【强制】操作 delete 或者 update 语句，加个 limit

delete语句添加limit的好处：

1. 降低写错 SQL 的代价，你在命令行执行这个 SQL 的时候，如果不加 limit，执行的时候一个不小心手抖，可能数据全删掉了，如果删错了呢？加了 limit 200，就不一样了。删错也只是丢失 200 条数据，可以通过 binlog 日志快速恢复的。
2. SQL 效率很可能更高，你在 SQL 行中，加了 limit 1，如果第一条就命中目标，并返回， 没有 limit 的话，还会继续执行扫描表。
3. 避免了长事务，delete 执行时，如果查询字段加了索引，MySQL会将所有相关的行加写锁和间隙锁，所有执行相关行会被锁住，如果删除数量大，会直接影响相关业务无法使用。
4. 数据量大的话，容易把 CPU 打满，如果你删除数据量很大时，不加 limit 限制一下记录数，容易把 CPU 打满，导致越删越慢的。

【推荐】SQL书写格式，关键字大小保持一致，使用缩进

【推荐】INSERT语句标明对应的字段名称

【推荐】写完 SQL 语句，检查 where,order by,group by后面的列，多表关联的列是否已加索引，优先考虑组合索引

【强制】修改或删除重要数据前，要先备份，先备份，先备份

【强制】修改或者删除SQL，先写WHERE查一下，确认后再补充 delete 或 update

尤其在操作生产的数据时，遇到修改或者删除的 SQL，先加个 where 查询一下，确认 OK 之后，再执行 update 或者 delete 操作。

【强制】SQL修改数据，养成begin + commit 事务的习惯；

如，begin;update account set balance = 1000000 where name = '业余草';commit;

【强制】如果修改更新数据过多，考虑批量进行

理由如下：

• 大批量操作会会造成主从延迟。
• 操作会产生大事务，阻塞。
• 大批量操作，数据量过大，会把CPU打满。

DDL语句规范

【强制】建表语句中不能带有drop table if exist语句

【推荐】数据库和表的字符集统一使用UTF8

统一使用 UTF8 编码的好处：

• 可以避免乱码问题
• 可以避免，不同字符集比较转换，导致的索引失效问题 如果是存储表情的，可以考虑 utf8mb4。

【推荐】设计表的时候，所有表和字段都添加相应的注释

【推荐】设计数据库表的时候，加上三个字段：主键，create_time，update_time

1. 主键一定要加上的，没有主键的表是没有灵魂的。
2. 创建时间和更新时间的话，还是建议加上吧，详细审计、跟踪记录，都是有用的。

【推荐】尽量把所有列定义为 NOT NULL

• NOT NULL 列更节省空间，NULL 列需要一个额外字节作为判断是否为 NULL 的标志位。
• NULL 列需要注意空指针问题，NULL 列在计算和比较的时候，需要注意空指针问题。

【推荐】所有表必须使用Innodb存储引擎

Innodb 支持事务，支持行级锁，更好的恢复性，高并发下性能更好，所以呢，没有特殊要求（即 Innodb 无法满足的功能如：列存储，存储空间数据等）的情况下，所有表必须使用 Innodb 存储引擎。

【推荐】尽量使用 varchar 代替 char

变长字段存储空间小，可以节省存储空间。

【推荐】如果修改字段含义或对字段表示的状态追加时，需要及时更新字段注释。

【推荐】索引命名要规范，主键索引名为 pk_ 字段名；唯一索引名为 uk _字段名 ； 普通索引名则为 idx _字段名。