5.7.8开始，开始支持json数据类型，json数据类型存储时会做格式检验

https://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html

create table json_test(
id int primary key,
data json
)

insert into json_test(id,data) values(1,null)
insert into json_test(id,data) values(2,'a')
com.mysql.jdbc.MysqlDataTruncation: Data truncation: Invalid JSON text

insert into json_test(id,data) values(3,'{"name":"cat","age":9}')
insert into json_test(id,data) values(4,'{"name":"DOG","age":1,"weight":1.0}')
insert into json_test(id,data) values(5,'{"name":"FISH","age":8,"weight":2}')




select  data->'$.name' from json_test
select JSON_EXTRACT(data, '$.name') as name from json_test;

# 去掉双引号
select  data->>'$.name' from json_test
select JSON_UNQUOTE(JSON_EXTRACT(data, '$.name')) as name from json_test

select JSON_KEYS(data) from json_test;

select JSON_PRETTY(data) from json_test


select JSON_DEPTH(data) from json_test

select JSON_LENGTH(data) from json_test

select JSON_STORAGE_SIZE(data) from json_test

select JSON_STORAGE_TYPE(data,'$.name') from json_test
select JSON_STORAGE_TYPE(data,'$.age') from json_test
select JSON_STORAGE_TYPE(data,'$.weight') from json_test

select JSON_TYPE(json_extract(data, '$.name')) from json_test

STRING
DECIMAL
INTEGER
ARRAY


类似map.putIfAbsent
update json_test set data=JSON_INSERT(data, '$.weight', '7')  where id=3;
update json_test set data=JSON_INSERT(data, '$.weight', 99)  where id=3;
update json_test set data=JSON_INSERT(data, '$.weight', 'abc')  where id=3;

类似map.replace
update json_test set data=JSON_REPLACE(data, '$.weight', 'abc')  where id=3;
update json_test set data=JSON_REPLACE(data, '$.weight', 99)  where id=3;

已存在的替换，不存在的新增，类似map.put
update json_test set data=JSON_SET(data, '$.weight', '7')  where id=3;
update json_test set data=JSON_SET(data, '$.weight', 99)  where id=3;
update json_test set data=JSON_SET(data, '$.weight', 'abc')  where id=3;

json文档内部字段 无模式  

删除指定位置元素

update json_test set data=JSON_REMOVE(data, '$.weight')  where id=3;

数组操作
update json_test set data=JSON_ARRAY_APPEND(data, '$.friends', 'a')  where id=1;
update json_test set data=JSON_ARRAY_APPEND(data, '$.friends', 'a')  where id=3;

update json_test set data=JSON_SET(data, '$.friends', 'a')  where id=1;
update json_test set data=JSON_SET(data, '$.friends', 'a')  where id=3;

在数组尾部添加
update json_test set data=JSON_ARRAY_APPEND(data, '$.friends', 'b')  where id=3;

在指定位置插入 
update json_test set data=JSON_ARRAY_INSERT(data, '$.friends[0]', '0')  where id=3;
update json_test set data=JSON_ARRAY_INSERT(data, '$.friends[1]', 'x')  where id=3;