MySQL - JSON

MySQL - 使用 JSON 数据类型

自 5.7.8 版本起，MySQL 提供了对 JSON (JavaScript Object Notation，JavaScript 对象表示法) 数据类型的原生支持。这是一个重大增强，允许开发人员直接在关系数据库中存储和查询无模式或半结构化数据。在此之前，开发人员通常将 JSON 存储为纯文本，这效率低下且缺乏验证。

原生 JSON 类型的优势

• 自动验证： MySQL 会自动验证插入或更新到 JSON 列的任何文档。如果文档不是有效的 JSON，操作将失败，从而确保数据完整性。
• 优化存储格式： JSON 文档以优化的二进制格式存储，而不是原始字符串。这允许通过键或数组索引快速直接访问文档中的单个元素，而无需解析整个字符串。
• 丰富的函数集： MySQL 提供了一套全面的函数和运算符来创建、操作和搜索 JSON 数据。

创建带有 JSON 列的表

您可以在 CREATE TABLE 语句中像定义任何其他类型一样定义 JSON 数据类型的列。JSON 列可以存储两种类型的结构：JSON 对象（用 {} 括起来的键值对集合）或 JSON 数组（用 [] 括起来的有序值列表）。

CREATE TABLE PRODUCTS (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    NAME VARCHAR(100) NOT NULL,
    -- Stores variable product attributes like color, size, material, etc.
    ATTRIBUTES JSON
);

-- Inserting data with JSON objects
INSERT INTO PRODUCTS (NAME, ATTRIBUTES) VALUES
('Laptop',   '{"brand": "ACME", "ram_gb": 16, "storage_gb": 512, "touchscreen": false}'),
('T-Shirt',  '{"brand": "Modern Apparel", "color": "blue", "size": "L", "materials": ["cotton", "polyester"]}'),
('Book',     '{"title": "The Art of SQL", "author": "A. Codd", "pages": 450}');

查询 JSON 数据

MySQL 提供了方便的列路径运算符 (-> 和 ->>) 来从 JSON 列中提取数据。这是 JSON_EXTRACT() 函数的快捷方式。

• ->：提取一个值并将其作为带 JSON 引号的值返回（例如，"blue"、16、false）。
• ->>：提取一个值并将其作为不带引号的字符串返回（例如，blue、16、false）。这通常对显示或比较更有用。

示例：提取值

-- Extract the brand and color for all products
-- Note how some products don't have a 'color', resulting in NULL
SELECT
    NAME,
    ATTRIBUTES->>'$.brand' AS Brand,
    ATTRIBUTES->>'$.color' AS Color
FROM PRODUCTS;

NAME	Brand	Color
Laptop	ACME	NULL
T-Shirt	Modern Apparel	blue
Book	NULL	NULL

示例：使用 WHERE 过滤

您可以直接在 WHERE 子句中使用这些运算符，根据 JSON 内容过滤行。

-- Find all products with more than 8GB of RAM
SELECT NAME, ATTRIBUTES->>'$.ram_gb' AS RAM
FROM PRODUCTS
WHERE ATTRIBUTES->>'$.ram_gb' > 8;

高级 JSON 函数

在数组中搜索：JSON_CONTAINS

JSON_CONTAINS() 函数检查 JSON 文档中是否存在给定值，这对于在数组中搜索特别有用。

-- Find all T-Shirts made of cotton
SELECT NAME
FROM PRODUCTS
WHERE NAME = 'T-Shirt' AND JSON_CONTAINS(ATTRIBUTES, '"cotton"', '$.materials');

修改 JSON 文档：JSON_SET、JSON_INSERT、JSON_REPLACE

这些函数允许您就地修改 JSON 文档。

• JSON_SET：更新现有值并插入新值。
• JSON_INSERT：仅插入新值；不覆盖现有值。
• JSON_REPLACE：仅替换现有值；不插入新值。

