MySQL8 下的 JSON

MySQL自5.7版本开始提供了json数据类型，并在8.0版本中进行了大幅度的性能优化。

json类型的字段的内容在mysql中是按text类型存储的。

官方文档地址:

• json数据类型: https://dev.mysql.com/doc/refman/8.0/en/json.html
• json类型搜索的函数: https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html
• 给json类型创建索引: https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-multi-valued

本文内容基于 win10+MySQL8.0.28 + MySQL workbench 环境编写。

mysql的JSON类型支持key-value及json数组格式，本文主要研究json数组格式，应用场景为信息的单、复选择项记录在筛选页面的过滤。

基本用法

1. 数据转换

-- key-value格式
SELECT CAST('{"deptName": "部门5", "deptId": "5", "deptLeaderId": "5"}' AS json) AS `key_value_format`

-- json数组格式
SELECT CAST( '[1,2,3]' AS json) AS `json_array_format`

2. key-value格式的查询

CREATE TABLE `dept` (
  `id` INT UNSIGNED NOT NULL,
  `dept` VARCHAR(255) DEFAULT NULL,
  `json_value` JSON DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO `dept`(id, dept, json_value)
VALUES(1,'部门1','{"deptName": "部门1", "deptId": 1, "deptLeaderId": 3}')
,(2,'部门2','{"deptName": "部门2", "deptId": 2, "deptLeaderId": 4}')
,(3,'部门3','{"deptName": "部门3", "deptId": 3, "deptLeaderId": 5}')
,(4,'部门4','{"deptName": "部门4", "deptId": 4, "deptLeaderId": 5}')
,(5,'部门5','{"deptName": "部门5", "deptId": 5, "deptLeaderId": 5}');

-- 查询
SELECT * FROM `dept` WHERE json_value->'$.deptLeaderId' IN (5, 3);

3. json数组格式查询

-- 必须全部包含
SELECT *
FROM (
    SELECT JSON_ARRAY(1,2,3,4,5,6,7,8) AS ids
) AS a
WHERE JSON_CONTAINS(a.ids, json_array(4,3));

-- 只要包含一个即可
SELECT *
FROM (
    SELECT JSON_ARRAY(1,2,3,4,5,6,7,8