MySQL高级数据类型之：`MySQL`的`JSON`数据类型：其在`文档存储`和`动态属性`中的应用。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

MySQL JSON 数据类型：文档存储与动态属性应用

大家好！今天我们来深入探讨 MySQL 中的 JSON 数据类型，它在文档存储和动态属性管理方面扮演着重要的角色。我们将通过实际案例，剖析 JSON 数据类型的优势、用法以及需要注意的地方。

1. JSON 数据类型简介

从 MySQL 5.7.22 开始，MySQL 引入了原生的 JSON 数据类型。这意味着 MySQL 可以直接存储和操作 JSON 文档，而无需将其解析为字符串或将其分割成多个列。这极大地简化了对非结构化数据的处理，并提高了查询效率。

JSON 数据类型可以存储有效的 JSON 文档，包括：

JSON 对象： { "key1": "value1", "key2": "value2" }
JSON 数组： [ "item1", "item2", "item3" ]
标量值： "string", 123, true, null

2. JSON 数据类型的优势

灵活性： 可以存储任意结构的 JSON 数据，无需预定义模式。这对于快速迭代和处理不断变化的数据结构非常有用。
高效查询： MySQL 提供了丰富的 JSON 函数，可以高效地查询和更新 JSON 文档中的特定元素。
节省存储空间： 对于稀疏数据，JSON 数据类型可以节省存储空间，因为只存储实际存在的数据。
简化开发： 避免了手动解析和序列化 JSON 字符串的复杂性，提高了开发效率。
数据校验： MySQL 会自动验证存储的 JSON 数据是否有效，确保数据质量。

3. JSON 数据类型的基本操作

3.1 创建包含 JSON 列的表

要使用 JSON 数据类型，首先需要在表中定义一个 JSON 列。

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    details JSON
);

在这个例子中，details 列被定义为 JSON 类型，用于存储产品的详细信息，例如规格、颜色、尺寸等。

3.2 插入 JSON 数据

可以使用 INSERT 语句将 JSON 数据插入到 JSON 列中。

INSERT INTO products (name, details) VALUES (
    'T-Shirt',
    '{"color": "blue", "size": "M", "material": "cotton"}'
);

INSERT INTO products (name, details) VALUES (
    'Laptop',
    '{"brand": "Dell", "model": "XPS 13", "specs": {"cpu": "i7", "ram": "16GB", "storage": "512GB SSD"}}'
);

也可以使用 JSON_OBJECT() 函数动态构建 JSON 对象：

INSERT INTO products (name, details) VALUES (
    'Book',
    JSON_OBJECT('title', 'The Lord of the Rings', 'author', 'J.R.R. Tolkien', 'genre', 'Fantasy')
);

3.3 查询 JSON 数据

MySQL 提供了多种函数来查询 JSON 数据。

JSON_EXTRACT(json_doc, path)：从 JSON 文档中提取指定路径的值。

SELECT name, JSON_EXTRACT(details, '$.color') AS color FROM products WHERE name = 'T-Shirt'; -- 获取T-Shirt的颜色
SELECT name, JSON_EXTRACT(details, '$.specs.cpu') AS cpu FROM products WHERE name = 'Laptop'; -- 获取Laptop的CPU

$ 表示 JSON 文档的根节点，. 用于访问对象属性，[] 用于访问数组元素。

-> 运算符：是 JSON_EXTRACT() 函数的简写形式。

SELECT name, details->'$.color' AS color FROM products WHERE name = 'T-Shirt'; -- 获取T-Shirt的颜色
SELECT name, details->'$.specs.cpu' AS cpu FROM products WHERE name = 'Laptop'; -- 获取Laptop的CPU

该运算符返回的结果是字符串类型。

->> 运算符：与 -> 类似，但会自动将结果转换为适当的数据类型。

SELECT name, details->>'$.color' AS color FROM products WHERE name = 'T-Shirt'; -- 获取T-Shirt的颜色
SELECT name, details->>'$.specs.cpu' AS cpu FROM products WHERE name = 'Laptop'; -- 获取Laptop的CPU

该运算符返回的结果是原本的数据类型。

JSON_CONTAINS(json_doc, target, path)：检查 JSON 文档是否包含指定的 target JSON 文档或值。

SELECT name FROM products WHERE JSON_CONTAINS(details, '{"color": "blue"}'); -- 查找包含颜色为蓝色的产品
SELECT name FROM products WHERE JSON_CONTAINS(details, '"Dell"', '$.brand'); -- 查找品牌为Dell的产品

JSON_CONTAINS_PATH(json_doc, one_or_all, path)：检查 JSON 文档是否包含指定的路径。

SELECT name FROM products WHERE JSON_CONTAINS_PATH(details, 'one', '$.color'); -- 查找包含color属性的产品
SELECT name FROM products WHERE JSON_CONTAINS_PATH(details, 'all', '$.specs.cpu'); -- 查找包含specs.cpu属性的产品

JSON_KEYS(json_doc, path)：返回 JSON 对象的键名数组。

SELECT JSON_KEYS(details) FROM products WHERE name = 'T-Shirt'; -- 获取T-Shirt的details对象的所有键
SELECT JSON_KEYS(details, '$.specs') FROM products WHERE name = 'Laptop'; -- 获取Laptop的specs对象的所有键

JSON_LENGTH(json_doc, path)：返回 JSON 文档或数组的长度。

