下面的sql查询同一张表的数据,差异仅在where条件不同,但是同样是active =true的记录在返回的结果中,排序顺序不同,这是为什么
1 | SELECT id,name FROM `tag` WHERE corp_id =4 ORDER BY active DESC LIMIT 10; |
这两条SQL语句返回的数据顺序不一致的原因在于第一条SQL语句中 active
列可能存在除了 true
(或 1) 以外的其他值,比如 false
(或 0) 或者 NULL
。
让我们更详细地分析一下:
SQL 1:
1 | SELECT id, name FROM `tag` WHERE corp_id = 4 ORDER BY active DESC LIMIT 10; |
- WHERE
corp_id
= 4: 首先过滤出corp_id
为 4 的所有行。 - ORDER BY
active
DESC: 然后按照active
列降序排序。 这意味着:true
(或 1) 的行会排在前面。false
(或 0) 的行会排在true
之后。NULL
的行会排在false
之前,但具体位置取决于数据库实现。 MySQL会默认把 NULL 值放在最前面
- LIMIT 10: 最后只返回排序后的前10行。
SQL 2:
1 | SELECT id, name FROM `tag` WHERE (corp_id = 4) AND active = true ORDER BY active DESC LIMIT 10; |
- WHERE
corp_id
= 4 ANDactive
= true: 首先过滤出corp_id
为 4 并且active
为true
的所有行。 - ORDER BY
active
DESC: 然后按照active
列降序排序。 由于WHERE
子句已经确保了所有行的active
都是true
,所以这个排序实际上是多余的,因为所有行的active
值都相同。 - LIMIT 10: 最后只返回排序后的前10行。
关键区别在于WHERE子句的过滤条件:
- 第一条SQL语句包含了所有
corp_id = 4
的行,然后根据active
进行排序,NULL值可能在最前面。 - 第二条SQL语句只包含了
corp_id = 4
并且active = true
的行。 排序没有实际作用。
举例说明:
假设你的 tag
表有以下数据(简化):
id | name | corp_id | active |
---|---|---|---|
1 | A | 4 | true |
2 | B | 4 | false |
3 | C | 4 | NULL |
4 | D | 4 | true |
5 | E | 4 | false |
6 | F | 4 | NULL |
7 | G | 4 | true |
8 | H | 4 | false |
9 | I | 4 | NULL |
10 | J | 4 | true |
11 | K | 4 | false |
SQL 1 的结果 (可能):
假设 MySQL 把 NULL 放在最前面排序
id = 3, name = C
(active = NULL)id = 6, name = F
(active = NULL)id = 9, name = I
(active = NULL)id = 1, name = A
(active = true)id = 4, name = D
(active = true)id = 7, name = G
(active = true)id = 10, name = J
(active = true)id = 2, name = B
(active = false)id = 5, name = E
(active = false)id = 8, name = H
(active = false)
SQL 2 的结果:
id = 1, name = A
(active = true)id = 4, name = D
(active = true)id = 7, name = G
(active = true)id = 10, name = J
(active = true)
… 以及其他active = true
的行,直到达到10行。 因为排序没意义,结果具有不确定性,取决于数据库内部的行存储顺序。
总结:
- 如果
active
列只包含true
和false
,并且没有 NULL 值,那么这两条SQL语句的结果在id
和name
上可能相同,但在结果排序上可能不同(第一条语句明确排序了,第二条排序实质上无效)。 - 如果
active
列包含NULL
值,这是最可能导致结果不同的原因,因为第一条语句会受到NULL值排序的影响。
如何解决:
如果你的意图是只获取 active
为 true
的记录,那么第二条SQL语句是正确的选择。
如果你想要获取所有 corp_id = 4
的记录,并让 active
为 true
的记录排在最前面,可以使用以下SQL语句(假设你想把 NULL 值放在最后):
1 | SELECT id, name |
或者,更简单的使用 IFNULL
函数(MySQL 特性):
1 | SELECT id, name |
这条语句将 NULL
视为 0
(false),从而将 true
的行放在最前面。 如果你想将 NULL 视为 True, 将 0 改为 1 即可.
或者,增加排序字段,用id和active同时排序:
1 | SELECT id,name |
选择哪种方法取决于你想要如何处理 active
列中的 NULL
值。 最重要的是理解你的数据和你想达到的结果,然后选择正确的SQL语句。