json-table官方文档地址

https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html#function_json-table

json数组中包含json对象输出

# 删除sql所对应的json对象1
SET @delete_result1 = (CONCAT('{', '"delete_sql"', ': ', '"DELETE FROM user where ac_code = 111"', ',', '"delete_size"', ': ', 11, '}'));
# 删除sql所对应的json对象2
SET @delete_result2 = (CONCAT('{', '"delete_sql"', ': ', '"DELETE FROM user where ac_code = 111"', ',', '"delete_size"', ': ', 22, '}'));
# 默认为第一个json对象
SET @delete_result_print = @delete_result1;
# 拼接第二个json对象
SET @delete_result_print = (SELECT CONCAT_WS(',', @delete_result_print, @delete_result2));
# 最后处理为json数组字符串
SET @delete_result_print = (SELECT CONCAT('[', @delete_result_print, ']'));
# 上述处理为json数组字符串的逻辑等效于当前所被注释的直接赋值的方式
# SET @delete_result_print = '[{"delete_sql": "DELETE FROM user where ac_code = 111","delete_size": 11},{"delete_sql": "DELETE FROM user where ac_code = 111","delete_size": 22}]';
# 输出结果
SELECT *
      JSON_TABLE(
        @delete_result_print,
        "$[*]"
        COLUMNS(
          # 自增序号
          row_index FOR ORDINALITY,
          delete_sql TEXT PATH "$.delete_sql",
          delete_size INT PATH "$.delete_size"
      ) AS tt;
+-------------------------------------------------------------------+
|  row_index  |            delete_sql                 | delete_size |
+-------------------------------------------------------------------+
|      1      |  DELETE FROM user where ac_code = 111 |      11     |
+-------------------------------------------------------------------+
|      2      |  DELETE FROM user where ac_code = 111 |      22     |
+-------------------------------------------------------------------+
 
json数组中包含普通类型对象输出
 
SELECT
	JSON_TABLE ( '["11", "22"]', '$[*]' 
COLUMNS 
		NESTED PATH '$' COLUMNS ( result INT PATH '$' ) ) 
	 AS tt;
+----------+
|  result  |
+----------+
|    11    |
+----------+
|    22    |
+----------+
SELECT
	JSON_TABLE (
		'[{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}]',
		'$[*]' COLUMNS (
		 a INT PATH '$.a', 
		 NESTED PATH '$.b[*]' COLUMNS ( b1 INT PATH '$' ),
		 NESTED PATH '$.b[*]' COLUMNS ( b2 INT PATH '$' )
	) AS tt;
+------+------+------+
|   a  |  b1  |  b2  |
+------+------+------+
|    1 |   11 | NULL |
|    1 |  111 | NULL |
|    1 | NULL |   11 |
|    1 | NULL |  111 |
|    2 |   22 | NULL |
|    2 |  222 | NULL |
|    2 | NULL |   22 |
|    2 | NULL |  222 |
+------+------+------+
SELECT
	JSON_TABLE ( '[{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}]', '$[*]' 
		COLUMNS (
			a INT PATH '$.a', 
			NESTED PATH '$.b[*]' COLUMNS ( b INT PATH '$' ) 
	AS tt;
+------+------+
|   a  |   b  |
+------+------+
|    1 |   11 |
|    1 |  111 |
|    2 |   22 |
|    2 |  222 |
+------+------+
SELECT
	JSON_TABLE ( '[{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' 
		COLUMNS (
			a INT PATH '$.a', 
			NESTED PATH '$.b[*]' COLUMNS ( b INT PATH '$' ) 
	 AS tt
	WHERE b IS NOT NULL;
+------+------+
|   a  |   b  |
+------+------+
|    1 |   11 |
|    1 |  111 |
|    2 |   22 |
|    2 |  222 |
+------+------+
SELECT
	JSON_TABLE ( '[{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}]', '$[*]' 
	COLUMNS ( NESTED PATH '$.b[*]' COLUMNS ( b INT PATH '$' ) ) ) 
AS tt;
+------+
|   b  |
+------+
|   11 |
|  111 |
|   22 |
|  222 |
+------+
                    # 删除sql所对应的json对象1SET @delete_print1 = (CONCAT('{', '"delete_sql"', ': ', '"DELETE FROM user where ac_code = 111"', ',', '"delete_size"', ': ', 11, '}'));# 删除sql所对应的json对象2SET @delete_print2 = (CONCAT('{', '"delete_sql"', ': ', '"DELETE FROM user wher..
length:获取参数值的字节个数（注意不是字符个数）
SELECT LENGTH('haosy')     结果是5
SELECT LENGTH('haosy郝')    结果是8 不是6（是字节个数不是字符个数，因编码格式是utf-8，所以一个汉字占3个字节）
concat(str1,str2,…) 拼接字符串
SELECT CONCAT(NAME,'___',nikcname)
FROM stringtable
upper(str)、lower(str) 转换大小写
upper：变大写
SELECT UPPER('h
1. 通过 Navicat Mysql 或 Navicat Premium 先到处表成json文件。
2. 把导出的文件复制到 Json2Lua 文件夹下的 json文件夹下。
3. 双击运行 run.bat
4. 没有报错的话，会把转换后的文件输出到 lua文件夹下。
FROM zichaxun t1
inner join JSON_TABLE(CONCAT('["', REPLACE('1,2', ',', '","'), '"]'), "$[*]" COLUMNS (score2 varchar(20) PATH "$")) t2
on JSON_CONTAINS(t2.score2, CAST(t1.score as JS...
这将会从 `users` 表中选择所有行，并将每一行的数据转换为一个 JSON 对象。对象的键名为 `name`, `age`, 和 `email`，值分别对应于该行中同名的列。
如果你希望将多行转换为单个 JSON 数组，你可以使用 JSON_ARRAYAGG 函数：
SELECT JSON_ARRAYAGG(JSON_OBJECT('name', name, 'age', age, 'email', email))
FROM users;
这将会生成包含所有行的 JSON 数组，每一行都被转换为一个 JSON 对象。