MySQL 8.0 可以操作 JSON 了

吾爱主题阅读：174 2024-04-02 08:06:42 评论：0

经过漫长的测试,即将整体迁移至Mysql8.0; Mysql8.0 对于Json操作新增/优化了很多相关Json的API操作; 阅读了一下官方文档，虽然绝大多数的JSON操作都是应用层完成，但是会一些Mysql的JSON语法，方便进行debug；选出基础的, 有价值的部分，供未来参考；

https://dev.mysql.com/doc/ref...
https://dev.mysql.com/doc/ref...

1、简单概述

不允许为null; Json格式定义与LONGBLOB or LONGTEXT类似；它的最大长度是受到max_allowed_packet所控制的;
查看JSON字段所占用空间大小的函数时JSON_STORAGE_SIZE（xxx） ;
除普通的Json操作，额外支持GeoJSON (基于几何图形的针对地理空间数据交换格式)一些相关操作;
对Json栏位支持索引（结合Mysql8.0新特性，函数index）;
一个可以支持部分的，原地更新Json Column 的可选优化项加入MySql8.0 ; 可以使用的函数有JSON_SET() , JSON_REPLACE() ,JSON_REMOVE() ; 使用时，有一些约束，但是会有更加的性能;

2、JSON基础工具

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 //使用JSON_ARRAY方法定义JSON数组； SELECT JSON_ARRAY(1, "abc" , NULL , TRUE , CURTIME()) //结果：[1, "abc" , null , true , "11:30:24.000000" ] //JSON_OBJECT 方法定义JSON对象 SELECT JSON_OBJECT( 'id' , 87, 'name' , 'carrot' ) //结果{ "id" : 87, "name" : "carrot" } //数组与对象嵌套的场景； [99, { "id" : "HK500" , "cost" : 75.99}, [ "hot" , "cold" ]] { "k1" : "value" , "k2" : [10, 20]} //日期/时间类型定义 [ "12:18:29.000000" , "2015-07-29" , "2015-07-29 12:18:29.000000" ] //JSON_QUOTE 将JSON对象转义成String, 就是将内部的符号进行转义，并整体包裹上双引号； JSON_QUOTE( ' "null" ' ) //结果 "\"null\"" //将JSON内容美化并输出； JSON_PRETTY() //可以将JSON/JSON内部的元素转化为其他数据类型; //如下将JSON jdoc 中的id元素，转化为 unsigned int ; [https://dev.mysql.com/doc/refman/8.0/en/json.html#json-converting- between -types] (https://dev.mysql.com/doc/refman/8.0/en/json.html#json-converting- between -types) ORDER BY CAST (JSON_EXTRACT(jdoc, '$.id' ) AS UNSIGNED);

合并JSON的操作 JSON_MERGE_PRESERVE() and JSON_MERGE_PATCH() 实际业务用的可能性很少；

-> -->操作符，按照key 找值；区别在于 -->会去除包裹的”以及转义符号; 它的等价的Function形式是JSON_EXTRACT()

1 2 3 4 5 // { "mascot" : "Our mascot is a dolphin named \"Sakila\"." } mysql> SELECT col-> "$.mascot" FROM qtest; //结果：| "Our mascot is a dolphin named \"Sakila\"." | SELECT sentence->> "$.mascot" FROM facts; // 结果： | Our mascot is a dolphin named "Sakila" . |

3、JSON Path expression

上面 --> 后双引号中的内容就是所谓的JSON Path expression;
该语法是ECMAScript规范的一部分，所以前端程序员应该特别熟悉；

以下面这段JSON为例：

1 2 3 4 [3, { "a" : [5, 6], "b" : 10}, [99, 100]] $[0] = 3 ; $[1] = { "a" : [5, 6], "b" : 10}; $[2] = [99, 100];

与此同时，$[1], $[2] 并非标量, 进一步

1 2 3 4 $[1].a = [5,6] $[1].a[1] = 6 $[1].b = 10; $[2][0] = 99;

更进一步支持的语法特性$[n to m]

1 2	`$[ 1` `to` `2] = [{` `"a"` `: [5, 6],` `"b"` `: 10}, [99, 100]]` `$[` `last` `-2` `to` `last` `-1] = [3, {` `"a"` `: [5, 6],` `"b"` `: 10}]`

总结一下;

a .*是代表所有的members in object;
b [*]是代表所有的cells in array;
c [prefix] ** suffix 是代表以prefix开始，以suffix为结束的所有路径；

4、查找并修改JSON

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 //如上, 应该可以用 -->语法取代; mysql> SELECT JSON_EXTRACT( '{"a": 1, "b": 2, "c": [3, 4, 5]}' , '$.*' ); //[1, 2, [3, 4, 5]] SELECT JSON_EXTRACT( '{"a": 1, "b": 2, "c": [3, 4, 5]}' , '$.c[*]' ) //[3, 4, 5] SELECT JSON_EXTRACT( '{"a": {"b": 1}, "c": {"b": 2}}' , '$**.b' ); //[1, 2] SELECT JSON_EXTRACT( '[1, 2, 3, 4, 5]' , '$[1 to 3]' ); //[2, 3, 4] //JSON_SET JSON_INSERT JSON_REPLACE JSON_REMOVE SET @j = '["a", {"b": [true, false]}, [10, 20]]' ; SELECT JSON_SET(@j, '$[1].b[0]' , 1, '$[2][2]' , 2); //| [ "a" , { "b" : [1, false ]}, [10, 20, 2]] SELECT JSON_INSERT(@j, '$[1].b[0]' , 1, '$[2][2]' , 2); //[ "a" , { "b" : [ true , false ]}, [10, 20, 2]] JSON_REPLACE(@j, '$[1].b[0]' , 1, '$[2][2]' , 2) //[ "a" , { "b" : [1, false ]}, [10, 20]] SELECT JSON_REMOVE(@j, '$[2]' , '$[1].b[1]' , '$[1].b[1]' ); //[ "a" , { "b" : [ true ]}]

JSON Table Functions 一个比较常见的场景是JSON数据本身是一个表的结构；

JSON_TABLE(*expr*, *path* COLUMNS (*column_list*) [AS\] *alias*)

1 2 3 4 5 6 7 8 9 SELECT * FROM JSON_TABLE( '[{"a":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]' , -> "$[*]" -> COLUMNS( -> rowid FOR ORDINALITY, -> ac VARCHAR (100) PATH "$.a" DEFAULT '111' ON EMPTY DEFAULT '999' ON ERROR, -> aj JSON PATH "$.a" DEFAULT '{"x": 333}' ON EMPTY, -> bx INT EXISTS PATH "$.b" -> ) -> ) AS tt;