INSERT、UPDATE、DELETE 与安全写入

写操作的正确性包括“写了什么、没写什么、失败时留下什么”。参数化解决值注入，不解决动态标识符；UPSERT 解决特定唯一冲突，不自动保证业务幂等；UPDATE 和 DELETE 若缺少范围保护，会把一次输入错误放大成全表事故。

一、学习目标

使用参数化 SQL 与明确列清单
设计批量 INSERT 和唯一冲突策略
理解 UPSERT 的键与受影响行语义
保护 UPDATE、DELETE 的范围与并发条件
通过事务和行数断言验证写入

二、INSERT 与默认值

始终写列清单：

INSERT INTO products (sku, name, price, active)
VALUES (?, ?, ?, TRUE);

这让新增带默认值列时保持兼容，也避免依赖表物理顺序。自增 ID 由服务端生成，调用方通过连接 API 获取；不要用 SELECT MAX(id)，并发下会得到别人的行。

严格模式下非法或超范围值应失败。插入后检查错误、受影响行数和生成键。

三、批量写入

多值 INSERT 减少往返：

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (?, ?, ?, ?), (?, ?, ?, ?);

批次大小受最大包、锁持有时间、复制日志和错误定位影响。超大导入应分批并保存进度。需要原子时整个业务批次放在同一事务；允许部分成功时，记录每批结果，不要把两种语义混用。

LOAD DATA 效率高，但文件权限、字符集、转义和 LOCAL 安全策略必须明确。

四、UPSERT 与幂等

MySQL 可通过唯一键触发 INSERT ... ON DUPLICATE KEY UPDATE。必须确认哪个唯一键代表同一业务对象；表有多个唯一键时，冲突来源可能不直观。

幂等写入通常需要请求键：

INSERT INTO audit_events (request_key, event_type, payload)
VALUES (?, ?, ?)
ON DUPLICATE KEY UPDATE request_key = VALUES(request_key);

新旧语法在版本间有弃用变化，应以 9.7 文档选择别名写法。不要把“重复时更新任意字段”当作通用去重。

五、UPDATE 与乐观并发

带状态前置条件更新：

UPDATE orders
SET status = 'paid', paid_at = UTC_TIMESTAMP(6)
WHERE id = ?
  AND status = 'pending';

受影响行数为 0 可能表示不存在或状态已变化，调用方按需要再查询区分。版本列可实现乐观并发：WHERE id=? AND version=?，成功时 version=version+1。

不要先 SELECT 判断再在事务外 UPDATE；两步之间状态可能变化。

六、DELETE、软删除与防护

删除前用完全相同 WHERE 做 SELECT/COUNT，仍不能替代事务和权限保护。运行账户可被限制不拥有 DELETE，或通过服务层只允许按主键删除。

软删除增加每个查询、唯一键和外键的复杂度，不是自动安全方案。历史订单通常保留并通过状态表达，临时或从属数据可物理删除。大批删除分批执行，观察锁、undo、复制延迟与磁盘回收。

七、从知识点到工程契约

本篇 SQL 最终要运行在长期存在的 schema、连接和事务中，而不是只在一个临时查询窗口里得到一次正确结果。先写清输入表与行数、会话设置、事务边界、允许的锁、期望结果和失败后的状态，再决定使用约束、查询、索引、存储对象还是运维命令。数据库行为同时受到版本、隔离级别、字符集、统计信息和并发会话影响，示例必须把这些前提显式化。

可以用以下顺序把知识点落到 StudyStore：

在全新数据库按迁移顺序建立最小 schema，保存 SHOW CREATE TABLE 与关键会话变量。
加入一个与“省略 INSERT 列清单并依赖表物理顺序”相关的反例，记录错误码、SQLSTATE、事务是否仍可用以及数据是否改变。
为正常、空集、边界值、重复值和并发冲突准备确定性种子，不依赖手工残留数据。
对读查询检查结果集和 EXPLAIN ANALYZE；对写操作同时检查受影响行数、约束、提交与回滚。
最后才讨论性能优化。索引和参数调整必须有执行计划、等待事件或容量数据支持，并保留变更前基线。

审查数据库设计时至少回答四个问题：谁能写，哪条约束保护不变量，事务在哪一层结束，失败后如何恢复。MySQL 能保证声明范围内的事务和持久性，却不会替应用补上缺失约束、幂等键、备份演练或最小权限。只要这些问题没有明确答案，就不要把一次成功执行当成生产方案。

