04 · 数据写入、表结构与视图

对应第 19～22 章。连接练习库并执行 USE showdocs_mysql_book;。本页写入示例使用事务回滚，建表与视图则单独清理。

查询主要回答「有哪些数据」，写入则需要同时回答三个问题：哪些行可以改、改后必须满足哪些约束、什么时候让别人看到改动。不要只验证语句成功，还要验证行数、值和事务结束后的状态。

第 19 章：写清楚插入列

sql

START TRANSACTION;
INSERT INTO learners (learner_id, learner_name, email)
VALUES (4, '吴四', NULL), (5, '郑五', 'zheng@example.test');
SELECT learner_id, learner_name FROM learners WHERE learner_id IN (4, 5);
ROLLBACK;

查询应看到两位临时学员，回滚后不再存在。VALUES 每一组括号是一行，值按前面的列名位置对应，不按值的内容自动寻找字段。

显式列名为什么重要

INSERT INTO learners VALUES (...) 依赖整张表的列顺序。以后增加一列，原来的语句可能失败，或在兼容类型之间写错位置。明确写 (learner_id, learner_name, email) 就把输入与目标的对应关系固定下来。

以下三种情况不能混为一谈：

写法	数据库如何处理	本例含义
显式写 `email = NULL`	存入 NULL，前提是列允许空值	不知道邮箱
显式写 `email = ''`	存入长度为零的文本	已提供一个空字符串
省略 email 列	使用该列默认行为	本例允许 NULL 且没有另设默认值，因此为 NULL

对于 NOT NULL 且没有默认值的必填列，不应依赖服务器帮忙补值。严格 SQL 模式通常会拒绝遗漏或不合法的输入；即使某种模式只产生警告，数据也未必符合业务意图。可用 SELECT @@SESSION.sql_mode; 检查当前会话设置，遇到意外结果再用 SHOW WARNINGS; 查看说明。

多行插入与约束失败

多行 INSERT 可以减少客户端往返，但每一行仍要通过主键、外键、非空等约束。在本实验的 InnoDB 和普通 INSERT 下，一条多行语句中的主键冲突会导致该语句失败，不会把其余行当成成功插入。这个「语句原子性」不等于同一事务中更早执行的语句也自动回滚，后面的事务单元会专门验证这一区别。

不要为了让导入看起来成功就立即改用 INSERT IGNORE。它会把部分错误降为警告、跳过或调整部分数据；REPLACE 也不是普通更新，它在唯一键冲突时具有删除旧行并插入新行的语义，可能涉及外键、触发器和默认值。需要幂等写入时，应先确定冲突键以及「保留旧值还是更新哪些列」的业务规则。

INSERT ... SELECT 把查询结果作为输入

先建立一个独立实验表，再把有邮箱的学员放进去。结构语句放在事务外，数据搬运放在事务内：

sql

CREATE TABLE book_learner_contacts (
  learner_id INT PRIMARY KEY,
  contact_name VARCHAR(40) NOT NULL,
  email VARCHAR(100) NOT NULL
) ENGINE=InnoDB;

START TRANSACTION;
INSERT INTO book_learner_contacts (learner_id, contact_name, email)
SELECT learner_id, learner_name, email
FROM learners WHERE email IS NOT NULL;
SELECT * FROM book_learner_contacts ORDER BY learner_id;
ROLLBACK;

SELECT COUNT(*) AS contacts_after_rollback FROM book_learner_contacts;
DROP TABLE book_learner_contacts;

事务内可看到学员 1、3 的两条联系信息，回滚后计数为 0。目标列与查询列仍按位置对应；源列叫 learner_name，目标列叫 contact_name 并不妨碍插入。这不是持续同步关系，源数据以后变化不会自动修改已经复制的数据。

第 20 章：先查询范围，再更新或删除

sql

START TRANSACTION;
SELECT course_id, title, price FROM courses WHERE course_id = 103;
UPDATE courses SET price = 45.00 WHERE course_id = 103;
SELECT ROW_COUNT() AS changed_rows;
SELECT price FROM courses WHERE course_id = 103;
ROLLBACK;

START TRANSACTION;
INSERT INTO learners (learner_id, learner_name) VALUES (4, '临时学员');
DELETE FROM learners WHERE learner_id = 4;
SELECT ROW_COUNT() AS deleted_rows;
ROLLBACK;

