MySQL 数据库实战教程

面向零基础用户的完整 MySQL 学习指南

1. MySQL 简介

MySQL 是全球最流行的开源关系型数据库管理系统（RDBMS），由瑞典 MySQL AB 公司开发，现归 Oracle 所有。它使用结构化查询语言（SQL）进行数据管理，具有以下特点：

开源免费：社区版可免费使用
跨平台：支持 Linux、Windows、macOS 等
高性能：读写速度快，适合 Web 应用
可靠性：被 Facebook、Twitter、YouTube 等大规模使用
生态丰富：大量工具和第三方支持

MySQL 采用客户端/服务器架构，核心组件包括：

组件	说明
`mysqld`	数据库服务器守护进程
`mysql`	命令行客户端工具
`mysqldump`	数据备份工具
`mysqladmin`	管理工具

2. 安装与配置

2.1 Ubuntu/Debian 安装

# 更新包索引
sudo apt update

# 安装 MySQL 服务器
sudo apt install mysql-server

# 启动服务
sudo systemctl start mysql

# 设置开机自启
sudo systemctl enable mysql

# 运行安全配置向导（设置 root 密码、删除匿名用户等）
sudo mysql_secure_installation

2.2 CentOS/RHEL 安装

# 安装 MySQL 仓库
sudo yum install mysql-server

# 启动服务
sudo systemctl start mysqld
sudo systemctl enable mysqld

# 获取临时密码
sudo grep 'temporary password' /var/log/mysqld.log

# 安全配置
sudo mysql_secure_installation

2.3 macOS 安装

# 使用 Homebrew
brew install mysql

# 启动服务
brew services start mysql

# 安全配置
mysql_secure_installation

2.4 连接数据库

# 本地连接
mysql -u root -p

# 指定主机和端口
mysql -h 127.0.0.1 -P 3306 -u root -p

# 指定数据库
mysql -u root -p my_database

2.5 核心配置文件

MySQL 配置文件通常位于 /etc/mysql/mysql.conf.d/mysqld.cnf（Linux）或 /etc/my.cnf：

[mysqld]
# 基础配置
port = 3306
bind-address = 127.0.0.1
datadir = /var/lib/mysql
socket = /var/run/mysqld/mysqld.sock

# 字符集
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# InnoDB 配置
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1

# 连接配置
max_connections = 200
wait_timeout = 600

# 慢查询日志
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

修改配置后需要重启服务：

sudo systemctl restart mysql

3. 数据库管理

3.1 创建数据库

-- 创建数据库（使用 UTF-8 编码）
CREATE DATABASE my_shop
    DEFAULT CHARACTER SET utf8mb4
    DEFAULT COLLATE utf8mb4_unicode_ci;

-- 如果不存在则创建
CREATE DATABASE IF NOT EXISTS my_shop
    DEFAULT CHARACTER SET utf8mb4;

3.2 查看数据库

-- 查看所有数据库
SHOW DATABASES;

-- 查看建库语句
SHOW CREATE DATABASE my_shop;

3.3 选择数据库

USE my_shop;

3.4 修改数据库

-- 修改字符集
ALTER DATABASE my_shop
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_general_ci;

3.5 删除数据库

-- 删除数据库（谨慎操作！）
DROP DATABASE IF EXISTS my_shop;

4. 数据类型详解

4.1 数值类型

类型	字节	范围（有符号）	用途
`TINYINT`	1	-128 ~ 127	状态标志、小整数
`SMALLINT`	2	-32768 ~ 32767	小范围整数
`MEDIUMINT`	3	-8388608 ~ 8388607	中等整数
`INT`	4	-21亿 ~ 21亿	常规整数、主键
`BIGINT`	8	极大范围	大数值、雪花ID
`FLOAT`	4	单精度浮点	不要求精度的小数
`DOUBLE`	8	双精度浮点	科学计算
`DECIMAL(M,D)`	可变	精确小数	金额、财务数据

-- DECIMAL 示例：M=总位数，D=小数位数
CREATE TABLE products (
    price DECIMAL(10, 2)  -- 最多10位，其中2位小数，如 99999999.99
);

4.2 字符串类型

类型	最大长度	特点
`CHAR(N)`	255 字节	定长，适合固定长度（如手机号）
`VARCHAR(N)`	65535 字节	变长，适合可变长度文本
`TEXT`	65535 字节	长文本，不计入行长度限制
`MEDIUMTEXT`	16M	中等长文本
`LONGTEXT`	4G	超长文本
`ENUM`	65535 个值	枚举，只能选预定义值
`SET`	64 个成员	集合，可多选

-- 选择建议
-- 手机号 → CHAR(11)
-- 用户名 → VARCHAR(50)
-- 文章内容 → TEXT 或 MEDIUMTEXT
-- 性别 → ENUM('男', '女', '未知')

4.3 日期和时间类型

类型	格式	范围
`DATE`	YYYY-MM-DD	1000-01-01 ~ 9999-12-31
`TIME`	HH:MM:SS	-838:59:59 ~ 838:59:59
`DATETIME`	YYYY-MM-DD HH:MM:SS	1000 ~ 9999
`TIMESTAMP`	YYYY-MM-DD HH:MM:SS	1970 ~ 2038
`YEAR`	YYYY	1901 ~ 2155

-- DATETIME vs TIMESTAMP
-- DATETIME：存储字面值，不受时区影响
-- TIMESTAMP：存储 UTC，显示时转换为当前时区
CREATE TABLE logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT NULL
);

4.4 二进制与 JSON 类型

-- JSON 类型（MySQL 5.7+）
CREATE TABLE configs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    settings JSON NOT NULL
);

INSERT INTO configs (settings) VALUES
('{"theme": "dark", "lang": "zh-CN", "notifications": true}');

-- 查询 JSON 字段
SELECT settings->>'$.theme' AS theme FROM configs;
-- 结果：dark

5. 表操作

5.1 创建表

