MySQL Json类型详解

一、Json类型是什么

Json（JavaScript Object Notation）是一种轻量级的数据交换格式，常用于前后端数据传输。MySQL从版本5.7.8开始支持json类型，以方便存储和查询json格式数据。

Json格式的数据结构是构建在键/值对的基础上，可以包含多种数据类型，比如字符串、数字、布尔值等，甚至可以嵌套。

-- json示例
{
  "name": "Tom",
  "age": 18,
  "hobby": ["swimming", "coding"],
  "address": {
    "province": "Beijing",
    "street": "Haidian"
  }
}

二、Json类型的创建和插入

创建表时声明json类型的列，可以使用JSON_OBJECT、JSON_ARRAY等函数插入json格式数据。

-- 创建表
CREATE TABLE test (
  id INT PRIMARY KEY,
  info JSON
);

-- 插入json数据
INSERT INTO test (id, info) 
VALUES (1, JSON_OBJECT('name','Tom','age',18,'hobby',JSON_ARRAY('swimming','coding'),'address',JSON_OBJECT('province','Beijing','street','Haidian')));

三、Json类型的查询

MySQL提供了多种操作json类型的函数，支持json格式数据的查询、修改、删除等操作。

1. 查询

使用箭头(->)或双冒号(::)操作符可以根据键名查询json数据中的值。

-- 查询单个键值
SELECT info->>'$.name' FROM test WHERE id=1;

-- 查询多个键值
SELECT info->>'$.name',info->>'$.age' FROM test WHERE id=1;

使用json_extract函数可以根据json path表达式查询json数据中的值，返回一个json格式的数组。

-- 查询json格式整体数据
SELECT info FROM test WHERE id=1;

-- 查询多层结构的键值
SELECT JSON_EXTRACT(info,'$.address.province') FROM test WHERE id=1;

-- 查询多个json path表达式
SELECT JSON_EXTRACT(info,'$.name','$.age') FROM test WHERE id=1;

2. 修改

使用json_set函数可以修改json数据中的指定键值。

-- 修改整个json数据
UPDATE test SET info=JSON_OBJECT('name','Jack','age',20,'hobby',JSON_ARRAY('swimming','reading'),'address',JSON_OBJECT('province','Shanghai','street','Xuhui'));

-- 修改单个键值
UPDATE test SET info=JSON_SET(info,'$.age',21) WHERE id=1;

3. 删除

使用json_remove函数可以删除json数据中的指定键值。

-- 删除单个键值
UPDATE test SET info=JSON_REMOVE(info,'$.address') WHERE id=1;

-- 删除整个json数据
DELETE FROM test WHERE id=1;

四、Json类型的索引

MySQL支持对json类型的列创建普通索引、全文索引和空间索引，以提高json格式数据的查询效率。

在创建索引时需要注意，json类型的列不支持部分索引，因为每个键值对都是独立的，无法在其中选取一部分进行索引。

-- 创建普通索引
CREATE INDEX idx_info ON test(info);

-- 创建全文索引
ALTER TABLE test ADD FULLTEXT INDEX ft_info(info);

-- 创建空间索引
ALTER TABLE test ADD SPATIAL INDEX sp_info(info);

五、Json类型的应用场景

Json格式的数据化初衷就是为了前后端数据交互，所以json类型常被应用在Web项目中，特别是单页应用。此外，json类型还可以用于存储和查询半结构化的数据，可以大大简化数据表的设计和维护工作。

举例来说，在一个用户注册的表单中，可以通过json类型的列存储用户填写的不同类型的信息，比如基本个人信息、教育经历、工作经验等。这样一来，一个表就可以存储多种不同的信息，而不需要为每个信息单独建立一个表。

-- 创建用户信息表
CREATE TABLE user_detail (
  id INT PRIMARY KEY,
  detail JSON
);

-- 插入用户信息
INSERT INTO user_detail (id,detail)
VALUES (1,JSON_OBJECT(
  'basic',JSON_OBJECT(
    'name', 'Tom',
    'age', 18,
    'gender', 'male',
    'phone', '123456789'
  ),
  'edu', JSON_ARRAY(
    JSON_OBJECT(
      'school', 'Tsinghua University',
      'major', 'Computer Science',
      'degree', 'Bachelor'
    ),
    JSON_OBJECT(
      'school', 'MIT',
      'major', 'Artificial Intelligence',
      'degree', 'Master'
    )
  ),
  'work', JSON_OBJECT(
    'company', 'Microsoft',
    'position', 'Software Engineer'
  )
));

六、结语

本文详细介绍了MySQL的json类型的创建、插入、查询、修改和删除等操作，并举了一个应用场景的例子。相信对学习和使用MySQL json类型的开发者们会有所帮助。