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

查询正常