将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;

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