Mysql的json函数使用和虚拟列

2021-08-30 本文已影响0人 Coding测试

mysql 5.7版本以上是支持json字段的函数

mysql官方对json的一些说明：

JSON列不能有默认值。
MySQL优化器还会查找与JSON表达式匹配的虚拟列上的兼容索引。
MySQL NDB Cluster 7.5（7.5.2及更高版本）支持 JSON列和MySQL JSON函数，包括创建从列生成的 JSON列索引作为无法对JSON列建立索引的解决方法。支持JSON每个NDB表最多3 列。
MySQL使用utf8mb4字符集和utf8mb4_bin排序规则来处理JSON上下文中使用的字符串和其他字符集中的字符串将utf8mb4根据需要进行转换。（对于在串ascii或 utf8字符集，则不需要转换，因为ascii和utf8都是的子集utf8mb4。）
因为utf8mb4_bin是二进制排序规则，JSON值的比较区分大小写。

查询操作的函数

01.-> 和  JSON_EXTRACT(json的字段名称 , '$.键')是等价的，作用相同。获取根据键值获取数据
SELECT json字段名称 -> '$.键' FROM 表名 WHERE json字段名称 -> '$.键' = 'fail'
SELECT * FROM 表名 WHERE JSON_EXTRACT(json字段名称,'$.键') = 'fail'
数组类型则是 $[角标]

02.无论使用 -> 和  JSON_EXTRACT() 获取到的数据是带有引号的，对我们的查询可能有影响，所以我们去除双引号
使用 ->>  或者 JSON_EXTRACT(json字段名称,'$.键')
SELECT JSON_UNQUOTE(JSON_EXTRACT(info,'$.ip'))  FROM test_json
SELECT info ->> '$.ip'  FROM test_json

03.按照json的值进行筛选，因为 JSON 不同于字符串，所以如果用字符串和 JSON 字段比较，是不会相等的，这时可以通过 CAST 将字符串转成 JSON 的形式
SELECT * FROM test_json WHERE info = CAST('[\"5,6,7,9\"]' AS json);

04.要特别注意的是，JSON中的元素搜索是严格区分变量类型的，比如说整型和字符串是严格区分的
SELECT * FROM lnmp WHERE category->'$.id' = '1';
SELECT * FROM lnmp WHERE category->'$.id' = 1;
这两条sql执行出来的结果不一样的。

05.除了用info ->'$.ip'的形式搜索，还可以用JSON_CONTAINS 函数，但和info ->'$.ip'的形式有点相反的是，JSON_CONTAINS 第二个参数是不接受整数的，无论 json 元素是整型还是字符串，否则会出现这个错误
mysql> SELECT * FROM lnmp WHERE JSON_CONTAINS(category, 1, '$.id');
ERROR 3146 (22032): Invalid data type for JSON data in argument 2 to function json_contains; a JSON string or JSON type is required.

06.比较JSON值采用两个级别。第一级是基于JSON类型的比较。如果类型不同，则取决于哪种类型具有更高的优先级。如果是相同的JSON类型，则是第二级，使用该类型的规则来比较。

07.JSON_TYPE ：函数返回指定属性对应的类型名称：
SELECT JSON_TYPE(json字段名称->'$.键') FROM  表名;

需要更多的json函数可以访问以下mysql的官方文档

官方文档的地址

更新的函数

01.JSON_INSERT() 插入新值，但不会覆盖已经存在的值
UPDATE 表名 SET json字段的名称 = JSON_INSERT(json字段的名称,'$.键','键对应的值','$.键','键对应的值') WHERE id = '1';
如果原先的键（或者原来的json数据里面没有这个键）对应的没有值，则会直接更新上，如果对应的键有值，则有值的就不会更新

02.JSON_SET() 插入新值，并覆盖已经存在的值
UPDATE 表名  SET json的字段名称 = JSON_SET(json的字段名称,'$.键','键对应的值','$.键','键对应的值','$.键','键对应的值') WHERE id = '1';
如果原先的键（或者原来的json数据里面没有这个键）对应的没有值，则会直接更新上，如果对应的键有值，也会直接更新掉

03.JSON_REPLACE() 只替换存在的值
UPDATE 表名 SET info = json_replace(info,'$.键','键对应的值','$.键','键对应的值','$.键','键对应的值') where id = '1';
如果键存在，直接更新键对应的值，如果键不存在，则直接忽略掉

 04\. JSON_REMOVE() 删除 JSON 元素
UPDATE 表名 SET json的字段名称 = JSON_REMOVE(json的字段名称,'$.键') WHERE id = '1';
json数据中，键存在，直接删除键和对应的数据，不存在此键，则直接或略

json中的虚拟列

过传统的B+树索引即可实现对JSON格式部分属性的快速查询。使用方法是首先创建该虚拟列，然后在该虚拟列上创建索引

 ALTER TABLE 表名 ADD 虚拟列的字段名称 VARCHAR(200 ) GENERATED ALWAYS AS (JSON_EXTRACT(json的字段名称,'$.键')) VIRTUAL;

释义：根据json中的某一个键创建一个虚拟的列，新增的时候，不必新增这个虚拟列的数据，会根据json里面的键自动把数据拿过来。如果这个键不存在，也可以创建成功，只不过没有值，不会报错。后期json里面新增这个键得话，还是根据键拿到值。而不必特意的向虚拟列增加值。