1. 认识DML增删改到底是什么为什么它才是数据库操作的主战场先说个可能颠覆认知的结论很多初学者学MySQL时把大量精力花在DDL建表语句上觉得创建表、修改字段才是重点。但真实项目里一天里你写的最多的语句一定是INSERT、UPDATE、DELETE这三兄弟。你写的CRUD接口、用户下单、修改订单状态、删除缓存数据落到数据库层面全都是DML操作。DML全称是Data Manipulation Language数据操纵语言核心就是三个关键字INSERT增、UPDATE改、DELETE删。注意SELECT查严格来说不算DML它属于DQLData Query Language。MySQL官方手册里把SELECT单列一类但我们平时说“增删改查”习惯把查也挂在嘴边。这一章我重点讲增、删、改因为查询的内容太多了值得单独开一篇。你只要记住一个原则DML操作的是表里的数据不会动表结构本身。你想给表加个字段那是DDL的事别混在一起。为什么我说DML才是主战场因为数据是流动的。用户在注册页面提交表单后端收到请求先校验参数然后往user表里INSERT一条记录用户改了昵称UPDATE一下用户注销账号DELETE或者标记删除。整个业务系统跑起来每个环节都在产生DML操作。有人统计过一个典型的电商系统读多写少是常态但写操作恰恰是保证数据准确性的关键。读操作出错了顶多是显示不对写操作一错数据就脏了后面查出来全是错的越跑越偏。还有一个容易被忽略的点DML操作是要走事务的。MySQL的InnoDB引擎默认每条DML语句自带事务但如果多条写操作要一起生效就得手动开启事务。比如转账功能A账户扣钱、B账户加钱两步必须同时成功或同时失败不能只扣不加。这就是事务要解决的原子性问题。学DML的时候如果不同时建立事务思维后面写业务代码很容易埋雷。这一章适合谁看已经能建库建表、熟悉MySQL基本命令的初学者以及写了一段时间SQL但没系统整理过增删改细节的朋友。我会从最基础的语法讲到多表操作、性能注意事项、踩坑实录全程用我实操过的场景做例子。学完之后你写增删改语句应该能做到“不查手册、不看报错也能一次写对”至少知道自己写的语句会带来什么影响。2. 插入数据INSERT的四种写法与踩坑现场2.1 最基础的INSERT INTO ... VALUES先看所有教材都会讲的标准写法INSERT INTO student (name, age, class_id, create_time) VALUES (张三, 20, 1, 2025-01-15 14:30:00);这里有两个细节初学者经常搞混。第一表名后面括号里是列名列表这段可以省略省略的话就默认把所有列都写上必须和表结构完全一致。我强烈建议你永远不要省略列名。为什么因为表结构总会变。今天表里有5列你写INSERT INTO student VALUES (1, 张三, 20, ...)写的时候刚刚好。明天别人给表加了一个remark字段你的语句立刻报错Column count doesnt match value count at row 1。但是你显式写列名加字段根本不影响你顶多是新字段用默认值。第二VALUES后面括号里是值列表顺序必须和前面的列名一一对应。这个规律很好记列名怎么排值就怎么排。字符串和日期要加单引号数字可以直接写。NULL表示空值如果某列有默认值你不想插入数据可以直接省略这列或者写成DEFAULT。2.2 一次插多行批量插入的效率差异真实业务不可能一行一行地插。用户批量导入、订单批量同步一次可能几百上千条。这时候用多行VALUESINSERT INTO student (name, age, class_id) VALUES (李四, 21, 2), (王五, 19, 3), (赵六, 22, 2);多行插入不是图省事而是数量级的效率提升。网上经常看到有人说“一次插1000行比循环插1000次快100倍”这话不严谨但方向上没错。有个很经典的比喻每执行一条INSERT数据库都要做一遍语法解析、权限检查、执行计划生成、事务日志写入。就像你寄快递哪怕每件包裹都只有一公里远你也要填1000次快递单、跑1000趟驿站。而批量插入等于一个快递单打包1000件包裹虽然包裹本身没变但流程和油费省了一大截。实测下来在我本地电脑的MySQL 8.0上单条插入1000条记录大概耗时1.2秒而批量插入10条一组、每次100条1000条总耗时约0.08秒快了十几倍。原因是批量插入减少了网络往返和日志刷盘次数。注意批量插入的数据量也别太大一次一万行以上会让binlog和事务日志压力骤增还可能锁很多行。我的建议是单次控制在500到1000行拆分批处理兼顾速度和资源占用。2.3 INSERT ... SET另一种顺手写法除了VALUESMySQL还支持SET写法INSERT INTO student SET name 孙七, age 18, class_id 4;这种写法最大的优点是可读性强列和值用等号配对一眼能看出哪个字段被赋了什么值但它的缺点是不支持一次插多行。所以实际开发中这种写法用得不算多主要出现在存储过程里或者临时调试时。写代码的时候为了统一风格还是建议把VALUES作为主力。2.4 从一张表复制数据INSERT ... SELECT最容易被忽略的用法是胯表复制。例如要根据老表数据生成新表记录INSERT INTO student_2025 (name, age, class_id) SELECT name, age, class_id FROM student WHERE create_time 2025-01-01;这个写法我愿称之为“数据搬家神器”。做报表、做数据清洗、把在线库数据同步到分析库都会用到。它的执行逻辑是先用SELECT查出数据再逐条INSERT到目标表。要注意两点一是SELECT出来的列数和类型要和目标表的列匹配这点和VALUES一样二是如果目标表有自增主键复制时千万别把源表的主键一起复制过去除非你就是想保留主键否则会让目标表的自增值乱掉。2.5 自增主键的坑别乱插ID自增主键是很多MySQL表的标准配置。它最大的好处是天然有序、索引效率高。但有三种坑我必须提醒你。第一显式插入一个超过当前自增值的ID会导致后续自增值跳变。比如你表里最大ID是100你手贱插入一条ID2000的记录下一次正常插入时自增会直接跳到2001而不是101。如果你没有清理习惯这个数字会越来越大。第二事务回滚后自增也不回退。InnoDB的自增是申请后就分配哪怕事务回滚了这个ID也不会重新用因为InnoDB不会去维护“已用但失败”的ID列表。所以看到ID中间有空洞不要慌这不是数据丢了是自增的正常表现。第三REPLACE INTO会影响自增。REPLACE是先删后插如果插入时没有指定ID它会找一条与唯一键冲突的记录删掉再用新ID插入会让ID增长更快。如果不需要这种“替换”语义建议还是用INSERT加上ON DUPLICATE KEY UPDATE语法至少更新时不会无谓地消耗自增ID。3. 更新数据UPDATE的正确打开方式以及误更新如何自救3.1 UPDATE基本语法与高危红线UPDATE的语法框架很简单UPDATE student SET age 21, update_time NOW() WHERE id 5;重点是SET后面是赋值操作WHERE决定要改哪些行。WHERE是UPDATE的高危红线。写UPDATE时先问自己一句这个WHERE能命中我真正想改的那几条记录吗如果WHERE子句写错或者干脆忘了写那就是全表更新。这个教训我见过太多次了。曾经有个同事在测试环境执行UPDATE user SET status 1没加WHERE把线上用户状态全改了。还好是测试库但任何时候养成“条件先行”的习惯都不亏。验证WHERE是否正确有一个土办法执行UPDATE之前先把语句改成SELECT看看会查出哪些行。-- 先执行确认影响范围 SELECT id, name FROM student WHERE age 20; -- 确认无误后再执行 UPDATE student SET age 21 WHERE age 20;这个习惯看起来多敲了几行代码但省下来的可能是几个小时甚至几天的恢复成本。在MySQL里你还可以在执行UPDATE时用LIMIT限制更新行数作为失控时的安全兜底但不是所有场景都支持最好还是靠严谨的WHERE来保证。3.2 更新操作里的表达式运算UPDATE不仅能直接赋固定值还能在现有值基础上计算。最常见的例子是把商品价格统一涨10%UPDATE product SET price price * 1.1 WHERE category_id 3;注意这里price price * 1.1SQL在执行时先读取每一行的当前price值乘1.1后再写回去。这和高年级编程语言里的“变量自增”是同一个思路。在MySQL中还可以在SET里同时更新多个字段例如UPDATE t SET score score 5, times times 1 WHERE id 1这两个更新会基于同一时间点的快照执行不用担心先后顺序的问题。这里有个隐藏性能细节如果WHERE条件用到的列上有索引UPDATE先走索引定位到目标行再去改数据速度快很多。如果没有索引那就是全表扫描一行一行匹配数据量一大就成了灾难。所以WHERE条件里的列尽量加索引尤其是在高频更新的表上。这个原则在DELETE里同样适用。3.3 多表更新UPDATE JOIN的两种写法业务里经常要按另一张表的条件来更新。例如根据教师表的评分来更新学生表的综合等级UPDATE student s JOIN teacher t ON s.teacher_id t.id SET s.grade_level A WHERE t.score 90;还有更接近自然语言的写法UPDATE student s, teacher t SET s.grade_level A WHERE s.teacher_id t.id AND t.score 90;两种写法都能实现区别在于是用显式JOIN还是用逗号连接加WHERE。我建议优先用第一种UPDATE ... JOIN ... ON的写法因为连接条件在ON里写得清楚不容易和过滤条件混在一起。多表更新最怕的坑是笛卡尔积如果忘记写表之间的关联条件就会把一个表的每一行都和另一表所有行配对更新范围瞬间爆炸。比如学生表1000行、教师表50行不加关联条件的话可能更新50000行那时候就哭都来不及了。3.4 误更新之后的急救措施万一真把UPDATE写错了、铺天盖地改了一片数据怎么办最有效的止损方式是立刻从备份恢复但备份可能不是实时的。如果你的表或者整个库开启了binlog而且配置了row格式就能根据binlog回放把被修改的行恢复到之前的状态。具体做法是先把误操作的时间点确定然后用mysqlbinlog工具解析出那段时间段的SQL提取出误更新之前的数据形态反向生成UPDATE或者重新INSERT。这个过程不轻松但确实能救命。没有binlog怎么办只能看有没有其他备份、有没有数据库层面的事务快照。所以备份意识和binlog开启意识必须从第一天写DML语句时就在脑子里生根。写了这么多年SQL我真心建议你生产环境千万不要关闭binlog这是你最重要的一道保险。4. 删除数据DELETE与TRUNCATE选错工具等于烧掉退路4.1 DELETE的两种终极形态DELETE是删除行的操作基本语法为DELETE FROM student WHERE id 10;执行后目标行从表里消失。注意DELETE只是逻辑删除行为底层InnoDB不会立刻把磁盘空间归还操作系统而是标记这些记录为删除状态空间留给后续新数据复用。所以你会发现刚删完几百万行数据表的物理文件大小变化不大。这个概念对理解MySQL的碎片化和空间占用非常重要。如果你不加WHERE条件写的是DELETE FROM student;这和上面那个安慰剂版本有本质区别。不带WHERE的DELETE是清空整张表的数据但保留了表结构、索引定义、自增值设置。很多人以为这和TRUNCATE差不多一开始我也这么想后来深入研究才发现完全两码事。4.2 DELETE与TRUNCATE的关键差异TRUNCATE的语法是TRUNCATE TABLE student它也删除全部行但实现方式和DELETE完全不同。DELETE是逐行删除期间会产生大量事务日志每条删除记录都会被记录到binlog里恢复时可以精确到某一行。TRUNCATE是直接把表的数据页整个重置相当于把一本写满的练习册直接换一本新的效率极高但无法逐行找回。还有一个关键区别TRUNCATE会把表的自增计数器重置为1而DELETE不支持重置除非你显式把自增值改回去。也就是说你清空一张表后如果希望ID重新从1开始用TRUNCATE就行如果还想从上次的ID继续递增得用DELETE。TRUNCATE还有一个隐藏特性它不能在有外键引用的情况下使用。如果其他表的外键指向这张表TRUNCATE会被拒绝而DELETE如果删的是子表没引用到的行倒是可能执行成功。因为InnoDB逐行删除时会检查外键约束TRUNCATE是按页重置不检查外键所以数据库干脆禁止了这种操作。在MySQL 8.0里TRUNCATE还会隐式提交事务也就是不支持事务回滚而DELETE是可以在事务里执行并回滚的。对比项DELETETRUNCATE是否逐行删除是逐行匹配并记录日志否直接重置数据页能否加WHERE条件可以不可以只能全表清空事务内是否可回滚可以不可以隐式提交是否重置自增ID不重置重置为初始值对空间占用影响空间复用但文件不一定变小删除数据页空间立即标记待用外键约束逐行检查不允许操作这个表格建议存一下。选择方案时我一般是要精准删除部分数据用DELETE要清空整张表、且不担心事务回滚的用TRUNCATE。日常开发里TRUNCATE用得少因为大多数表都有保留价值哪怕是所有数据都过期也常会用DELETE加条件分批次删而不是一把梭清空。4.3 物理删除还是逻辑删除新人写项目一接到“删除用户”、“删除订单”这种需求第一反应就是DELETE。但真实业务系统里硬删除物理删除要非常谨慎。电商平台的订单删除用户后订单状态变成“已取消”但这条记录得保留用于对账、售后、司法取证。用户的账号注销也不是直接DELETE用户表通常是把status字段更改为-1或者打一个deleted_at时间戳。这种保留数据、只改变标记的做法叫逻辑删除也叫软删除。它最大的好处是数据可追溯随时可以“反悔”。当然也有代价每次查询都要记得过滤掉已经标记删除的记录否则统计结果会虚高。很多框架比如MyBatis-Plus有逻辑删除插件配置一个deleted字段后查询会自动加上WHERE deleted 0DML操作也会被自动改写。如果你给业务做设计我建议默认优先用逻辑删除只有当数据量实在太大、或者有法律法规要求彻底删除时再考虑物理删除。划分标准很简单这条数据删了之后有没有可能在某一天被查到、被统计到、被恢复只要能说不上来“不可能”就说明该用逻辑删除。5. 事务加持让增删改更安全5.1 事务到底是什么事务把多条DML语句打包成一个原子性的执行单元要么全成功要么全失败不会出现执行了一半的情况。现实比喻就是转账A账户扣1000、B账户加1000这两步必须作为一个整体。如果扣款成功、加款失败整个事务回滚A账户的1000也恢复原样不会让钱凭空消失。MySQL的InnoDB引擎原生支持事务。MyISAM引擎在MySQL 8.0已经被移除了因为它不支持事务、不支持崩溃恢复数据安全系数很低。你只要记住目前的主流默认引擎InnoDB事务是可靠的。5.2 手动事务的正确写法MySQL默认自动提交autocommit1每一条DML语句独立成一个事务。需要多条语句作为一个整体时要手动控制START TRANSACTION; UPDATE account SET balance balance - 1000 WHERE id 1; UPDATE account SET balance balance 1000 WHERE id 2; COMMIT;如果第二条UPDATE执行失败你可以在应用程序里发送ROLLBACK把第一个UPDATE的结果也撤销掉。判断是否成功在命令行里可以看ROW COUNT和是否有报错在Java里通常是捕获异常后主动rollback。这条顺序能严格控制的情况下事务的性能影响很小但能换来数据一致性这笔账怎么算都划算。5.3 事务隔离级别不用背但要知道有这么回事事务的核心特性有四个ACID原子性Atomicity、一致性Consistency、隔离性Isolation、持久性Durability。其中隔离性对新人最不直观。多个事务同时操作同一行数据如果没有隔离就会出现脏读、不可重复读、幻读这些问题。MySQL的默认隔离级别是Repeatable Read可重复读意思是一个事务里多次查询同一数据结果是一致的不会因为其他事务的提交而变来变去。这个默认值已经解决了很多不必要的麻烦所以新手阶段你不需要调整隔离级别但至少要知道有这个概念。隔离级别影响DML的一个重要场景是锁。行级锁在InnoDB里是默认启用的一个事务UPDATE一行另一个事务再UPDATE同一行时会被阻塞等第一个事务提交或回滚后才能继续。这个机制保证了并发写不出错但也带来了一个问题长事务会长时间锁住热数据行导致业务响应变慢。我遇到过一个案例某个定时任务在一个事务里更新了几千行数据事务没提交导致前台用户下订单时同一行数据被锁一直等待最终出现大量超时。所以写DML时事务保持简短COMMIT要快锁的持有时间自然也就短了。6. 实战演示一个完整的学生管理系统中的增删改操作6.1 场景设计为了让你把前面零散的知识串起来我模拟一个学生管理系统的最核心操作。假设有一张学生表CREATE TABLE student ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, age TINYINT UNSIGNED, class_id INT, status TINYINT DEFAULT 1 COMMENT 1-在读0-离校, create_time DATETIME DEFAULT CURRENT_TIMESTAMP, update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );插入两条初始数据INSERT INTO student (name, age, class_id) VALUES (张三, 20, 1); INSERT INTO student (name, age, class_id) VALUES (李四, 21, 2);6.2 完整操作流程演示新学年来了王五入学INSERT INTO student (name, age, class_id) VALUES (王五, 19, 3);张三转班从1班转到2班UPDATE student SET class_id 2, update_time NOW() WHERE name 张三;注意这里用name 张三定位是有风险的因为现实中可能有两个张三。实务开发里WHERE条件一定要用唯一主键ID比如WHERE id 1否则可能一次更新多条记录。演示代码图方便就用名称了你到了正式项目千万别这么写。李四毕业离校逻辑删除UPDATE student SET status 0 WHERE id 2;这部分数据其实还留在表里只是业务查询时过滤掉status0的用户。需要物理删除一条测试数据时DELETE FROM student WHERE id 3;但这些操作往往混合在一个业务流程里。例如批量导入学生名单先校验表格数据再循环执行INSERT如果某几条数据格式不对可能导致整个导入失败。正确做法是把整个导入包在一个事务里任一条失败就整体回滚避免半截数据留在库里。这种“要么全部成功要么一条都不放进去”的思维就是DML操作最重要的素养。6.3 备份与恢复的简单套路说到DML操作就不能不提如何不把数据玩脱。最轻量级的备份方案是mysqldump逻辑备份mysqldump -u root -p database_name backup.sql恢复时执行mysql -u root -p database_name backup.sql还有一个效率更高的工具是MySQL 8.0带来的物理备份工具但那是进阶话题。新手阶段你有两条底线第一重要操作前先备份第二在事务里执行重要的DELETE或UPDATE先START TRANSACTION确认无误再COMMIT。这样万一出问题可以在没有关闭会话时用ROLLBACK及时救回来。7. 常见问题与排查思路那些年我们踩过的坑7.1 UPDATE或DELETE执行不下去一直卡着最常见的现象是执行一条UPDATE或DELETE时命令行或客户端一直转圈没有报错也不返回。这是被锁了。另一个会话正在修改同一行数据事务还没提交你的语句在等待行锁。排查方法是查performance_schema里的锁等待或用SHOW PROCESSLIST看当前有哪些连接在跑什么语句。简单判断那个lock前面有个连接的状态是“Updating”或者“Sending data”就说明它占着锁。遇到锁等待第一反应不是立刻kill进程而是要问占锁的事务是不是一条很长的事务。杀掉占锁会话前要谨慎如果它的事务正在写重要数据杀掉可能造成部分回滚但只要没提交数据可以回滚到一致状态不至于脏。生产环境里处理锁等待的规范流程是先定位阻塞源头评估是否可以等待确实需要才KILL掉阻塞会话然后快速提交你自己的事务减少锁持有时间。7.2 忘记WHERE导致数据全没这一类问题没有任何技术难度纯粹是习惯问题。DELETE或UPDATE不带WHERE或者WHERE写错导致全表数据被清空或全部被改错。遇到这种情形先不要慌立刻评估有没有备份和binlog。如果误操作前有备份恢复即可如果没有binlog也许能反转。举一个真实的恢复案例某天凌晨3点运营同事把用户表的status字段全部UPDATE成0本该是3个月前的老用户。因为UPDATE是逐行记录binlog的我们通过解析binlog把凌晨3点前那段日志对应的行状态读出来然后生成一批新的UPDATE语句把每个用户恢复成操作之前的值。整个过程大概花了一个多小时最终数据完全恢复。这个案例的关键是binlog配置的是ROW格式日志里保留了修改前和修改后的值才可以精准恢复。如果你配置的是STATEMENT格式日志里只记录SQL语句本身恢复还原度就低了。所以我会建议生产环境一律使用ROW格式的binlog方便排查和误操作恢复。7.3 插入中文报错或乱码向表里插入中文出现乱码或者报错Incorrect string value一般是字符集没有设置正确。MySQL 8.0默认字符集是utf8mb4能存emoji和所有中文。如果你的表和库还停留在latin1或utf8mb3就需要改成utf8mb4。建表时建议直接写明字符集CREATE TABLE student ( ... ) DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;utf8mb4_unicode_ci是比较规则它对大小写不敏感适合大多数场景。如果你要考虑更精确的排序可以用utf8mb4_0900_ai_ciMySQL 8.0默认。字符集优先在建库建表阶段规划好不要寄希望於事后转变字符集那是一场灾难级的工作量。7.4 NULL值参与运算的坑DML里NULL是个隐形地雷。例如UPDATE student SET score score 5 WHERE id 5;如果原来score NULL那么运算结果不是5而是NULL。因为在SQL中NULL和任何数字做四则运算结果都是NULL。这会导致原本没成绩的学生更新完后成绩还是空值。排查这类问题需要两个意识一是知道SQL的NULL传播规则二是提前做空值处理比如UPDATE student SET score IFNULL(score, 0) 5 WHERE id 5;用了IFNULL当字段为NULL时先替换成0再做加法结果就是5了。这个函数在INSERT、UPDATE里都很常用学DML的时候顺手把它记住后面写查询也少不了它。7.5 数值越界或类型不匹配insert into student (age) values (二十)会报错。MySQL对类型检查其实不算严格有些情况它会自动做隐式转换字符串的数字可以插入到INT列里但纯中文不行。年龄设计成TINYINT的话范围是0到255插一个300进去会报OUT OF RANGE错误。最佳实践是字段类型设计时留足余量同时代码层面做参数校验别让错误数据到达数据库层。8. 几个值得坚持的DML习惯多年经验总结做DML操作这行纯靠技术挡不住失误真正能救命的是习惯。我把自己多年攒下的经验整理成几条铁律你可以直接抄走。第一写UPDATE和DELETE之前先写SELECT确认影响范围。哪怕只多花十秒钟也能避免“炸掉一张表”的惨剧。第二重要的写操作都在事务里执行COMIT确认无误后再提交。这条适用于任何需要两步以上变化的场景。第三批量写入数据时控制每个批次的行数不要一条SQL包几千上万条插入容易让数据库服务压力飙高。第四历史数据或敏感数据宁可保留不要随意物理删除。逻辑删除是好习惯查询时带上状态过滤就行。第五生产环境一定开启binlog并配置为ROW格式。做不到这一点就没有资格操作重要数据。我见过太多新手对着工具咔咔点DELETE一把梭最后数据找不回来一脸无辜地看着我说“我没想到会这样”。所以我会反复强调DML操作永远是“谨慎第一效率第二”。SQL的编写速度可以通过练习提升但安全意识必须从一开始就建立。一个高级开发者和初级开发者的差别往往不是谁SQL写得快而是谁更少把生产库搞出事故。如果这章的内容你能吃透并且把上面几条习惯内化成肌肉记忆那么MySQL的增删改这一关就算真正过了。后面再学存储过程、触发器、分区表这些进阶内容会更加从容。接下来我还会继续更新查询、索引优化、事务隔离级别的高级用法跟着这个节奏走MySQL这条路会越走越稳。 MySQL DML技术详解：从增删改到事务与误操作恢复
1. 认识DML增删改到底是什么为什么它才是数据库操作的主战场先说个可能颠覆认知的结论很多初学者学MySQL时把大量精力花在DDL建表语句上觉得创建表、修改字段才是重点。但真实项目里一天里你写的最多的语句一定是INSERT、UPDATE、DELETE这三兄弟。你写的CRUD接口、用户下单、修改订单状态、删除缓存数据落到数据库层面全都是DML操作。DML全称是Data Manipulation Language数据操纵语言核心就是三个关键字INSERT增、UPDATE改、DELETE删。注意SELECT查严格来说不算DML它属于DQLData Query Language。MySQL官方手册里把SELECT单列一类但我们平时说“增删改查”习惯把查也挂在嘴边。这一章我重点讲增、删、改因为查询的内容太多了值得单独开一篇。你只要记住一个原则DML操作的是表里的数据不会动表结构本身。你想给表加个字段那是DDL的事别混在一起。为什么我说DML才是主战场因为数据是流动的。用户在注册页面提交表单后端收到请求先校验参数然后往user表里INSERT一条记录用户改了昵称UPDATE一下用户注销账号DELETE或者标记删除。整个业务系统跑起来每个环节都在产生DML操作。有人统计过一个典型的电商系统读多写少是常态但写操作恰恰是保证数据准确性的关键。读操作出错了顶多是显示不对写操作一错数据就脏了后面查出来全是错的越跑越偏。还有一个容易被忽略的点DML操作是要走事务的。MySQL的InnoDB引擎默认每条DML语句自带事务但如果多条写操作要一起生效就得手动开启事务。比如转账功能A账户扣钱、B账户加钱两步必须同时成功或同时失败不能只扣不加。这就是事务要解决的原子性问题。学DML的时候如果不同时建立事务思维后面写业务代码很容易埋雷。这一章适合谁看已经能建库建表、熟悉MySQL基本命令的初学者以及写了一段时间SQL但没系统整理过增删改细节的朋友。我会从最基础的语法讲到多表操作、性能注意事项、踩坑实录全程用我实操过的场景做例子。学完之后你写增删改语句应该能做到“不查手册、不看报错也能一次写对”至少知道自己写的语句会带来什么影响。2. 插入数据INSERT的四种写法与踩坑现场2.1 最基础的INSERT INTO ... VALUES先看所有教材都会讲的标准写法INSERT INTO student (name, age, class_id, create_time) VALUES (张三, 20, 1, 2025-01-15 14:30:00);这里有两个细节初学者经常搞混。第一表名后面括号里是列名列表这段可以省略省略的话就默认把所有列都写上必须和表结构完全一致。我强烈建议你永远不要省略列名。为什么因为表结构总会变。今天表里有5列你写INSERT INTO student VALUES (1, 张三, 20, ...)写的时候刚刚好。明天别人给表加了一个remark字段你的语句立刻报错Column count doesnt match value count at row 1。但是你显式写列名加字段根本不影响你顶多是新字段用默认值。第二VALUES后面括号里是值列表顺序必须和前面的列名一一对应。这个规律很好记列名怎么排值就怎么排。字符串和日期要加单引号数字可以直接写。NULL表示空值如果某列有默认值你不想插入数据可以直接省略这列或者写成DEFAULT。2.2 一次插多行批量插入的效率差异真实业务不可能一行一行地插。用户批量导入、订单批量同步一次可能几百上千条。这时候用多行VALUESINSERT INTO student (name, age, class_id) VALUES (李四, 21, 2), (王五, 19, 3), (赵六, 22, 2);多行插入不是图省事而是数量级的效率提升。网上经常看到有人说“一次插1000行比循环插1000次快100倍”这话不严谨但方向上没错。有个很经典的比喻每执行一条INSERT数据库都要做一遍语法解析、权限检查、执行计划生成、事务日志写入。就像你寄快递哪怕每件包裹都只有一公里远你也要填1000次快递单、跑1000趟驿站。而批量插入等于一个快递单打包1000件包裹虽然包裹本身没变但流程和油费省了一大截。实测下来在我本地电脑的MySQL 8.0上单条插入1000条记录大概耗时1.2秒而批量插入10条一组、每次100条1000条总耗时约0.08秒快了十几倍。原因是批量插入减少了网络往返和日志刷盘次数。注意批量插入的数据量也别太大一次一万行以上会让binlog和事务日志压力骤增还可能锁很多行。我的建议是单次控制在500到1000行拆分批处理兼顾速度和资源占用。2.3 INSERT ... SET另一种顺手写法除了VALUESMySQL还支持SET写法INSERT INTO student SET name 孙七, age 18, class_id 4;这种写法最大的优点是可读性强列和值用等号配对一眼能看出哪个字段被赋了什么值但它的缺点是不支持一次插多行。所以实际开发中这种写法用得不算多主要出现在存储过程里或者临时调试时。写代码的时候为了统一风格还是建议把VALUES作为主力。2.4 从一张表复制数据INSERT ... SELECT最容易被忽略的用法是胯表复制。例如要根据老表数据生成新表记录INSERT INTO student_2025 (name, age, class_id) SELECT name, age, class_id FROM student WHERE create_time 2025-01-01;这个写法我愿称之为“数据搬家神器”。做报表、做数据清洗、把在线库数据同步到分析库都会用到。它的执行逻辑是先用SELECT查出数据再逐条INSERT到目标表。要注意两点一是SELECT出来的列数和类型要和目标表的列匹配这点和VALUES一样二是如果目标表有自增主键复制时千万别把源表的主键一起复制过去除非你就是想保留主键否则会让目标表的自增值乱掉。2.5 自增主键的坑别乱插ID自增主键是很多MySQL表的标准配置。它最大的好处是天然有序、索引效率高。但有三种坑我必须提醒你。第一显式插入一个超过当前自增值的ID会导致后续自增值跳变。比如你表里最大ID是100你手贱插入一条ID2000的记录下一次正常插入时自增会直接跳到2001而不是101。如果你没有清理习惯这个数字会越来越大。第二事务回滚后自增也不回退。InnoDB的自增是申请后就分配哪怕事务回滚了这个ID也不会重新用因为InnoDB不会去维护“已用但失败”的ID列表。所以看到ID中间有空洞不要慌这不是数据丢了是自增的正常表现。第三REPLACE INTO会影响自增。REPLACE是先删后插如果插入时没有指定ID它会找一条与唯一键冲突的记录删掉再用新ID插入会让ID增长更快。如果不需要这种“替换”语义建议还是用INSERT加上ON DUPLICATE KEY UPDATE语法至少更新时不会无谓地消耗自增ID。3. 更新数据UPDATE的正确打开方式以及误更新如何自救3.1 UPDATE基本语法与高危红线UPDATE的语法框架很简单UPDATE student SET age 21, update_time NOW() WHERE id 5;重点是SET后面是赋值操作WHERE决定要改哪些行。WHERE是UPDATE的高危红线。写UPDATE时先问自己一句这个WHERE能命中我真正想改的那几条记录吗如果WHERE子句写错或者干脆忘了写那就是全表更新。这个教训我见过太多次了。曾经有个同事在测试环境执行UPDATE user SET status 1没加WHERE把线上用户状态全改了。还好是测试库但任何时候养成“条件先行”的习惯都不亏。验证WHERE是否正确有一个土办法执行UPDATE之前先把语句改成SELECT看看会查出哪些行。-- 先执行确认影响范围 SELECT id, name FROM student WHERE age 20; -- 确认无误后再执行 UPDATE student SET age 21 WHERE age 20;这个习惯看起来多敲了几行代码但省下来的可能是几个小时甚至几天的恢复成本。在MySQL里你还可以在执行UPDATE时用LIMIT限制更新行数作为失控时的安全兜底但不是所有场景都支持最好还是靠严谨的WHERE来保证。3.2 更新操作里的表达式运算UPDATE不仅能直接赋固定值还能在现有值基础上计算。最常见的例子是把商品价格统一涨10%UPDATE product SET price price * 1.1 WHERE category_id 3;注意这里price price * 1.1SQL在执行时先读取每一行的当前price值乘1.1后再写回去。这和高年级编程语言里的“变量自增”是同一个思路。在MySQL中还可以在SET里同时更新多个字段例如UPDATE t SET score score 5, times times 1 WHERE id 1这两个更新会基于同一时间点的快照执行不用担心先后顺序的问题。这里有个隐藏性能细节如果WHERE条件用到的列上有索引UPDATE先走索引定位到目标行再去改数据速度快很多。如果没有索引那就是全表扫描一行一行匹配数据量一大就成了灾难。所以WHERE条件里的列尽量加索引尤其是在高频更新的表上。这个原则在DELETE里同样适用。3.3 多表更新UPDATE JOIN的两种写法业务里经常要按另一张表的条件来更新。例如根据教师表的评分来更新学生表的综合等级UPDATE student s JOIN teacher t ON s.teacher_id t.id SET s.grade_level A WHERE t.score 90;还有更接近自然语言的写法UPDATE student s, teacher t SET s.grade_level A WHERE s.teacher_id t.id AND t.score 90;两种写法都能实现区别在于是用显式JOIN还是用逗号连接加WHERE。我建议优先用第一种UPDATE ... JOIN ... ON的写法因为连接条件在ON里写得清楚不容易和过滤条件混在一起。多表更新最怕的坑是笛卡尔积如果忘记写表之间的关联条件就会把一个表的每一行都和另一表所有行配对更新范围瞬间爆炸。比如学生表1000行、教师表50行不加关联条件的话可能更新50000行那时候就哭都来不及了。3.4 误更新之后的急救措施万一真把UPDATE写错了、铺天盖地改了一片数据怎么办最有效的止损方式是立刻从备份恢复但备份可能不是实时的。如果你的表或者整个库开启了binlog而且配置了row格式就能根据binlog回放把被修改的行恢复到之前的状态。具体做法是先把误操作的时间点确定然后用mysqlbinlog工具解析出那段时间段的SQL提取出误更新之前的数据形态反向生成UPDATE或者重新INSERT。这个过程不轻松但确实能救命。没有binlog怎么办只能看有没有其他备份、有没有数据库层面的事务快照。所以备份意识和binlog开启意识必须从第一天写DML语句时就在脑子里生根。写了这么多年SQL我真心建议你生产环境千万不要关闭binlog这是你最重要的一道保险。4. 删除数据DELETE与TRUNCATE选错工具等于烧掉退路4.1 DELETE的两种终极形态DELETE是删除行的操作基本语法为DELETE FROM student WHERE id 10;执行后目标行从表里消失。注意DELETE只是逻辑删除行为底层InnoDB不会立刻把磁盘空间归还操作系统而是标记这些记录为删除状态空间留给后续新数据复用。所以你会发现刚删完几百万行数据表的物理文件大小变化不大。这个概念对理解MySQL的碎片化和空间占用非常重要。如果你不加WHERE条件写的是DELETE FROM student;这和上面那个安慰剂版本有本质区别。不带WHERE的DELETE是清空整张表的数据但保留了表结构、索引定义、自增值设置。很多人以为这和TRUNCATE差不多一开始我也这么想后来深入研究才发现完全两码事。4.2 DELETE与TRUNCATE的关键差异TRUNCATE的语法是TRUNCATE TABLE student它也删除全部行但实现方式和DELETE完全不同。DELETE是逐行删除期间会产生大量事务日志每条删除记录都会被记录到binlog里恢复时可以精确到某一行。TRUNCATE是直接把表的数据页整个重置相当于把一本写满的练习册直接换一本新的效率极高但无法逐行找回。还有一个关键区别TRUNCATE会把表的自增计数器重置为1而DELETE不支持重置除非你显式把自增值改回去。也就是说你清空一张表后如果希望ID重新从1开始用TRUNCATE就行如果还想从上次的ID继续递增得用DELETE。TRUNCATE还有一个隐藏特性它不能在有外键引用的情况下使用。如果其他表的外键指向这张表TRUNCATE会被拒绝而DELETE如果删的是子表没引用到的行倒是可能执行成功。因为InnoDB逐行删除时会检查外键约束TRUNCATE是按页重置不检查外键所以数据库干脆禁止了这种操作。在MySQL 8.0里TRUNCATE还会隐式提交事务也就是不支持事务回滚而DELETE是可以在事务里执行并回滚的。对比项DELETETRUNCATE是否逐行删除是逐行匹配并记录日志否直接重置数据页能否加WHERE条件可以不可以只能全表清空事务内是否可回滚可以不可以隐式提交是否重置自增ID不重置重置为初始值对空间占用影响空间复用但文件不一定变小删除数据页空间立即标记待用外键约束逐行检查不允许操作这个表格建议存一下。选择方案时我一般是要精准删除部分数据用DELETE要清空整张表、且不担心事务回滚的用TRUNCATE。日常开发里TRUNCATE用得少因为大多数表都有保留价值哪怕是所有数据都过期也常会用DELETE加条件分批次删而不是一把梭清空。4.3 物理删除还是逻辑删除新人写项目一接到“删除用户”、“删除订单”这种需求第一反应就是DELETE。但真实业务系统里硬删除物理删除要非常谨慎。电商平台的订单删除用户后订单状态变成“已取消”但这条记录得保留用于对账、售后、司法取证。用户的账号注销也不是直接DELETE用户表通常是把status字段更改为-1或者打一个deleted_at时间戳。这种保留数据、只改变标记的做法叫逻辑删除也叫软删除。它最大的好处是数据可追溯随时可以“反悔”。当然也有代价每次查询都要记得过滤掉已经标记删除的记录否则统计结果会虚高。很多框架比如MyBatis-Plus有逻辑删除插件配置一个deleted字段后查询会自动加上WHERE deleted 0DML操作也会被自动改写。如果你给业务做设计我建议默认优先用逻辑删除只有当数据量实在太大、或者有法律法规要求彻底删除时再考虑物理删除。划分标准很简单这条数据删了之后有没有可能在某一天被查到、被统计到、被恢复只要能说不上来“不可能”就说明该用逻辑删除。5. 事务加持让增删改更安全5.1 事务到底是什么事务把多条DML语句打包成一个原子性的执行单元要么全成功要么全失败不会出现执行了一半的情况。现实比喻就是转账A账户扣1000、B账户加1000这两步必须作为一个整体。如果扣款成功、加款失败整个事务回滚A账户的1000也恢复原样不会让钱凭空消失。MySQL的InnoDB引擎原生支持事务。MyISAM引擎在MySQL 8.0已经被移除了因为它不支持事务、不支持崩溃恢复数据安全系数很低。你只要记住目前的主流默认引擎InnoDB事务是可靠的。5.2 手动事务的正确写法MySQL默认自动提交autocommit1每一条DML语句独立成一个事务。需要多条语句作为一个整体时要手动控制START TRANSACTION; UPDATE account SET balance balance - 1000 WHERE id 1; UPDATE account SET balance balance 1000 WHERE id 2; COMMIT;如果第二条UPDATE执行失败你可以在应用程序里发送ROLLBACK把第一个UPDATE的结果也撤销掉。判断是否成功在命令行里可以看ROW COUNT和是否有报错在Java里通常是捕获异常后主动rollback。这条顺序能严格控制的情况下事务的性能影响很小但能换来数据一致性这笔账怎么算都划算。5.3 事务隔离级别不用背但要知道有这么回事事务的核心特性有四个ACID原子性Atomicity、一致性Consistency、隔离性Isolation、持久性Durability。其中隔离性对新人最不直观。多个事务同时操作同一行数据如果没有隔离就会出现脏读、不可重复读、幻读这些问题。MySQL的默认隔离级别是Repeatable Read可重复读意思是一个事务里多次查询同一数据结果是一致的不会因为其他事务的提交而变来变去。这个默认值已经解决了很多不必要的麻烦所以新手阶段你不需要调整隔离级别但至少要知道有这个概念。隔离级别影响DML的一个重要场景是锁。行级锁在InnoDB里是默认启用的一个事务UPDATE一行另一个事务再UPDATE同一行时会被阻塞等第一个事务提交或回滚后才能继续。这个机制保证了并发写不出错但也带来了一个问题长事务会长时间锁住热数据行导致业务响应变慢。我遇到过一个案例某个定时任务在一个事务里更新了几千行数据事务没提交导致前台用户下订单时同一行数据被锁一直等待最终出现大量超时。所以写DML时事务保持简短COMMIT要快锁的持有时间自然也就短了。6. 实战演示一个完整的学生管理系统中的增删改操作6.1 场景设计为了让你把前面零散的知识串起来我模拟一个学生管理系统的最核心操作。假设有一张学生表CREATE TABLE student ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, age TINYINT UNSIGNED, class_id INT, status TINYINT DEFAULT 1 COMMENT 1-在读0-离校, create_time DATETIME DEFAULT CURRENT_TIMESTAMP, update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );插入两条初始数据INSERT INTO student (name, age, class_id) VALUES (张三, 20, 1); INSERT INTO student (name, age, class_id) VALUES (李四, 21, 2);6.2 完整操作流程演示新学年来了王五入学INSERT INTO student (name, age, class_id) VALUES (王五, 19, 3);张三转班从1班转到2班UPDATE student SET class_id 2, update_time NOW() WHERE name 张三;注意这里用name 张三定位是有风险的因为现实中可能有两个张三。实务开发里WHERE条件一定要用唯一主键ID比如WHERE id 1否则可能一次更新多条记录。演示代码图方便就用名称了你到了正式项目千万别这么写。李四毕业离校逻辑删除UPDATE student SET status 0 WHERE id 2;这部分数据其实还留在表里只是业务查询时过滤掉status0的用户。需要物理删除一条测试数据时DELETE FROM student WHERE id 3;但这些操作往往混合在一个业务流程里。例如批量导入学生名单先校验表格数据再循环执行INSERT如果某几条数据格式不对可能导致整个导入失败。正确做法是把整个导入包在一个事务里任一条失败就整体回滚避免半截数据留在库里。这种“要么全部成功要么一条都不放进去”的思维就是DML操作最重要的素养。6.3 备份与恢复的简单套路说到DML操作就不能不提如何不把数据玩脱。最轻量级的备份方案是mysqldump逻辑备份mysqldump -u root -p database_name backup.sql恢复时执行mysql -u root -p database_name backup.sql还有一个效率更高的工具是MySQL 8.0带来的物理备份工具但那是进阶话题。新手阶段你有两条底线第一重要操作前先备份第二在事务里执行重要的DELETE或UPDATE先START TRANSACTION确认无误再COMMIT。这样万一出问题可以在没有关闭会话时用ROLLBACK及时救回来。7. 常见问题与排查思路那些年我们踩过的坑7.1 UPDATE或DELETE执行不下去一直卡着最常见的现象是执行一条UPDATE或DELETE时命令行或客户端一直转圈没有报错也不返回。这是被锁了。另一个会话正在修改同一行数据事务还没提交你的语句在等待行锁。排查方法是查performance_schema里的锁等待或用SHOW PROCESSLIST看当前有哪些连接在跑什么语句。简单判断那个lock前面有个连接的状态是“Updating”或者“Sending data”就说明它占着锁。遇到锁等待第一反应不是立刻kill进程而是要问占锁的事务是不是一条很长的事务。杀掉占锁会话前要谨慎如果它的事务正在写重要数据杀掉可能造成部分回滚但只要没提交数据可以回滚到一致状态不至于脏。生产环境里处理锁等待的规范流程是先定位阻塞源头评估是否可以等待确实需要才KILL掉阻塞会话然后快速提交你自己的事务减少锁持有时间。7.2 忘记WHERE导致数据全没这一类问题没有任何技术难度纯粹是习惯问题。DELETE或UPDATE不带WHERE或者WHERE写错导致全表数据被清空或全部被改错。遇到这种情形先不要慌立刻评估有没有备份和binlog。如果误操作前有备份恢复即可如果没有binlog也许能反转。举一个真实的恢复案例某天凌晨3点运营同事把用户表的status字段全部UPDATE成0本该是3个月前的老用户。因为UPDATE是逐行记录binlog的我们通过解析binlog把凌晨3点前那段日志对应的行状态读出来然后生成一批新的UPDATE语句把每个用户恢复成操作之前的值。整个过程大概花了一个多小时最终数据完全恢复。这个案例的关键是binlog配置的是ROW格式日志里保留了修改前和修改后的值才可以精准恢复。如果你配置的是STATEMENT格式日志里只记录SQL语句本身恢复还原度就低了。所以我会建议生产环境一律使用ROW格式的binlog方便排查和误操作恢复。7.3 插入中文报错或乱码向表里插入中文出现乱码或者报错Incorrect string value一般是字符集没有设置正确。MySQL 8.0默认字符集是utf8mb4能存emoji和所有中文。如果你的表和库还停留在latin1或utf8mb3就需要改成utf8mb4。建表时建议直接写明字符集CREATE TABLE student ( ... ) DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;utf8mb4_unicode_ci是比较规则它对大小写不敏感适合大多数场景。如果你要考虑更精确的排序可以用utf8mb4_0900_ai_ciMySQL 8.0默认。字符集优先在建库建表阶段规划好不要寄希望於事后转变字符集那是一场灾难级的工作量。7.4 NULL值参与运算的坑DML里NULL是个隐形地雷。例如UPDATE student SET score score 5 WHERE id 5;如果原来score NULL那么运算结果不是5而是NULL。因为在SQL中NULL和任何数字做四则运算结果都是NULL。这会导致原本没成绩的学生更新完后成绩还是空值。排查这类问题需要两个意识一是知道SQL的NULL传播规则二是提前做空值处理比如UPDATE student SET score IFNULL(score, 0) 5 WHERE id 5;用了IFNULL当字段为NULL时先替换成0再做加法结果就是5了。这个函数在INSERT、UPDATE里都很常用学DML的时候顺手把它记住后面写查询也少不了它。7.5 数值越界或类型不匹配insert into student (age) values (二十)会报错。MySQL对类型检查其实不算严格有些情况它会自动做隐式转换字符串的数字可以插入到INT列里但纯中文不行。年龄设计成TINYINT的话范围是0到255插一个300进去会报OUT OF RANGE错误。最佳实践是字段类型设计时留足余量同时代码层面做参数校验别让错误数据到达数据库层。8. 几个值得坚持的DML习惯多年经验总结做DML操作这行纯靠技术挡不住失误真正能救命的是习惯。我把自己多年攒下的经验整理成几条铁律你可以直接抄走。第一写UPDATE和DELETE之前先写SELECT确认影响范围。哪怕只多花十秒钟也能避免“炸掉一张表”的惨剧。第二重要的写操作都在事务里执行COMIT确认无误后再提交。这条适用于任何需要两步以上变化的场景。第三批量写入数据时控制每个批次的行数不要一条SQL包几千上万条插入容易让数据库服务压力飙高。第四历史数据或敏感数据宁可保留不要随意物理删除。逻辑删除是好习惯查询时带上状态过滤就行。第五生产环境一定开启binlog并配置为ROW格式。做不到这一点就没有资格操作重要数据。我见过太多新手对着工具咔咔点DELETE一把梭最后数据找不回来一脸无辜地看着我说“我没想到会这样”。所以我会反复强调DML操作永远是“谨慎第一效率第二”。SQL的编写速度可以通过练习提升但安全意识必须从一开始就建立。一个高级开发者和初级开发者的差别往往不是谁SQL写得快而是谁更少把生产库搞出事故。如果这章的内容你能吃透并且把上面几条习惯内化成肌肉记忆那么MySQL的增删改这一关就算真正过了。后面再学存储过程、触发器、分区表这些进阶内容会更加从容。接下来我还会继续更新查询、索引优化、事务隔离级别的高级用法跟着这个节奏走MySQL这条路会越走越稳。