MySQL 8.0 新增功能函数和使用案例

MySQL 8.0 引入了众多新特性和功能，显著提升了性能、安全性和易用性。

窗口函数

允许在结果集的分区上执行计算，而无需将行分组为单个输出行，这使得复杂的分析查询（如排名、移动平均值等）更容易实现。

排名函数

为每一行分配一个唯一的连续序号。

• ROW_NUMBER()：为每一行分配一个唯一的连续序号（如果有相同值，排名也会不同）。

• RANK()：与 ROW_NUMBER() 类似（如果有相同值，排名也相同，下一个排名值会跳过）。

• DENSE_RANK()：与 RANK() 类似（如果有相同值，排名也相同，下一个排名值不会跳过）。

场景：查询每个班按分数排序的名次

ROW_NUMBER() 实现排名

-- 相同分数名次也不同
SELECT 
    class,
    student,
    score,
    ROW_NUMBER() OVER (PARTITION BY class ORDER BY score DESC) AS row_num
FROM scores
ORDER BY class, row_num;

-- 结果:
-- | class | student | score | row_num |
-- |-------|---------|-------|---------|
-- | A     | Bob     | 90    | 1       |
-- | A     | Alice   | 85    | 2       |
-- | A     | Charlie | 85    | 3       |
-- | A     | Ali	 | 82    | 4       |
-- | B     | David   | 92    | 1       |
-- | B     | Frank   | 90    | 2       |
-- | B     | Eve     | 88    | 3       |

RANK() 实现排名

-- 相同分数名次相同，但是后面会占用一个名次
SELECT 
    class,
    student,
    score,
    RANK() OVER (PARTITION BY class ORDER BY score DESC) AS rank_num
FROM scores
ORDER BY class, rank_num;

-- 结果:
-- | class | student | score | rank_num |
-- |-------|---------|-------|----------|
-- | A     | Bob     | 90    | 1        |
-- | A     | Alice   | 85    | 2        |   Alice 和 Charlie 并列第2，后面没有第3名，会直接跳到了4
-- | A     | Charlie | 85    | 2        |
-- | A     | Ali	 | 82    | 4        |	Ali 直接是第4名
-- | B     | David   | 92    | 1        |
-- | B     | Frank   | 90    | 2        |
-- | B     | Eve     | 88    | 3        |

DENSE_RANK() 实现排名

-- 相同分数名次相同，后面的名次正常
SELECT 
    class,
    student,
    score,
    DENSE_RANK() OVER (PARTITION BY class ORDER BY score DESC) AS dense_rank_num
FROM scores
ORDER BY class, dense_rank_num;

-- 结果:
-- | class | student | score | rank_num |
-- |-------|---------|-------|----------|
-- | A     | Bob     | 90    | 1        |
-- | A     | Alice   | 85    | 2        |   Alice 和 Charlie 并列第2，后面就是第3名
-- | A     | Charlie | 85    | 2        |
-- | A     | Ali	 | 82    | 3        |	
-- | B     | David   | 92    | 1        |
-- | B     | Frank   | 90    | 2        |
-- | B     | Eve     | 88    | 3        |

值函数

用于访问窗口中其他行的值。

• LAG()：访问当前行之前某一行的值。

• LEAD()：访问当前行之后某一行的值。

• FIRST_VALUE()：获取窗口中第一行的值。

• LAST_VALUE()：获取窗口中最后一行的值。

场景：根据以下数据计算

(‘2023-01-01’, 1000.00),
(‘2023-01-02’, 1200.00),
(‘2023-01-03’, 1100.00),
(‘2023-01-04’, 1300.00);

**LAG()**的使用：

-- 计算每一天与前一天的销售额差异
SELECT 
    sale_date,
    amount,
    LAG(amount, 1) OVER (ORDER BY sale_date) AS prev_amount,
    amount - LAG(amount, 1) OVER (ORDER BY sale_date) AS daily_diff
FROM sales;

-- 结果:
-- | sale_date  | amount  | prev_amount | daily_diff |
-- |------------|---------|-------------|------------|
-- | 2023-01-01 | 1000.00 | NULL        | NULL       |
-- | 2023-01-02 | 1200.00 | 1000.00     | 200.00     |
-- | 2023-01-03 | 1100.00 | 1200.00     | -100.00    |
-- | 2023-01-04 | 1300.00 | 1100.00     | 200.00     |