本篇最重要的能力是“使用参数化 SQL 与明确列清单”。能由主键、唯一键、外键、CHECK 或数据类型表达的规则，应优先落到数据库；需要跨聚合或外部系统判断的规则，再由应用和事务协调。不要用注释或约定替代可执行约束。

八、验证策略与复盘

验证分为 schema、数据、并发和恢复四层。schema 层检查定义与会话基线；数据层断言结果和约束；并发层至少用两个独立连接观察锁与隔离；恢复层在新实例重放迁移、备份与恢复。只看客户端显示“Query OK”不能证明结果正确，更不能证明在另一组数据和并发时仍正确。

建议保存下面的实验记录：

项目	需要记录的证据
版本	`SELECT VERSION()` 与镜像标签
会话	sql_mode、time_zone、事务隔离与字符集
输入	schema 版本、种子行数和参数值
输出	结果集、受影响行数、警告、错误码与 SQLSTATE
性能	执行计划、实际行数、耗时与等待
恢复	COMMIT/ROLLBACK 结果、备份校验和与恢复行数

每组脚本都应能在空数据库从头执行，并通过显式断言退出非零。涉及权限时分别用管理员和最小权限账户连接；涉及锁时设置有限等待，避免测试永久挂起；涉及复制时等待 GTID 状态而不是固定 sleep。清理只删除本次创建的临时容器、网络和数据库。

本篇可以用以下目标做验收：设计批量 INSERT 和唯一冲突策略；理解 UPSERT 的键与受影响行语义；保护 UPDATE、DELETE 的范围与并发条件。把每个目标转换为一条可重复 SQL、一个预期错误或一项恢复检查。若优化后结果正确但计划退化，应把执行计划也纳入回归证据。

发布前再从三个方向反向审查：把数据量放大两个数量级，判断扫描、锁和日志是否仍可控；让两个会话以最不利顺序并发，判断约束和事务是否仍保护不变量；让执行在任意一步失败，判断备份、回滚或幂等键能否恢复。只在十几行数据和单连接中成功的 SQL 仍只是功能草稿。

最后把脚本交给一个只知道镜像版本和入口命令的干净环境执行。脚本不得依赖图形客户端自动设置、个人默认数据库或先前会话变量；所有对象名、字符集、事务和预期错误都应明确。对仅用于说明、不可直接执行的片段要标注上下文，避免把省略条件的示意 SQL 当作完整迁移。

还要保存一份“结果为什么可信”的说明：约束证明哪些坏数据无法进入，事务证明哪些变化共同提交，执行计划证明访问了哪些行，权限测试证明哪些身份无法越界，恢复演练证明故障后能重新得到可用状态。这五类证据缺一时，都应在结论旁写明限制。数据库实验的价值不只是得到答案，而是让另一位操作者在不同机器、不同时间仍能重现同一判断。

若结论依赖当前数据分布或配置，应把适用范围写在 SQL 旁，并安排数据增长或版本升级后的复测条件；不要把一次测量永久固化为规则。

九、StudyStore 实验

实现创建订单的事务写入、按唯一请求键防重复、带状态前置条件的支付和受限删除。制造唯一冲突、库存不足、状态竞争和批次中途失败，验证受影响行数与最终数据符合约定。

完成本节后，不要只保存代码或 SQL。请同时保存执行命令、关键输出和失败案例；学习笔记真正有价值的部分，是能够说明输入、状态变化、输出以及失败后的恢复方式。

十、常见错误

省略 INSERT 列清单并依赖表物理顺序
使用 SELECT MAX(id) 获取刚插入主键
把 UPSERT 当作不需要业务幂等键的万能方案
先查询后在事务外更新，留下竞争窗口
执行没有精确 WHERE 的 UPDATE 或 DELETE

十一、练习与自测

用唯一 request_key 实现可重复提交的审计事件。
比较单行和多值 INSERT 的往返与批次失败语义。
实现 version 列乐观更新并模拟两个会话竞争。
为大批过期审计记录设计分批删除与停止条件。

自测时应在干净的临时目录或临时数据库中重新执行，而不是依赖上一节遗留的状态。如果结果与预期不同，先记录实际输出，再缩小问题范围。

十二、官方资料

版本行为与二手文章不一致时，以本系列固定版本的官方文档、命令输出和可重复测试结果为准。

上一篇：SELECT、表达式、NULL 与分页下一篇：连接、子查询、CTE 与集合运算