php - 仅使用 SQL 获取 MySQL 枚举的值

Question

在网络应用程序中，我需要填充 <select>具有 MySQL 枚举的所有可能值。

当我执行以下任一操作时:

SHOW COLUMNS FROM  mytable LIKE  'mycolumn';SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'mytable' AND COLUMN_NAME = 'mycolumn';

我总是以 enum('valueA','valueB','valueC')结尾.

我知道我可以用 PHP 解析它，但它可以仅使用 SQL 来完成吗？我需要这样的东西:

+-----------------+| values          |+-----------------+| valueA          || valueB          || valueC          |+-----------------+

Answer 1

这是 Chris Komlenic 的 8 Reasons Why MySQL's ENUM Data Type Is Evil 之一:

 4. Getting a list of distinct ENUM members is a pain.

A very common need is to populate a select-box or drop down list with possible values from the database. Like this:

Select color:

[ select box ]

If these values are stored in a reference table named 'colors', all you need is: SELECT * FROM colors ...which can then be parsed out to dynamically generate the drop down list. You can add or change the colors in the reference table, and your sexy order forms will automatically be updated. Awesome.

Now consider the evil ENUM: how do you extract the member list? You could query the ENUM column in your table for DISTINCT values but that will only return values that are actually used and present in the table, not necessarily all possible values. You can query INFORMATION_SCHEMA and parse them out of the query result with a scripting language, but that's unnecessarily complicated. In fact, I don't know of any elegant, purely SQL way to extract the member list of an ENUM column.