LEAD() 的使用：

-- 计算每一天与后一天的销售额差异
SELECT 
    sale_date,
    amount,
    LEAD(amount, 1) OVER (ORDER BY sale_date) AS next_amount,
    LEAD(amount, 1) OVER (ORDER BY sale_date) - amount AS next_diff
FROM sales;

-- 结果:
-- | sale_date  | amount  | next_amount | next_diff |
-- |------------|---------|-------------|-----------|
-- | 2023-01-01 | 1000.00 | 1200.00     | 200.00    |
-- | 2023-01-02 | 1200.00 | 1100.00     | -100.00   |
-- | 2023-01-03 | 1100.00 | 1300.00     | 200.00    |
-- | 2023-01-04 | 1300.00 | NULL        | NULL      |

LAST_VALUE() 的使用

-- 查看每一天的销售额与该月最后一天销售额的比较
SELECT
	sale_date,
	amount,
	-- LAST_VALUE 默认是到当前行为止的窗口，需要使用 `ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING` 来获取到窗口末尾的值
	LAST_VALUE( amount ) OVER ( ORDER BY sale_date ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) AS last_amount_of_month,
	LAST_VALUE( amount ) OVER ( ORDER BY sale_date ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) - amount AS last_diff 
FROM
	sales;

-- 结果:
-- | sale_date  | amount  | last_amount_of_month |	last_diff	|
-- |------------|---------|----------------------|------------- |
-- | 2023-01-01 | 1000.00 | 1300.00              |	300.00		|
-- | 2023-01-02 | 1200.00 | 1300.00              |	100.00		|
-- | 2023-01-03 | 1100.00 | 1300.00              |	200.00		|
-- | 2023-01-04 | 1300.00 | 1300.00              |	0.00		|

聚合函数

普通的聚合函数（如 SUM, AVG, COUNT, MIN, MAX）也可以作为窗口函数使用。

• SUM()：计算窗口内的总和。

• AVG()：计算窗口内的平均值。

• COUNT()：计算窗口内的行数。

统计函数

-- 计算每一天的累计销售额
SELECT 
    sale_date,
    amount,
    SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM sales;

-- 结果:
-- | sale_date  | amount  | running_total |
-- |------------|---------|---------------|
-- | 2023-01-01 | 1000.00 | 1000.00       |
-- | 2023-01-02 | 1200.00 | 2200.00       |
-- | 2023-01-03 | 1100.00 | 3300.00       |
-- | 2023-01-04 | 1300.00 | 4600.00       |

平均函数

-- 计算每一天的移动平均销售额（例如，包含当前天和前两天）
SELECT 
    sale_date,
    amount,
    AVG(amount) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM sales;

-- 结果:
-- | sale_date  | amount  | moving_avg          |
-- |------------|---------|---------------------|
-- | 2023-01-01 | 1000.00 | 1000.00             | -- 只有1天
-- | 2023-01-02 | 1200.00 | 1100.00             | -- (1000+1200)/2
-- | 2023-01-03 | 1100.00 | 1100.00             | -- (1000+1200+1100)/3
-- | 2023-01-04 | 1300.00 | 1200.00             | -- (1200+1100+1300)/3

通用表表达式（CTE）

查询树形结构：包括非递归和递归 CTE，使查询结构更清晰，易于编写和维护复杂的查询。

递归查询

基础语法：

WITH RECURSIVE cte_name AS (
    -- 1. 锚点成员（Base Case）：定义递归的初始行
    initial_query
    UNION ALL | UNION
    -- 2. 递归成员（Recursive Member）：引用 CTE 自身，定义如何从上一行生成下一行
    recursive_query
)
SELECT ... FROM cte_name ...;

-- cte_name: 别名
-- initial_query: 返回递归的起始行，不引用 cte_name 本身。
-- UNION ALL: 通常使用 UNION ALL，因为递归需要合并所有层级的结果。UNION 会去重，可能导致性能问题或意外结果。
-- recursive_query: 引用 cte_name，定义如何从当前行找到下一级的行。

场景：组织架构中查找某员工的所有上级。其中每个员工只有一个上级

-- 递归查询“小明”的所有上级（包括间接上级），manager_id为上级ID
WITH RECURSIVE manager_chain AS (
    SELECT id, name, manager_id FROM org_chart WHERE name = '小明'
    UNION ALL
    SELECT oc.id, oc.name, oc.manager_id FROM org_chart oc
    INNER JOIN manager_chain mc ON oc.id = mc.manager_id
)
SELECT name FROM manager_chain WHERE name != '小明';

