MySQL按数字排序，空字符串（或0）排在最后。

Question

我刚刚问了一个与这个问题非常相似的问题...

目前我在语句中执行一个非常基本的OrderBy。

SELECT * FROM tablename WHERE visible=1 ORDER BY position ASC, id DESC

问题在于，对于“position”为空字符串的条目被视为0。因此，所有“position”为空字符串的条目出现在那些'1,2,3,4'之前。例如：

'', '', '', 1, 2, 3, 4

或者：

0, 0, 0, 1, 2, 3, 4

有没有一种方法可以实现以下排序：

1, 2, 3, 4, '', '', ''.

或者：

1, 2, 3, 4, 0, 0, 0.

我猜解决方案可能有某种替换函数，但我找不到符合我的要求的函数。

- JonB

Answer 1

SELECT * 
FROM tablename 
WHERE visible=1 
ORDER BY 
    case when position in('', '0') then 1 else 0 end,
    position ASC, 
    id DESC

- D'Arcy Rittich

---

我认为这个方法可行，但不是最好的解决方案。我认为 position 应该是一个整数列，如果是这样的话，CASE 表达式应该使用整数，而不是字符串。如果 position 不是整数或至少是数字，以数字排序就无法工作。此外，这个解决方案增加了一个新的排序列，很可能会对性能产生负面影响。如果我的假设是正确的，即 position 实际上是一个整数，那么 OP 可能指的是在 'empty string' 写入时的 NULL 值，这意味着你可以简单地写成 ORDER BY COALESCE(position, ~0), id DESC。 - Roland Bouman

@Roland：OP 明确暗示 position 是一个字符串：“'position' 的空字符串条目被视为 0，这是问题所在。” - D'Arcy Rittich

OrbMan 是的，他这样做了。我认为这可能是问题的一部分。我的建议是事先给它一个适当的数据类型，而不是试图事后收拾残局。 - Roland Bouman

@Roland：哦，我误解了你。我完全同意！我的假设是这不是一个选项，但明确指出肯定是好的。 - D'Arcy Rittich

我只是想问一下，如果我使用了这种技术，它是否仍然能够使用我在case语句中使用的列上定义的索引？ - Christopher Pelayo

@ChristopherPelayo - 我不知道它是否会，EXPLAIN会告诉你答案... - D'Arcy Rittich

Answer 2

你可以尝试使用CASE语句，像这样：

SELECT *
FROM tablename
WHERE visible = 1
ORDER BY CASE position WHEN '' THEN '9999' ELSE position END CASE ASC,
    ID DESC

- Scott Arrington

---

我知道这是一个旧答案，但它刚刚救了我的命。谢谢你，伙计！ - mingos

Answer 3

你说position包含空字符串条目...你是真的指空字符串，还是指NULL？如果它实际上包含NULL条目，那么你应该使用Orbman语句的轻微修改：

SELECT * 
FROM tablename 
WHERE visible=1 
ORDER BY 
    COALESCE(position, ~0)
,   id DESC

COALESCE() 返回第一个不为 NULL 的参数值。 ~0 是一种黑魔法，可以获得 MySQL 支持的最大整数值。 (~ 对所有 0 位执行按位取反，将它们变成 1)。因此，在这种情况下，如果 position IS NULL 为真，则返回 18446744073709551615，否则返回 position 的值。

我还想指出，您的 position 列的数据类型很可能是某种整数类型（请参见 http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html）。因为您提到了空字符串，所以我认为您应该通过执行 SHOW CREATE TABLE <tablename> 来检查表定义。如果 position 不是整数类型，我建议您更改它。主要原因是字符串，即使看起来像数字，也不能作为数字进行排序。

- Roland Bouman

Answer 4

你可以尝试将两个子查询连接起来，其中一个选择id > 0且非空，另一个选择仅为空和0

- Funky Dude