仅使用SQL获取MySQL枚举的值 [英] Getting the values of a MySQL enum using only SQL

问题描述

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

In a web application, I need to populate a <select> 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';

我总是以 enum（'valueA'，'valueB'，'valueC'）结尾。

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

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          |
+-----------------+

推荐答案

虽然我大部分时间都同意不使用枚举，但可以在单个SQL语句中使用：-

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'

这将适用于最多100个可能值的枚举

This will work for enums with up to 100 possible values

这篇关于仅使用SQL获取MySQL枚举的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！