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', 反义词则需要向两边取值了即: '= 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反转,这是其正常的执行引擎流程。但具体是否如此,暂不得而知,待以后有机会再研究研究。

  通过本文的介绍,在以后的工作中,咱们也可以多了一个选择了,虽然少见,但不排除遇见。希望能为大家多一点参考。

  

文章来源于互联网:sql语法巧用之not取反

THE END
分享
二维码