MYSQL 中的 ORDER BY “ENUM 字段”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17603907/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORDER BY "ENUM field" in MYSQL
提问by abdulmanov.ilmir
There is a field 'noticeBy' enum('email','mobile','all','auto','nothing') NOT NULL DEFAULT 'auto'. As it known ordering by ENUM field performs relative to its index. However, how it possible make order by its values?
有一个字段 'noticeBy' enum('email','mobile','all','auto','nothing') NOT NULL DEFAULT 'auto'。众所周知，按 ENUM 字段排序是相对于其索引执行的。但是，如何按其值排序？
回答by eggyal
As documented under Sorting:
如Sorting下所述：
ENUMvalues are sorted based on their index numbers, which depend on the order in which the enumeration members were listed in the column specification. For example,'b'sorts before'a'forENUM('b', 'a'). The empty string sorts before nonempty strings, andNULLvalues sort before all other enumeration values.To prevent unexpected results when using the
ORDER BYclause on anENUMcolumn, use one of these techniques:
Specify the
ENUMlist in alphabetic order.Make sure that the column is sorted lexically rather than by index number by coding
ORDER BY CAST(col AS CHAR)orORDER BY CONCAT(col).
ENUM值根据它们的索引号排序，索引号取决于枚举成员在列规范中的列出顺序。例如，'b'在'a'for之前排序ENUM('b', 'a')。空字符串排在非空字符串之前，NULL值排在所有其他枚举值之前。为防止
ORDER BY在ENUM列上使用子句时出现意外结果，请使用以下技术之一：
ENUM按字母顺序指定列表。确保该列按词法排序，而不是通过编码
ORDER BY CAST(col AS CHAR)或索引号排序ORDER BY CONCAT(col)。
Per the second bullet, you can therefore sort on the column after it has been castto a string:
每份第二子弹，你可以因此排序上已经后列投为字符串：
ORDER BY CAST(noticeBy AS CHAR)
回答by Andy G
This also works:
这也有效：
ORDER BY FIELD(noticeBy, 'all','auto','email','mobile','nothing')
(I don't believe that there is a setting to achieve this, you have to provide the sort-values.)
（我不相信有设置可以实现这一点，您必须提供排序值。）
回答by Fabian Bigler
You can define your order however you wish:
您可以随意定义订单：
ORDER BY CASE noticeBy
WHEN 'email' THEN 1
WHEN 'mobile' THEN 2
WHEN 'all' THEN 3
WHEN 'auto' THEN 4
ELSE 5
END
This will return the rows in the following order: email, mobile, all, auto, nothing.
这将按以下顺序返回行：电子邮件、移动设备、全部、自动、无。
回答by Devner
In my case, I had to sort enum results by the "ENUM" field and also make sure the values are in DESCENDING order. My enum had the following values: 'Open','Closed'
就我而言，我必须按“ENUM”字段对枚举结果进行排序，并确保值按降序排列。我的枚举具有以下值：'Open','Closed'
So when I used ORDER BY CAST(status AS CHAR), the results were in this order:
因此，当我使用 时ORDER BY CAST(status AS CHAR)，结果按以下顺序排列：
Closed
Open
Open
But I wanted the Openstatus tickets to be shown first and then the Closedtickets. So I used the following:
但我希望Open先显示状态票，然后再显示Closed票。所以我使用了以下内容：
ORDER BY CAST(status AS CHAR) DESC
ORDER BY CAST(status AS CHAR) DESC
This gave me the order that I was looking for i.e.
这给了我我正在寻找的订单，即
Open
Open
Closed
Summary:
概括：
Just using ORDER BY CASTon an enum did not seem to help. To sort the results in a specific order, mentioning ASCor DESCas well, did the trick.
仅ORDER BY CAST在枚举上使用似乎没有帮助。要按特定顺序对结果进行排序，提及ASC或DESC也可以解决问题。