CREATE TABLE users (
    id          INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
    username    VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
    email       VARCHAR(100) NOT NULL UNIQUE COMMENT '邮箱',
    password    VARCHAR(255) NOT NULL COMMENT '密码哈希',
    nickname    VARCHAR(50) DEFAULT '' COMMENT '昵称',
    avatar      VARCHAR(255) DEFAULT '' COMMENT '头像URL',
    status      TINYINT DEFAULT 1 COMMENT '状态：0禁用 1正常',
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_email (email),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

关键语法说明：

AUTO_INCREMENT：自动递增
PRIMARY KEY：主键
NOT NULL：不允许为空
UNIQUE：唯一约束
DEFAULT：默认值
COMMENT：字段注释
ON UPDATE CURRENT_TIMESTAMP：更新时自动修改时间

5.2 查看表结构

-- 查看表结构
DESC users;

-- 查看建表语句
SHOW CREATE TABLE users;

-- 查看当前数据库所有表
SHOW TABLES;

-- 查看表状态
SHOW TABLE STATUS LIKE 'users'\G

5.3 修改表结构

-- 添加字段
ALTER TABLE users ADD COLUMN phone VARCHAR(20) DEFAULT NULL COMMENT '手机号' AFTER nickname;

-- 修改字段类型
ALTER TABLE users MODIFY COLUMN nickname VARCHAR(100) NOT NULL DEFAULT '';

-- 修改字段名和类型
ALTER TABLE users CHANGE COLUMN phone mobile VARCHAR(20) DEFAULT NULL COMMENT '手机号';

-- 删除字段
ALTER TABLE users DROP COLUMN mobile;

-- 添加索引
ALTER TABLE users ADD INDEX idx_nickname (nickname);

-- 删除索引
ALTER TABLE users DROP INDEX idx_nickname;

-- 添加外键
ALTER TABLE orders ADD CONSTRAINT fk_orders_user
    FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE ON UPDATE CASCADE;

-- 修改表名
ALTER TABLE users RENAME TO members;
-- 或
RENAME TABLE members TO users;

5.4 删除表

-- 删除表
DROP TABLE IF EXISTS users;

-- 清空表数据（保留结构）
TRUNCATE TABLE users;

6. 数据操作（CRUD）

6.1 插入数据（INSERT）

-- 插入单条
INSERT INTO users (username, email, password, nickname)
VALUES ('zhangsan', 'zhangsan@example.com', SHA2('mypassword', 256), '张三');

-- 插入多条
INSERT INTO users (username, email, password, nickname) VALUES
('lisi',    'lisi@example.com',    SHA2('password1', 256), '李四'),
('wangwu',  'wangwu@example.com',  SHA2('password2', 256), '王五'),
('zhaoliu', 'zhaoliu@example.com', SHA2('password3', 256), '赵六');

-- 插入或忽略（遇到重复键不报错）
INSERT IGNORE INTO users (username, email, password)
VALUES ('zhangsan', 'new@example.com', SHA2('pass', 256));

-- 插入或更新（遇到重复键则更新）
INSERT INTO users (username, email, password, nickname)
VALUES ('zhangsan', 'updated@example.com', SHA2('newpass', 256), '张三更新')
ON DUPLICATE KEY UPDATE email = VALUES(email), nickname = VALUES(nickname);

6.2 查询数据（SELECT）

-- 基础查询
SELECT * FROM users;
SELECT username, email FROM users;

-- 条件查询
SELECT * FROM users WHERE status = 1;
SELECT * FROM users WHERE id IN (1, 3, 5);
SELECT * FROM users WHERE nickname LIKE '%张%';
SELECT * FROM users WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';
SELECT * FROM users WHERE email IS NOT NULL;

-- 排序
SELECT * FROM users ORDER BY created_at DESC;
SELECT * FROM users ORDER BY id DESC, username ASC;

-- 分页（第2页，每页10条）
SELECT * FROM users LIMIT 10 OFFSET 10;
-- 等价写法
SELECT * FROM users LIMIT 10, 10;

-- 去重
SELECT DISTINCT status FROM users;

-- 别名
SELECT username AS 用户名, email AS 邮箱 FROM users;

6.3 更新数据（UPDATE）

-- 更新单个字段
UPDATE users SET nickname = '新昵称' WHERE id = 1;

-- 更新多个字段
UPDATE users SET
    nickname = '张三三',
    email = 'zhangsan_new@example.com'
WHERE username = 'zhangsan';

-- 带条件的批量更新
UPDATE users SET status = 0
WHERE created_at < '2023-01-01' AND status = 1;

-- 使用 LIMIT 限制更新行数
UPDATE users SET status = 0 WHERE status = 1 LIMIT 100;

6.4 删除数据（DELETE）

-- 删除指定记录
DELETE FROM users WHERE id = 10;

-- 带条件删除
DELETE FROM users WHERE status = 0 AND created_at < '2023-01-01';

-- 限制删除行数
DELETE FROM users WHERE status = 0 LIMIT 100;

-- ⚠️ 危险操作：不带 WHERE 会删除所有数据
-- DELETE FROM users;

7. 高级查询

7.1 聚合函数

-- 常用聚合函数
SELECT
    COUNT(*) AS total,              -- 总数
    COUNT(DISTINCT email) AS unique_emails,  -- 去重计数
    MAX(id) AS max_id,              -- 最大值
    MIN(id) AS min_id,              -- 最小值
    AVG(id) AS avg_id               -- 平均值
FROM users WHERE status = 1;

-- GROUP BY 分组统计
SELECT
    status,
    COUNT(*) AS cnt
FROM users
GROUP BY status;

-- HAVING 过滤分组结果
SELECT
    DATE(created_at) AS reg_date,
    COUNT(*) AS daily_count
FROM users
GROUP BY DATE(created_at)
HAVING daily_count > 10
ORDER BY daily_count DESC;

7.2 多表连接查询

-- 假设有 orders 表
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    status ENUM('pending', 'paid', 'shipped', 'completed', 'cancelled') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- INNER JOIN（内连接：两表都匹配的记录）
SELECT u.username, o.id AS order_id, o.total_amount, o.created_at
FROM users u
INNER JOIN orders o ON u.id = o.user_id
ORDER BY o.created_at DESC;

-- LEFT JOIN（左连接：左表全部 + 右表匹配的）
SELECT u.username, COUNT(o.id) AS order_count, IFNULL(SUM(o.total_amount), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.status != 'cancelled'
GROUP BY u.id;

-- 多表连接
-- 假设有 order_items 表
SELECT
    u.username,
    o.id AS order_id,
    p.name AS product_name,
    oi.quantity,
    oi.price
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
WHERE o.status = 'completed'
ORDER BY o.created_at DESC;

7.3 子查询

-- WHERE 中的子查询
SELECT * FROM users
WHERE id IN (
    SELECT DISTINCT user_id FROM orders
    WHERE status = 'completed'
);

-- EXISTS 子查询（通常比 IN 更高效）
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.id AND o.status = 'completed'
);

-- FROM 中的子查询（派生表）
SELECT t.reg_month, t.cnt FROM (
    SELECT
        DATE_FORMAT(created_at, '%Y-%m') AS reg_month,
        COUNT(*) AS cnt
    FROM users
    GROUP BY DATE_FORMAT(created_at, '%Y-%m')
) t
ORDER BY t.reg_month;

7.4 UNION 合并查询

-- UNION 去重合并
SELECT username, email FROM users WHERE status = 1
UNION
SELECT username, email FROM users WHERE created_at > '2024-01-01';

-- UNION ALL 不去重（性能更好）
SELECT username, '活跃' AS type FROM users WHERE status = 1
UNION ALL
SELECT username, '禁用' AS type FROM users WHERE status = 0;

7.5 窗口函数（MySQL 8.0+）

-- ROW_NUMBER：行号
SELECT
    username,
    created_at,
    ROW_NUMBER() OVER (ORDER BY created_at DESC) AS rn
FROM users;

-- RANK：排名（有并列）
SELECT
    u.username,
    SUM(o.total_amount) AS total_spent,
    RANK() OVER (ORDER BY SUM(o.total_amount) DESC) AS spending_rank
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed'
GROUP BY u.id;

-- 分组排名
SELECT
    username,
    status,
    created_at,
    ROW_NUMBER() OVER (PARTITION BY status ORDER BY created_at DESC) AS group_rn
FROM users;

-- 累计求和
SELECT
    DATE(created_at) AS order_date,
    total_amount,
    SUM(total_amount) OVER (ORDER BY DATE(created_at)) AS running_total
FROM orders
WHERE status = 'completed';

8. 索引与优化

8.1 索引类型

-- 主键索引（自动创建）
CREATE TABLE example (
    id INT AUTO_INCREMENT PRIMARY KEY
);

-- 唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);

