第 11 章：MySQL 高级特性

在掌握了 MySQL 的基础知识和核心功能后，我们来学习一些高级特性。这些特性可以帮助我们更好地处理复杂的数据场景，提高数据库的性能和灵活性。本章将介绍分区表、外键约束、字符集与排序规则、临时表、游标、预处理语句以及 JSON 数据类型支持等高级功能。

本章内容概要

分区表
外键约束
字符集与排序规则
临时表
游标
预处理语句
JSON 数据类型支持
实战练习：使用高级特性优化应用

分区表

分区表是将一个大表的数据按照某种规则分割成多个较小的、更易管理的部分。每个分区可以独立存储和管理，从而提高查询性能和维护效率。

分区类型

1. RANGE 分区

按照连续的区间范围进行分区：

sql

-- 创建按年份范围分区的订单表
CREATE TABLE orders_partitioned (
    order_id INT AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (order_id, order_date)
)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 插入测试数据
INSERT INTO orders_partitioned (customer_id, order_date, amount) VALUES
(1, '2020-01-15', 100.00),
(2, '2021-03-20', 250.50),
(3, '2022-07-10', 180.75),
(4, '2023-11-05', 320.00),
(5, '2024-02-28', 150.25);

2. LIST 分区

按照离散的值列表进行分区：

sql

-- 创建按地区列表分区的用户表
CREATE TABLE users_partitioned (
    user_id INT AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    region ENUM('north', 'south', 'east', 'west') NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, region)
)
PARTITION BY LIST COLUMNS (region) (
    PARTITION p_north VALUES IN ('north'),
    PARTITION p_south VALUES IN ('south'),
    PARTITION p_east VALUES IN ('east'),
    PARTITION p_west VALUES IN ('west')
);

-- 插入测试数据
INSERT INTO users_partitioned (username, region) VALUES
('张三', 'north'),
('李四', 'south'),
('王五', 'east'),
('赵六', 'west');

3. HASH 分区

按照哈希函数的结果进行分区：

sql

-- 创建按哈希分区的订单表
CREATE TABLE orders_hash (
    order_id INT AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (order_id)
)
PARTITION BY HASH(customer_id)
PARTITIONS 4;

-- 插入测试数据
INSERT INTO orders_hash (customer_id, order_date, amount) VALUES
(1, '2023-01-15', 100.00),
(2, '2023-02-20', 250.50),
(3, '2023-03-10', 180.75),
(4, '2023-04-05', 320.00);

4. KEY 分区

类似于 HASH 分区，但使用 MySQL 内部的哈希函数：

sql

-- 创建按键分区的用户表
CREATE TABLE users_key (
    user_id INT AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id)
)
PARTITION BY KEY()
PARTITIONS 4;

分区管理

sql

-- 查看表的分区信息
SELECT 
    PARTITION_NAME,
    PARTITION_METHOD,
    PARTITION_EXPRESSION,
    TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS 
WHERE TABLE_NAME = 'orders_partitioned';

-- 添加新分区
ALTER TABLE orders_partitioned 
ADD PARTITION (PARTITION p2025 VALUES LESS THAN (2026));

-- 删除分区（会删除分区中的数据）
ALTER TABLE orders_partitioned 
DROP PARTITION p_future;

