ORDER BY “ENUM 字段"在 MYSQL

2021-12-29 00:00:00 sql enums mysql sql-order-by

有一个字段 'noticeBy' enum('email','mobile','all','auto','nothing') NOT NULL DEFAULT 'auto'.众所周知，按 ENUM 字段排序是相对于其索引执行的.但是，如何按其值排序?

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?

推荐答案

如排序:

ENUM 值根据它们的索引号排序，索引号取决于枚举成员在列规范中的列出顺序.例如，对于 ENUM('b', 'a')，'b' 在 'a' 之前排序.空字符串排在非空字符串之前，NULL 值排在所有其他枚举值之前.

ENUM values 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' for ENUM('b', 'a'). The empty string sorts before nonempty strings, and NULL values sort before all other enumeration values.

为防止在 ENUM 列上使用 ORDER BY 子句时出现意外结果，请使用以下技术之一:

To prevent unexpected results when using the ORDER BY clause on an ENUM column, use one of these techniques:

按字母顺序指定 ENUM 列表.

通过编码ORDER BY CAST(col AS CHAR)或ORDER BY CONCAT(col)，确保列按词法排序，而不是按索引号排序.

Make sure that the column is sorted lexically rather than by index number by coding ORDER BY CAST(col AS CHAR) or ORDER BY CONCAT(col).

根据第二个项目符号，您可以在投射后对列进行排序到一个字符串:

Per the second bullet, you can therefore sort on the column after it has been cast to a string:

ORDER BY CAST(noticeBy AS CHAR)

相关文章