编程笔记

lifelong learning & practice makes perfect

MySQL|为什么同样的sql多个where条件,排序就不一样了

下面的sql查询同一张表的数据,差异仅在where条件不同,但是同样是active =true的记录在返回的结果中,排序顺序不同,这是为什么

1
2
3
SELECT id,name FROM `tag` WHERE corp_id =4 ORDER BY active DESC LIMIT 10;

SELECT id,name FROM `tag` WHERE (corp_id =4) AND active =true 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 AND active = true: 首先过滤出 corp_id 为 4 并且 activetrue 的所有行。
  • 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 放在最前面排序

  1. id = 3, name = C (active = NULL)
  2. id = 6, name = F (active = NULL)
  3. id = 9, name = I (active = NULL)
  4. id = 1, name = A (active = true)
  5. id = 4, name = D (active = true)
  6. id = 7, name = G (active = true)
  7. id = 10, name = J (active = true)
  8. id = 2, name = B (active = false)
  9. id = 5, name = E (active = false)
  10. id = 8, name = H (active = false)

SQL 2 的结果:

  1. id = 1, name = A (active = true)
  2. id = 4, name = D (active = true)
  3. id = 7, name = G (active = true)
  4. id = 10, name = J (active = true)
    … 以及其他 active = true 的行,直到达到10行。 因为排序没意义,结果具有不确定性,取决于数据库内部的行存储顺序。

总结:

  • 如果 active 列只包含 truefalse,并且没有 NULL 值,那么这两条SQL语句的结果在 idname 上可能相同,但在结果排序上可能不同(第一条语句明确排序了,第二条排序实质上无效)。
  • 如果 active 列包含 NULL 值,这是最可能导致结果不同的原因,因为第一条语句会受到NULL值排序的影响。

如何解决:

如果你的意图是只获取 activetrue 的记录,那么第二条SQL语句是正确的选择。

如果你想要获取所有 corp_id = 4 的记录,并让 activetrue 的记录排在最前面,可以使用以下SQL语句(假设你想把 NULL 值放在最后):

1
2
3
4
5
6
7
8
9
10
SELECT id, name
FROM `tag`
WHERE corp_id = 4
ORDER BY
CASE
WHEN active IS NULL THEN 1 -- 把 NULL 放在最后
ELSE 0 -- 把 true 和 false 放在前面
END,
active DESC -- 然后按照 true > false 排序
LIMIT 10;

或者,更简单的使用 IFNULL 函数(MySQL 特性):

1
2
3
4
5
SELECT id, name
FROM `tag`
WHERE corp_id = 4
ORDER BY IFNULL(active, 0) DESC
LIMIT 10;

这条语句将 NULL 视为 0 (false),从而将 true 的行放在最前面。 如果你想将 NULL 视为 True, 将 0 改为 1 即可.

或者,增加排序字段,用id和active同时排序:

1
2
3
4
5
6
7
8
SELECT id,name 
FROM `tag`
WHERE corp_id =4
ORDER BY id,active DESC LIMIT 10;

SELECT id,name
FROM `tag` WHERE (corp_id =4) AND active =true
ORDER BY id,active DESC LIMIT 10;

选择哪种方法取决于你想要如何处理 active 列中的 NULL 值。 最重要的是理解你的数据和你想达到的结果,然后选择正确的SQL语句。

欢迎关注我的其它发布渠道