-- 重新组织分区
ALTER TABLE orders_partitioned 
REORGANIZE PARTITION p2023, p2024 INTO (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

分区查询优化

sql

-- 查询特定分区的数据（分区裁剪）
SELECT * FROM orders_partitioned 
WHERE order_date >= '2022-01-01' AND order_date < '2023-01-01';

-- 查看查询执行计划
EXPLAIN SELECT * FROM orders_partitioned 
WHERE order_date >= '2022-01-01' AND order_date < '2023-01-01';

外键约束

外键约束用于维护表之间的引用完整性，确保数据的一致性。

创建外键约束

sql

-- 创建父表
CREATE TABLE departments (
    dept_id INT AUTO_INCREMENT PRIMARY KEY,
    dept_name VARCHAR(50) NOT NULL UNIQUE
);

-- 创建子表并添加外键约束
CREATE TABLE employees (
    emp_id INT AUTO_INCREMENT PRIMARY KEY,
    emp_name VARCHAR(50) NOT NULL,
    dept_id INT,
    salary DECIMAL(10,2),
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

-- 插入测试数据
INSERT INTO departments (dept_name) VALUES ('技术部'), ('销售部'), ('人事部');
INSERT INTO employees (emp_name, dept_id, salary) VALUES 
('张三', 1, 8000.00),
('李四', 2, 7000.00),
('王五', 1, 9000.00);

外键约束选项

sql

-- 不同的外键操作选项
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON DELETE CASCADE    -- 删除父记录时级联删除子记录
        ON UPDATE CASCADE    -- 更新父记录时级联更新子记录
);

CREATE TABLE order_items (
    item_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
        ON DELETE RESTRICT   -- 限制删除父记录（默认）
        ON UPDATE NO ACTION  -- 不采取任何动作
);

管理外键约束

sql

-- 查看表的外键约束
SELECT 
    CONSTRAINT_NAME,
    TABLE_NAME,
    COLUMN_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE REFERENCED_TABLE_NAME IS NOT NULL 
AND TABLE_SCHEMA = 'your_database_name';

-- 添加外键约束
ALTER TABLE employees 
ADD CONSTRAINT fk_emp_dept 
FOREIGN KEY (dept_id) REFERENCES departments(dept_id);

-- 删除外键约束
ALTER TABLE employees 
DROP FOREIGN KEY fk_emp_dept;

字符集与排序规则

字符集和排序规则决定了数据库如何存储和比较字符数据。

查看字符集信息

sql

-- 查看服务器支持的字符集
SHOW CHARSET;

-- 查看服务器支持的排序规则
SHOW COLLATION;

-- 查看当前数据库的字符集和排序规则
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

设置字符集和排序规则

sql

-- 创建数据库时指定字符集和排序规则
CREATE DATABASE mydb 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

-- 创建表时指定字符集和排序规则
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 创建列时指定字符集和排序规则
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
    description TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
);

常用字符集和排序规则

sql

-- utf8mb4 是推荐的字符集，支持完整的 UTF-8
-- utf8mb4_unicode_ci 是推荐的排序规则，支持多语言正确排序

-- 不区分大小写的排序规则
utf8mb4_general_ci   -- 通用排序（较快）
utf8mb4_unicode_ci   -- Unicode 排序（更准确）

-- 区分大小写的排序规则
utf8mb4_bin          -- 二进制排序（区分大小写）

转换字符集

sql

-- 修改数据库字符集
ALTER DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 修改表字符集
ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 修改列字符集
ALTER TABLE users MODIFY username VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

临时表

临时表是一种特殊的表，只在当前会话中可见，会话结束时自动删除。

创建临时表

sql

-- 创建临时表
CREATE TEMPORARY TABLE temp_sales_report (
    product_id INT,
    product_name VARCHAR(100),
    total_sales DECIMAL(10,2),
    sales_count INT
);

-- 插入数据
INSERT INTO temp_sales_report (product_id, product_name, total_sales, sales_count)
SELECT 
    p.product_id,
    p.product_name,
    SUM(oi.quantity * oi.subtotal) AS total_sales,
    COUNT(*) AS sales_count
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.product_id, p.product_name;

-- 查询临时表
SELECT * FROM temp_sales_report ORDER BY total_sales DESC;

临时表特点

sql

-- 临时表的特点：
-- 1. 只在当前会话中可见
-- 2. 会话结束时自动删除
-- 3. 可以与普通表同名
-- 4. 不记录在系统表中

-- 创建与普通表同名的临时表
CREATE TEMPORARY TABLE users (
    temp_id INT,
    temp_name VARCHAR(50)
);

-- 查询临时表
SELECT * FROM users;  -- 返回临时表数据

-- 删除临时表
DROP TEMPORARY TABLE users;

-- 现在查询的是普通表
SELECT * FROM users;  -- 返回普通表数据

游标

游标允许在存储过程中逐行处理查询结果。

使用游标

sql

-- 创建使用游标的存储过程
DELIMITER //

CREATE PROCEDURE sp_process_users()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE user_id INT;
    DECLARE user_name VARCHAR(50);
    DECLARE user_email VARCHAR(100);
    
    -- 声明游标
    DECLARE user_cursor CURSOR FOR 
        SELECT id, name, email FROM users;
    
    -- 声明继续处理程序
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    -- 创建临时表存储处理结果
    CREATE TEMPORARY TABLE temp_user_stats (
        user_id INT,
        processed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );
    
    -- 打开游标
    OPEN user_cursor;
    
    -- 循环处理每一行
    read_loop: LOOP
        FETCH user_cursor INTO user_id, user_name, user_email;
        
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- 处理每一行数据
        INSERT INTO temp_user_stats (user_id) VALUES (user_id);
        
        -- 可以在这里添加更多的处理逻辑
        -- 例如：发送邮件、更新统计信息等
        
    END LOOP;
    
    -- 关闭游标
    CLOSE user_cursor;
    
    -- 返回处理结果
    SELECT * FROM temp_user_stats;
    
    -- 清理临时表
    DROP TEMPORARY TABLE temp_user_stats;
