袋鼠知识

主题

第七章 MySQL 详解 ​

📖 ⏱️ 预计阅读时长

1. MySQL 概述与架构 ​

MySQL 是目前世界上使用最广泛的开源关系型数据库管理系统之一，由瑞典 MySQL AB 公司开发，现归属于 Oracle 公司。MySQL 以其高性能、高可靠性和易用性著称，被广泛应用于 Web 应用、企业信息系统和大规模互联网服务中。学习 MySQL 是掌握关系型数据库实践的重要环节。

1.1 MySQL 的诞生与发展 ​

MySQL 的名称来源于创始人 Michael Widenius 的女儿 "My"。1995 年，MySQL 1.0 首次发布。2000 年，MySQL 采用 GPL 协议开源，迅速在开源社区中获得广泛 adoption。2008 年 Sun 公司收购 MySQL AB，2009 年 Oracle 收购 Sun，MySQL 遂成为 Oracle 旗下产品。为避免商业收购带来的不确定性，MySQL 的原始开发者基于 MySQL 5.5 创建了 MariaDB 分支，二者在语法和接口上保持高度兼容。

1.2 MySQL 的核心特性 ​

  • 开源免费：社区版遵循 GPL 协议，可免费用于商业和非商业场景。
  • 跨平台：支持 Windows、Linux、macOS 等多种操作系统。
  • 存储引擎架构：采用可插拔存储引擎设计，InnoDB、MyISAM 等引擎各有适用场景。
  • 标准化：支持 SQL 标准，与第三章所学的 SQL 语言高度兼容。
  • 主从复制与集群：支持主从复制、组复制等高可用架构。

1.3 MySQL 体系结构 ​

MySQL 采用经典的客户端/服务器架构。服务器端主要包括以下层次：

  1. 连接层：负责客户端连接管理、身份认证、权限校验。
  2. SQL 层：负责 SQL 解析、查询优化、执行计划生成、缓存管理。
  3. 存储引擎层：负责数据的物理存储和检索。InnoDB 是 MySQL 5.5 之后的默认引擎，支持事务、行级锁和外键。

理解 MySQL 时，有一个特别重要的视角：它不是一套从上到下完全单一的实现，而是把通用 SQL 能力与可插拔存储引擎结合起来的平台。对用户来说，写的仍然是统一的 SQL；但对系统内部来说，事务、索引组织、锁粒度和崩溃恢复等关键行为，很大程度上由底层引擎决定。也正因为如此，学习 MySQL 不能只记住语法，还要知道某种行为究竟是 SQL 标准带来的，还是 InnoDB 这类引擎实现带来的。把这一层边界看清楚，后续学习索引、事务和恢复时会轻松很多。

2. MySQL 安装与配置 ​

2.1 安装方式概述 ​

MySQL 的安装方式因操作系统而异：

  • Linux：可通过包管理器（如 apt、yum）安装，或从官网下载二进制包解压使用。
  • Windows：推荐使用官方 MSI 安装包，提供图形化安装向导。
  • macOS：可使用 Homebrew (brew install mysql) 或官方 DMG 安装包。

安装完成后，需要启动 MySQL 服务并设置 root 用户的初始密码。

安装环节看似偏操作，实际是在为后续全部实践建立基础环境。教学中容易忽视的一点是，数据库软件不是装完就能放心使用，还必须在安装阶段就确定字符集、时区、端口、数据目录、日志目录和认证方式等关键默认项。尤其在中文环境和多系统协作环境中，若初始字符集和排序规则没有统一，后续会在导入导出、接口交换和模糊查询中反复出现问题。数据库的很多长期稳定性，往往从安装配置阶段就已经埋下伏笔。

2.2 连接 MySQL ​

使用命令行客户端连接：

bash
mysql -h 主机地址 -u 用户名 -p
  • -h：指定服务器主机，省略时默认 localhost。
  • -u：指定登录用户名。
  • -p：提示输入密码，也可直接写 -p密码（不推荐，密码可能被记录）。

连接成功后，将进入 MySQL 命令行提示符 mysql>。

2.3 基础管理命令 ​

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