-- 普通索引
CREATE INDEX idx_username ON users(username);

-- 复合索引（多列索引）
CREATE INDEX idx_status_created ON users(status, created_at);

-- 前缀索引（对长字符串只索引前N个字符）
CREATE INDEX idx_email_prefix ON users(email(10));

-- 全文索引（用于全文搜索）
CREATE FULLTEXT INDEX idx_content ON articles(title, content);
-- 使用全文搜索
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('MySQL 教程' IN BOOLEAN MODE);

8.2 索引优化原则

最左前缀原则：复合索引 (a, b, c) 只有在查询条件从最左列开始时才生效。

-- ✅ 能用到索引
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3
WHERE a = 1 AND c = 3          -- 只用到 a

-- ❌ 用不到索引
WHERE b = 2
WHERE c = 3
WHERE b = 2 AND c = 3

索引失效的常见情况：

-- 1. 对索引列使用函数
WHERE YEAR(created_at) = 2024      -- ❌ 失效
WHERE created_at >= '2024-01-01'   -- ✅ 生效

-- 2. 隐式类型转换
WHERE phone = 13800138000           -- ❌ phone 是 VARCHAR，传入数字
WHERE phone = '13800138000'        -- ✅

-- 3. LIKE 左模糊
WHERE username LIKE '%san'          -- ❌ 失效
WHERE username LIKE 'zhang%'       -- ✅ 生效

-- 4. OR 条件中有非索引列
WHERE id = 1 OR nickname = '张三'   -- 如果 nickname 没索引，全表扫描

8.3 EXPLAIN 分析查询

-- 查看执行计划
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

EXPLAIN 输出关键字段：

字段	含义	期望值
`type`	访问类型	`const` > `eq_ref` > `ref` > `range` > `index` > `ALL`
`key`	实际使用的索引	不为 NULL
`rows`	预估扫描行数	越小越好
`Extra`	额外信息	`Using index` 表示覆盖索引

-- 详细分析
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE email = 'test@example.com'\G

9. 视图

视图是虚拟表，基于 SELECT 查询定义，不存储数据。

-- 创建视图：活跃用户的订单统计
CREATE VIEW v_user_order_stats AS
SELECT
    u.id,
    u.username,
    u.email,
    COUNT(o.id) AS order_count,
    IFNULL(SUM(o.total_amount), 0) AS total_spent,
    MAX(o.created_at) AS last_order_at
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'completed'
WHERE u.status = 1
GROUP BY u.id;

-- 使用视图（像普通表一样查询）
SELECT * FROM v_user_order_stats WHERE order_count > 5;
SELECT * FROM v_user_order_stats ORDER BY total_spent DESC LIMIT 10;

-- 修改视图
ALTER VIEW v_user_order_stats AS
SELECT u.id, u.username, COUNT(o.id) AS order_count
FROM users u LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

-- 删除视图
DROP VIEW IF EXISTS v_user_order_stats;

-- 查看视图定义
SHOW CREATE VIEW v_user_order_stats;

10. 存储过程

存储过程是预编译的 SQL 代码块，可包含逻辑控制语句。

10.1 基本存储过程

-- 修改分隔符（因为存储过程中会用到分号）
DELIMITER //

-- 创建存储过程：获取用户订单统计
CREATE PROCEDURE sp_get_user_stats(IN p_user_id INT)
BEGIN
    SELECT
        u.username,
        COUNT(o.id) AS order_count,
        IFNULL(SUM(o.total_amount), 0) AS total_spent
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'completed'
    WHERE u.id = p_user_id
    GROUP BY u.id;