第一次修改预期改变 1 行，回滚后 103 的价格恢复为 49.00。ROW_COUNT() 应紧跟需要观察的写入语句；如果先执行其他查询，再读取它，就不再是刚才那条更新的结果。删除示例只删除本次插入的临时行，回滚后整个实验对学员表没有影响。

用相同条件预览，再明确并发要求

基本步骤是先用 SELECT ... WHERE ... 检查目标，再用同一条件写入，最后核对变化。这能发现把 course_id = 103 写错为 category = '运维' 等范围问题，但普通预览不会自动阻止其他会话在两条语句之间修改数据。

若业务依赖「只有价格仍是我看到的值时才能修改」，可把原值放进写入条件：

sql

START TRANSACTION;
UPDATE courses
SET price = 45.00
WHERE course_id = 103 AND price = 49.00;
SELECT ROW_COUNT() AS changed_rows;
ROLLBACK;

本数据返回 1。如果返回 0，可能是记录不存在，也可能是价格已经变化；应用需要按业务解释，不能直接当作更新成功。涉及多个相互依赖的读写时，还可在事务中考虑 SELECT ... FOR UPDATE 等锁定读，但应理解锁范围、等待与死锁，而不是对所有查询都加锁。

匹配行数与实际改变行数

sql

START TRANSACTION;
UPDATE courses SET price = 49.00 WHERE course_id = 103;
SELECT ROW_COUNT() AS unchanged_update_rows;
ROLLBACK;

默认 mysql 客户端下结果为 0：条件匹配了 103，但新旧价格相同。部分驱动启用 CLIENT_FOUND_ROWS 后报告匹配行数，结果可能为 1。因此程序判断写入是否达到目标时，要结合驱动配置；不要把数据库客户端与应用的 affected rows 差异当成数据丢失。

同样，UPDATE 没有 WHERE 会针对全表，DELETE 没有 WHERE 会删除全表行。SQL_SAFE_UPDATES 可拦截部分危险写法，但不是业务正确性的证明：使用了键列的错误条件仍可能改错对象。

删除遇到外键意味着什么

learner 1 有报名记录，直接删除该学员会被本例外键拒绝。数据库在保护「报名必须属于存在的学员」这一约束。合理处理方式取决于需求：保留历史记录并软删除、明确删除依赖记录，或在设计时使用适当的级联规则。不要为了通过删除操作就临时关闭外键检查。

操作	作用	在本实验 InnoDB 中的事务特点
`DELETE FROM t WHERE ...`	删除符合条件的行，保留表	普通事务内可回滚；会执行相应 DELETE 触发器
`TRUNCATE TABLE t`	清空表，不能带 WHERE	DDL，隐式提交；不是可回滚的批量 DELETE，也不触发 DELETE 触发器
`DROP TABLE t`	删除表定义及数据	DDL，隐式提交；随后连表也不存在

不要把后两者作为当前三张基础表的练习命令直接执行。它们与带条件的行删除并不等价。

第 21 章：结构变更不等同于普通写入

下面建立一个独立演示表，按顺序执行并在最后删除：

sql

CREATE TABLE book_tags (
  tag_id INT AUTO_INCREMENT PRIMARY KEY,
  tag_name VARCHAR(40) NOT NULL UNIQUE,
  enabled BOOLEAN NOT NULL DEFAULT TRUE
) ENGINE=InnoDB;
INSERT INTO book_tags (tag_name) VALUES ('练习');
ALTER TABLE book_tags ADD COLUMN note VARCHAR(100) NULL;
SHOW CREATE TABLE book_tags;
DROP TABLE book_tags;

SHOW CREATE TABLE 能看到数据库实际保存的定义，包括默认值、索引和表选项。BOOLEAN 在 MySQL 中是 TINYINT(1) 的同义写法，并不会只允许 0、1；需要严格限定时，应添加明确的约束。

类型与约束共同表达规则

定义	负责什么	不负责什么
`INT AUTO_INCREMENT PRIMARY KEY`	生成整数标识，并保证非空唯一	不保证编号无空洞，也不说明业务先后顺序
`VARCHAR(40) NOT NULL`	限制字符长度，禁止 NULL	不自动禁止空字符串或全空格
`UNIQUE(tag_name)`	依照该列校对规则判断唯一	不保证大小写按字节区分；可空唯一列通常允许多个 NULL
`DEFAULT TRUE`	省略该列时采用默认值	不会把显式传入的不合法值自动修正
`CHECK (...)`	检查每行的表达式	不替代跨表外键或跨多行的业务事务
`FOREIGN KEY`	保护引用关系	不自动选择级联删除或适合查询的所有索引

