MySQL中Json字段

概述

MySQL在5.7.8中提供了Json类型的支持。
官网
json字段支持：json数组（数组中可以是不同的类型）、json对象
json字段类型就是：json

基本使用

写入json字段

全量写入

全量写入json字段时，会覆盖之前的整个字段。此时直接以json字符串的形式给字段赋值即可。

• insert

// 这里的hobbies是一个数组，如果数组内部还是对象的话直接[{}, {}]
INSERT INTO `table_name` (`json_column`) VALUES ('{"age": 80, "name": "王八", "hobbies": ["1", "2"]}');

• update

UPDATE table_name SET json_column = '{"name": "赵六", "age": 22}' where id = xxx;

• 还可以使用json函数构建json对象和json数组
  • JSON_OBJECT(k1, v1 [, k2, v2, k3, v3…])：构建json对象
  • JSON_ARRAY(v1, v2, …)：构建json数组

增量更新

对于已经有值的json字段，如果要更新其中部分字段则需要使用相关JSON函数。属性值如果是复杂类型。则使用对应的json构建函数：JSON_OBJECT, JSON_ARRAY

• JSON_SET(json_column, 字段路径($.json属性名), json属性值)：设置json字段中的键值对

// 修改id = 53的数据。将json字段props中的name属性设置【无论是否有这个属性就设置】为 张三
UPDATE table_name SET props = JSON_SET(props, '$.name', '张三') where id = 53;

• JSON_REPLACE(json_column, 字段路径($.json属性名), json属性值)替换json字段中的键值对

// 修改id = 53的数据。将json字段props中的name属性【替换，必须要已经存在name这个属性，否则不更新】为 张三
UPDATE table_name SET props = JSON_REPLACE(props, '$.name', '张三') where id = 53;

• JSON_REMOVE(json_column, 字段路径($.json属性名))：删除json字段中的键

// 修改id = 53的数据。将json字段props中的hobbyies属性移除
UPDATE table_name SET props = JSON_REMOVE(props, '$.hobbyies') where id = 53;

读取json字段

简单字段

语法：json_column -> ‘$.json键’（返回json对象，如果要获取纯文本或数值类型的值可以使用 ->>）

// 查询json字段 props 对象的age属性
select (props -> '$.age' from table_name;// 查询json字段 props 中的hobbies属性的第一项
SELECT props  -> '$.hobbies[0]' FROM table_name;

复杂类型

数组套对象

当json字段为数组套对象时，如果要查询数组内对象的字段。

• 可以使用JSON_EXTRACT函数，提取json数组
• 也可以使用 -> 语法进行解析

select json_arr -> '$[*].add' from test;SELECT JSON_EXTRACT(json_arr, '$[*].add') FROM test;

• 还可以使用 JSON_TABLE将数组映射为一个临时表

select temp.addr from 
test
, JSON_TABLE(json_arr, '$[*]' COLUMNS (addr VARCHAR(255) path '$.add'
)) AS temp;

过滤json字段

当使用json字段进行过滤时，其取值方式与读取json字段类似。

// 查询props字段中 age属性 大于 10的数据
select * from table_name where props -> '$.age' > 10;// 如果查询条件同时存在上下限可以使用 (BETWEEN 下限 AND 上限) 进行过滤，上下限都是闭区间
select * from auth_sub_app2 where props -> '$.age' BETWEEN 10 AND 20;==等同于==
select * from auth_sub_app2 where props -> '$.age' >= 10 and props -> '$.age' <= 20;

JSON_CONTAINS和JSON_CONTAINS_PATH

mysql也支持对json字段进行深层的过滤。

• JSON_CONTAINS：支持过滤json数组值中的过滤，匹配包含某个值的json数组

// 注意后面的值本身有个 ""
select * from table_name WHERE JSON_CONTAINS(props -> '$.books', '"art"')

• JSON_CONTAINS_PATH：支持过滤json对象键的过滤，匹配包含指定路径（不管值，只要存在匹配的键即可）的json对象
  语法：JSON_CONTAINS_PATH(json_column, one 或者 all, json路径1, json路径2…)
  one：只要后面的json路径匹配任意一个即可命中。
  all：必须匹配后面的所有的json路径

// props中只要有 name或者age中任何一个就匹配
select * from table_name WHERE JSON_CONTAINS_PATH(props, 'one', '$.name', '$.age') ;// props中同时有 name和age 才匹配
select * from table_name WHERE JSON_CONTAINS_PATH(props, 'one', '$.name', '$.age') ;

JSON_SEARCH

JSON_SEARCH用于嵌套对象属性过滤。
语法：JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] …])
如果后面的可选参数不指定，则在整个json字段中搜索关键字，只要json对象的值完全匹配关键字就能匹配上。

如果函数JSON_SEARCH返回值不为null，则意味着匹配上了。因此在sql中 就写成 JSON_SEARCH(…) is not null

// json_arr：json字段。
// one / all：后面可以指定多个查找json路径。one表示匹配任何一个路径即可。all表示匹配所有路径
// null 表示不使用转义字符
// '$[*].add' 查找路径 每个数组内对象的add属性
select * from test WHERE JSON_SEARCH(json_arr, 'one', '重庆', null ,'$[*].add') is not null;// 不指定查找路径，匹配所有路径
select * from test WHERE JSON_SEARCH(json_arr, 'all', 10) is not null;

虚拟字段

在mysql中，支持类似Vue中的计算属性的东西。名为：虚拟字段
虚拟字段，顾名思义它不是实际存在的字段。
它的值来自其他的现有字段的计算结果
虚拟字段分为两种类型：

• VIRTUAL：虚拟列（使用时，实时计算，不占用存储空间）
• STORED：存储列（计算条件赋值时，计算结果后存储。查询性能更高，但会占据存储空间）

当设置了虚拟字段后，我们可以像普通字段一样使用它们
虚拟字段可以简化日常的一些查询操作，并且可以为其创建索引。以提高查询性能

@Configuration
public class UndertowConfig implements WebServerFactoryCustomizer<UndertowServletWebServerFactory> {@Overridepublic void customize(UndertowServletWebServerFactory factory) {// 创建虚拟线程执行器
//        ExecutorService virtualThreadExecutor = Executors.newVirtualThreadPerTaskExecutor();factory.addDeploymentInfoCustomizers(deploymentInfo -> {deploymentInfo.setExecutor(Executors.newVirtualThreadPerTaskExecutor());deploymentInfo.setAsyncExecutor(Executors.newVirtualThreadPerTaskExecutor());});}
}

• tomcat

@Configuration
public class TomcatThreadPoolConfiguration {@Beanpublic TomcatProtocolHandlerCustomizer<?> protocolHandlerVirtualThreadExecutorCustomizer() {return protocolHandler -> protocolHandler.setExecutor(new VirtualThreadExecutor("http-"));}
}