END //

DELIMITER ;

-- 调用
CALL sp_get_user_stats(1);

10.2 带输入输出参数

DELIMITER //

-- 创建订单的存储过程
CREATE PROCEDURE sp_create_order(
    IN p_user_id INT,
    IN p_amount DECIMAL(10,2),
    OUT p_order_id INT
)
BEGIN
    -- 检查用户是否存在
    DECLARE v_user_exists INT DEFAULT 0;
    SELECT COUNT(*) INTO v_user_exists FROM users WHERE id = p_user_id AND status = 1;

    IF v_user_exists = 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = '用户不存在或已禁用';
    END IF;

    -- 创建订单
    INSERT INTO orders (user_id, total_amount, status)
    VALUES (p_user_id, p_amount, 'pending');

    -- 返回新订单ID
    SET p_order_id = LAST_INSERT_ID();
END //

DELIMITER ;

-- 调用并获取输出参数
CALL sp_create_order(1, 299.99, @new_order_id);
SELECT @new_order_id;

10.3 游标与循环

DELIMITER //

-- 批量处理：给所有活跃用户添加积分
CREATE PROCEDURE sp_add_points(IN p_points INT)
BEGIN
    DECLARE v_done INT DEFAULT FALSE;
    DECLARE v_user_id INT;
    DECLARE v_cursor CURSOR FOR SELECT id FROM users WHERE status = 1;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;

    OPEN v_cursor;

    read_loop: LOOP
        FETCH v_cursor INTO v_user_id;
        IF v_done THEN
            LEAVE read_loop;
        END IF;

        -- 假设有 user_points 表
        INSERT INTO user_points (user_id, points, reason)
        VALUES (v_user_id, p_points, '系统赠送积分')
        ON DUPLICATE KEY UPDATE points = points + p_points;
    END LOOP;

    CLOSE v_cursor;
END //

DELIMITER ;

CALL sp_add_points(100);

10.4 条件与循环

DELIMITER //

-- 根据消费金额计算会员等级
CREATE PROCEDURE sp_update_vip_level(IN p_user_id INT)
BEGIN
    DECLARE v_total DECIMAL(10,2) DEFAULT 0;
    DECLARE v_level VARCHAR(20) DEFAULT '普通';

    SELECT IFNULL(SUM(total_amount), 0) INTO v_total
    FROM orders
    WHERE user_id = p_user_id AND status = 'completed';

    IF v_total >= 10000 THEN
        SET v_level = '钻石会员';
    ELSEIF v_total >= 5000 THEN
        SET v_level = '金牌会员';
    ELSEIF v_total >= 1000 THEN
        SET v_level = '银牌会员';
    ELSE
        SET v_level = '普通会员';
    END IF;

    UPDATE users SET nickname = CONCAT(nickname, '【', v_level, '】')
    WHERE id = p_user_id;

    SELECT v_level AS member_level, v_total AS total_spent;
END //

DELIMITER ;

10.5 管理存储过程

-- 查看所有存储过程
SHOW PROCEDURE STATUS WHERE Db = 'my_shop';

-- 查看存储过程定义
SHOW CREATE PROCEDURE sp_get_user_stats;

-- 删除存储过程
DROP PROCEDURE IF EXISTS sp_get_user_stats;

11. 触发器

触发器在表的 INSERT、UPDATE、DELETE 操作前后自动执行。

11.1 INSERT 触发器

DELIMITER //

-- 新用户注册时自动创建用户配置
CREATE TRIGGER trg_after_user_insert
AFTER INSERT ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_settings (user_id, theme, language, created_at)
    VALUES (NEW.id, 'light', 'zh-CN', NOW());
END //

DELIMITER ;

11.2 UPDATE 触发器

DELIMITER //

-- 订单状态变更时记录日志
CREATE TRIGGER trg_after_order_update
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
    IF OLD.status != NEW.status THEN
        INSERT INTO order_logs (order_id, old_status, new_status, changed_at)
        VALUES (NEW.id, OLD.status, NEW.status, NOW());
    END IF;
END //

DELIMITER ;

11.3 DELETE 触发器

DELIMITER //

-- 删除用户前归档数据
CREATE TRIGGER trg_before_user_delete
BEFORE DELETE ON users
FOR EACH ROW
BEGIN
    -- 将用户数据移到归档表
    INSERT INTO users_archive (id, username, email, nickname, deleted_at)
    VALUES (OLD.id, OLD.username, OLD.email, OLD.nickname, NOW());

    -- 删除相关订单
    DELETE FROM orders WHERE user_id = OLD.id;
END //

DELIMITER ;

11.4 NEW 和 OLD 关键字

触发器类型	NEW	OLD
INSERT	✅ 新插入的行	❌ 不可用
UPDATE	✅ 修改后的值	✅ 修改前的值
DELETE	❌ 不可用	✅ 被删除的行

11.5 管理触发器

-- 查看触发器
SHOW TRIGGERS;
SHOW TRIGGERS LIKE 'users';

-- 删除触发器
DROP TRIGGER IF EXISTS trg_after_user_insert;

12. 事务与锁

12.1 事务基础

事务保证一组操作要么全部成功，要么全部回滚。

-- 开启事务
START TRANSACTION;

-- 转账示例：从用户1扣款，给用户2加款
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;

-- 检查余额是否足够
SELECT balance INTO @bal FROM accounts WHERE user_id = 1;
IF @bal < 0 THEN
    ROLLBACK;   -- 余额不足，回滚
ELSE
    COMMIT;     -- 提交事务
END IF;

12.2 事务特性（ACID）

特性	说明
Atomicity（原子性）	事务中的操作要么全做，要么全不做
Consistency（一致性）	事务前后数据库状态保持一致
Isolation（隔离性）	并发事务之间互不干扰
Durability（持久性）	事务提交后数据永久保存

12.3 隔离级别

-- 查看当前隔离级别
SELECT @@transaction_isolation;

-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

隔离级别	脏读	不可重复读	幻读
READ UNCOMMITTED	✅	✅	✅
READ COMMITTED	❌	✅	✅
REPEATABLE READ（默认）	❌	❌	✅
SERIALIZABLE	❌	❌	❌

