数据写入、更新与删除

学习目标

能完成单行、多行写入并读取当前连接生成的自增键。
能先验证条件，再在短事务中修改和删除目标行。
能区分 IGNORE、REPLACE 与重复键更新的冲突处理语义。

核心知识点

本节使用 MySQL 8.0 / InnoDB 的 learning_lab.users/orders，先完成建表与种子数据。前四段按顺序在同一个客户端连接执行，其余冲突实验使用独立表。DDL 设置与业务写入分开，避免隐式提交。INSERT 新建行，UPDATE 修改满足条件的行，DELETE 移除满足条件的行；CRUD 的读取语义将在查询篇展开。

安全写入的重点是确认环境、精确条件、检查结果及恢复能力。事务演练先 ROLLBACK，正式执行只在核对条件与影响行数后 COMMIT；不要在开启事务后离开客户端长时间等待。应用必须使用参数绑定，不能把用户输入拼成 WHERE。受影响行数取决于操作和客户端选项，不等于每次都发生业务变化。

常用用法

INSERT：写入单行与多行

用于明确指定字段并一次写入一条或多条用户数据。

sql

USE learning_lab;
-- 初始：users 只有 id=1、2 的种子数据；本组实验只执行一次。
INSERT INTO users(username,balance,created_at)
VALUES ('赵同学',30.00,'2026-10-05 11:00:00');
SELECT LAST_INSERT_ID() INTO @new_user_id;
-- 关键变化：新增赵同学，保存此连接生成的主键到 @new_user_id。
INSERT INTO users(username,balance,created_at) VALUES
 ('孙同学',20.00,'2026-10-05 11:10:00'),
 ('周同学',10.00,'2026-10-05 11:20:00');
SELECT ROW_COUNT() AS inserted_rows;
SELECT username,balance FROM users
WHERE username IN ('赵同学','孙同学','周同学') ORDER BY balance DESC;
-- 输出：inserted_rows=2；赵同学/30.00，孙同学/20.00，周同学/10.00。

不写列名的 INSERT 对列顺序敏感，结构变更后容易出错。单条多行 INSERT 与循环发多条 INSERT 的往返次数不同；事务批次还应按包大小和锁持有时间控制。INSERT 官方参考。

SELECT LAST_INSERT_ID()：读取本连接的生成键

用于在写入之后读取当前连接最近成功 INSERT 生成的第一个自增值。

sql

-- 前置条件：执行上段，@new_user_id 是赵同学主键，本连接又插入了孙、周两人。
SELECT @new_user_id AS zhao_id, LAST_INSERT_ID() AS first_batch_id;
SELECT username FROM learning_lab.users WHERE id=@new_user_id;
-- 关键变化：读取 @new_user_id 保存的主键；用户名为赵同学，多行插入返回孙同学的主键。
-- 输出：目标行 username=赵同学；主键数值取决于本实验库此前的自增状态。

全新、无并发写入的本章实验里，两值通常是 3 和 4；不能把连续编号当生产承诺，回滚、失败、并发都可能留下空洞。多行插入返回第一个生成键，不能拿返回值推算全部 ID。Java 获取生成键的方式见 JDBC。

UPDATE：在短事务中演练余额变更

用于按主键更新单个用户并验证操作前、操作中及回滚后的余额。

sql

USE learning_lab;
-- 初始：users.id=1 的 balance=100.00。
SELECT id,balance FROM users WHERE id=1;
START TRANSACTION;
-- 风险：UPDATE 漏写 WHERE 会修改全表；仅用已核对的主键条件并检查影响行数。
UPDATE users SET balance=balance-25.00 WHERE id=1 AND balance>=25.00;
SELECT ROW_COUNT() AS changed_rows;
-- 关键变化：当前事务中 balance 变为 75.00；changed_rows=1。
SELECT id,balance FROM users WHERE id=1;
ROLLBACK;
SELECT id,balance FROM users WHERE id=1;
-- 输出：回滚后 id=1 的 balance 恢复 100.00。

带余额条件的单语句修改可避免应用“先查后扣”的竞争窗口；实际扣款还需订单幂等与完整事务设计。默认 UPDATE 影响行数是实际改变的行数，值本来相同可能为 0；连接启用 CLIENT_FOUND_ROWS 时可改为匹配行数。正式提交前需要业务校验，但不要长时间持锁等待人工判断。

DELETE：按主键演练删除

用于限制删除范围并在事务内核对删除行和回滚后的恢复结果。

sql

USE learning_lab;
-- 初始：@new_user_id 指向余额 30.00 的赵同学，且本实验未为他创建订单。
SELECT id,username FROM users WHERE id=@new_user_id;
START TRANSACTION;
-- 风险：DELETE 无 WHERE 会移除全表；先查同一主键范围，核对影响行数并保留回滚路径。
DELETE FROM users WHERE id=@new_user_id;
SELECT ROW_COUNT() AS deleted_rows;
-- 关键变化：删除赵同学 1 行，本事务内该主键查询为空。
SELECT id FROM users WHERE id=@new_user_id;
ROLLBACK;
SELECT username FROM users WHERE id=@new_user_id;
-- 输出：deleted_rows=1；回滚后再次返回赵同学。

删除前必须考虑外键、关联业务和审计需求；没有外键不等于没有关联。大批量删除应按有索引的稳定范围分批处理、观察锁与日志负担，不能只靠加 LIMIT 就认定安全。禁止把无条件 UPDATE/DELETE 作为日常示例。

INSERT IGNORE：检查被忽略的冲突