MySQL 8.0.16 及之后会执行 CHECK 约束；更早版本的行为不同。以下示例面向 8.4，在独立表中同时表达非空、唯一和取值范围：

sql

CREATE TABLE book_tag_rules (
  tag_id INT AUTO_INCREMENT PRIMARY KEY,
  tag_name VARCHAR(40) NOT NULL,
  enabled BOOLEAN NOT NULL DEFAULT TRUE,
  CONSTRAINT uq_book_tag_name UNIQUE (tag_name),
  CONSTRAINT chk_book_tag_name CHECK (CHAR_LENGTH(TRIM(tag_name)) > 0),
  CONSTRAINT chk_book_tag_enabled CHECK (enabled IN (0, 1))
) ENGINE=InnoDB;

INSERT INTO book_tag_rules (tag_name) VALUES ('数据库');
SELECT tag_id, tag_name, enabled FROM book_tag_rules;
SHOW CREATE TABLE book_tag_rules;
DROP TABLE book_tag_rules;

第一条记录的 enabled 为 1。如果另行尝试空白名称或 enabled 为 2，应被拒绝。CHECK 对 UNKNOWN 的处理也需要留意：仅写 CHECK(price >= 0) 并不等于禁止 NULL，必填字段仍要声明 NOT NULL。

自增编号不是计数器

回滚、插入失败、删除以及并发分配都可能使编号不连续。不要用 MAX(id) 代表记录数，也不要通过猜测下一个编号来建立关联。在同一连接成功插入后，可用 LAST_INSERT_ID() 获取刚生成的编号；连接池中必须确保读取仍在执行插入的同一连接上。

改表前先区分事务 DML 与 DDL

普通 INSERT、UPDATE、DELETE 修改行；CREATE TABLE、ALTER TABLE、DROP TABLE 修改结构。一般的表结构 DDL 会隐式提交当前事务，不能靠外面包一层 START TRANSACTION 再 ROLLBACK 撤销。

用独立实验表观察这一点，避免触碰课程数据：

sql

CREATE TABLE book_ddl_demo (id INT PRIMARY KEY) ENGINE=InnoDB;
START TRANSACTION;
INSERT INTO book_ddl_demo VALUES (1);
ALTER TABLE book_ddl_demo ADD COLUMN note VARCHAR(20) NULL;
ROLLBACK;
SELECT id, note FROM book_ddl_demo;
DROP TABLE book_ddl_demo;

回滚后仍能看到 1 / NULL。原因是 ALTER TABLE 已使之前的插入提交，回滚已无法撤销它。MySQL 8 的原子 DDL 主要保证受支持的单条结构操作在故障恢复时的一致性，不表示用户能把多条 DDL 放进普通事务后统一回滚。

真实表改结构还需评估既有数据是否合法、表大小、锁等待和算法。能对四行表瞬间完成 ALTER，不能证明对数亿行表也可无影响执行。本页只演示语法与事务边界。

第 22 章：用视图保存查询定义

sql

CREATE VIEW book_course_revenue AS
SELECT c.course_id, c.title, COALESCE(SUM(e.paid), 0) AS revenue
FROM courses AS c
LEFT JOIN enrollments AS e ON e.course_id = c.course_id
GROUP BY c.course_id, c.title;

SELECT course_id, revenue FROM book_course_revenue ORDER BY course_id;
DROP VIEW book_course_revenue;

收入预期仍为 78、79、0、0。普通视图保存查询定义，不是定期刷新的一份结果副本；底层数据变化会影响再次查询的结果。查询视图可以理解为复用这个关系表达式，但最终执行方式由优化器决定，不能假设建立视图就预先算好了结果。

视图解决的是复用与访问接口

多个报表都需要「每课收入」时，统一视图可减少重复的连接和空值处理。它也能只暴露特定列，但是否真正形成权限边界，还取决于调用者是否拥有基础表权限，以及视图的 SQL SECURITY DEFINER / INVOKER 设置。创建时的定义者账号也需要纳入迁移与恢复考虑。

视图不是万能封装：普通视图本身不储存独立的汇总数据，也不能在它上面随意创建索引来加速；索引通常应建在基础表。外层查询若要求顺序，仍需显式 ORDER BY。

哪些视图可以更新