12.4 锁机制

-- 共享锁（读锁）：其他事务可以读但不能写
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;

-- 排他锁（写锁）：其他事务不能读也不能写
SELECT * FROM users WHERE id = 1 FOR UPDATE;

-- 死锁处理：InnoDB 会自动检测并回滚较小的事务
-- 手动查看锁信息
SHOW ENGINE INNODB STATUS\G

13. 用户与权限管理

13.1 创建用户

-- 创建本地用户
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'StrongPassword123!';

-- 创建允许远程连接的用户
CREATE USER 'app_user'@'%' IDENTIFIED BY 'StrongPassword123!';

-- 创建指定IP段的用户
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'StrongPassword123!';

13.2 权限管理

-- 授予单库所有权限
GRANT ALL PRIVILEGES ON my_shop.* TO 'app_user'@'localhost';

-- 授予特定权限
GRANT SELECT, INSERT, UPDATE ON my_shop.users TO 'app_user'@'localhost';

-- 授予只读权限
GRANT SELECT ON my_shop.* TO 'reader'@'%';

-- 刷新权限
FLUSH PRIVILEGES;

-- 查看用户权限
SHOW GRANTS FOR 'app_user'@'localhost';

-- 撤销权限
REVOKE INSERT ON my_shop.users FROM 'app_user'@'localhost';

-- 删除用户
DROP USER 'app_user'@'localhost';

-- 修改密码
ALTER USER 'app_user'@'localhost' IDENTIFIED BY 'NewPassword456!';

13.3 权限最佳实践

-- 应用程序使用专用低权限账户
CREATE USER 'blog_app'@'%' IDENTIFIED BY 'BlogApp2024!';
GRANT SELECT, INSERT, UPDATE, DELETE ON blog_db.* TO 'blog_app'@'%';

-- 备份账户只需 SELECT 和 LOCK TABLES
CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'Backup2024!';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup_user'@'localhost';

-- 避免使用 root 连接应用
-- 避免使用 GRANT ALL ON *.*
-- 定期审计权限
SELECT user, host FROM mysql.user;

14. 备份与恢复

14.1 mysqldump 备份

-- 备份单个数据库
-- mysqldump -u root -p my_shop > my_shop_backup.sql

-- 备份多个数据库
-- mysqldump -u root -p --databases db1 db2 > multi_db_backup.sql

-- 备份所有数据库
-- mysqldump -u root -p --all-databases > all_backup.sql

-- 只备份表结构
-- mysqldump -u root -p --no-data my_shop > schema_only.sql

-- 只备份数据
-- mysqldump -u root -p --no-create-info my_shop > data_only.sql

-- 带压缩备份
-- mysqldump -u root -p my_shop | gzip > my_shop_backup.sql.gz

-- 备份特定表
-- mysqldump -u root -p my_shop users orders > tables_backup.sql

14.2 恢复数据

# 从 SQL 文件恢复
mysql -u root -p my_shop < my_shop_backup.sql

# 从压缩文件恢复
gunzip < my_shop_backup.sql.gz | mysql -u root -p my_shop

# 在 MySQL 命令行中恢复
# mysql> SOURCE /path/to/backup.sql;

14.3 定时备份脚本

#!/bin/bash
# backup_mysql.sh - MySQL 自动备份脚本

BACKUP_DIR="/var/backups/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
DB_NAME="my_shop"
RETENTION_DAYS=7

# 创建备份目录
mkdir -p "$BACKUP_DIR"

# 执行备份
mysqldump -u backup_user -p'Backup2024!' \
    --single-transaction \
    --routines \
    --triggers \
    --events \
    "$DB_NAME" | gzip > "$BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz"

# 检查备份是否成功
if [ $? -eq 0 ]; then
    echo "[$(date)] 备份成功: ${DB_NAME}_${DATE}.sql.gz"
else
    echo "[$(date)] 备份失败!" >&2
    exit 1
fi

# 删除超过保留天数的旧备份
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete
echo "[$(date)] 已清理 ${RETENTION_DAYS} 天前的备份"

加入定时任务：

# 每天凌晨2点执行备份
# crontab -e
# 0 2 * * * /home/user/backup_mysql.sh >> /var/log/mysql_backup.log 2>&1

14.4 逻辑备份 vs 物理备份

对比	逻辑备份 (mysqldump)	物理备份 (xtrabackup)
速度	较慢	快
文件大小	较小（SQL文本）	较大（数据文件副本）
恢复速度	较慢	快
跨版本	支持	不支持
锁表	可能短暂锁表	不锁表

15. 性能优化实战

15.1 慢查询分析

-- 开启慢查询日志
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;    -- 超过1秒记录
SET GLOBAL log_queries_not_using_indexes = 1;

-- 查看慢查询状态
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 查看慢查询统计
SHOW GLOBAL STATUS LIKE 'Slow_queries';

15.2 查询优化技巧

-- ❌ 避免 SELECT *
SELECT * FROM users;
-- ✅ 只查需要的列
SELECT id, username, email FROM users;

-- ❌ 大偏移量分页（扫描大量行）
SELECT * FROM orders LIMIT 1000000, 10;
-- ✅ 使用游标分页
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 10;

-- ❌ 在循环中执行查询
-- for user in users: SELECT * FROM orders WHERE user_id = user.id
-- ✅ 批量查询
SELECT * FROM orders WHERE user_id IN (1, 2, 3, 4, 5);

-- ❌ 子查询（某些场景下性能差）
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);
-- ✅ 使用 JOIN
SELECT DISTINCT u.* FROM users u INNER JOIN orders o ON u.id = o.user_id;

-- ❌ OR 条件可能导致全表扫描
SELECT * FROM users WHERE status = 1 OR username = 'admin';
-- ✅ 使用 UNION
SELECT * FROM users WHERE status = 1
UNION
SELECT * FROM users WHERE username = 'admin';

15.3 InnoDB 配置优化

-- 缓冲池大小（建议物理内存的 60-80%）
SET GLOBAL innodb_buffer_pool_size = 2147483648;  -- 2GB