END //

DELIMITER ;

-- 调用存储过程
CALL sp_process_users();

游标操作

sql

-- 游标的主要操作：
-- DECLARE cursor_name CURSOR FOR select_statement;  -- 声明游标
-- OPEN cursor_name;                                 -- 打开游标
-- FETCH cursor_name INTO variables;                 -- 获取数据
-- CLOSE cursor_name;                                -- 关闭游标

-- 不同类型的游标处理程序：
-- CONTINUE HANDLER FOR NOT FOUND     -- 数据未找到
-- CONTINUE HANDLER FOR SQLSTATE '02000'  -- 同上
-- CONTINUE HANDLER FOR SQLEXCEPTION  -- SQL 异常

预处理语句

预处理语句可以提高执行效率，特别是对于重复执行的 SQL 语句。

使用预处理语句

sql

-- 准备预处理语句
PREPARE stmt1 FROM 'SELECT * FROM users WHERE id = ?';
PREPARE stmt2 FROM 'UPDATE users SET email = ? WHERE id = ?';

-- 设置参数
SET @user_id = 1;
SET @new_email = 'newemail@example.com';

-- 执行预处理语句
EXECUTE stmt1 USING @user_id;
EXECUTE stmt2 USING @new_email, @user_id;

-- 删除预处理语句
DEALLOCATE PREPARE stmt1;
DEALLOCATE PREPARE stmt2;

在存储过程中使用预处理语句

sql

DELIMITER //

CREATE PROCEDURE sp_dynamic_query(
    IN table_name VARCHAR(50),
    IN condition_column VARCHAR(50),
    IN condition_value VARCHAR(100)
)
BEGIN
    SET @sql = CONCAT('SELECT * FROM ', table_name, ' WHERE ', condition_column, ' = ?');
    PREPARE stmt FROM @sql;
    EXECUTE stmt USING condition_value;
    DEALLOCATE PREPARE stmt;
END //

DELIMITER ;

-- 调用存储过程
CALL sp_dynamic_query('users', 'name', '张三');

JSON 数据类型支持

MySQL 5.7+ 开始支持原生的 JSON 数据类型，可以存储和查询 JSON 格式的数据。

创建 JSON 字段

sql

-- 创建包含 JSON 字段的表
CREATE TABLE user_profiles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    profile_data JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入 JSON 数据
INSERT INTO user_profiles (user_id, profile_data) VALUES
(1, '{"name": "张三", "age": 25, "skills": ["Java", "MySQL", "Python"]}'),
(2, '{"name": "李四", "age": 30, "skills": ["JavaScript", "React", "Node.js"]}'),
(3, '{"name": "王五", "age": 28, "skills": ["Python", "Django", "PostgreSQL"]}');

-- 查询 JSON 数据
SELECT id, user_id, profile_data FROM user_profiles;

JSON 函数

sql

-- 提取 JSON 数据
SELECT 
    id,
    JSON_EXTRACT(profile_data, '$.name') AS name,
    JSON_EXTRACT(profile_data, '$.age') AS age
FROM user_profiles;

-- 使用箭头操作符（MySQL 5.7.13+）
SELECT 
    id,
    profile_data->'$.name' AS name,
    profile_data->'$.age' AS age
FROM user_profiles;

-- 提取并去除引号
SELECT 
    id,
    profile_data->>'$.name' AS name,
    profile_data->>'$.age' AS age
FROM user_profiles;

-- 查询包含特定值的记录
SELECT * FROM user_profiles 
WHERE JSON_CONTAINS(profile_data, '"Python"', '$.skills');

-- 查询数组长度
SELECT 
    id,
    JSON_LENGTH(profile_data, '$.skills') AS skill_count
FROM user_profiles;

-- 修改 JSON 数据
UPDATE user_profiles 
SET profile_data = JSON_SET(profile_data, '$.age', 26) 
WHERE user_id = 1;

-- 添加数组元素
UPDATE user_profiles 
SET profile_data = JSON_ARRAY_APPEND(profile_data, '$.skills', 'Docker') 
WHERE user_id = 1;

