sql语法巧用之not取反
数据库的重要性和通用性都不用说了,什么sql的通用性,sql优化之类的也不必说了,咱们今天来聊聊另一个有意思的话题:如何取一个筛选的反面案例。
1. 举几个正反案例的例子
为了让大家理解我们的假设场景,什么叫做正反案例?比如:
0. 正向案例为:取出年龄为空的用户,那么反面案例为:取出年龄不为空的用户;
1. 正向案例为:取出年龄大于25的用户,那么反面案例则为:取出年龄小于等于25的用户;
2. 正向案例为:取出姓名为男的用户,那么反面案例则为:取出性别不等于男的用户;
3. 正向案例为:取出薪资在1000-2000之间的用户,那么反面案例为:取出薪资小于1000或者大于2000的用户;
4. 正向案例为:取出年龄大于25且性别为男的用户,那么反面案例为:取出年龄小于25或者性别不为男的用户;
5. 正向案例为:取出年龄大于25且为男性或者薪资大于2000的用户,那么反面案例为:取出年龄小于25或者性别不为男的用户且薪资小于等于2000的用户;
相信大家都理解了,其实就相当于取反义词。也就是说输入是一个正向规则,我们需要输出一个反向规则。当然一个前提是咱们使用sql语言。
从前到后,我们可以理解为一个实现难度的提升,比如第一个 'is null' 的反义词则是 'is not null',第二个 '>' 的反义词则是 '<='。这些简单的是单个规则的表述。
但到第4个案例,就涉及到区间了,相当于有组合词了,即 'between 1000 and 2000', 反义词则需要向两边取值了即: '<= 100 or >= 2000';
第6个则更复杂,涉及三个变量,即 'age > 25 and sex = '男' or salary > 2000', 反义词则需要考虑到优先级的问题了。
至于更复杂的咱们就不说了。
2. 正面硬刚反义词
通过以上案例,相信大家已经明白我在说什么了。没错,就是求反义词。具体应用场景是啥呢?举个例子,用户配置了一个基础规则,然后其他地方可以引用,正向引用,则是条件为真,反向引用则是条件为假。
不管怎么样,考题就是如何求解一个条件的反向表示?
正向解题思路是啥呢?首先,如果想要自行求反解,那么第一步就是必须要先理解正向表达的语义,即你至少得有分词、构建语法树、理解语义的过程。
这样做完之后,至少你可以做一些事了,就像前面几个简单的单条件配置,为空的反义词就是不为空,中间加个 not 就可以了,则可以直接套用固定反转即可。抽象点说就是,根据一个固定的规则映射字典,就可以找到反义词了。
但是,针对有多个条件表达的情况,则会复杂起来,先来看有两个条件连接为'且'的表达,那么求反就不能通过单个字典映射进行处理了。但仍然可以拆解为两个求反操作,即'条件1求反' 或者 '条件2求反'。
而针对两个条件连接为'或'的表达,则需要对单个未反,然后用'且'连接,即 '条件1求反' 且 '条件2求反'。
以上,仍然停留在比较简单的场景,即只有1个条件或者2个条件的情况下,而更多的是,可能3个、4个、10个甚至更多个,甚至还有'()'括号的场景,多层嵌套,这样的求反,其实就相当复杂了。但到底能不能实现求反呢?理论上可行的,实际上不管条件有多少个,在sql的表达中,都是一个个的bool表达式,然后使用'and'/'or' 连接,而且更重要的,不管有多少个'and'/'or', 最终总要一个个计算,所以我们只需要一直拆解条件表达式,直到它是一个原子表达式,然后再套用字典转换,就可以做到求反的效果了。当然了,这个实现应该还是一个很复杂的过程,而且不一定适用,咱们就只给出一些伪代码框架供参考了。
表达式求反函数(入参: 原始表达式) { 分词; 语法树构建; 语义解析构造优先级的bool表达式树; 复杂条件求反 -> 代入现有解析好的bool表达式; } 复杂条件求反(入参: bool表达式) { if 原子表达式: return 字典映射求反表达式; if 当前连接符是 'and': 复杂条件求反 -> 代入左边的bool表达式; 复杂条件求反 -> 代入右边的bool表达式; return 以上两个结果用'or'连接; if 当前连接符是 'or': 复杂条件求反 -> 代入左边的bool表达式; 复杂条件求反 -> 代入右边的bool表达式; return 以上两个结果用'and'连接; } 字典映射求反表达式(入参: bool表达式) { 为空 -> 不为空; 等于 -> 不等于; 大于 -> 小于等于; in -> not..in.. ... }
可以看出,按照这个框架,理论上实现反义词转换还是可行的。但是对于像优先级,括号,四则运算之类的处理,那应该是相当的复杂的。对于非专业搞数据库开发,或者编译器的同学而言,应该是非常之难的。具体咱也不知道,看你咯。
3. sql语法巧用
我们知道,一个sql的bool表达式,有true/false之分,正常情况下都是以 true 作为判断条件的。比如 is null 为true, 那么 is not null 就为false。 =1为true, 那么 !=false, in 为true, 那么 not in 就为false。between 为true, 那么 not between 就为false.
虽然情况很多,但是我们已经看到,sql中天然就有一个词代表了取反的意思。只是好像只有特定的场景下才可以使用not关键词。好像有点失望了。
为什么不试一试呢?比如 x=1 的反义词是否可以是 not x = 1 ? 为测试方便,我们直接使用内存数据库sqllite测试, https://www.sqlite.org/download.html 。参考下载链接: https://www.sqlite.org/2022/sqlite-tools-win32-x86-3390400.zip
接下来我们用两张表测试下。
-- 新建测试表1 create table test1 ( id int, name varchar(50), age int ) -- comment '用户基础信息表'; -- 新建测试表2 create table test2 ( uid int, salary double, company varchar(50) ) -- comment '用户工作信息表'; -- 插入测试数据 insert into test1 (id, name, age) values (1, 'zhangsan', 18); insert into test1 (id, name, age) values (2, 'lisi', 20); insert into test1 (id, name, age) values (3, 'wanger', 30); insert into test2 (uid, salary, company) values (1, 1000.1, 'axxx'); insert into test2 (uid, salary, company) values (2, 2000.1, 'bxxx'); insert into test2 (uid, salary, company) values (3, 3000.1, 'cxxx');
接下来我们用not语法和非not语法测试下。
sqlite> select * from test1 where name = 'zhangsan'; 1|zhangsan|18 sqlite> select * from test1 where name != 'zhangsan'; 2|lisi|20 3|wanger|30 sqlite> select * from test1 where not (name = 'zhangsan'); 2|lisi|20 3|wanger|30
看起来语法是支持的,而且两个语法的简单语句执行结果居然是一样的。接下来我们测试稍微复杂点的:
sqlite> select * from test1 where name = 'zhangsan' or name = 'lisi'; 1|zhangsan|18 2|lisi|20 sqlite> select * from test1 where name != 'zhangsan' and name != 'lisi'; 3|wanger|30 sqlite> select * from test1 where not( name = 'zhangsan' or name = 'lisi'); 3|wanger|30
看起来多个条件的连接not语法也是支持的,而且结果也是正确的呢。我们来测试一个三条件的语句:
sqlite> select * from test1 where name = 'zhangsan' or name = 'lisi' and age = 20; 1|zhangsan|18 2|lisi|20 sqlite> select * from test1 where name != 'zhangsan' and (name != 'lisi' or age != 20); 3|wanger|30 sqlite> select * from test1 where not (name = 'zhangsan' or name = 'lisi' and age = 20); 3|wanger|30 sqlite> select * from test1 where age > 20; 3|wanger|30 sqlite> select * from test1 where age <= 20; 1|zhangsan|18 2|lisi|20 sqlite> select * from test1 where not( age > 20 ); 1|zhangsan|18 2|lisi|20
好吧,看起来单表的操作并没有问题。会不会是因为单表简单的原因?我们试试多表join的:
sqlite> select t1.name,t1.age, t2.salary from test1 t1 left join test2 t2 on t1.id = t2.uid where t1.age >= 20 and t2.salary > 2000; lisi|20|2000.1 wanger|30|3000.1 sqlite> select t1.name,t1.age, t2.salary from test1 t1 left join test2 t2 on t1.id = t2.uid where t1.age < 20 or t2.salary <= 2000; zhangsan|18|1000.1 sqlite> select t1.name,t1.age, t2.salary from test1 t1 left join test2 t2 on t1.id = t2.uid where not (t1.age >= 20 and t2.salary > 2000); zhangsan|18|1000.1
是了,没问题,语法支持,结果正确。换成其他的sql类数据库做同样的测试,仍然一致。基本可以确定,not语法是可以覆盖结果取反的场景的。
4. not语法的底层原理
虽然not语法看起来没啥问题,但是在官方的介绍里,貌似并没有找到相应的章节描述,为什么呢?不得而知。
那么sql的not在底层是怎么实现的呢?两个思路吧:一是像咱们前面提到的进行反面条件转换,得到后再进行执行;二是直接计算的时候,先正向计算出结果,然后遇到not之后,当作一个运算符,直接将结果取反,从而决定结果是抛弃还是保留。
因为数据库底层都是是根据规则依次计算结果判定,所以最合适的方式是正向计算结果,然后遇到not进行true/false反转,这是其正常的执行引擎流程。但具体是否如此,暂不得而知,待以后有机会再研究研究。
通过本文的介绍,在以后的工作中,咱们也可以多了一个选择了,虽然少见,但不排除遇见。希望能为大家多一点参考。