-- 查看当前 MySQL 版本
SELECT VERSION();

-- 查看当前用户
SELECT USER();

-- 退出
EXIT;

3. 数据库操作 ​

3.1 创建数据库 ​

sql
CREATE DATABASE [IF NOT EXISTS] 数据库名
  [DEFAULT] CHARACTER SET 字符集名
  [DEFAULT] COLLATE 校对规则名;

示例：

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

utf8mb4 是 MySQL 中推荐的字符集，完整支持四字节 UTF-8 字符（如 emoji），避免 utf8 仅支持三字节的限制。COLLATE 指定排序和比较规则。

数据库级字符集和排序规则不是无关紧要的细节，它们会直接影响字符串如何存储、如何比较、如何排序，以及唯一索引怎样判断“相同”。如果一个系统的数据库、表和连接使用的字符集不一致，就可能出现插入时能通过、查询时又行为异常的现象。尤其在中文业务中，大小写是否敏感、全角半角如何处理、不同语言字符怎样排序，都会受到排序规则影响。也正因为如此，数据库创建阶段的字符集设计应被看作语义层选择，而不是一次性的安装参数。

3.2 选择数据库 ​

对表进行操作前，需要先选定当前数据库：

sql
USE school;

3.3 删除数据库 ​

sql
DROP DATABASE [IF EXISTS] 数据库名;

WARNING

DROP DATABASE 会删除数据库中的所有表和数据，且不可恢复。生产环境中务必谨慎执行。

4. 数据类型与表操作 ​

4.1 常用数据类型 ​

MySQL 支持丰富的数据类型，按类别可分为：

类别类型示例说明
整数TINYINT, SMALLINT, INT, BIGINT可加 UNSIGNED 表示无符号
小数DECIMAL(M,D), FLOAT, DOUBLEDECIMAL 精确，浮点型有精度损失
字符串CHAR(n), VARCHAR(n), TEXTCHAR 定长，VARCHAR 变长
日期时间DATE, TIME, DATETIME, TIMESTAMPTIMESTAMP 受时区影响
二进制BLOB, BINARY, VARBINARY存储二进制数据

数据类型选得是否合适，常常比初学者想象的更重要。金额若误用浮点数，后续就可能在累计计算中出现精度误差；状态若全用自由字符串，统计和约束就会越来越难做；时间字段若混用 DATETIME 与 TIMESTAMP 却不清楚时区语义，跨地区系统就很容易出现时间解释偏差。MySQL 允许你把很多值“存进去”，但是否适合这样存，取决于设计者是否理解业务语义。正确的数据类型设计，实际上是在为未来的查询、校验和索引预先铺路。

4.2 创建表 ​

