将ENUM值转换为JSON_TABLE的可能方法?

Question

使用以下查询:

SELECT REPLACE(CONCAT("[", SUBSTRING(COLUMN_TYPE, 6, LENGTH(COLUMN_TYPE) - 6), "]"), "'", '"') AS types
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA='myschema' 
AND TABLE_NAME='tbl_items'
AND COLUMN_NAME='itemTypes';

我得到这样的结果:

+-------------------------+
| types                   |
+-------------------------+
| ['a','b','c','d']       |
+-------------------------+

我怎么能得到这样的结果呢?

+--------------+
| type         |
+--------------+
| a            |
| b            |
| c            |
| d            |
+--------------+

我知道JSON_TABLE，我确实得到了我想要的静态列表:

SELECT *
FROM JSON_TABLE('["a","b","c","d"]', '$[*]' COLUMNS( type CHAR(1) PATH '$' )) AS tt;

但是这行不通:

WITH t AS (
SELECT REPLACE(CONCAT("[", SUBSTRING(COLUMN_TYPE, 6, LENGTH(COLUMN_TYPE) - 6), "]"), "'", '"') AS types
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA='myschema' 
AND TABLE_NAME='tbl_items'
AND COLUMN_NAME='itemTypes'
LIMIT 1
)
SELECT *
FROM JSON_TABLE(t.types, "$[*]" COLUMNS( type CHAR(1) PATH "$" )) AS tt;

#1109 -表函数参数中未知的表't'

既不:

SELECT *
FROM JSON_TABLE((
SELECT REPLACE(CONCAT("[", SUBSTRING(COLUMN_TYPE, 6, LENGTH(COLUMN_TYPE) - 6), "]"), "'", '"')
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA='myschema' 
AND TABLE_NAME='tbl_items'
AND COLUMN_NAME='itemTypes'
LIMIT 1
), "$[*]" COLUMNS( type CHAR(1) PATH "$" )) AS tt;

#1210 - JSON_TABLE参数不正确

我在做什么，这可能吗?

Answer 1

这是ENUM的缺点之一——允许的值的列表存储在BLOB中，您可以做一个"有趣的"操作。如果你想查询离散值以在UI中创建值的下拉列表或类似的东西，可以使用解析作业将其取出。

您在正确的轨道上，但是要使用CTE，您必须在查询中引用该CTE:

WITH t AS (
SELECT REPLACE(CONCAT("[", SUBSTRING(COLUMN_TYPE, 6, LENGTH(COLUMN_TYPE) - 6), "]"), "'", '"') AS types
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA='test2'
AND TABLE_NAME='tbl_items'
AND COLUMN_NAME='itemTypes'
LIMIT 1
)
SELECT *
FROM t CROSS JOIN JSON_TABLE(t.types, "$[*]" COLUMNS( type CHAR(1) PATH "$" )) AS tt;

或者，只需按原样获取ENUM定义，并在应用程序代码中解析它。