上面的聚合视图包含分组与聚合，不能直接 UPDATE revenue。一条汇总行对应多条报名，数据库无法知道应修改哪笔实付金额。应对基础业务记录执行写入，再重新查询汇总。

一些简单的单表视图可以更新。WITH CHECK OPTION 还可要求写入后的行继续满足视图条件：

sql

CREATE VIEW book_database_courses AS
SELECT course_id, title, category, price, summary
FROM courses WHERE category = '数据库'
WITH CASCADED CHECK OPTION;

START TRANSACTION;
UPDATE book_database_courses SET price = 40.00 WHERE course_id = 101;
SELECT price FROM courses WHERE course_id = 101;
ROLLBACK;

SHOW CREATE VIEW book_database_courses;
DROP VIEW book_database_courses;

事务内基础表中的 101 同样显示 40 元，说明写入实际落到基础表。若尝试通过该视图把 101 的类别改为「运维」，CHECK OPTION 会拒绝，因为新行不再符合「数据库」条件。直接写基础表不经过这一视图检查，因此它不能代替基础表上的通用完整性约束。

练习与核对

练习一：把试写入完整地撤销

在一个事务中新增学员 4「吴四」，再为其报名课程 104，实付 50 元、日期为 2026-10-04。查询这位学员的报名详情，然后回滚。

参考答案与核对

sql

START TRANSACTION;
INSERT INTO learners (learner_id, learner_name) VALUES (4, '吴四');
INSERT INTO enrollments (learner_id, course_id, paid, enrolled_at)
VALUES (4, 104, 50.00, '2026-10-04');
SELECT l.learner_name, c.title, e.paid
FROM enrollments AS e
JOIN learners AS l ON l.learner_id = e.learner_id
JOIN courses AS c ON c.course_id = e.course_id
WHERE e.learner_id = 4;
ROLLBACK;
SELECT COUNT(*) AS remaining_learner FROM learners WHERE learner_id = 4;
SELECT COUNT(*) AS remaining_enrollment FROM enrollments WHERE learner_id = 4;

事务中得到「吴四 / SQL 报表 / 50.00」，两个回滚后计数都为零。必须先插学员再插报名，否则外键引用的学员不存在。应用中任何一步出错都应进入明确的异常处理，不能因为某条语句失败就假定前面所有写入已自动撤销。

练习二：课程调价会不会改变历史收入

在事务中把 101 从 39 元改为 45 元，分别查询课程现价与报名实付，再回滚。预期现价变了，两笔历史实付仍各为 39 元。若你的更新同时改了过去的 paid，那是在重写历史交易，已经超出单纯课程调价的含义。

练习三：识别无法回滚的地方

为什么 DDL 实验中的 ROLLBACK 没有删除 id 为 1 的行？为什么先建表、再开始写入事务，可以在回滚后留下空表？答案都来自事务边界：结构操作与其后的数据操作不是同一个可由用户整体回滚的事务。

完成后执行：

sql

SELECT (SELECT COUNT(*) FROM learners) AS learners,
       (SELECT COUNT(*) FROM courses) AS courses,
       (SELECT COUNT(*) FROM enrollments) AS registrations,
       (SELECT SUM(paid) FROM enrollments) AS revenue;
SELECT course_id, price FROM courses ORDER BY course_id;

应为 3 / 4 / 3 / 157.00，价格为 39、79、49、59。如果不一致，先检查是否漏执行了回滚，或是否把 DDL 放进了写入事务；不要直接重新运行初始化脚本覆盖问题现场。

官方隐式提交说明 · 官方视图说明

返回导读 · 下一单元：存储程序与事务

04 · 数据写入、表结构与视图 ​

第 19 章：写清楚插入列 ​

显式列名为什么重要 ​

多行插入与约束失败 ​

INSERT ... SELECT 把查询结果作为输入 ​

第 20 章：先查询范围，再更新或删除 ​

用相同条件预览，再明确并发要求 ​

匹配行数与实际改变行数 ​

删除遇到外键意味着什么 ​

第 21 章：结构变更不等同于普通写入 ​

类型与约束共同表达规则 ​

自增编号不是计数器 ​

改表前先区分事务 DML 与 DDL ​

第 22 章：用视图保存查询定义 ​

视图解决的是复用与访问接口 ​

哪些视图可以更新 ​

练习与核对 ​

练习一：把试写入完整地撤销 ​

练习二：课程调价会不会改变历史收入 ​

练习三：识别无法回滚的地方 ​

继续阅读