MySQL 8.0 可以操作 JSON 了，牛逼…

点击上方“芋道源码”，选择“设为星标”

管她前浪，还是后浪？

能浪的浪，才是好浪！

每天 10:33 更新文章，每天掉亿点点头发...

源码精品专栏

来源：www.jianshu.com/p/d4b012769a3b

经过漫长的测试,即将整体迁移至Mysql8.0; Mysql8.0 对于Json操作新增/优化了很多相关Json的API操作。

阅读了一下官方文档，虽然绝大多数的JSON操作都是应用层完成，但是会一些Mysql的JSON语法，方便进行debug；选出基础的, 有价值的部分，供未来参考；

https://dev.mysql.com/doc/refman/8.0/en/json.html https://dev.mysql.com/doc/refman/8.0/en/json-utility-functions.html

简单概述

不允许为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(); 使用时，有一些约束，但是会有更加的性能;

推荐下自己做的 Spring Boot 的实战项目：
https://github.com/YunaiV/ruoyi-vue-pro

JSON基础工具

//使用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()

// {"mascot": "Our mascot is a dolphin named \"Sakila\"."}

mysql> 
SELECTcol->
"$.mascot"FROM qtest;

//结果：| "Our mascot is a dolphin named \"Sakila\"." |

SELECT sentence->>
"$.mascot"FROM facts;

// 结果： | Our mascot is a dolphin named "Sakila". |

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

以下面这段JSON为例；

 [
3, {
"a": [
5, 
6], 
"b": 
10}, [
99, 
100]]

        $[
0] = 
3 ;

        $[
1] = {
"a": [
5, 
6], 
"b": 
10};

        $[
2] = [
99, 
100];

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

$[
1].a = [
5,
6]

        $[
1].a[
1] = 
6
        $[
1].b = 
10;

        $[
2][
0] = 
99;

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

 $[ 
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为结束的所有路径；

推荐下自己做的 Spring Cloud 的实战项目：
https://github.com/YunaiV/onemall

查找并修改JSON

//如上, 应该可以用
-->语法取代;
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*)

SELECT *  
FROM JSON_TABLE(   
'[{"a":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]',

        ->     
"$[*]"
        ->     
COLUMNS(

        ->       
rowidFORORDINALITY,

        ->       ac 
VARCHAR(
100) 
PATH"$.a"DEFAULT'111'ONEMPTYDEFAULT'999'ONERROR,

        ->       aj 
JSONPATH"$.a"DEFAULT'{"x": 333}'ONEMPTY,

        ->       bx 
INTEXISTSPATH"$.b"
        ->     )

        ->   ) 
AS tt;