>记录生活, 工作的点点滴滴...

MySQL条件or/and默认执行方式

今天突然被问到了一个问题, 在SQL表达式的where条件中, 如果 a or b and c, 执行逻辑是怎样的呢?

虽然经常使用and/or, 或者也一起用, 但为了使意义明确, 一般都会用括号括起来, 不用括号的执行逻辑倒竟真的没有注意过...

于是, 就想探索一下...

一. 虚构数据集

select 0 a, 0 b, 0 c 
union all
select 0, 0, 1
union all
select 0,1,0
union all
select 0,1,1
union all
select 1,0,0
union all
select 1,0,1
union all
select 1,1,0
union all
select 1,1,1

a b c
0 0 0
0 0 1
0 1 0
0 1 1
1 0 0
1 0 1
1 1 0
1 1 1

select a*power(2,2)+b*power(2,1)+c*power(2,0) as id,v.* from
(
select 0 a, 0 b, 0 c
union all
select 0, 0, 1
union all
select 0,1,0
union all
select 0,1,1
union all
select 1,0,0
union all
select 1,0,1
union all
select 1,1,0
union all
select 1,1,1
)v
where a=0 or b=0 and c=0
;
-- 查询结果为:
id a b c
0 0 0 0
1 0 0 1
2 0 1 0
3 0 1 1
4 1 0 0
--调整一下a, b的顺序 
select a*power(2,2)+b*power(2,1)+c*power(2,0) as id,v.* from
(
select 0 a, 0 b, 0 c
union all
select 0, 0, 1
union all
select 0,1,0
union all
select 0,1,1
union all
select 1,0,0
union all
select 1,0,1
union all
select 1,1,0
union all
select 1,1,1
)v
where b=0 or a=0 and c=0
;
-- 查询结果为:
id a b c
0 0 0 0
1 0 0 1
2 0 1 0
4 1 0 0
5 1 0 1

经分析, 可以发现, and的优先级是高于or的, 且是右先运算, 以where a=0 or b=0 and c=0为例,

先运行 b=0 and c=0 条件, 取出结果,

然后运行a=0条件, 取出结果, 并合并结果.

故 where a=0 or b=0 and c=0 与 where a=0 or (b=0 and c=0)是相等逻辑.

同理, a=0 or b=0 and c=0 or d=0 应与 a=0 or (b=0 and c=0) or d=0 等价,

a=0 or b=0 and c=0 and d=0 应与 a=0 or (b=0 and (c=0 and d=0)) 等价,

a=0 and b=0 or c=0 and d=0 与 (a=0 and b=0) or (c=0 and d=0)等价,

...

至此, 终于搞明白啦...


其实,在mysql官方文档里也早有说明:

AND and OR may be intermixed, although AND has higher precedence than OR. If you use both operators, it is a good idea to use parentheses to indicate explicitly how conditions should be grouped

mysql官方文档 

发表于:2019-09-26 17:39:24浏览(521) 评论(0) MySQL