如何用MySQL从指定数据库生成多元素JSON_ARRAY而非单元素数组？

问题场景 #

我有一个名为my_database的数据库，包含tbl1、tbl2、tbl3等数据表，想要生成包含所有表名的多元素JSON数组，但执行以下SQL后得到的是单元素数组（所有表名被拼成一个字符串作为数组唯一元素）：

SET @bd = 'my_database';

SELECT GROUP_CONCAT(DISTINCT TABLE_NAME) INTO @my_tables
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = @bd;

SELECT JSON_ARRAY(@my_tables);

执行结果：

+-------------------------+
| JSON_ARRAY(@my_tables)  |
+-------------------------+
| ["tbl1,tbl2,tbl3"]      |
+-------------------------+

需要的目标结果是：["tbl1","tbl2","tbl3"]

解决方案 #

方法一：使用JSON_ARRAYAGG（推荐，MySQL 5.7.22+支持）

直接使用MySQL内置的JSON聚合函数JSON_ARRAYAGG，可以直接将查询到的表名聚合为多元素JSON数组，无需额外拼接处理：

SET @bd = 'my_database';

SELECT JSON_ARRAYAGG(DISTINCT TABLE_NAME) AS table_names
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = @bd;

执行后会直接输出符合要求的结果：["tbl1","tbl2","tbl3"]

方法二：兼容低版本MySQL的处理方式

如果你的MySQL版本低于5.7.22，不支持JSON_ARRAYAGG，可以通过先给每个表名添加双引号，再将拼接后的字符串转换为JSON数组：

SET @bd = 'my_database';

-- 拼接带双引号的表名字符串，格式如"tbl1","tbl2","tbl3"
SELECT GROUP_CONCAT(DISTINCT CONCAT('"', TABLE_NAME, '"')) INTO @my_tables
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = @bd;

-- 将拼接后的字符串转为JSON数组
SELECT JSON_UNQUOTE(CONCAT('[', @my_tables, ']')) AS table_names;

这个方法通过手动构造JSON数组格式的字符串，再用JSON_UNQUOTE去除转义，得到目标数组。

内容的提问来源于stack exchange，提问作者MTK