场景：查找某员工及其所有下级员工。

-- 查找 'CTO' (id=2) 及其所有下级员工
WITH RECURSIVE subordinates AS (
    -- 锚点：从 CTO 开始
    SELECT id, name, manager_id, 0 AS level FROM employees WHERE id = 2 -- 起始点

    UNION ALL

    -- 递归：找到 CTO 的直接下属，然后是下属的下属...
    SELECT e.id, e.name, e.manager_id, s.level + 1 FROM employees e
    INNER JOIN subordinates s ON e.manager_id = s.id
)
SELECT id, name, level FROM subordinates
ORDER BY level, id;

场景：生成一个从 1 到 10 的数字列表。

WITH RECURSIVE number_series AS (
    -- 锚点：从 1 开始
    SELECT 1 AS n

    UNION ALL

    -- 递归：n 递增
    SELECT n + 1
    FROM number_series
    WHERE n < 10 -- 递归终止条件：当 n >= 10 时停止
)
SELECT n FROM number_series;

非递归查询

基础语法：

WITH cte_name [(column_list)] AS (
    query_definition
)
SELECT ... FROM cte_name ...;

-- cte_name: 起的别名。
-- column_list: 可选，用于明确指定 CTE 的列名。
-- query_definition: 生成中间结果的 SELECT 语句。

场景：找出每个客户在 2023 年的总订单金额，只显示总金额超过 5000 的客户。

(1, ‘2023-01-15’, 2000.00),
(1, ‘2023-02-20’, 3500.00),
(2, ‘2023-03-10’, 1500.00),
(2, ‘2023-04-05’, 2000.00),
(3, ‘2023-05-12’, 6000.00),
(1, ‘2022-12-31’, 1000.00);

-- 不使用 CTE （嵌套子查询）：
SELECT 
    customer_id,
    total_amount
FROM (
    SELECT 
        customer_id, 
        SUM(order_amount) AS total_amount
    FROM orders
    WHERE YEAR(order_date) = 2023
    GROUP BY customer_id
) AS yearly_totals
WHERE total_amount > 5000;

-- 使用非递归 CTE ：
WITH yearly_totals AS (
    SELECT 
        customer_id, 
        SUM(order_amount) AS total_amount
    FROM orders
    WHERE YEAR(order_date) = 2023 -- 筛选2023年的订单
    GROUP BY customer_id         -- 按客户分组计算总金额
)
SELECT 
    customer_id,
    total_amount
FROM yearly_totals
WHERE total_amount > 5000;       -- 筛选总金额大于5000的客户

-- 结果:
-- | customer_id | total_amount |
-- |-------------|--------------|
-- | 1           | 5500.00      | -- 2000 + 3500
-- | 3           | 6000.00      |

CTE 链式计算

场景：计算每个部门的平均工资，然后找出高于公司整体平均工资的部门。

(‘Alice’, ‘Engineering’, 8000.00),
(‘Bob’, ‘Engineering’, 9000.00),
(‘Charlie’, ‘Sales’, 6000.00),
(‘David’, ‘Sales’, 7000.00),
(‘Eve’, ‘HR’, 5500.00);

WITH 
-- CTE 1: 计算公司整体平均工资
company_avg AS (
    SELECT AVG(salary) AS avg_salary FROM employees
),
-- CTE 2: 计算每个部门的平均工资
dept_avg AS (
    SELECT 
        department, 
        AVG(salary) AS avg_dept_salary
    FROM employees
    GROUP BY department
)
-- 主查询：连接两个 CTE 的结果
SELECT 
    dept_avg.department,
    dept_avg.avg_dept_salary
FROM dept_avg
CROSS JOIN company_avg -- 交叉连接，将公司平均工资与每个部门平均工资关联
WHERE dept_avg.avg_dept_salary > company_avg.avg_salary;

-- 结果:
-- | department  | avg_dept_salary |
-- |-------------|-----------------|
-- | Engineering | 8500.00         | -- 8500 > 7100 (公司平均)
-- | Sales       | 6500.00         | -- 6500 < 7100 (公司平均), 不会出现在结果中
-- | HR          | 5500.00         | -- 5500 < 7100 (公司平均), 不会出现在结果中

