MySQL JSON 数据类型使用教程

MySQL 5.7.8 版本开始引入了原生的 JSON 数据类型，它允许在数据库中存储和处理 JSON 格式的数据，提供了高效的存储结构以及一系列用于操作 JSON 数据的函数。下面是关于 MySQL 中 JSON 数据类型的详细使用教程：

1. 创建表并使用 JSON 数据类型

使用 CREATE TABLE 语句创建表时，可以指定列的数据类型为 JSON。例如，创建一个存储用户信息的表，其中包含 user_info 列用于存储用户的详细 JSON 信息：

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    user_info JSON
);

2. 插入 JSON 数据

插入简单 JSON 对象

可以直接插入包含键值对的 JSON 对象：

INSERT INTO users (username, user_info)
VALUES ('JohnDoe', '{"name": "John Doe", "age": 30, "email": "johndoe@example.com"}');

插入 JSON 数组

如果需要插入 JSON 数组，例如用户收藏的商品列表：

INSERT INTO users (username, user_info)
VALUES ('JaneSmith', '{"name": "Jane Smith", "age": 25, "favorites": ["product1", "product2", "product3"]}');

3. 查询 JSON 数据

查询整个 JSON 列

查询表中的 JSON 列时，可以直接选择该列：

SELECT user_info FROM users WHERE username = 'JohnDoe';

提取 JSON 对象中的特定值

使用 -> 操作符可以提取 JSON 对象中指定键的值，返回 JSON 格式的数据；使用 ->> 操作符可以提取值并以字符串形式返回。

-- 以 JSON 格式提取 age 值
SELECT user_info -> '$.age' AS age FROM users WHERE username = 'JohnDoe';

-- 以字符串形式提取 age 值
SELECT user_info ->> '$.age' AS age FROM users WHERE username = 'JohnDoe';

提取 JSON 数组中的元素

对于 JSON 数组，可以使用索引（从 0 开始）提取元素：

SELECT user_info ->> '$.favorites[0]' AS first_favorite FROM users WHERE username = 'JaneSmith';

使用 JSON_TABLE 函数（MySQL 8.0 及以上）

JSON_TABLE 函数可以将 JSON 数据转换为关系表结构，方便进行复杂查询。例如，将 favorites 数组转换为多行数据：

SELECT jt.favorite
FROM users,
     JSON_TABLE(user_info, '$.favorites[*]' COLUMNS (favorite VARCHAR(100) PATH '$')) AS jt
WHERE username = 'JaneSmith';

4. 更新 JSON 数据

更新 JSON 对象中的单个值

使用 JSON_SET 函数可以更新 JSON 对象中的值，如果键不存在则会创建。

UPDATE users
SET user_info = JSON_SET(user_info, '$.age', 31)
WHERE username = 'JohnDoe';

向 JSON 数组中添加元素

使用 JSON_ARRAY_APPEND 函数向 JSON 数组中添加元素：

UPDATE users
SET user_info = JSON_ARRAY_APPEND(user_info, '$.favorites', 'product4')
WHERE username = 'JaneSmith';

5. 删除 JSON 数据中的元素

使用 JSON_REMOVE 函数删除 JSON 对象中的键或 JSON 数组中的元素：

-- 删除 user_info 中 age 键对应的值
UPDATE users
SET user_info = JSON_REMOVE(user_info, '$.age')
WHERE username = 'JohnDoe';

-- 删除 favorites 数组中的第一个元素
UPDATE users
SET user_info = JSON_REMOVE(user_info, '$.favorites[0]')
WHERE username = 'JaneSmith';

6. 索引 JSON 数据

为了提高对 JSON 数据的查询性能，可以在 JSON 列上创建索引。

创建普通索引

CREATE INDEX idx_user_info ON users ((user_info ->> '$.name'));

创建函数索引（MySQL 8.0 及以上）

如果需要对 JSON 数组中的元素创建索引，可以使用函数索引：

CREATE INDEX idx_favorites ON users ((JSON_TABLE(user_info, '$.favorites[*]' COLUMNS (favorite VARCHAR(100) PATH '$'))));

7. 条件查询 JSON 数据

可以在 WHERE 子句中使用 JSON 操作符进行条件查询：

-- 查询年龄大于 30 岁的用户
SELECT * FROM users WHERE user_info ->> '$.age' > 30;

-- 查询收藏了特定商品的用户
SELECT * FROM users WHERE JSON_CONTAINS(user_info -> '$.favorites', '"product2"');

8. JSON 函数的更多应用

MySQL 还提供了许多其他 JSON 函数，如 JSON_LENGTH（获取 JSON 数组长度或 JSON 对象的键值对数量）、JSON_KEYS（获取 JSON 对象的所有键）等：

-- 获取 favorites 数组的长度
SELECT JSON_LENGTH(user_info -> '$.favorites') AS favorites_count FROM users WHERE username = 'JaneSmith';

-- 获取 user_info 对象的所有键
SELECT JSON_KEYS(user_info) AS keys FROM users WHERE username = 'JohnDoe';