-- 删除 JSON 属性
UPDATE user_profiles 
SET profile_data = JSON_REMOVE(profile_data, '$.age') 
WHERE user_id = 1;

JSON 索引

sql

-- 创建虚拟列和索引以提高 JSON 查询性能
ALTER TABLE user_profiles 
ADD COLUMN name_virtual VARCHAR(50) 
GENERATED ALWAYS AS (profile_data->>'$.name') VIRTUAL;

-- 为虚拟列创建索引
CREATE INDEX idx_name ON user_profiles(name_virtual);

-- 现在可以高效地查询 JSON 数据
SELECT * FROM user_profiles WHERE name_virtual = '张三';

实战练习：使用高级特性优化应用

让我们通过一个实际的例子来练习使用这些高级特性。

1. 创建分区日志表

sql

-- 创建按月份分区的日志表
CREATE TABLE system_logs (
    log_id BIGINT AUTO_INCREMENT,
    log_level ENUM('DEBUG', 'INFO', 'WARN', 'ERROR') NOT NULL,
    module VARCHAR(50) NOT NULL,
    message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (log_id, created_at)
)
PARTITION BY RANGE (TO_DAYS(created_at)) (
    PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')),
    PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01')),
    PARTITION p202303 VALUES LESS THAN (TO_DAYS('2023-04-01')),
    PARTITION p202304 VALUES LESS THAN (TO_DAYS('2023-05-01')),
    PARTITION p202305 VALUES LESS THAN (TO_DAYS('2023-06-01')),
    PARTITION p202306 VALUES LESS THAN (TO_DAYS('2023-07-01')),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 插入测试数据
INSERT INTO system_logs (log_level, module, message) VALUES
('INFO', 'user', '用户登录成功'),
('ERROR', 'database', '数据库连接失败'),
('WARN', 'cache', '缓存即将过期'),
('DEBUG', 'api', 'API调用参数验证通过');

2. 创建带外键约束的订单系统

sql

-- 创建客户表
CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建产品表
CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock_quantity INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建订单表（带外键约束）
CREATE TABLE orders_enhanced (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    status ENUM('pending', 'confirmed', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    metadata JSON,  -- 存储订单元数据
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

-- 创建订单项表（带外键约束）
CREATE TABLE order_items_enhanced (
    item_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders_enhanced(order_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(product_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

-- 插入测试数据
INSERT INTO customers (customer_name, email, phone) VALUES
('张三', 'zhangsan@example.com', '13800138001'),
('李四', 'lisi@example.com', '13800138002');

INSERT INTO products (product_name, price, stock_quantity) VALUES
('笔记本电脑', 5999.00, 50),
('无线鼠标', 99.00, 200),
('机械键盘', 299.00, 100);

INSERT INTO orders_enhanced (customer_id, order_date, total_amount, metadata) VALUES
(1, '2023-05-15', 6397.00, 
 '{"payment_method": "alipay", "shipping_address": "北京市朝阳区"}'),
(2, '2023-05-16', 398.00,
 '{"payment_method": "wechat", "shipping_address": "上海市浦东新区"}');

INSERT INTO order_items_enhanced (order_id, product_id, quantity, unit_price) VALUES
(1, 1, 1, 5999.00),
(1, 2, 1, 99.00),
(1, 3, 1, 299.00),
(2, 2, 2, 99.00),
(2, 3, 1, 299.00);

3. 创建使用游标的统计存储过程

sql

DELIMITER //

CREATE PROCEDURE sp_generate_sales_report()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE customer_id INT;
    DECLARE customer_name VARCHAR(100);
    DECLARE total_spent DECIMAL(10,2) DEFAULT 0;
    
    -- 声明游标
    DECLARE customer_cursor CURSOR FOR
        SELECT c.customer_id, c.customer_name
        FROM customers c
        JOIN orders_enhanced o ON c.customer_id = o.customer_id;
    
    -- 声明处理程序
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    -- 创建临时表存储报告
    CREATE TEMPORARY TABLE temp_sales_report (
        customer_id INT,
        customer_name VARCHAR(100),
        total_spent DECIMAL(10,2),
        order_count INT
    );
    
    -- 打开游标
    OPEN customer_cursor;
    
    -- 处理每个客户
    customer_loop: LOOP
        FETCH customer_cursor INTO customer_id, customer_name;
        
        IF done THEN
            LEAVE customer_loop;
        END IF;
        
        -- 计算客户总消费
        SELECT COALESCE(SUM(total_amount), 0) INTO total_spent
        FROM orders_enhanced
        WHERE customer_id = customer_id;
        
        -- 插入到报告表
        INSERT INTO temp_sales_report (customer_id, customer_name, total_spent, order_count)
        SELECT 
            customer_id,
            customer_name,
            total_spent,
            COUNT(*) AS order_count
        FROM orders_enhanced
        WHERE customer_id = customer_id;
        
    END LOOP;
    
    -- 关闭游标
    CLOSE customer_cursor;
    
    -- 返回报告结果
    SELECT * FROM temp_sales_report ORDER BY total_spent DESC;
    
    -- 清理临时表
    DROP TEMPORARY TABLE temp_sales_report;
END //

DELIMITER ;

-- 调用存储过程
CALL sp_generate_sales_report();

4. 使用 JSON 数据的高级查询

sql

-- 查询使用支付宝支付的订单
SELECT 
    order_id,
    customer_id,
    total_amount,
    metadata->>'$.payment_method' AS payment_method,
    metadata->>'$.shipping_address' AS shipping_address
FROM orders_enhanced
WHERE JSON_EXTRACT(metadata, '$.payment_method') = 'alipay';

-- 更新订单元数据
UPDATE orders_enhanced
SET metadata = JSON_SET(metadata, '$.tracking_number', 'SF123456789CN')
WHERE order_id = 1;

-- 查询包含特定字段的 JSON 数据
SELECT * FROM orders_enhanced
WHERE JSON_CONTAINS_PATH(metadata, 'one', '$.tracking_number');

高级特性最佳实践

1. 分区表使用建议

sql

-- 适合分区的场景：
-- 1. 大表（千万级以上记录）
-- 2. 有明显的时间维度
-- 3. 经常按分区字段查询

-- 不适合分区的场景：
-- 1. 小表（万级以下记录）
-- 2. 频繁跨分区查询
-- 3. 复杂的分区策略

2. 外键约束使用建议

sql

-- 外键约束的优点：
-- 1. 保证数据一致性
-- 2. 自动维护引用完整性
-- 3. 简化应用层逻辑

-- 外键约束的缺点：
-- 1. 影响插入/更新性能
-- 2. 增加锁竞争
-- 3. 复杂的级联操作

3. JSON 数据类型使用建议

sql

-- 适合使用 JSON 的场景：
-- 1. 半结构化数据
-- 2. 配置信息
-- 3. 日志数据
-- 4. 不频繁查询的字段

-- 不适合使用 JSON 的场景：
-- 1. 需要频繁查询的字段
-- 2. 需要建立索引的字段
-- 3. 严格的结构化数据

小结

在本章中，我们学习了 MySQL 的高级特性：

分区表 - 提高大表查询性能和管理效率
外键约束 - 维护表间引用完整性
字符集与排序规则 - 处理多语言字符数据
临时表 - 会话级临时数据存储
游标 - 逐行处理查询结果
预处理语句 - 提高重复 SQL 执行效率
JSON 数据类型 - 存储和查询半结构化数据

这些高级特性可以帮助我们构建更强大、更灵活的数据库应用。在下一章中，我们将通过一个完整的项目实战来综合运用所学的所有知识。

第 11 章：MySQL 高级特性 ​

本章内容概要 ​

分区表 ​

分区类型 ​

1. RANGE 分区 ​

2. LIST 分区 ​

3. HASH 分区 ​

4. KEY 分区 ​

分区管理 ​

分区查询优化 ​

外键约束 ​

创建外键约束 ​

外键约束选项 ​

管理外键约束 ​

字符集与排序规则 ​

查看字符集信息 ​

设置字符集和排序规则 ​

常用字符集和排序规则 ​

转换字符集 ​

临时表 ​

创建临时表 ​

临时表特点 ​

游标 ​

使用游标 ​

游标操作 ​

预处理语句 ​

使用预处理语句 ​

在存储过程中使用预处理语句 ​

JSON 数据类型支持 ​

创建 JSON 字段 ​

JSON 函数 ​

JSON 索引 ​

实战练习：使用高级特性优化应用 ​

1. 创建分区日志表 ​

2. 创建带外键约束的订单系统 ​

3. 创建使用游标的统计存储过程 ​

4. 使用 JSON 数据的高级查询 ​

高级特性最佳实践 ​

1. 分区表使用建议 ​

2. 外键约束使用建议 ​

3. JSON 数据类型使用建议 ​

小结 ​