sql
CREATE TABLE Student (
    id       INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    sno      CHAR(9)     NOT NULL UNIQUE,
    sname    VARCHAR(50) NOT NULL,
    ssex     ENUM('男','女') DEFAULT '男',
    sage     TINYINT UNSIGNED,
    sdept    VARCHAR(30),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  • AUTO_INCREMENT：自增列，常用于主键。
  • ENGINE=InnoDB：指定存储引擎。
  • DEFAULT CHARSET：表级字符集。

在 MySQL 中，表结构设计与 InnoDB 的数据组织方式关系非常紧密。InnoDB 采用聚簇索引存储数据，主键索引的叶子节点直接保存整行记录，这意味着主键不只是“逻辑上的唯一标识”，还决定了数据在页中的物理组织方式。因此，主键通常应尽量满足三个特点：短小、稳定、尽量有序。若主键过长，所有二级索引都要跟着变大；若主键频繁变化，会带来更高的数据重组成本；若主键过于离散，则容易增加页分裂和写入随机性。很多 MySQL 设计经验，归根结底都来自这一底层事实。

4.3 修改表结构 (ALTER TABLE) ​

sql
-- 添加列
ALTER TABLE Student ADD COLUMN phone VARCHAR(20);

-- 修改列类型
ALTER TABLE Student MODIFY COLUMN sname VARCHAR(80);

-- 重命名列
ALTER TABLE Student CHANGE COLUMN sdept department VARCHAR(30);

-- 删除列
ALTER TABLE Student DROP COLUMN phone;

-- 添加索引
ALTER TABLE Student ADD INDEX idx_sdept (sdept);

4.4 删除表 ​

sql
DROP TABLE [IF EXISTS] 表名;

TRUNCATE TABLE 表名 可快速清空表数据并重置自增值，比 DELETE 更快，但不可回滚。

5. 数据操纵 (DML) ​

5.1 插入数据 (INSERT) ​

sql
-- 插入单行，指定列
INSERT INTO Student (sno, sname, ssex, sage, sdept)
VALUES ('2021001', '张三', '男', 20, '计算机系');

-- 插入单行，省略列名（按表定义顺序）
INSERT INTO Student VALUES (NULL, '2021002', '李四', '男', 19, '数学系', NOW());

-- 批量插入
INSERT INTO Student (sno, sname, sdept)
VALUES ('2021003', '王五', '计算机系'),
       ('2021004', '赵六', '物理系');

-- 从查询结果插入
INSERT INTO TempTable (sno, sname)
SELECT sno, sname FROM Student WHERE sdept = '计算机系';

5.2 更新数据 (UPDATE) ​

sql
UPDATE Student SET sage = sage + 1 WHERE sdept = '计算机系';
UPDATE Student SET sdept = '软件工程', sage = 21 WHERE sno = '2021001';

IMPORTANT

务必谨慎使用 UPDATE，省略 WHERE 子句将更新表中全部行，极易造成数据事故。

5.3 删除数据 (DELETE) ​

sql
DELETE FROM Student WHERE sno = '2021004';
DELETE FROM Student WHERE sage < 18;  -- 删除不符合条件的记录

5.4 查询数据 (SELECT) 基础 ​

sql
-- 查询所有列
SELECT * FROM Student;

-- 查询指定列
SELECT sno, sname, sdept FROM Student;

-- 去重
SELECT DISTINCT sdept FROM Student;

-- 使用别名
SELECT sno AS 学号, sname AS 姓名, 2026 - sage AS 出生年份 FROM Student;

-- 条件查询
SELECT * FROM Student WHERE sage >= 20 AND sdept = '计算机系';

MySQL 在大量业务系统中之所以表现稳定，一个重要原因就是它非常适合承载高频、结构化、短事务的在线读写场景。用户登录、订单查询、状态更新、后台筛选等操作，本质上都属于围绕少量结构化字段进行快速定位和修改的事务型访问。学习 MySQL 的增删改查，不能只把它当成教材中的例子，而要看到这些语句其实正对应着现实系统中最常见的一类业务动作。

6. 查询进阶 ​

6.1 WHERE 子句与运算符 ​

运算符含义示例
=, <>, <, >, <=, >=比较sage > 18
BETWEEN a AND b区间sage BETWEEN 18 AND 25
IN (v1, v2, ...)成员sdept IN ('计算机系','数学系')
LIKE模式匹配sname LIKE '张%'
IS NULL / IS NOT NULL空值phone IS NULL
AND, OR, NOT逻辑sage > 20 AND ssex = '男'

LIKE 通配符：% 匹配任意长度字符串，_ 匹配单个字符。例如 sname LIKE '李_' 匹配"李四"但不匹配"李明明"。

条件查询阶段最容易暴露数据设计是否合理。若字段类型和取值范围设计清楚，WHERE 子句通常能直接表达业务筛选条件；若字段语义混乱、同一列混存多种格式、空值使用随意，查询条件就会越来越复杂，最终既不易读也不易优化。很多慢查询表面上是索引问题，根源却在于字段本身不是为可筛选、可比较、可排序而设计的。也正因为如此，MySQL 的查询优化应从字段语义和访问模式一起理解，而不能只盯着执行层。

6.2 ORDER BY 排序 ​

sql
SELECT * FROM Student ORDER BY sage DESC, sno ASC;

ASC 升序（默认），DESC 降序。可按多列排序。

6.3 LIMIT 分页 ​

sql
SELECT * FROM Student ORDER BY sno LIMIT 10;        -- 前 10 条
SELECT * FROM Student ORDER BY sno LIMIT 10, 20;    -- 跳过 10 条，取 20 条
SELECT * FROM Student ORDER BY sno LIMIT 20 OFFSET 10;  -- 同上，推荐写法

6.4 聚合函数与 GROUP BY ​

sql
SELECT sdept, COUNT(*) AS 人数, AVG(sage) AS 平均年龄
FROM Student
GROUP BY sdept
HAVING COUNT(*) > 5;

常用聚合函数：COUNT, SUM, AVG, MAX, MIN。HAVING 对分组结果进行过滤，可引用聚合函数；WHERE 在分组前过滤行，不能使用聚合函数。

6.5 连接查询 (JOIN) ​

sql
-- 内连接
SELECT s.sno, s.sname, sc.grade
FROM Student s INNER JOIN SC sc ON s.sno = sc.sno
WHERE sc.cno = 'C001';

-- 左外连接
SELECT s.sno, s.sname, sc.grade
FROM Student s LEFT JOIN SC sc ON s.sno = sc.sno AND sc.cno = 'C001';

连接查询体现了关系数据库最核心的能力，也最能暴露表结构是否设计合理。若主键和外键边界清晰，连接路径通常会非常自然；若表之间关系含糊，连接就容易引入重复记录、丢失记录或大量无意义扫描。MySQL 在处理连接时，并不是“表一多就一定慢”，真正关键的是连接条件是否命中索引、连接方向是否符合访问习惯、筛选条件能否尽早缩小中间结果。把连接写对，本质上是在把业务关系翻译成可执行路径。

6.6 子查询 ​

sql
-- 标量子查询
SELECT * FROM Student WHERE sage > (SELECT AVG(sage) FROM Student);

-- IN 子查询
SELECT sname FROM Student WHERE sno IN (SELECT sno FROM SC WHERE cno = 'C001');

-- EXISTS 子查询
SELECT sname FROM Student s
WHERE EXISTS (SELECT 1 FROM SC sc WHERE sc.sno = s.sno AND sc.cno = 'C001');

6.7 UNION 合并结果集 ​

sql
SELECT sno FROM SC WHERE cno = 'C001'
UNION
SELECT sno FROM SC WHERE cno = 'C002';

UNION 自动去重；UNION ALL 保留重复行。

MySQL 查询之所以有时很快、有时又会突然变慢，并不只是“有没有索引”这么简单。一次查询从进入服务器到返回结果，至少会经历解析、优化、执行和存储引擎访问多个阶段。影响性能的因素既包括索引是否匹配过滤条件，也包括数据选择性、连接顺序、排序是否需要额外临时空间、统计信息是否足够准确、是否发生回表以及是否遇到锁等待。把慢查询简单理解为“多建几个索引就能解决”，往往会忽略真正的访问模式问题。更稳妥的做法，是先明确系统最常按什么条件查询，再围绕这些高频路径设计结构和索引。

7. 约束与索引 ​

7.1 约束类型 ​

MySQL 支持主键、唯一、非空、默认值、外键和 CHECK 约束（MySQL 8.0.16+ 支持 CHECK）：

sql
CREATE TABLE Course (
    cno     CHAR(4) PRIMARY KEY,
    cname   VARCHAR(50) NOT NULL,
    credit  DECIMAL(3,1) DEFAULT 2.0 CHECK (credit > 0)
);

CREATE TABLE SC (
    sno     CHAR(9),
    cno     CHAR(4),
    grade   SMALLINT,
    PRIMARY KEY (sno, cno),
    FOREIGN KEY (sno) REFERENCES Student(sno) ON DELETE CASCADE,
    FOREIGN KEY (cno) REFERENCES Course(cno) ON UPDATE CASCADE
);

7.2 索引的创建与使用 ​

sql
-- 创建普通索引
CREATE INDEX idx_sname ON Student(sname);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_sno ON Student(sno);

-- 创建联合索引
CREATE INDEX idx_dept_age ON Student(sdept, sage);

-- 查看索引
SHOW INDEX FROM Student;

-- 删除索引
DROP INDEX idx_sname ON Student;

最左前缀原则：联合索引 (sdept, sage) 可用于 WHERE sdept = ? 或 WHERE sdept = ? AND sage = ?，但不能单独用于 WHERE sage = ?。

索引的价值在于回答“最常见的查询如何更快定位数据”，而不是把所有字段都提前建上索引。高频过滤列、高频排序列和业务唯一约束通常是最值得优先索引的对象；低选择性字段、频繁更新字段和很少作为查询条件使用的列，则应谨慎建立索引。对 MySQL 来说，索引一旦过多，不仅会拖慢写入，还会增加缓存压力和维护成本。因此，真正高质量的索引设计一定是围绕访问模式来做的。离开访问模式讨论索引，很容易把索引从性能资产变成写入负担。

8. 视图、存储过程与触发器 ​

8.1 视图 ​

sql
CREATE VIEW CS_Student AS
SELECT sno, sname, sage FROM Student WHERE sdept = '计算机系';

SELECT * FROM CS_Student;

视图是虚拟表，不存储数据，查询时动态执行底层 SELECT。

在 MySQL 中，视图的最大价值并不只是“少写一段查询”，而是把复杂访问路径封装成稳定接口。对上层应用和分析人员来说，视图可以隐藏底层表结构变化，让他们始终围绕更接近业务语义的对象工作；对权限管理来说，视图还能限制用户只看到必要列和必要行。也就是说，视图既服务于可读性，也服务于治理边界。

8.2 存储过程 ​

sql
DELIMITER //
CREATE PROCEDURE GetDeptCount(OUT cnt INT)
BEGIN
    SELECT COUNT(DISTINCT sdept) INTO cnt FROM Student;
END //
DELIMITER ;

CALL GetDeptCount(@count);
SELECT @count;

存储过程可封装复杂逻辑，减少网络往返，提高执行效率。

但是否把逻辑写入存储过程，需要谨慎划分边界。与数据强相关、且在数据库侧执行更可靠的逻辑，例如批量统计、审计辅助和权限封装，适合放到数据库层；依赖复杂业务编排、跨系统调用和快速版本迭代的流程，则更适合留在应用层。MySQL 并不要求把所有能力都压进数据库，合理协作的关键在于让数据库承担它最擅长的那部分责任。

8.3 触发器 ​

sql
CREATE TRIGGER tr_after_insert_student
AFTER INSERT ON Student
FOR EACH ROW
BEGIN
    INSERT INTO AuditLog (action, table_name, record_id, created_at)
    VALUES ('INSERT', 'Student', NEW.sno, NOW());
END;

触发器在 INSERT、UPDATE、DELETE 时自动执行。NEW 表示新行，OLD 表示旧行（UPDATE/DELETE）。

触发器在教学中经常给人一种“很方便”的印象，但真正成熟的用法应该是克制的。因为它会在数据变化时自动触发额外逻辑，若设计不慎，就可能让系统行为变得隐蔽。适合交给触发器处理的，通常是与数据一致性强绑定、且离开数据库层难以保证的动作，如审计日志、简单同步、强约束校验；不适合放进去的，则是复杂业务流程和大量外部依赖。越是自动执行的机制，越需要控制范围。

9. 事务与锁 ​

9.1 事务控制 ​

sql
START TRANSACTION;
UPDATE Account SET balance = balance - 100 WHERE id = 1;
UPDATE Account SET balance = balance + 100 WHERE id = 2;
COMMIT;   -- 提交
-- 或 ROLLBACK;   -- 回滚

InnoDB 支持 ACID 事务。SET autocommit = 0 可关闭自动提交，需手动 COMMIT 或 ROLLBACK。

事务语句背后依赖的是一整套日志与恢复机制。对于 InnoDB 而言，redo log 负责保证已提交修改在故障后可以重做恢复，undo log 负责回滚未完成事务并为 MVCC 提供旧版本，binlog 则更多承担复制和时间点恢复职责。可以把它们理解为三个不同方向的“记账本”：一个面向向前恢复，一个面向向后撤销，一个面向语义级变更历史。只要明白事务不是靠 COMMIT 这一个单词魔法般生效，而是靠日志先行、数据后刷盘的机制在支撑，就更容易理解 MySQL 的可靠性来源。

同时，事务边界也必须服务于业务动作本身。一个事务最好只负责一个清晰的业务闭环，例如一次下单、一次库存扣减、一次支付确认，而不要把消息推送、报表同步、推荐更新等外围动作全部强行塞进同一事务。事务越长，锁保持时间越久，日志压力越大，系统越容易出现等待和回滚成本升高的问题。事务不是越大越安全，而是边界越清晰越可靠。

9.2 隔离级别 ​

MySQL 支持四种隔离级别：READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ（默认）、SERIALIZABLE。InnoDB 在 REPEATABLE READ 下通过 MVCC 避免幻读。

sql
-- 查看隔离级别
SELECT @@transaction_isolation;

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

隔离级别选择的本质，是在并发性能与观察一致性之间做权衡。并不是每个业务都必须追求最高隔离，也不是所有场景都可以随意降低隔离。后台查询、普通列表展示与核心账务、库存扣减的风险等级完全不同，因此对同一时刻“允许看到怎样的数据变化”也会有不同要求。理解 MySQL 的隔离级别时，最好始终把它和业务承诺联系起来，而不是把它仅仅当作几个英文单词去背。

9.3 锁机制 ​

InnoDB 支持行级锁和表级锁。SELECT ... FOR UPDATE 对查询行加排他锁；SELECT ... LOCK IN SHARE MODE 加共享锁。锁的粒度与索引使用情况相关，无索引时可能升级为表锁。

这也解释了为什么索引设计会直接影响并发表现。很多人只把索引理解为“让查询更快”，却忽略了它还能帮助数据库更精准地锁定目标记录。若缺少合适索引，本应锁几行的数据操作可能退化为扫描并影响更大范围，系统并发能力随之下降。因此，MySQL 的索引、事务和锁从来不是三个孤立主题，它们在在线业务里经常是同时发挥作用的。

10. 备份、导入导出与安全 ​

10.1 逻辑备份 ​

bash
# 导出整个数据库
mysqldump -u root -p school > school_backup.sql

# 导出单表
mysqldump -u root -p school Student > student_backup.sql

# 导入
mysql -u root -p school < school_backup.sql

备份的真正目标不是“生成一个文件”，而是保证数据在故障后能够按预期恢复。因此，任何备份策略都必须和恢复演练配套看待。只做备份、不做恢复验证，是生产环境中最危险的错觉之一。对 MySQL 来说，逻辑备份适合中小规模库和对象级迁移，但在大规模场景里，通常还需要结合二进制日志、增量备份或物理备份方案，才能满足更严格的恢复时间和恢复点要求。

10.2 用户与权限 ​

sql
-- 创建用户
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'password';

-- 授权
GRANT SELECT, INSERT, UPDATE ON school.* TO 'app_user'@'localhost';

-- 撤销权限
REVOKE INSERT ON school.* FROM 'app_user'@'localhost';

-- 刷新权限
FLUSH PRIVILEGES;

10.3 安全建议 ​

  • 禁止 root 远程登录，使用专用账号并限制主机。
  • 密码使用强策略，定期更换。
  • 遵循最小权限原则，按需授权。
  • 启用 SSL 连接，防止中间人攻击。
  • 定期备份，并验证备份可恢复性。

如果把 MySQL 放进真实业务系统中观察，还需要建立长期运行的容量意识。数据库在上线初期往往运行顺畅，但随着记录数增长、热点业务上升和历史数据累积，索引层级、缓存命中、主从延迟和备份窗口都会逐渐变化。也就是说，压垮数据库的常常不是某一天突然多出一条 SQL，而是长期增长趋势没有被及时管理。因此，除了会写增删改查，还应持续关注连接数、慢查询、锁等待、磁盘 I/O、复制延迟和大表增长情况。MySQL 真正的价值，不在于某次查询跑得快，而在于它能在长期演进中持续稳定服务。

🔒 会员专属内容

检查登录状态中...