mysql中定义sql枚举值_仅使用SQL获取MySQL枚举的值

In a web application, I need to populate a with all possible values of a MySQL enum.

When I execute either of:SHOW COLUMNS FROM mytable LIKE 'mycolumn';

SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = 'mytable' AND COLUMN_NAME = 'mycolumn';

I always end up with enum('valueA','valueB','valueC').

I know I can parse it with PHP but can it be done using SQL only?

I need something like this:

+-----------------+

| values |

+-----------------+

| valueA |

| valueB |

| valueC |

+-----------------+

解决方案

While I would agree about not using enums most of the time, it is possible in a single SQL statement:-

SELECT DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING(COLUMN_TYPE, 7, LENGTH(COLUMN_TYPE) - 8), "','", 1 + units.i + tens.i * 10) , "','", -1)

FROM INFORMATION_SCHEMA.COLUMNS

CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) units

CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) tens

WHERE TABLE_NAME = 'mytable'

AND COLUMN_NAME = 'mycolumn'

This will work for enums with up to 100 possible values