-- 缓冲池实例数（减少锁竞争）
SET GLOBAL innodb_buffer_pool_instances = 8;

-- 日志文件大小
-- innodb_log_file_size = 256M（需在配置文件中修改并重启）

-- 刷盘策略
-- 1：最安全，每次提交都刷盘（默认）
-- 2：每秒刷盘，兼顾性能和安全
-- 0：完全由操作系统决定
SET GLOBAL innodb_flush_log_at_trx_commit = 2;

-- IO 容量
SET GLOBAL innodb_io_capacity = 2000;
SET GLOBAL innodb_io_capacity_max = 4000;

15.4 连接池与缓存

-- 查看连接状态
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Threads_running';
SHOW GLOBAL STATUS LIKE 'Max_used_connections';

-- 调整最大连接数
SET GLOBAL max_connections = 500;

-- 查询缓存（MySQL 8.0 已移除）
-- 对于 MySQL 5.7 及以下
-- SET GLOBAL query_cache_type = 1;
-- SET GLOBAL query_cache_size = 67108864;  -- 64MB

-- 表缓存
SET GLOBAL table_open_cache = 4000;
SET GLOBAL table_definition_cache = 2000;

-- 线程缓存
SET GLOBAL thread_cache_size = 64;

15.5 性能监控

-- 查看服务器状态
SHOW GLOBAL STATUS;

-- 关键指标
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';
SHOW GLOBAL STATUS LIKE 'Innodb_row_lock%';
SHOW GLOBAL STATUS LIKE 'Connections';
SHOW GLOBAL STATUS LIKE 'Queries';
SHOW GLOBAL STATUS LIKE 'Uptime';

-- 查看正在执行的查询
SHOW PROCESSLIST;
-- 或更详细的
SELECT * FROM information_schema.PROCESSLIST WHERE COMMAND != 'Sleep' ORDER BY TIME DESC;

-- 杀掉慢查询
KILL <process_id>;

16. 实战项目：博客系统数据库设计

16.1 需求分析

设计一个博客系统，核心功能包括：

用户注册与登录
文章发布与管理
分类与标签
评论系统
点赞与收藏

16.2 数据库创建

CREATE DATABASE IF NOT EXISTS blog_system
    DEFAULT CHARACTER SET utf8mb4
    DEFAULT COLLATE utf8mb4_unicode_ci;

USE blog_system;

16.3 表结构设计

-- ============================================
-- 1. 用户表
-- ============================================
CREATE TABLE users (
    id          BIGINT AUTO_INCREMENT PRIMARY KEY,
    username    VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
    email       VARCHAR(100) NOT NULL UNIQUE COMMENT '邮箱',
    password    VARCHAR(255) NOT NULL COMMENT '密码哈希',
    nickname    VARCHAR(50) NOT NULL DEFAULT '' COMMENT '昵称',
    avatar      VARCHAR(500) DEFAULT '' COMMENT '头像URL',
    bio         VARCHAR(500) DEFAULT '' COMMENT '个人简介',
    role        ENUM('user', 'editor', 'admin') DEFAULT 'user' COMMENT '角色',
    status      TINYINT DEFAULT 1 COMMENT '0禁用 1正常 2待验证',
    last_login  DATETIME DEFAULT NULL COMMENT '最后登录时间',
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_status (status),
    INDEX idx_role (role)
) ENGINE=InnoDB COMMENT='用户表';

