MySQL8 的 JSON 数据类型操作方法（增/删/改/查）讲解

🙏废话不多说系列，直接开整🙏

1. 前言

在 MySQL 中， 从 json 格式的字段中提取数据可以使用 【json_extrack()】函数，或者使用 【->】 操作符。示例如下：

-- 假设你有一个  mytable 的表，其中有一个字段  json_data 字段，他的数据类型为 JSON，并且包含了一个JSON对象。

{
	"name": "demo",
    "age": 25,
    "email": "demo@example.com",
    "address": {
    	"street": "123 Main St",
        "city": "Anytown",
        "state": "CA",
        "zip": "90210"
    }
}

2. 使用方法

你可以使用一下方法来提取  name,age  和 email 等字段：

-- 方法1：使用 json_extract() 函数
select 
	json_extract(json_data, '$.name') as name,
    json_extract(json_data, '$.age') as age,
    json_extract(json_data, '$.email') as email
from mytable;

-- 方法2：使用 -> 操作符（这是  json_extract() 的简化形式）
select 
	json_data -> '$.name' as name,
    json_data -> '$.age' as age,
    json_data -> '$.email' as email
from mytable;

如果你要提取 嵌套的 address 对象中的字段，例如： street，你可以这样做：

select 
	json_extract(json_data, '$.address.street') as street
from mytable;

select json_data ->> '$.address.street' as street from mytable;

-- 注意：
-- 【->> 】操作符会自动将提取的 JSON值 转换为 字符串，并去除引号
-- 如果你使用【 -> 】操作符，结果将是一个 JSON格式的字符串，包括引号。

在实际应用中，需要根据你的 MySQL 版本和具体需求选择合适的方法。MySQL 5.7 以及以上版本对JSON类型的支持更加完善。

3. MySQL 8 改进之前 MySQL5.7 的JSON字段数据功能

使用 JSON 路径表达式和 JSON 函数来查询和操作这个表中的 JSON 数据

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    contact_info JSON
);

INSERT INTO employees (name, contact_info) VALUES 
('Alice', '{"email": "alice@example.com", "phone": "123-456-7890"}'),
('Bob', '{"email": "bob@example.com", "phone": "987-654-3210"}');

-- 1.查询JSON字段特定的字段（返回每个员工的姓名和邮箱地址）
SELECT 
	name,
    JSON_UNQUOTE(JSON_EXTRACT(contact_info, '$.email')) AS email
FROM employees;

-- 2.使用 JSON 函数创建新的 JSON 数据：（返回每个员工的姓名和邮箱地址，以 JSON 格式返回）
SELECT
	name,
    JSON_OBJECT('name', name, 'email', JSON_UNQUOTE(JSON_EXTRACT(contact_info, '$.email'))) AS employee_info
FROM employees;

-- 3.使用 JSON 函数更新 JSON 数据：（更新 Alice 的电话号码，而不需要读取整个 JSON 字段再进行更新）
UPDATE employees
	SET contact_info = JSON_SET(contact_info, '$.phone', '555-555-5555')
WHERE name = 'Alice';

4. 补充：MySQL 5.7 时的 JSON 字段操作方法

可以使用 JSON 函数 和 操作符 来查询和操作 JSON 类型的字段数据。以下是一些常用的 JSON 操作方法：

-- 1. JSON_EXTRACT：用于提取 JSON 数据中的特定字段或元素。语法为：
select JSON_EXTRACT(json_column, '$.field_name') from t_table;
-- json_column 是 JSON 类型的字段名，$.field_name 是要提取的字段或元素的路径。

-- 2. JSON_UNQUOTE：用于去除 JSON 数据中的双引号。语法为：
select JSON_UNQUOTE(JSON_EXTRACT(json_column, '$.field_name')) from t_table;
-- json_column 和 $.field_name 的含义与 JSON_EXTRACT 相同。

-- 3. JSON_OBJECT：用于创建 JSON 对象。语法为：
JSON_OBJECT('key1', value1, 'key2', value2, ...)
-- 其中，key1、key2 等是 JSON 对象的键名，value1、value2 等是 JSON 对象的键值。

-- 4. JSON_ARRAY：用于创建 JSON 数组。语法为：
JSON_ARRAY(value1, value2, ...)
-- 其中，value1、value2 等是 JSON 数组的元素。

-- 5. -> 和 ->> 操作符：用于提取 JSON 数据中的特定字段或元素。语法为：
select
	json_column->'$.field_name'
	json_column->>'$.field_name'
from t_table;
-- 其中，json_column 是 JSON 类型的字段名，$.field_name 是要提取的字段或元素的路径。
-- 【->】 操作符返回的是 JSON 类型的值，
-- 【->>】操作符返回的是字符串类型的值。

5. 将 MySQL 的 JSON 字段置空方法

-- 1.使用 NULL ：将 JSON 字段设置为 NULL 值是将其置空的最简单方法。可以使用 UPDATE 语句将 JSON 字段设置为 NULL。
UPDATE table_name SET json_column = NULL WHERE condition;
-- 其中，table_name 是表名，json_column 是 JSON 字段名，condition 是更新条件。

-- 2.使用空的 JSON 对象：除了将 JSON 字段设置为 NULL，还可以将其设置为空的 JSON 对象。
--   可以使用 JSON_OBJECT() 函数创建一个空的 JSON 对象。
UPDATE table_name SET json_column = JSON_OBJECT() WHERE condition;

-- 3.使用空的 JSON 数组：类似地，可以将 JSON 字段设置为空的 JSON 数组。
--   可以使用 JSON_ARRAY() 函数创建一个空的 JSON 数组。
UPDATE table_name SET json_column = JSON_ARRAY() WHERE condition;

-- 这些方法可以将 JSON 字段置空，根据实际需求选择适合的方法即可。

🙏至此，非常感谢阅读🙏