MySQL 文档存储知识点扫盲

1. 什么是MySQL文档存储

MySQL文档存储是MySQL 5.7及以上版本引入的功能，它允许用户以JSON格式存储和查询数据，提供了NoSQL风格的文档数据库能力，同时保留了传统关系型数据库的优势。

2. 核心特性

• JSON数据类型：MySQL 5.7+原生支持JSON数据类型
• X DevAPI：提供面向文档的API接口
• 混合模式：可在同一数据库中同时使用关系表和文档集合
• ACID事务：保持传统MySQL的事务特性
• SQL与NoSQL结合：既可用SQL查询JSON数据，也可用NoSQL方式操作

3. JSON数据类型操作

创建包含JSON列的表

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    details JSON,
    price DECIMAL(10,2)
);

插入JSON数据

INSERT INTO products (name, details, price) 
VALUES ('Laptop', '{"brand": "Dell", "specs": {"cpu": "i7", "ram": "16GB"}}', 999.99);

查询JSON数据

-- 提取JSON字段
SELECT name, details->'$.brand' AS brand FROM products;

-- 条件查询
SELECT name FROM products WHERE details->'$.specs.ram' = '16GB';

4. 文档集合操作

创建集合

// 使用MySQL Shell
var myColl = db.createCollection('products');

文档CRUD操作

// 插入文档
myColl.add({
    name: "Smartphone",
    brand: "Apple",
    specs: {
        model: "iPhone 13",
        storage: "128GB"
    },
    price: 899.00
}).execute();

// 查询文档
myColl.find("brand = 'Apple'").execute();

// 更新文档
myColl.modify("name = 'Smartphone'")
    .set("price", 849.00)
    .execute();

// 删除文档
myColl.remove("name = 'Smartphone'").execute();

5. 索引优化

创建JSON字段索引

-- 在JSON字段上创建虚拟列并索引
ALTER TABLE products ADD COLUMN brand VARCHAR(50) 
    GENERATED ALWAYS AS (details->'$.brand') VIRTUAL;
CREATE INDEX idx_brand ON products(brand);

-- 直接创建JSON路径索引(MySQL 8.0+)
CREATE INDEX idx_specs ON products((CAST(details->'$.specs.ram' AS CHAR(10))));

6. 常用JSON函数

• JSON_EXTRACT() / ->：提取JSON值
• JSON_SET()：设置JSON值
• JSON_REMOVE()：删除JSON元素
• JSON_MERGE_PATCH()：合并JSON文档
• JSON_SEARCH()：搜索JSON文档
• JSON_VALID()：验证JSON有效性

7. 最佳实践

1. 合理设计文档结构：避免过度嵌套，保持文档大小适中
2. 混合使用关系型和文档型：对需要严格关系的数据使用表，对灵活结构使用JSON
3. 适当索引：为频繁查询的JSON路径创建索引
4. 考虑文档大小：MySQL单个JSON文档最大支持1GB
5. 版本兼容性：注意不同MySQL版本对JSON功能的支持差异

8. 适用场景

• 需要灵活模式的应用程序
• 半结构化数据存储
• 快速原型开发
• 需要同时使用SQL和NoSQL功能的场景
• 已有MySQL基础设施但需要文档存储能力的项目

9. 限制

• JSON列不能有默认值
• 不能直接对JSON列创建主键
• 某些复杂JSON操作性能可能不如专用文档数据库
• 大文档处理效率可能较低

通过掌握这些知识点，您可以充分利用MySQL文档存储功能，在传统关系型数据库基础上获得NoSQL的灵活性。