结合窗口函数

场景：在每个部门内，找出薪资最高的员工，并显示该部门的最高薪资。

WITH ranked_employees AS (
    SELECT 
        name,
        department,
        salary,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn,
        MAX(salary) OVER (PARTITION BY department) AS max_dept_salary -- 窗口函数计算部门最高薪资
    FROM employees
)
SELECT 
    name,
    department,
    salary,
    max_dept_salary
FROM ranked_employees
WHERE rn = 1; -- 只取每个部门排序第一（薪资最高）的员工

-- 结果:
-- | name  | department  | salary  | max_dept_salary |
-- |-------|-------------|---------|-----------------|
-- | Bob   | Engineering | 9000.00 | 9000.00         |
-- | David | Sales       | 7000.00 | 7000.00         |
-- | Eve   | HR          | 5500.00 | 5500.00         |

索引优化

联合索引

索引跳跃扫描（ index skip scan） 是 MySQL 8.0 的重要优化特性。它允许在跳过复合索引最左列的情况下仍能使用索引，前提是前导列取值少（低基数），能显著提升特定查询的性能，避免不必要的全表扫描。

核心思想：当查询条件跳过了复合索引的前导列（第一列），但该前导列的**取值数量较少（低基数）**时，优化器会：

1. 枚举前导列的每一个可能值；
2. 对每个值，执行一次“常规的最左前缀扫描”；
3. 合并所有结果。

本质上是“用多个小范围扫描，代替一次全表扫描”。

在不符合组合索引最左前缀的原则的情况，优化器依然能使用组合索引。

# 打开 skip_scan特性
 set session optimizer_switch='skip_scan=on';
 set optimizer_switch='skip_scan=on';

# 示例：表t1有一个联合索引idx_u1(rank1,rank2)
select * from t1 where rank2 = 30    # 这在8.0之前，基于最左匹配原则，不会使用联合索引
# 8.0 之后的原理，会转换为以下方式查询
select * from t1 where rank1 = 1 and rank2 = 30
union all
select * from t1 where rank1 = 5 and rank2 = 30
# MySQL其实内部自己把左边的列做了一次DISTINCT 。适合在这种左边字段的唯一值较少的情况下，效率来的高。比如性别，状态等等。如果值很多，就不会选择 ISS，而选了FULL INDEX SCAN。

限制条件

• 前缀的值数据量少，过大不会使用ISS

• select 选择的字段不能包含非该联合索引的字段，在其他联合索引的也无效如c1 字段在组合索引里面可以 ，并且这个组合索引的前缀值较少，会使用到该联合索引
  select * 的sql 就走不了skip scan

• sql 中不能带 group by或者distinct 语法

• Skip scan仅支持单表查询，多表关联是无法使用该特性。

• 对于组合索引 ([A_1, …, A_k,] B_1, …, B_m, C [, D_1, …, D_n])，A,D 可以为空，但是B ,C 字段不能为空。

示例：

phone 包含了两个联合索引，其中name散列度较高，几乎唯一，数据量大。gender表示性别的状态，数据量小。

此时SSI，使用的联合索引为 gender、phone的。不会使用跟name联合的索引

会用到ISS的sql：
# 查询中包含phone, gender的任意一列
explain select phone, gender from app_user where phone = '18103430049'

不会用到ISS的sql：
# 除了phone, gender的任意一列的其他列
explain select name from app_user where phone = '18103430049'
explain select * from app_user where phone = '18103430049'

隐藏索引

• 软删除：当索引不需要时，只需要将这个索引先设置为隐藏索引，使查询优化器不再使用这个索引，但是，此时这个索引还是需要MySQL后台进行维护，当确认将这个索引设置为隐藏索引系统不会受到影响时，再将索引彻底删除。

• 灰度发布：创建索引时，首先将索引设置为隐藏索引，通过修改查询优化器的开关，使隐藏索引对查询优化器可见，通过explain对索引进行测试，确认这个索引有效，某些查询可以使用到这个索引，就可以将其设置为可见索引，完成灰度发布的效果。

# 表示优化器是否使用不可见索引，默认为off不使用
set session optimizer_switch="use_invisible_indexes=on"; 
# 查看查询优化器的开关和其他开关
select @@optimizer_switch
# 创建隐藏索引，在其后添加 关键字 invisible
create indextest_idx on employees(j) invisible;