-- Update the laptop's RAM to 32GB and add a 'backlit_keyboard' attribute
UPDATE PRODUCTS
SET ATTRIBUTES = JSON_SET(
    ATTRIBUTES,
    '$.ram_gb', 32,
    '$.backlit_keyboard', true
)
WHERE ID = 1;

将 JSON 转换为行：JSON_TABLE() (MySQL 8.0+)

现代 MySQL 中最强大的功能之一是 JSON_TABLE()，它允许您在查询中将 JSON 数据投影为关系型表格格式。这对于报告或将 JSON 数据与其他表连接非常有用。

-- Create a virtual table of all materials from all T-Shirts
SELECT
    p.NAME,
    m.material
FROM
    PRODUCTS p,
    JSON_TABLE(
        p.ATTRIBUTES,
        '$.materials[*]'
        COLUMNS (material VARCHAR(50) PATH '$')
    ) AS m
WHERE p.NAME = 'T-Shirt';

为提高性能而索引 JSON 列

查询大型 JSON 列可能很慢。为了优化性能，您可以对从 JSON 中提取的值创建索引。这通过创建一个虚拟生成列，然后对其进行索引来完成。

-- Add a virtual column for the brand
ALTER TABLE PRODUCTS
ADD COLUMN brand_virtual VARCHAR(100) AS (ATTRIBUTES->>'$.brand') VIRTUAL;

-- Create an index on the new virtual column
CREATE INDEX idx_brand ON PRODUCTS (brand_virtual);

现在，像 WHERE ATTRIBUTES->>'$.brand' = 'ACME' 这样的查询将能够使用 idx_brand 索引，从而显著加快速度。

客户端程序中的 JSON

在应用程序中使用 JSON 时，您的数据库驱动程序通常可以自动处理序列化和反序列化。例如，在 Python 中，您可以直接将字典作为参数传递，并获取 JSON 数据，然后将其解析为 Python 对象。

示例：Python 与 mysql-connector

Python




import mysql.connector
import json

# --- Configuration ---
config = {
    'user': 'root',
    'password': 'password',
    'host': '127.0.0.1',
    'database': 'TUTORIALS',
}

# --- Data to Insert ---
new_product_name = 'Smartwatch'
new_product_attrs = {
    "brand": "TechGear",
    "display_type": "OLED",
    "features": ["GPS", "Heart Rate Monitor"],
    "water_resistant": True
}

# --- Database Interaction ---
try:
    with mysql.connector.connect(**config) as connection:
        with connection.cursor(dictionary=True) as cursor:
            print("Connection established.")

            # Insert a new product with JSON data
            # The connector can handle the dictionary, but explicit json.dumps is safer
            insert_query = "INSERT INTO PRODUCTS (NAME, ATTRIBUTES) VALUES (%s, %s)"
            cursor.execute(insert_query, (new_product_name, json.dumps(new_product_attrs)))
            connection.commit()
            product_id = cursor.lastrowid
            print(f"Inserted new product '{new_product_name}' with ID: {product_id}")

            # Fetch and display the new product's attributes
            select_query = "SELECT ATTRIBUTES FROM PRODUCTS WHERE ID = %s"
            cursor.execute(select_query, (product_id,))
            result = cursor.fetchone()

            if result and result['ATTRIBUTES']:
                # The connector returns the JSON as a string, so we parse it
                attributes_data = json.loads(result['ATTRIBUTES'])
                print("\nFetched attributes:")
                print(f"  - Brand: {attributes_data.get('brand')}")
                print(f"  - Features: {', '.join(attributes_data.get('features', []))}")

except mysql.connector.Error as err:
    print(f"Database error: {err}")
finally:
    print("\nExecution finished.")


Output
The expected output:

Connection established.
Inserted new product 'Smartwatch' with ID: 4

Fetched attributes:
  - Brand: TechGear
  - Features: GPS, Heart Rate Monitor

Execution finished.