基本知识
mysql从5.7开始，增加了JSON函数，支持对字符串进行JSON格式转换。这里用到四个函数：JSON_EXTRACT、JSON_UNQUOTE、JSON_LENGTH、JSON_VALID
JSON_EXTRACT(json_doc, path[, path] ...)
从json_doc中解析JSON文档，返回path参数指定的数据。如果任一参数为NULL，那么返回值也是NULL。如果json_doc不是合法的JSON数据，或者path不是合法的参数，都会抛出异常。
如果提供了多个path参数，返回结果会自动封装为数组，按照提供的参数顺序封装数据。如果只有一个path参数，返回结果就只有一个数据。
JSON_UNQUOTE(json_val)
取消引用 JSON 值并将结果作为 utf8mb4字符串返回。NULL如果参数是 则 返回 NULL。如果值以双引号开头和结尾但不是有效的 JSON 字符串文字，则会发生错误。
JSON_LENGTH(json_doc[, path])
返回 JSON 文档的长度，或者，如果 path给定参数，则返回路径标识的文档中值的长度。如果任何参数是 NULL 或 path 参数不标识文档中的值，则返回 NULL。json_doc如果参数不是有效的 JSON 文档或 path参数不是有效的路径表达式，则会发生错误。在 MySQL 8.0.26 之前，如果路径表达式包含一个*或 **通配符，也会引发错误。
JSON_VALID(val)
返回 0 或 1 以指示值是否为有效 JSON。如果参数是 NULL 则返回 NULL。
实现原理
为了将JSON数组转换为行，需要遍历数组的所有元素。
MySQL自带的mysql库为我们提供了一个help_topic表，该表中help_topic_id字段是从0自增的行，大概有几百行数据、一般数组的length已经足够使用了，通过枚举下标的方式，与JSON数组进行联合查询，获得所有数组元素。
原始数据
+-----------+------------------+ | id | business_tags | +-----------+------------------+ | 1 | ["A","B","C"] | | 2 | ["D","E"] | +-----------+------------------+
查询示例
SELECT
id,
JSON_UNQUOTE(
JSON_EXTRACT(
business_tags,
CONCAT('$[', help_topic.help_topic_id, ']')
)
) AS tag_name
FROM
tag
JOIN mysql.help_topic ON help_topic.help_topic_id < JSON_LENGTH(business_tags)
WHERE
JSON_VALID(business_tags)
AND JSON_LENGTH(business_tags);
查询结果
+-----------+-------------+ | id | tag_name | +-----------+-------------+ | 1 | A | | 1 | B | | 1 | C | | 2 | D | | 2 | E | +-----------+-------------+
mysql8之后我们可以利用新特性 公用表表达式(CTE) 递归公用表表达式替换help_topic.help_topic_id，也可以利用新的JSON函数 JSON_TABLE 实现同样的目的。
以下是一个利用CTE递归获取当月开始至结束日期的示例
WITH RECURSIVE dates(seq, date) AS(
SELECT
1,
DATE_FORMAT(CURDATE(), '%Y-%m-01')
UNION ALL
SELECT
seq + 1,
date + INTERVAL 1 DAY
FROM
dates
WHERE
date + INTERVAL 1 DAY <= DATE_FORMAT(LAST_DAY(CURDATE()), '%Y-%m-%d')
)
SELECT
seq,
date_format(date, "%Y-%m-%d") as date
FROM
dates;
查询结果
+------+------------+ | seq | date | +------+------------+ | 1 | 2022-09-01 | | 2 | 2022-09-02 | | 3 | 2022-09-03 | | 4 | 2022-09-04 | | 5 | 2022-09-05 | | 6 | 2022-09-06 | | 7 | 2022-09-07 | | 8 | 2022-09-08 | | 9 | 2022-09-09 | | 10 | 2022-09-10 | | 11 | 2022-09-11 | | 12 | 2022-09-12 | | 13 | 2022-09-13 | | 14 | 2022-09-14 | | 15 | 2022-09-15 | | 16 | 2022-09-16 | | 17 | 2022-09-17 | | 18 | 2022-09-18 | | 19 | 2022-09-19 | | 20 | 2022-09-20 | | 21 | 2022-09-21 | | 22 | 2022-09-22 | | 23 | 2022-09-23 | | 24 | 2022-09-24 | | 25 | 2022-09-25 | | 26 | 2022-09-26 | | 27 | 2022-09-27 | | 28 | 2022-09-28 | | 29 | 2022-09-29 | | 30 | 2022-09-30 | +------+------------+