# 将字段j上的隐藏索引j_idx设置为可见
alter table employees alter index idx_dept visible;
# 将字段j上的隐藏索引j_idx设置为不可见
alter table employees alter index idx_dept invisible;

-- 查看索引状态
SHOW INDEX FROM employees WHERE Key_name = 'idx_dept';

降序索引

• 只有InnoDB存储引擎支持降序索引，只支持BTREE降序索引。

• 不再对GROUP BY操作进行隐式排序

# 创建表并指定排序索引 idx1，包含排序关键字
# 使用c1字段降序，c2字段升序排序。在8.0中使用索引，之前版本不使用索引
create table if not exists t2(c1 int, c2 int, index idx1(c1 asc, c2 desc));

# 8.0 之前c2字段上会进行升序排序操作，之后版本并没有排序
select count(*), c2 from t2 group by c2;
# 在MySQL 8.x中如果需要对c2字段进行排序，则需要使用order by语句明确指定排序规则
select count(*), c2 from t2 group by c2 order by c2;

函数索引

函数索引允许你基于表达式创建索引，例如 INDEX(UPPER(name)) 或 INDEX(DATE(created_at))。

• 在索引中使用函数（表达式）的值。 基于虚拟列功能实现

• 支持JSON数据的索引

-- 语法
CREATE INDEX index_name ON table_name ((expression));

-- 创建函数索引(大小写不敏感查询)
CREATE INDEX idx_name_upper ON users ((UPPER(name)));

-- 创建函数索引,日期部分查询(查询某一天)
CREATE INDEX idx_created_date ON user_logs ((DATE(created_at)));

-- 创建函数索引，字符串处理(邮箱域名)
CREATE INDEX idx_email_domain ON accounts ((SUBSTRING_INDEX(email, '@', -1)));

-- 创建函数索引,JSON 字段提取(查询 JSON 字段中的特定值)
CREATE INDEX idx_category ON products ((JSON_EXTRACT(details, '$.category')));

JSON 相关

对 JSON 字段的操作函数

JSON_TABLE

将 JSON 字段转成关系表输出，同时可以实现统计等聚合查询。

场景：存储了用户兴趣的 JSON 字段，想统计每种兴趣人数。

-- (1, '张三', '["阅读", "游泳", "编程"]'),
-- (2, '李四', '["摄影", "阅读"]');

-- 使用 JSON_TABLE 把 JSON 数组展开成行
SELECT interest, COUNT(*) as count
FROM users,
     JSON_TABLE(interests, '$[*]' COLUMNS (interest VARCHAR(50) PATH '$')) AS jt
GROUP BY interest;

输出如下：

interest	count
阅读	2
游泳	1
编程	1
摄影	1

JSON 文档验证

创建表时添加 JSON 验证约束

CREATE TABLE table_name (
    id INT AUTO_INCREMENT PRIMARY KEY,
    data JSON,
    CONSTRAINT json_valid CHECK (
        JSON_SCHEMA_VALID('schema_definition', data)
    )
);

-- JSON_SCHEMA_VALID(schema, json_doc)：返回 1（有效）或 0（无效）
-- schema_definition：一个合法的 JSON Schema 字符串

场景：用户信息必须包含 name（字符串）和 age（整数），且 age 在 18~100 之间。

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    profile JSON,
    CONSTRAINT valid_profile CHECK (
        JSON_SCHEMA_VALID(
            '{
                "type": "object",
                "properties": {
                    "name": {"type": "string"},
                    "age": {"type": "integer", "minimum": 18, "maximum": 100}
                },
                "required": ["name", "age"]
            }',
            profile
        )
    )
);

-- 插入合法数据
INSERT INTO users (profile) VALUES ('{"name": "张三", "age": 25}');

-- 插入合法数据（带额外字段）
INSERT INTO users (profile) VALUES ('{"name": "李四", "age": 30, "email": "li@example.com"}'); 

-- 插入非法数据：age 不是整数
INSERT INTO users (profile) VALUES ('{"name": "赵六", "age": "thirty"}');
-- ERROR 3819: Check constraint 'valid_profile' is violated.

-- 插入非法数据：age 超出范围
INSERT INTO users (profile) VALUES ('{"name": "钱七", "age": 150}');
-- ERROR 3819: Check constraint 'valid_profile' is violated.

