PawSQL更新 | 新增9个SQL正确性审核规则

PawSQL最新版本针对DML和DQL新增了审核和重写优化规则共计33个，整体的规则数目达到了83个，覆盖了正确性，安全性、可维护性、性能四个方面的SQL质量问题，并提供了优化建议，已经形成比较完善的针对数据操作的SQL质量审查体系。本文介绍其中新增的9个正确性相关的审核规则。

正确性规则

1. 避免常量字符串开头或结尾包含空格

规则描述

一个字符串开头和结尾的空格通常来讲都没有意义，在SQL中出现的类似常量通常是由于开发人员误输入导致的，但是它可能会影响SQL查询的结果，因此需要特别关注。

默认预警级别

• 警告

触发条件

• 常量字符串开头或结尾包含空格
• 常量字符串出现在判断条件中

2. 避免COUNT DISTINCT多个可空列

规则描述

当你使用COUNT(DISTINCT) 进行多列的计算时，它的计算结果可能和你预想的不同。COUNT(DISTINCT col) 计算该列除 NULL 之外的不重复行数，而COUNT(DISTINCT col, col2)则会排除掉任何一列为NULL的行。

譬如对于如下的查询,对列a和列组合（a，b）的统计不同值的个数，

select count(distinct t.a) a_cnt, count(distinct t.a,t.b) a_b_cnt 
from (values 
      row(1,2),
      row(3,null)) as t(a,b);

其返回对于列a为2，而对于列组合（a，b）则为1，这可能不符合您的预想，需特别关注。

默认预警级别

• 提示

触发条件

• SQL中存在COUNT DISTINCT函数
• COUNT的参数包括二个或两个以上的表达式
• 参数至少有一个是可以为空的

3. 同表同字段比较

规则描述

同表同字段进行比较一般可以重写为更为简洁的表达式，一般都是由于错误导致的不合理的SQL语句。譬如：

默认预警级别

• 警告

触发条件

• 同表同字段比较

4. INSERT...VALUES列和值数量一致

规则描述

INSERT...VALUES语句是根据列和值的顺序建立对应关系的，如果二者数量不一致，说明语句不正确。

默认预警级别

• 禁止

触发条件

• INSERT...VALUES的列显式的指定
• INSERT...VALUES列和值数量一致

5. 禁止对非整形常量进行GROUP BY

规则描述

对非整数常量进行分组，其实没有意义；且在PostgreSQL里会语法报错，但是在MySQL里却不会，需特别关注。

SELECT l_orderkey, sum(l_quantity) 
FROM lineitem
GROUP BY '1';

默认预警级别

• 禁止

触发条件

• 分组字段为非整型的常量

6. 禁止对非整形常量进行ORDER BY

规则描述

对非整数常量进行排序，其实没有意义；且在PostgreSQL里会语法报错，但是在MySQL里却不会，需特别关注。

SELECT l_orderkey 
FROM lineitem
ORDER BY '1';

默认预警级别

• 警告

触发条件

• 排序字段为非整型的常量

7. INSERT语句必须包含主键字段

规则描述

对于没有自增主键的表，即使其有默认值，插入数据时应该指定主键的值。

默认预警级别

• 警告

触发条件

• 表有主键 主键非自增列

8. INSERT语句禁止使用SYSDATE函数

规则描述

当SYSDATE()函数在基于STATEMENT模式的主从环境下可能造成数据的不一致，因为语句在主库中执行到日志传递到备库，存在时间差，到备库执行的时候就会变成不同的时间值。

默认预警级别

• 警告

触发条件

• Insert语句中使用SYSDATE函数作为取值
• MySQL数据库。

9. 避免使用没有通配符的 LIKE 查询

规则描述

不包含通配符的LIKE 查询逻辑上与等值查询相同，建议使用等值查询替代。而且不包含通配符的LIKE 查询逻辑通常是由于开发者错误导致的，可能不符合其期望的业务逻辑实现。需要开发这特别关注。

默认预警级别

• 警告

触发条件

• SQL中存在LIKE查询条件
• LIKE的右操作数中不存在'%'或'_'通配符

关于PawSQL

PawSQL专注数据库性能优化的自动化和智能化，支持MySQL，PostgreSQL，Opengauss等，提供的SQL优化产品包括

• PawSQL Cloud，在线自动化SQL优化工具，支持SQL审查，智能查询重写、基于代价的索引推荐，适用于数据库管理员及数据应用开发人员，
• PawSQL Advisor，IntelliJ 插件, 适用于数据应用开发人员，可以IDEA/DataGrip应用市场通过名称搜索“PawSQL Advisor”安装。
• PawSQL Engine, 是PawSQL系列产品的后端优化引擎，可以以docker镜像的方式独立安装部署，并通过http/json的接口提供SQL优化服务。
• PawSQL Ora2pg/PawsQL Ora2op，Oracle语法的SQL应用转换为PostgreSQL和openGauss语法的工具。