命令“ENUM字段”在MYSQL中 [英] ORDER BY "ENUM field" in MYSQL

问题描述

有一个字段'noticeBy'枚举（'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 根据其索引号进行排序，这取决于枚举成员在列规范中列出的顺序。例如，'b'在a之前排序 ENUM（'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）或 ORCER（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)

这篇关于命令“ENUM字段”在MYSQL中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！