场景：订单包含一个 items 数组，每个项必须有 product_id（整数）和 quantity（正整数）。

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_data JSON,
    CONSTRAINT valid_order CHECK (
        JSON_SCHEMA_VALID(
            '{
                "type": "object",
                "properties": {
                    "order_id": {"type": "string"},
                    "items": {
                        "type": "array",
                        "items": {
                            "type": "object",
                            "properties": {
                                "product_id": {"type": "integer"},
                                "quantity": {"type": "integer", "minimum": 1}
                            },
                            "required": ["product_id", "quantity"]
                        }
                    }
                },
                "required": ["order_id", "items"]
            }',
            order_data
        )
    )
);

-- 合法插入
INSERT INTO orders (order_data) VALUES 
('{
    "order_id": "ORD-001",
    "items": [
        {"product_id": 101, "quantity": 2},
        {"product_id": 102, "quantity": 1}
    ]
}');

-- 非法：quantity 为 0
INSERT INTO orders (order_data) VALUES 
('{
    "order_id": "ORD-002",
    "items": [{"product_id": 103, "quantity": 0}]
}');
-- ERROR 3819: Check constraint 'valid_order' is violated.

场景：确保 email 字段是合法邮箱格式。

-- MySQL 支持内置格式：email, date, date-time, uri 等
CREATE TABLE contacts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    contact_info JSON,
    CONSTRAINT valid_email CHECK (
        JSON_SCHEMA_VALID(
            '{
                "type": "object",
                "properties": {
                    "name": {"type": "string"},
                    "email": {
                        "type": "string",
                        "format": "email"
                    }
                },
                "required": ["name", "email"]
            }',
            contact_info
        )
    )
);

-- 合法
INSERT INTO contacts (contact_info) VALUES ('{"name": "张三", "email": "zhangsan@example.com"}');

-- 非法：不是邮箱格式
INSERT INTO contacts (contact_info) VALUES ('{"name": "李四", "email": "not-an-email"}');
-- ERROR 3819: Check constraint 'valid_email' is violated.

JSON 部分更新

只修改 JSON 文档中发生变化的部分，而不是重写整个文档！使用了一种称为 “In-Place JSON Patching” 的机制：

• InnoDB 将 JSON 文档存储为一种内部二进制格式（基于 BSON 类似结构）；

• 当执行 JSON_SET，JSON_REPLACE，JSON_REMOVE 等操作时，引擎会：

  • 定位到需要修改的具体节点；
  • 只更新该节点及其父路径上的元数据；
  • 避免重写未变化的部分；

• 如果修改导致文档大小显著增加，仍可能触发完整重写，但大多数小修改是原地完成的。

INSERT INTO products (data) VALUES (
'{
  "id": 1001,
  "name": "智能手机",
  "price": 2999.00,
  "spec": {
    "brand": "某品牌",
    "model": "X1",
    "storage": "128GB",
    "color": ["黑色", "白色"]
  },
  "stock": {
    "warehouse_a": 50,
    "warehouse_b": 30,
    "total": 80
  },
  "tags": ["热销", "新品", "智能"]
}'
);


-- 更新 price 字段
UPDATE products 
SET data = JSON_SET(data, '$.price', 2799.00) 
WHERE id = 1;
-- 删除某个颜色选项
UPDATE products 
SET data = JSON_REMOVE(data, '$.spec.color[1]') 
WHERE id = 1;
-- 替换整个子对象
UPDATE products 
SET data = JSON_REPLACE(data, '$.spec.model', 'X2 Pro') 
WHERE id = 1;

只有 $.price 等指定字段被修改，其他字段不会被重写。减少日志写入量（Redo Log、Undo Log），提升性能。

其他优化

• 简化权限管理：引入了角色的概念，给一组开发人员统一授予读写权限

• 持久化变量：重启后配置不丢失

• 原子 DDL：DDL 语句现在是原子性的，要么完全成功，要么失败并回滚，保证了数据字典的一致性。

• 数据字典：引入了一个事务性数据字典，替代了旧的 .frm 文件，提高了元数据操作的一致性和可靠性。

• InnoDB 引擎增强: 包括自增计数器的持久化、在线 DDL 改进、临时表的改进等。

• 隐藏列 (Hidden Columns): 在 CREATE TABLE 或 ALTER TABLE 时可以指定列是否在 SELECT * 中显示。

• 正则表达式函数: 引入了 REGEXP_SUBSTR, REGEXP_INSTR, REGEXP_REPLACE 等函数。