SELECT name, JSON_LENGTH(details) FROM products; -- 获取所有产品的details对象的长度
SELECT name, JSON_LENGTH(details, '$.specs') FROM products WHERE name = 'Laptop'; -- 获取Laptop的specs对象的长度

3.4 更新 JSON 数据

MySQL 提供了多种函数来更新 JSON 数据。

JSON_SET(json_doc, path, val, ...)：设置 JSON 文档中指定路径的值。如果路径不存在，则创建该路径。

UPDATE products SET details = JSON_SET(details, '$.price', 29.99) WHERE name = 'T-Shirt'; -- 设置T-Shirt的价格
UPDATE products SET details = JSON_SET(details, '$.specs.screen_size', '13.3 inch') WHERE name = 'Laptop'; -- 设置Laptop的屏幕尺寸

JSON_INSERT(json_doc, path, val, ...)：插入 JSON 文档中指定路径的值。如果路径已存在，则不执行任何操作。
```
UPDATE products SET details = JSON_INSERT(details, '$.discount', 0.1) WHERE name = 'T-Shirt'; -- 插入T-Shirt的折扣
```
JSON_REPLACE(json_doc, path, val, ...)：替换 JSON 文档中指定路径的值。如果路径不存在，则不执行任何操作。
```
UPDATE products SET details = JSON_REPLACE(details, '$.color', 'red') WHERE name = 'T-Shirt'; -- 替换T-Shirt的颜色
```

JSON_REMOVE(json_doc, path, ...)：删除 JSON 文档中指定路径的值。

UPDATE products SET details = JSON_REMOVE(details, '$.color') WHERE name = 'T-Shirt'; -- 删除T-Shirt的颜色属性

4. JSON 数据类型的应用场景

4.1 文档存储

JSON 数据类型非常适合存储非结构化的文档数据，例如：

日志数据
配置文件
用户资料
产品信息

通过将文档数据存储为 JSON 格式，可以灵活地查询和更新文档中的特定字段，而无需预定义模式。

案例：存储用户资料

假设我们需要存储用户资料，包括姓名、年龄、地址、兴趣爱好等。

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    profile JSON
);

INSERT INTO users (name, profile) VALUES (
    'Alice',
    '{"age": 30, "address": {"city": "New York", "street": "Broadway"}, "interests": ["reading", "hiking"]}'
);

SELECT name, profile->>'$.address.city' AS city FROM users WHERE name = 'Alice'; -- 获取Alice的城市
SELECT name, JSON_LENGTH(profile->'$.interests') AS num_interests FROM users WHERE name = 'Alice'; -- 获取Alice的兴趣爱好数量

4.2 动态属性

JSON 数据类型可以用于存储对象的动态属性，例如：

产品规格
文章标签
事件属性

通过将动态属性存储为 JSON 格式，可以灵活地添加、修改和删除属性，而无需修改表结构。

案例：存储产品规格

假设我们需要存储产品的规格，例如尺寸、颜色、重量等。不同的产品可能具有不同的规格。

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    specifications JSON
);

INSERT INTO products (name, specifications) VALUES (
    'Table',
    '{"width": 120, "height": 75, "material": "wood"}'
);

INSERT INTO products (name, specifications) VALUES (
    'Chair',
    '{"height": 90, "color": "black", "material": "leather"}'
);

SELECT name FROM products WHERE specifications->>'$.color' = 'black'; -- 查找颜色为黑色的产品
SELECT name, specifications->>'$.width' AS width FROM products WHERE name = 'Table'; -- 获取Table的宽度

5. JSON 数据类型的注意事项

索引： 默认情况下，不能直接在 JSON 列上创建索引。但是，可以使用 VIRTUAL 列和 JSON_EXTRACT() 函数来创建索引。

ALTER TABLE products ADD COLUMN color VARCHAR(255) AS (details->>'$.color');
CREATE INDEX idx_color ON products (color);

SELECT name FROM products WHERE color = 'blue'; -- 可以使用索引 idx_color

性能： 大量的 JSON 数据可能会影响查询性能。建议对 JSON 数据进行适当的索引和优化。
数据类型： 使用 ->> 运算符时，MySQL 会自动将 JSON 值转换为适当的数据类型。但是，需要注意数据类型转换可能导致精度损失或错误。
版本兼容性： JSON 数据类型是在 MySQL 5.7.22 中引入的。如果使用较旧的 MySQL 版本，则需要升级到支持 JSON 数据类型的版本。

6. 总结

MySQL 的 JSON 数据类型为我们提供了一种灵活、高效的方式来存储和处理非结构化数据。通过合理地利用 JSON 函数和索引，可以充分发挥 JSON 数据类型的优势，简化开发，提高查询效率。在选择使用 JSON 数据类型时，需要考虑数据结构的复杂性、查询需求以及性能要求。JSON数据类型在数据管理和查询方面有独特的优势，但需要根据实际应用场景权衡使用。

7. 一些建议

对于结构化数据，仍然建议使用传统的关系型数据模型。
对于半结构化或非结构化数据，可以考虑使用 JSON 数据类型。
在设计 JSON 数据结构时，尽量保持结构简单，避免嵌套过深。
使用适当的索引来提高查询性能。
定期检查和优化 JSON 数据，确保数据质量和性能。

MySQL JSON 数据类型：文档存储与动态属性应用

发表回复 取消回复

发表回复取消回复