SQL中 AND 和OR 优先级别的问题及解决方案

本文最后更新于2023.05.06-10:53,某些文章具有时效性,若有错误或已失效,请在下方留言或联系涛哥

前言

起因是这样,今天在做项目时做了一个多字段的短句LIKE搜索语句。可是不管输入什么字段都能查到,SQL语句如下

SQL

SELECT
	AfficheID,
	NtcTitle,
	NtcContent,
	SortCode,
	SortName,
	IssueTime,
	nlogid 
FROM
	sAffiche
	LEFT JOIN tm_sys_notice_log ON AfficheID = affId 
	AND isDelFlag = 0 
	AND UserId = '1619' 
WHERE
	ObjRole LIKE CONCAT ( '%', '920', '%' ) 
	AND concat ( NtcTitle, NtcContent ) LIKE '%dsfdfsdf%' 
	AND ( SortCode = '21' AND SortName = '紧急' ) 
	OR ( SortCode = '31' AND SortName = '专向' ) 
ORDER BY
	IssueTime DESC

 

然后后面发现AND 和 OR 的优先级别是AND优先级别要高于OR ,就好比

select * from 字段1=1 or 字段1=2 and 字段2=3

等价于

select * from 字段1=1 or (字段1=2 and 字段2=3 )

正确写法

select * from (字段1=1 or 字段1=2) and 字段2=3

所以语句应该修改为

SELECT
	AfficheID,
	NtcTitle,
	NtcContent,
	SortCode,
	SortName,
	IssueTime,
	nlogid 
FROM
	sAffiche
	LEFT JOIN tm_sys_notice_log ON AfficheID = affId 
	AND isDelFlag = 0 
	AND UserId = '1619' 
WHERE
	ObjRole LIKE CONCAT ( '%', '920', '%' ) 
	AND concat ( NtcTitle, NtcContent ) LIKE '%dsfdfsdf%' 
	AND ( ( SortCode = '21' AND SortName = '紧急' ) OR ( SortCode = '31' AND SortName = '专向' ) ) 
ORDER BY
	IssueTime DESC

查询正常

阅读剩余
THE END