-- ============================================
-- 2. 分类表
-- ============================================
CREATE TABLE categories (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(50) NOT NULL UNIQUE COMMENT '分类名称',
    slug        VARCHAR(50) NOT NULL UNIQUE COMMENT 'URL 别名',
    description VARCHAR(200) DEFAULT '' COMMENT '描述',
    sort_order  INT DEFAULT 0 COMMENT '排序',
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB COMMENT='文章分类表';

-- ============================================
-- 3. 标签表
-- ============================================
CREATE TABLE tags (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(50) NOT NULL UNIQUE COMMENT '标签名称',
    slug        VARCHAR(50) NOT NULL UNIQUE COMMENT 'URL 别名',
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB COMMENT='标签表';

-- ============================================
-- 4. 文章表
-- ============================================
CREATE TABLE articles (
    id              BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id         BIGINT NOT NULL COMMENT '作者ID',
    category_id     INT DEFAULT NULL COMMENT '分类ID',
    title           VARCHAR(200) NOT NULL COMMENT '标题',
    slug            VARCHAR(200) NOT NULL UNIQUE COMMENT 'URL 别名',
    summary         VARCHAR(500) DEFAULT '' COMMENT '摘要',
    content         LONGTEXT NOT NULL COMMENT '内容（Markdown）',
    cover_image     VARCHAR(500) DEFAULT '' COMMENT '封面图',
    status          ENUM('draft', 'published', 'archived') DEFAULT 'draft' COMMENT '状态',
    is_top          TINYINT DEFAULT 0 COMMENT '是否置顶',
    view_count      INT DEFAULT 0 COMMENT '浏览次数',
    like_count      INT DEFAULT 0 COMMENT '点赞数',
    comment_count   INT DEFAULT 0 COMMENT '评论数',
    published_at    DATETIME DEFAULT NULL COMMENT '发布时间',
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,

    INDEX idx_user_id (user_id),
    INDEX idx_category_id (category_id),
    INDEX idx_status_published (status, published_at DESC),
    INDEX idx_slug (slug),
    FULLTEXT INDEX idx_ft_title_content (title, content)
) ENGINE=InnoDB COMMENT='文章表';

-- ============================================
-- 5. 文章-标签关联表（多对多）
-- ============================================
CREATE TABLE article_tags (
    article_id  BIGINT NOT NULL,
    tag_id      INT NOT NULL,
    PRIMARY KEY (article_id, tag_id),
    FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='文章标签关联表';

-- ============================================
-- 6. 评论表（支持嵌套回复）
-- ============================================
CREATE TABLE comments (
    id          BIGINT AUTO_INCREMENT PRIMARY KEY,
    article_id  BIGINT NOT NULL COMMENT '文章ID',
    user_id     BIGINT DEFAULT NULL COMMENT '评论用户ID（NULL为游客）',
    parent_id   BIGINT DEFAULT NULL COMMENT '父评论ID（NULL为顶级评论）',
    nickname    VARCHAR(50) DEFAULT '' COMMENT '游客昵称',
    email       VARCHAR(100) DEFAULT '' COMMENT '游客邮箱',
    content     TEXT NOT NULL COMMENT '评论内容',
    status      TINYINT DEFAULT 1 COMMENT '0待审核 1已通过 2已拒绝',
    ip_address  VARCHAR(45) DEFAULT '' COMMENT '评论者IP',
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (parent_id) REFERENCES comments(id) ON DELETE CASCADE,

    INDEX idx_article_status (article_id, status),
    INDEX idx_parent (parent_id),
    INDEX idx_user_id (user_id)
) ENGINE=InnoDB COMMENT='评论表';

-- ============================================
-- 7. 点赞表
-- ============================================
CREATE TABLE likes (
    id          BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id     BIGINT NOT NULL,
    article_id  BIGINT NOT NULL,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    UNIQUE KEY uk_user_article (user_id, article_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='点赞表';

-- ============================================
-- 8. 收藏表
-- ============================================
CREATE TABLE favorites (
    id          BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id     BIGINT NOT NULL,
    article_id  BIGINT NOT NULL,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    UNIQUE KEY uk_user_article (user_id, article_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='收藏表';

16.4 初始化数据

-- 插入分类
INSERT INTO categories (name, slug, description, sort_order) VALUES
('技术',     'tech',       '技术相关文章',     1),
('生活',     'life',       '生活随笔',         2),
('读书',     'reading',    '读书笔记与书评',   3),
('项目实战', 'projects',   '项目实战经验分享', 4);

-- 插入标签
INSERT INTO tags (name, slug) VALUES
('MySQL',     'mysql'),
('Python',    'python'),
('JavaScript','javascript'),
('Docker',    'docker'),
('Linux',     'linux'),
('算法',      'algorithms'),
('设计模式',  'design-patterns'),
('前端',      'frontend'),
('后端',      'backend'),
('DevOps',    'devops');

-- 插入用户
INSERT INTO users (username, email, password, nickname, role) VALUES
('admin',   'admin@blog.com',   SHA2('admin123', 256), '管理员', 'admin'),
('editor',  'editor@blog.com',  SHA2('editor123', 256), '编辑小王', 'editor'),
('zhangsan', 'zhang@blog.com',  SHA2('user123', 256), '张三', 'user');

-- 插入文章
INSERT INTO articles (user_id, category_id, title, slug, summary, content, status, published_at) VALUES
(1, 1, 'MySQL 索引详解', 'mysql-index-guide',
 '本文详细讲解 MySQL 索引的原理和使用技巧...',
 '# MySQL 索引详解\n\n索引是数据库性能优化的核心...',
 'published', NOW()),

(1, 1, 'Docker 入门教程', 'docker-tutorial',
 '从零开始学习 Docker 容器化技术...',
 '# Docker 入门\n\nDocker 是一个开源的应用容器引擎...',
 'published', NOW()),

(2, 2, '我的2024年度总结', '2024-summary',
 '回顾2024年的成长与收获...',
 '# 2024 年度总结\n\n这一年发生了许多事...',
 'published', NOW());

-- 关联文章和标签
INSERT INTO article_tags (article_id, tag_id) VALUES
(1, 1), (1, 9),   -- MySQL文章 → MySQL, 后端
(2, 4), (2, 10),  -- Docker文章 → Docker, DevOps
(3, 9);            -- 总结文章 → 后端

16.5 常用业务查询

-- 查询文章列表（带作者、分类、标签）
SELECT
    a.id,
    a.title,
    a.slug,
    a.summary,
    a.view_count,
    a.like_count,
    a.published_at,
    u.nickname AS author_name,
    u.avatar AS author_avatar,
    c.name AS category_name
FROM articles a
INNER JOIN users u ON a.user_id = u.id
LEFT JOIN categories c ON a.category_id = c.id
WHERE a.status = 'published'
ORDER BY a.is_top DESC, a.published_at DESC
LIMIT 10;

-- 查询单篇文章详情（含标签）
SELECT
    a.*,
    u.nickname AS author_name,
    u.avatar AS author_avatar,
    u.bio AS author_bio,
    c.name AS category_name,
    GROUP_CONCAT(t.name) AS tags
FROM articles a
INNER JOIN users u ON a.user_id = u.id
LEFT JOIN categories c ON a.category_id = c.id
LEFT JOIN article_tags at2 ON a.id = at2.article_id
LEFT JOIN tags t ON at2.tag_id = t.id
WHERE a.slug = 'mysql-index-guide'
GROUP BY a.id;

-- 查询文章评论（含嵌套）
SELECT
    c.id,
    c.content,
    c.created_at,
    COALESCE(u.nickname, c.nickname) AS commenter,
    u.avatar,
    c.parent_id
FROM comments c
LEFT JOIN users u ON c.user_id = u.id
WHERE c.article_id = 1 AND c.status = 1
ORDER BY c.created_at ASC;

-- 统计每篇文章的评论数
SELECT
    a.id,
    a.title,
    COUNT(c.id) AS comment_count
FROM articles a
LEFT JOIN comments c ON a.id = c.article_id AND c.status = 1
WHERE a.status = 'published'
GROUP BY a.id
ORDER BY comment_count DESC;

-- 按标签查询文章
SELECT a.id, a.title, a.slug, a.published_at
FROM articles a
INNER JOIN article_tags at2 ON a.id = at2.article_id
INNER JOIN tags t ON at2.tag_id = t.id
WHERE t.slug = 'mysql' AND a.status = 'published'
ORDER BY a.published_at DESC;

-- 全文搜索
SELECT id, title, summary,
    MATCH(title, content) AGAINST('MySQL 索引' IN NATURAL LANGUAGE MODE) AS relevance
FROM articles
WHERE MATCH(title, content) AGAINST('MySQL 索引' IN NATURAL LANGUAGE MODE)
ORDER BY relevance DESC;

-- 月度文章统计
SELECT
    DATE_FORMAT(published_at, '%Y-%m') AS month,
    COUNT(*) AS article_count,
    SUM(view_count) AS total_views,
    SUM(like_count) AS total_likes
FROM articles
WHERE status = 'published' AND published_at >= '2024-01-01'
GROUP BY DATE_FORMAT(published_at, '%Y-%m')
ORDER BY month;

-- 热门文章排行（综合浏览和点赞）
SELECT
    a.id,
    a.title,
    a.view_count,
    a.like_count,
    (a.view_count * 1 + a.like_count * 3) AS hot_score
FROM articles a
WHERE a.status = 'published'
    AND a.published_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
ORDER BY hot_score DESC
LIMIT 10;

16.6 存储过程示例

DELIMITER //

-- 点赞/取消点赞
CREATE PROCEDURE sp_toggle_like(
    IN p_user_id BIGINT,
    IN p_article_id BIGINT,
    OUT p_liked TINYINT
)
BEGIN
    DECLARE v_exists INT DEFAULT 0;

    SELECT COUNT(*) INTO v_exists
    FROM likes WHERE user_id = p_user_id AND article_id = p_article_id;

    IF v_exists > 0 THEN
        -- 取消点赞
        DELETE FROM likes WHERE user_id = p_user_id AND article_id = p_article_id;
        UPDATE articles SET like_count = GREATEST(like_count - 1, 0) WHERE id = p_article_id;
        SET p_liked = 0;
    ELSE
        -- 点赞
        INSERT INTO likes (user_id, article_id) VALUES (p_user_id, p_article_id);
        UPDATE articles SET like_count = like_count + 1 WHERE id = p_article_id;
        SET p_liked = 1;
    END IF;
END //

DELIMITER ;

-- 调用点赞
CALL sp_toggle_like(1, 1, @is_liked);
SELECT @is_liked;  -- 1=已点赞, 0=已取消

16.7 触发器示例

DELIMITER //

-- 新评论自动更新文章评论计数
CREATE TRIGGER trg_after_comment_insert
AFTER INSERT ON comments
FOR EACH ROW
BEGIN
    IF NEW.status = 1 THEN
        UPDATE articles
        SET comment_count = comment_count + 1
        WHERE id = NEW.article_id;
    END IF;
END //

-- 删除评论时更新计数
CREATE TRIGGER trg_after_comment_delete
AFTER DELETE ON comments
FOR EACH ROW
BEGIN
    IF OLD.status = 1 THEN
        UPDATE articles
        SET comment_count = GREATEST(comment_count - 1, 0)
        WHERE id = OLD.article_id;
    END IF;
END //

DELIMITER ;

17. 常见问题与排错

17.1 连接问题

-- 错误：Access denied for user
-- 解决：检查用户名密码和权限
SELECT user, host FROM mysql.user;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

-- 错误：Can't connect to MySQL server
-- 解决：检查服务状态和端口
-- systemctl status mysql
-- netstat -tlnp | grep 3306

-- 错误：Too many connections
-- 解决：增大连接数
SET GLOBAL max_connections = 500;

17.2 性能问题

-- 查看表大小
SELECT
    table_name,
    ROUND(data_length / 1024 / 1024, 2) AS data_mb,
    ROUND(index_length / 1024 / 1024, 2) AS index_mb,
    table_rows
FROM information_schema.tables
WHERE table_schema = 'blog_system'
ORDER BY data_length DESC;

-- 查看索引使用情况
SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'blog_system';
SELECT * FROM sys.schema_redundant_indexes WHERE table_schema = 'blog_system';

-- 优化表（回收碎片空间）
OPTIMIZE TABLE articles;
ANALYZE TABLE articles;

17.3 数据修复

-- 修复表
REPAIR TABLE users;

-- 检查表
CHECK TABLE articles;

-- 从备份恢复单张表
-- mysql -u root -p blog_system < articles_backup.sql

-- 找回误删数据（如果有 binlog）
-- mysqlbinlog --start-datetime="2024-01-01 00:00:00" \
--   --stop-datetime="2024-01-01 12:00:00" \
--   /var/log/mysql/mysql-bin.000001 | mysql -u root -p

17.4 常用运维命令速查

-- 服务器信息
SELECT VERSION();                    -- MySQL 版本
SELECT NOW();                        -- 当前时间
SHOW VARIABLES LIKE '%timeout%';     -- 超时配置
SHOW VARIABLES LIKE '%buffer%';      -- 缓冲区配置

-- 连接信息
SHOW STATUS LIKE 'Threads%';         -- 线程状态
SHOW STATUS LIKE 'Connections';      -- 连接统计
SHOW PROCESSLIST;                    -- 当前连接

-- InnoDB 信息
SHOW ENGINE INNODB STATUS\G          -- InnoDB 详细状态
SHOW STATUS LIKE 'Innodb_buffer_pool%';  -- 缓冲池命中率

-- 计算缓冲池命中率（应 > 99%）
SELECT
    (1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100 AS hit_rate
FROM (
    SELECT
        VARIABLE_VALUE AS Innodb_buffer_pool_reads
    FROM performance_schema.global_status
    WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
) a, (
    SELECT
        VARIABLE_VALUE AS Innodb_buffer_pool_read_requests
    FROM performance_schema.global_status
    WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
) b;

总结

本教程涵盖了 MySQL 从入门到实战的核心知识点：

阶段	内容
基础	安装配置、数据类型、表操作、CRUD
进阶	连接查询、子查询、索引、视图、事务
高级	存储过程、触发器、用户权限、备份恢复
实战	博客系统完整数据库设计

学习建议：

动手实践：每节的 SQL 示例都在本地运行一遍
理解原理：不要只记语法，理解为什么这样设计
多用 EXPLAIN：每个查询都用 EXPLAIN 分析执行计划
备份先行：任何生产操作前先备份
持续学习：关注 MySQL 官方文档和版本更新

📅 最后更新：2026年5月

本文档为原创教程，可自由使用和分享。

MySQL数据库实战教程

教程简介