用于演示重复唯一键如何转为警告，并核对最终实际留下的行。

sql

USE learning_lab;
CREATE TABLE lab_ignore (id INT PRIMARY KEY, label VARCHAR(16) NOT NULL) ENGINE=InnoDB;
INSERT INTO lab_ignore VALUES (1,'原值');
-- 初始：lab_ignore 只有 (1,原值)。
INSERT IGNORE INTO lab_ignore VALUES (1,'覆盖值'),(2,'新增值');
SHOW WARNINGS;
-- 关键变化：重复 id=1 被忽略，id=2 写入；警告包含 Duplicate entry。
SELECT id,label FROM lab_ignore ORDER BY id;
-- 输出：(1,原值)、(2,新增值)。

IGNORE 不仅处理重复键，还可能把部分转换错误降级为警告并调整数据；不能当成无条件安全的去重工具。业务需要失败即停止时，应使用普通 INSERT 并处理错误。INSERT 的 IGNORE 规则。

REPLACE：观察删除再插入的代价

用于验证重复主键时未指定列如何恢复默认值而非保留旧值。

sql

USE learning_lab;
CREATE TABLE lab_replace (
 id INT PRIMARY KEY, label VARCHAR(16) NOT NULL,
 note VARCHAR(16) NOT NULL DEFAULT '默认备注'
) ENGINE=InnoDB;
INSERT INTO lab_replace VALUES (1,'旧名称','需要保留');
-- 初始：id=1 的 note 为 需要保留。
-- 风险：REPLACE 冲突时具有删除旧行再插入语义，仅在实验表验证，不用于保留关联信息的业务更新。
REPLACE INTO lab_replace(id,label) VALUES (1,'新名称');
SELECT ROW_COUNT() AS affected_rows;
-- 关键变化：旧行被替换，未指定 note 采用默认值；affected_rows=2。
SELECT id,label,note FROM lab_replace;
-- 输出：1 | 新名称 | 默认备注。

REPLACE 可触发删除/插入相关副作用和外键行为，多个唯一键冲突甚至可能删除多行；它不是 UPDATE 的别名。官方依据：REPLACE。

INSERT ... ON DUPLICATE KEY UPDATE：只更新明确列

用于在主键或唯一键冲突时修改指定字段而保留行的其他信息。

sql

USE learning_lab;
CREATE TABLE lab_upsert (
 id INT PRIMARY KEY, label VARCHAR(16) NOT NULL,
 note VARCHAR(16) NOT NULL DEFAULT '默认备注'
) ENGINE=InnoDB;
INSERT INTO lab_upsert VALUES (1,'旧名称','需要保留');
-- 初始：lab_upsert 含 (1,旧名称,需要保留)。
INSERT INTO lab_upsert(id,label) VALUES (1,'新名称')
-- 风险：ON DUPLICATE KEY UPDATE 依赖唯一键冲突定位行，仅在实验表核对键与影响行数。
ON DUPLICATE KEY UPDATE label='新名称';
SELECT ROW_COUNT() AS affected_rows;
-- 关键变化：只更新 label，note 保留；affected_rows=2。
SELECT id,label,note FROM lab_upsert;
-- 输出：1 | 新名称 | 需要保留。

默认每行计数：新插入为 1，更新旧行为 2，原值不变为 0；CLIENT_FOUND_ROWS 会使无变化情况返回 1。该判断不是“SQL 返回成功就新增了用户”。冲突来自多个唯一键时要审查到底更新哪一行，不建议含多个唯一键的复杂表直接使用这种语句。

本例字面值写法适用于整个 8.0 系列。引用待插入行的新值可使用 8.0.19+ 的行别名；旧 VALUES(column) 写法从 8.0.20 起弃用，应按实际版本选择。参考：重复键更新官方说明。

易混点

ROW_COUNT() 应紧跟要检查的 DML，后续 SELECT 会改变其意义。自增键属于连接，不是全局“刚写入的用户”；连接切换后查询可能得到别的状态。IGNORE 忽略部分错误，REPLACE 替换行，ON DUPLICATE KEY UPDATE 修改指定列，这三者不能互换。

课后小问

为什么 UPDATE 影响 0 行不一定代表用户不存在？
答：条件可能未匹配，也可能新值和旧值相同；还要结合 WHERE、原值和连接的 found-rows 选项判断。
REPLACE 只指定名称，会保留原来的备注吗？
答：不会保证保留。重复键下按删除再插入语义处理，未指定备注使用默认值；需保留字段应使用明确列的 UPDATE 或冲突更新。

本节小结

指定插入列，生成键在同连接及时获取。
更新和删除先确认条件，事务演练后检查行数与状态。
冲突处理策略有不同副作用，需按业务要求明确选择。

快速回顾

LAST_INSERT_ID() 返回本连接最近生成的第一个自增值。
ROW_COUNT() 紧跟 DML 读取受影响行数。
ROLLBACK 撤销 InnoDB 写入，不能撤销普通 DDL。

数据写入、更新与删除 ​

学习目标 ​

核心知识点 ​

常用用法 ​

INSERT：写入单行与多行 ​

SELECT LAST_INSERT_ID()：读取本连接的生成键 ​

UPDATE：在短事务中演练余额变更 ​

DELETE：按主键演练删除 ​

INSERT IGNORE：检查被忽略的冲突 ​

REPLACE：观察删除再插入的代价 ​

INSERT ... ON DUPLICATE KEY UPDATE：只更新明确列 ​

易混点 ​

课后小问 ​

本节小结 ​

快速回顾 ​