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 并且 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 值放在最后）：

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 特性）：

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同时排序:

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语句。