MySQL数据库设计规范（二）：SQL语句

发表于 2020-11-07 分类于项目管理

本文为我们项目中所采用的MySQL数据库设计规范。

计划分为三篇文章，前一篇为数据库的表结构设计，本篇为SQL语句的书写，第三篇为PDM文档规范。

SQL书写规则

SQL语句应尽可能简单，减小响应时间。
使用查询条件时，值与字段数据类型必须保持一致，例如id为INT类型，查询条件则不应该写成id='1'。
需要JOIN时，关联的字段的数据类型必须保持一致。
避免使用NOT IN。该语句会将NULL值查出来。
避免使用NOT IN、NOT LIKE、<>、!=，否则查询时不走索引，影响性能。
避免使用OR，尽量改写成IN或UNION ALL。
IN条件的数据项要控制在1000以内。如可以，建议用EXISTS代替IN。
禁止列运算，例如WHERE age+1>20会导致表扫描，使索引失效，应改写为WHERE age>19。
禁止使用左模糊与全模糊（即LIKE '%TEXT'、LIKE '%TEXT%'）。如有需要，使用搜索引擎处理。
禁止SELECT *，必须指明具体字段。SELECT COUNT(*)除外。
尽量使用LIMIT限定查询结果数量。
尽量不要在SQL语句中进行计算，而是放入调用参数中，以利用查询缓存，例如WHERE date<CURDATE()改为WHERE date<?然后在Java层传入当前时间。
书写查询语句后，应采用EXPLAIN等方式进行性能分析。

注意：COUNT(列名)不会统计NULL值，而COUNT(1)与COUNT(*)会统计NULL值。
注意：如果表中没有数据，或者全部为NULL，SUM(列名)、AVG(列名)、MIN(列名)、MAX(列名)等统计函数会返回NULL，而COUNT(列名)只会返回0，不会返回NULL。
如字段可空，应使用ISNULL()判断是否为NULL，且不能用<>、NOT IN判断取值。
必须使用#{}、#param#配置参数，禁止使用${}配置参数，以避免SQL注入。
严禁人工拼接SQL查询参数，一是容易拼接不全导致语法错误，二是容易产生SQL注入，三是无法利用查询缓存。
字符计数使用CHARACTER_LENGTH。注意一个汉字不是2字节，不一定是3字节。
在多表关联查询时，每个字段应总是指定表的名称。也可使用t1、t2等别名简化书写。