mysql - 对枚举进行排序实际上是如何工作的？

Question

在 MySQL 命令行客户端中运行以下查询时，我遇到了一些奇怪的行为:

SELECT favoriteGenre, firstName, lastName 
FROM members 
ORDER BY favoriteGenre, firstName;

我得到以下结果:

favoriteGenre       firstName       lastName
crime               Jane            Field
crime               John            Sparks
horror              Marty           Pareene
thriller            Mary            Newton
romance             Jo              Scrivener
sciFi               Nick            Blakeley
nonFiction          Bill            Swan

然而我期待这些结果:

favoriteGenre       firstName       lastName
crime               Jane            Field
crime               John            Sparks
horror              Marty           Pareene
nonFiction          Bill            Swan
romance             Jo              Scrivener
sciFi               Nick            Blakeley
thriller            Mary            Newton

请注意，nonFiction 排在第 7 位，而我预计它会排在第 4 位...

这是用于创建表的 SQL:

USE mydatabase;

CREATE TABLE members (
  id              SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  username        VARCHAR(30) BINARY NOT NULL UNIQUE,
  password        CHAR(41) NOT NULL,
  firstName       VARCHAR(30) NOT NULL,
  lastName        VARCHAR(30) NOT NULL,
  joinDate        DATE NOT NULL,
  gender          ENUM( 'm', 'f' ) NOT NULL,
  favoriteGenre   ENUM( 'crime', 'horror', 'thriller', 'romance', 'sciFi', 'adventure', 'nonFiction' ) NOT NULL,
  emailAddress    VARCHAR(50) NOT NULL UNIQUE,
  otherInterests  TEXT NOT NULL,
  PRIMARY KEY (id)
);

INSERT INTO members VALUES( 1, 'sparky', password('mypass'), 'John', 'Sparks', '2007-11-13', 'm', 'crime', 'jsparks@example.com', 'Football, fishing and gardening' );
INSERT INTO members VALUES( 2, 'mary', password('mypass'), 'Mary', 'Newton', '2007-02-06', 'f', 'thriller', 'mary@example.com', 'Writing, hunting and travel' );
INSERT INTO members VALUES( 3, 'jojo', password('mypass'), 'Jo', 'Scrivener', '2006-09-03', 'f', 'romance', 'jscrivener@example.com', 'Genealogy, writing, painting' );
INSERT INTO members VALUES( 4, 'marty', password('mypass'), 'Marty', 'Pareene', '2007-01-07', 'm', 'horror', 'marty@example.com', 'Guitar playing, rock music, clubbing' );
INSERT INTO members VALUES( 5, 'nickb', password('mypass'), 'Nick', 'Blakeley', '2007-08-19', 'm', 'sciFi', 'nick@example.com', 'Watching movies, cooking, socializing' );
INSERT INTO members VALUES( 6, 'bigbill', password('mypass'), 'Bill', 'Swan', '2007-06-11', 'm', 'nonFiction', 'billswan@example.com', 'Tennis, judo, music' );
INSERT INTO members VALUES( 7, 'janefield', password('mypass'), 'Jane', 'Field', '2006-03-03', 'f', 'crime', 'janefield@example.com', 'Thai cookery, gardening, traveling' );

幕后究竟发生了什么，这种排序是如何进行的？这种行为是 MySQL 独有的吗？

Answer 1

favoriteGenre 是一个枚举，而不是我们大多数人期望看到的文本字段。

正如您在 documentation 中所读到的那样, enum 按索引排序，而不是按文本表示排序。这使您的查询结果正确。

要根据值对 enum 进行排序，您需要使用 CAST(col AS CHAR)(感谢 McAdam331 添加此内容，另请参阅他的 SQLFiddle ).不过，我建议对您的数据库进行适当的规范化。