mysql条件 or_MySQL条件or/and默认执行方式

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

abc

000

001

010

011

100

101

110

111select 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

;

-- 查询结果为:

idabc

0000

1001

2010

3011

4100--调整一下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

;

-- 查询结果为:

idabc

0000

1001

2010

4100

5101

经分析, 可以发现, 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

发表于:2019-09-26 17:39:24浏览(576)

评论(0)

MySQL