1. 这一章到底在讲什么查询之外的“基本功”如果你正在学MySQL或者已经写了一段时间SQL大概会有这种感觉查询的语法花样最多SELECT能玩出各种花来。但真正到了实际项目里天天写的最多的反而不是那些复杂的JOIN和子查询而是最基础的增删改查——尤其是增、删、改这三件事。它们看起来简单但恰恰是出错率最高的地方因为任何一条写错的UPDATE或DELETE都可能直接改坏线上数据。这篇文章想聊的就是“基本查询下”这个阶段最核心的内容表的增删改查。它通常是在你已经掌握了最简单的SELECT查询之后紧接着要面对的一整块知识。说白了就是下面这几件事增INSERT往表里插入数据单条、多条、指定字段、甚至从另一张表复制数据。删DELETE按条件删除数据以及清空整张表。改UPDATE更新已有记录配合WHERE条件做精准修改。查SELECT把前面学过的查询能力和增删改组合起来形成完整的日常操作闭环。我把这一篇定位成“实战入门向”。适合刚学完基础查询、准备开始操作真实数据的读者也适合那些已经写了一阵子SQL、但有些细节始终没完全搞清楚的开发者。接下来我会把每个操作的用法、执行逻辑、容易踩的坑以及我在实际项目中怎么用它们一次讲清楚。2. 增删改查的底层思维先搞清楚每一条SQL到底在动什么在敲第一条INSERT之前我建议你先在脑子里建立一个最基本的认知增删改查的每一条语句本质上都在操作“表”这个二维结构里的“行”和“列”。SELECT按条件把某些行的某些列取出来不改动原表。INSERT往表里增加新行。UPDATE把某些行的某些列的值改掉。DELETE把某些行整体移除。听起来像废话但很多人出错就是因为没有把“行”和“列”这两个维度分开想。比如UPDATE语句你写SET name 张三这是改列但它真正定位到哪些行靠的是WHERE条件。如果WHERE写错就会把不该改的行也改了。这就像你要在一面墙上换几块瓷砖结果拿错了房间号把别人家的墙砸了。所以业内有个非常朴素的经验法则操作数据之前先问自己三个问题——我要影响哪些行我要改哪些列我有没有办法验证影响范围如果把这三个问题想清楚了增删改查基本不会出大乱子。2.1 先看一眼表结构再动手不管你接下来要做INSERT还是UPDATE第一步永远是确认表结构。我见过太多人凭记忆写SQL结果字段名写错或者漏了必填字段报错之后才回头查表结构。查看表结构的语句非常简单DESC 表名;或者更详细一点直接看建表语句SHOW CREATE TABLE 表名;这两个命令在实操里很常用。DESC能快速看到字段名、类型、是否允许NULL、是否有默认值、是不是主键SHOW CREATE TABLE则能看到完整定义包括索引、字符集、存储引擎这些细节。我个人的习惯是凡是第一次接手的表先跑一遍SHOW CREATE TABLE。因为DESC看不到默认值以外的约束细节比如某个字段是否有UNIQUE约束、外键指向哪里这些信息直接影响你INSERT时怎么构造数据。2.2 实操准备建一张练习用的表为了下面的操作演示我们先建一张足够简单、但涵盖了常见字段类型的练习表。这张表模拟的是一个最基础的“用户表”字段包括自增主键、用户名、邮箱、年龄、注册时间。CREATE TABLE user ( id INT NOT NULL AUTO_INCREMENT COMMENT 主键ID, username VARCHAR(50) NOT NULL COMMENT 用户名, email VARCHAR(100) DEFAULT NULL COMMENT 邮箱, age INT DEFAULT NULL COMMENT 年龄, created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 注册时间, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户表;这张表看起来简单但已经包含了几个关键点自增主键、非空字段、可空字段、带默认值的字段。后面所有示例都基于它你可以直接复制到自己的MySQL里跑一遍。提示MySQL版本建议在5.7及以上DEFAULT CURRENT_TIMESTAMP这个语法在5.6就已经支持了老版本可能会报错遇到问题升级就好。3. INSERT的完整用法从单条插入到批量导入INSERT是所有增删改查里最“友善”的一个操作因为它的失败通常只影响自己不会波及已有数据。但友善不代表可以乱写尤其是涉及主键冲突、字符集、必填字段时照样有一堆细节。3.1 最基本的单条插入全字段和指定字段的差别全字段插入的写法是这样INSERT INTO user VALUES (1, 小明, xiaomingexample.com, 20, 2024-01-01 10:00:00);这种写法要求VALUES里按表结构的字段顺序一个不落地给出所有值。它看着省事但特别脆弱——只要表结构一变比如加了新字段这条SQL立刻失效。所以我更推荐写法是指定字段列表INSERT INTO user (username, email, age) VALUES (小明, xiaomingexample.com, 20);这里有几个关键点不写的字段id会自动生成因为它是自增主键。不写的字段created_at会自动填当前时间因为建表时给了默认值。如果有一个非空字段既没有默认值也没有出现在字段列表里INSERT会直接报错。我有一个习惯所有INSERT都显式写出字段列表哪怕字段很多。好处是SQL可读性强语义清晰后续维护的人一眼就能看出这行数据设置了哪些列更重要的是不会被表结构调整悄悄坑到。就算哪天往表里加了一个可空字段老SQL依然能跑。3.2 一次插入多行批量插入的性能优势需要同时插入多条记录时可以把VALUES后面跟多组值INSERT INTO user (username, email, age) VALUES (小明, xiaomingexample.com, 20), (小红, xiaohongexample.com, 21), (小刚, xiaogangexample.com, 22);这种方式在实操中非常常见。相比逐条INSERT它有几个实打实的好处减少客户端和MySQL之间的网络往返。每条INSERT都是一次单独的语句执行批量插入只有一次网络开销小得多。在InnoDB引擎下批量插入能减少事务日志写入次数整体速度有明显提升。代码结构更清晰一组数据堆在一起方便审查。在真实项目里如果你要一次性往表里塞几百条、几千条数据直接拼一条多行INSERT通常能跑得飞快。但要注意一点单条SQL不宜无限长。虽然MySQL有max_allowed_packet参数控制单次传输大小默认通常是64MB但没必要炫技一次插个几百行到几千行性能就已经很理想了。上十万行的批量导入我建议走LOAD DATA INFILE或分批提交下一篇可以展开聊。3.3 插入时处理主键冲突INSERT IGNORE与ON DUPLICATE KEY UPDATE日常开发中我们经常会遇到“这行数据可能已经存在我需要插入但不想报错”的场景。比如采集中间表第一次跑任务要把数据落库第二次跑任务数据已经在了怎么办MySQL提供了两个实用方案。第一个是INSERT IGNORE。它的行为是如果插入的数据和已有数据冲突最常见的是主键或唯一键冲突就默默跳过这条不报错不影响其他行INSERT IGNORE INTO user (id, username, email, age) VALUES (1, 小明, xiaomingexample.com, 20);如果id1已经存在这条语句不会插入也不会报错。适合那种“有就跳过没有就插入”的幂等需求。第二个是ON DUPLICATE KEY UPDATE。它更进一步冲突时不仅不报错还能把已有行的某些字段更新掉INSERT INTO user (id, username, email, age) VALUES (1, 小明, xiaomingexample.com, 20) ON DUPLICATE KEY UPDATE username 小明, email xiaomingexample.com, age 20;这个写法的应用场景很典型同步外部数据源时数据源那边可能改了某条记录这边希望“有则更新无则插入”。这在不少面向业务的系统里称为“upsert”是个非常高频的需求。注意ON DUPLICATE KEY UPDATE不只对主键冲突生效对任何唯一键冲突都生效。如果一个表有多个唯一索引要小心这条语句可能更新到你以为不会命中的行。实际项目中这种表尽量让主键和唯一键语义清晰、数量精简。3.4 从查询结果直接建表插入INSERT INTO ... SELECT有时我们需要把一张表的部分数据复制到另一张表或者把查询结果保存成一张新表这时不需要手动拼VALUES而是可以直接把SELECT的结果喂给INSERT。INSERT INTO user (username, email, age) SELECT username, email, age FROM user_backup WHERE age 18;这条语句的逻辑很简单先执行SELECT子句然后把结果一行一行插入到目标表。它比“导出数据再导入”快得多也避免了文件传输的问题。另一个相关语法是CREATE TABLE ... AS SELECT但要注意它默认不会复制原表的索引和约束。如果只是做临时表、报表中间表问题不大但如果想复制完整表结构还是得用CREATE TABLE ... LIKE或者手动补索引。3.5 我踩过的一个INSERT坑字符集不一致导致的中文乱码初学阶段最经典的翻车现场就是插入中文后查询出来全是乱码。原因往往不是INSERT语句写错了而是客户端连接字符集、表字符集、字段字符集不一致。我遇到过一种情况表是utf8mb4但我在某个老项目里用的连接字符集是latin1结果插入的中文直接变成问号。排查起来特别头疼因为看起来像是数据写坏了实际上只要调整连接字符集就能解决。解决办法是在连接建立后执行SET NAMES utf8mb4;或者在客户端连接参数里指定characterEncodingutf8mb4JDBC连接串常见做法。只要连接字符集、表字符集、字段字符集统一成utf8mb4中文基本就不会出问题。还有个小细节MySQL里utf8和utf8mb4不是一回事。utf8是最大3字节的字符集存不了emoji和部分生僻字utf8mb4才是完整的4字节字符集。强烈建议从建表开始就统一用utf8mb4省得以后遇到emoji存不进去的尴尬。4. UPDATE的正确打开方式改数据之前先想清楚WHERE如果说INSERT的失败只影响新数据那么UPDATE一旦写错影响的是已经存在的线上数据而且大概率不可逆。所以我对UPDATE的态度始终是谨慎再谨慎。4.1 UPDATE的基本语法和执行顺序UPDATE的标准写法是UPDATE 表名 SET 字段1 值1, 字段2 值2 WHERE 条件;它的执行逻辑其实是先根据WHERE条件确定哪些行需要被更新然后对这些行统一设置SET中指定的列。看起来简单但很多人没意识到WHERE决定的是“影响哪些行”SET决定的是“这些行变成什么样”两者是独立的。如果只想改一行但WHERE条件匹配了十行那就是十行一起改。来几个实际例子-- 修改id1的用户年龄 UPDATE user SET age 25 WHERE id 1; -- 把所有年龄小于18的用户邮箱清空 UPDATE user SET email NULL WHERE age 18; -- 把所有用户的年龄加1没有WHERE就是全表更新 UPDATE user SET age age 1;第三个例子尤其重要它展示了UPDATE中用字段当前值参与计算的写法。这种“在原有值基础上修改”的操作在计数器、库存、积分等场景非常常见。但要注意它必须写成age age 1这种形式不能简单写age 1——否则就是直接把所有行年龄改成1了。4.2 为什么我坚持“UPDATE必须带WHERE”不带WHERE的UPDATE会更新整张表的所有行这在绝大多数业务场景里都是灾难级的操作。假设一个运营后台管理员想修正某个用户的邮箱结果SQL写成UPDATE user SET email tempexample.com;这条语句会瞬间把全表所有用户的邮箱都改成tempexample.com。等发现的时候可能已经晚了——而且一般来说MySQL默认开启autocommit没包在事务里的话改完就提交了想回滚都难。所以我在团队里一直强调一条铁律上线前检查UPDATE语句没有WHERE条件的UPDATE必须有明确理由且要经过双重确认。这不是夸张很多时候事故就是这么来的。4.3 用SELECT先验证WHERE条件的命中范围我非常推荐一个习惯在真正执行UPDATE之前先用相同WHERE条件的SELECT看一眼命中哪些行。比如我想把年龄小于18的用户的邮箱清空先执行SELECT id, username, age, email FROM user WHERE age 18;确认返回的行确实是我想要修改的那一批再执行UPDATE。这个方法简单、可靠几乎不花额外时间却能把误操作的概率降低一个数量级。如果数据量很大SELECT全部记录太大可以加LIMIT看前几条SELECT id, username, age, email FROM user WHERE age 18 LIMIT 10;虽然只是抽查前10条但配合其他条件判断也足够确认WHERE条件的方向对不对。4.4 用好事务让UPDATE有机会反悔在MySQL里InnoDB引擎支持事务。事务最大的价值之一就是给UPDATE/DELETE这种危险操作一个“后悔药”。一条典型的操作流程是BEGIN; UPDATE user SET email NULL WHERE age 18; -- 检查影响行数如果发现不对劲立即回滚 ROLLBACK; -- 如果确认没问题提交 COMMIT;如果用的是命令行或某些图形化客户端执行UPDATE后能看到“Query OK, 2 rows affected”这个影响行数就是判断对错的重要依据。当影响行数和预期不符时立即ROLLBACK数据安然无恙。当然事务也不是没有代价。长事务会占用连接资源锁住的行会阻塞其他事务操作。所以实操中我一般只在“可能搞坏数据”的场景里显式使用事务日常简单UPDATE就直接执行。4.5 UPDATE中容易忽略的一个细节受影响行数为0不代表没执行成功UPDATE执行后提示“0 rows affected”通常意味着两种情况WHERE条件没有匹配到任何行。匹配到了行但SET的值和原值相同被MySQL判定为“无需实际修改”。第二种情况字段虽然没变化但MySQL确实执行了查询和比较的过程。所以“0 rows affected”不代表SQL写错了也不代表没执行。它只是告诉你“没有行被实际修改”。这在判断业务逻辑时要注意。比如你想确认某个用户是否存在不应该用UPDATE是否影响了行来判断而应该用SELECT。如果UPDATE后是0行可能是用户不存在也可能是数据没变化两个原因混在一起容易误判。5. DELETE的边界感删数据不是玩游戏先确认再动手DELETE是增删改查里“最危险”的操作因为数据删了没有后悔药的场景比比皆是。除非你有备份或binlog否则一次错误DELETE造成的数据丢失代价可能远超想象。5.1 DELETE的基本语法按行删除不是按列删除DELETE的语法DELETE FROM 表名 WHERE 条件;它的语义是从表中删除满足WHERE条件的整行。不能只删某一列——要删某一列应该用UPDATE把该列设为NULL或默认值。-- 删除id1的用户 DELETE FROM user WHERE id 1; -- 删除年龄小于18的所有用户 DELETE FROM user WHERE age 18;不带WHERE的DELETE会清空整张表DELETE FROM user;这比不带WHERE的UPDATE更可怕因为UPDATE至少还留着所有行只是改了值DELETE直接让表变空。所以我给DELETE准备了两条原则即使写了多年SQL也一直在遵守第一条DELETE必须带WHERE除非是想清空整表并且确认这是一张临时表或可重建的表。第二条DELETE之前先用同条件SELECT看一眼命中行数。这和UPDATE的做法一脉相承都是为了降低误删风险。5.2 DELETE和TRUNCATE的区别不只是速度问题如果想清空整张表其实还有一个更快的命令TRUNCATE。TRUNCATE TABLE user;TRUNCATE和DELETE FROM之间有几个关键区别在面试里也经常被问到这里直接讲透对比项DELETETRUNCATE是否可带WHERE可以按条件删除不可以只能全表清空删除方式逐行删除走事务可回滚快速释放表数据页DDL操作默认不可回滚自增ID继续沿用当前自增值不会重置自增ID会重置为初始值性能数据量大时较慢通常远快于DELETE触发器会触发DELETE触发器不触发行级DELETE触发器对表结构保留表结构和索引保留表结构重新初始化表如果只是想让表变成一个空壳并且所有自增ID从1重新开始TRUNCATE是最合适的。如果只想清掉一部分数据或者需要留下审计日志就只能用DELETE。注意TRUNCATE在InnoDB引擎下的行为其实和“DROP TABLE CREATE TABLE”类似所以在某些情况下的回滚能力极其有限。千万不要以为它和DELETE一样能随时ROLLBACK这属于高风险操作执行前确认数据库有可用备份会稳妥很多。5.3 大表DELETE的实操痛点一次删太多会锁表实际项目里最常见的问题不是DELETE语法写错而是一次性删太多数据导致锁表、拖垮线上服务。比如你有一张几千万行的日志表想删除上个月之前的所有数据DELETE FROM access_log WHERE created_at 2024-01-01;这条SQL听起来合理但执行的一瞬间InnoDB会锁住大量符合条件的行。如果数据量在百万、千万级别长时间持有锁在线写入业务会直接阻塞严重时可能造成连接堆积。我处理这类需求的惯用套路是分批删除每次只删一小部分间隔几秒钟让其他事务有机会执行DELETE FROM access_log WHERE created_at 2024-01-01 LIMIT 1000;反复执行这条语句直到影响行数为0。在脚本或后台任务里这通常写成循环。每次只删1000行单条SQL执行时间短锁持有时间短对线上影响就小得多。LIMIT在DELETE里的使用是很多人没意识到的实用技巧。虽然没有标准SQL的明确规定但MySQL确实支持DELETE ... LIMIT这在分批删除中非常重要。经验之谈超过几十万行的删除不要追求一条SQL跑完分批处理虽然慢但胜在对线上环境友好。删除任务的执行时间尽量安排在业务低峰期且务必先备份再动手。5.4 彻底删除数据表DROP TABLE的用途DELETE是删除数据DROP TABLE则是把整张表连同结构、索引一起删除。DROP TABLE IF EXISTS user;这个命令平时不太常用一般在重构、清理临时表时才用。它的危害比DELETE更大——删除后表和里面的数据彻底消失如果没备份基本等于永久丢失。所以我个人对DROP TABLE的态度是必须三思而后行。如果要删的是重要业务表先备份再确认没有其他表的外键依赖它最后在低峰期执行。6. 把查询和增删改结合日常数据维护的完整闭环前面把INSERT、UPDATE、DELETE分别讲了一遍但真实项目里它们总是和SELECT组合出现。这一节我用几个常见的业务场景把“查”和“改”串起来帮你看清整个过程是怎么运转的。6.1 场景一注册新用户先查重再插入业务逻辑上一个用户名通常不能重复。最稳妥的流程是SELECT检查用户名是否存在不存在则INSERT如果并发高可能还要依靠唯一索引兜底SQL层面的示意-- 1. 检查用户名是否存在 SELECT id FROM user WHERE username 新用户; -- 2. 不存在则插入 INSERT INTO user (username, email, age) VALUES (新用户, newexample.com, 18);但这两条语句之间有时间差高并发下可能出现两个请求同时通过第一步的检查然后同时插入。所以正确的做法是在username字段上加唯一索引再配合INSERT或INSERT IGNORE让数据库来兜底。这也是我前面提过ON DUPLICATE KEY UPDATE的典型应用场合。6.2 场景二修改用户资料前先确认目标记录存在改资料的流程往往是前台传来用户ID和新资料UPDATE前先用SELECT确认用户存在执行UPDATE并检查影响行数-- 1. 确认用户存在 SELECT id FROM user WHERE id 123; -- 2. 更新资料 UPDATE user SET email newexample.com, age 25 WHERE id 123;如果UPDATE影响行数为0可能是用户不存在也可能是字段没变化。为了明确反馈给前端可以在UPDATE之前先SELECT一次根据SELECT的结果判断是返回“用户不存在”还是“资料没有变化”。这里的核心思想是SELECT负责确认状态UPDATE负责执行变更两者结合才能形成可靠的业务逻辑。6.3 场景三清理无效数据DELETE配合SELECT确认范围清数据的场景比如删除所有未激活且注册时间超过一年的用户-- 1. 确认范围 SELECT COUNT(*) FROM user WHERE status 0 AND created_at DATE_SUB(NOW(), INTERVAL 1 YEAR); -- 2. 分批删除 DELETE FROM user WHERE status 0 AND created_at DATE_SUB(NOW(), INTERVAL 1 YEAR) LIMIT 500;我这里特意先COUNT一下看看要删多少行。如果结果数量级完全超出预期说明条件有问题。确认没问题后再用带LIMIT的DELETE分批执行。这个流程看着多写了几个步骤但放到生产环境里它会保护你远离“误删”“锁表”等各种坑。数据维护从来不是“一句话的事”而是“确认、执行、验证”的闭环。6.4 场景四批量更新用IN或BETWEEN精准圈定目标有时需要更新一批特定ID的用户比如把ID为1、3、5的用户年龄改成30UPDATE user SET age 30 WHERE id IN (1, 3, 5);如果是一个范围比如2024年注册的用户统一加一岁UPDATE user SET age age 1 WHERE created_at BETWEEN 2024-01-01 00:00:00 AND 2024-12-31 23:59:59;IN和BETWEEN在UPDATE的WHERE条件里用得非常频繁。它们本质上就是查询过滤逻辑的复用。所以“查询学得好UPDATE也差不了”这句话是很有道理的——UPDATE/DELETE的WHERE子句用的就是SELECT的各种条件语法。7. 增删改查里最常见的五个坑从实战中总结的避障指南这一节我把自己和团队在实际开发中反复踩过的坑集中整理一下。每一个都是真实发生过的问题不是理论推演。7.1 字段名写错却报错未知列手写SQL的时候最烦的就是字段名记错了。比如表里字段叫user_name你写成usernameMySQL会报“Unknown column”错误虽然不影响数据安全但浪费排查时间。避免办法很简单写SQL前先用DESC或SHOW CREATE TABLE看一眼字段名别靠记忆复现。IDE或数据库客户端的自动补全功能也能大幅减少这类低级错误。7.2 数值和字符串的隐式转换MySQL里有一个容易忽略的行为字符串和数值比较时会尝试隐式转换。比如SELECT * FROM user WHERE age 20;这条语句能正常执行因为MySQL会把字符串20转成数字20再比较。但如果字符串不能转成数字比如age abc比较结果可能不是你想的那样甚至导致索引失效。规范习惯是数值字段写数值字符串字段写字符串不要依赖数据库的隐式转换。这既能避免歧义也有利于走索引。7.3 空字符串和NULL的区别很多新手会把空字符串和NULL混为一谈但它们完全不同。NULL表示没有值、未知值。空字符串表示一个长度为0的字符串。在WHERE条件里email NULL永远查不出任何行必须写email IS NULL。同理email 只能查空字符串查不到NULL。-- 正确写法 SELECT * FROM user WHERE email IS NULL; -- 错误写法无法匹配NULL SELECT * FROM user WHERE email NULL;这个坑在统计“有多少用户没填邮箱”时特别常见一不留神就把NULL漏掉了统计结果偏少。7.4 UPDATE/DELETE影响行数超出预期很多时候SQL语法没错但WHERE条件写得太宽导致影响范围比预期大。比如只想更新某个用户却用了一个不够精确的LIKE条件UPDATE user SET status 1 WHERE username LIKE 张%;这句会命中所有张姓用户如果本意是只更新“张三”那就误伤了一大片。所以定位具体记录时优先用主键或唯一键条件比如id或email用模糊匹配做范围操作时先SELECT确认范围。7.5 批量插入时最后一行缺少逗号导致语法错误批量INSERT写多了最容易出的语法错误是最后一组VALUES后面多了一个逗号或者少了一个逗号。这种错误一眼很难看出来尤其SQL很长的时候。我的习惯是写完批量插入后先在测试库里跑一遍确认无误再执行线上SQL。如果批量插入的值很多建议写一段小脚本生成SQL而不是手拼。人眼检查几十组值难免漏脚本生成起码能保证格式一致。8. 理解InnoDB的锁和事务为什么增删改查要了解这些概念别急着跳过这一节。增删改查看起来是纯语法问题但实际上和底层的事务、锁机制紧密相关。如果完全不懂这些概念遇到“更新卡住了”“插入超时”这类现象时会非常被动。8.1 InnoDB的行锁和表锁为什么UPDATE会阻塞别的UPDATEInnoDB默认使用行级锁。也就是说当你UPDATE某些行时这些行会被锁住其他事务在修改同一批行时只能等待直到你COMMIT或ROLLBACK。举个例子事务A执行BEGIN; UPDATE user SET age 30 WHERE id 1; -- 尚未COMMIT此时事务B再执行UPDATE user SET age 31 WHERE id 1;事务B会一直阻塞直到事务A提交或回滚。这不是死锁只是锁等待。死锁是更复杂的状态两个事务互相持有对方需要的锁谁也走不了最终由MySQL自动选择一个事务回滚来解除。理解这个机制的意义在于UPDATE和DELETE的执行时间越短越好锁的持有时间越短越好。所以不要在一个事务里做大量计算、调用外部接口、拖很久才COMMIT。这也是前面反复强调“分批删除”的重要原因。8.2 什么时候会升级成表锁虽然InnoDB是行锁但在某些情况下也可能锁住大量行甚至等同于全表锁定。比如DELETE或UPDATE的WHERE条件没有索引可用InnoDB为了定位目标行可能需要扫描很多行并对这些行加锁。行数一大锁的范围就跟着扩大其他业务的写入就会被严重阻塞。这也是为什么DBA们总说“UPDATE/DELETE的WHERE条件要走上索引”。走索引锁的范围小性能好不走索引锁的范围大性能差。建表时合理设置索引不仅是查询性能的事同样影响增删改的并发能力。8.3 理解自增主键和AUTO_INCREMENT自增主键是InnoDB表的默认选择。它有几个行为值得知道INSERT时不指定该字段MySQL会自动生成一个新值。如果插入失败、事务回滚自增ID仍然可能被消耗掉不会复用。DELETE删除某些行后自增ID不会自动回退TRUNCATE则会重置自增ID。所以如果你发现某张表的id出现不连续比如1、2、5、9这通常是正常现象因为中间有INSERT失败或事务回滚发生过。不要试图人为把ID“补回连续”这是自增主键的正常特性不影响业务。8.4 一个完整的卡片式总结增删改查事务的推荐姿势把前面所有经验浓缩成一张表操作推荐姿势关键注意点INSERT显式字段列表、批量插入注意字符集、唯一键冲突UPDATE必须带WHERE更新前先SELECT优先按主键/唯一键定位DELETE必须带WHERE分批执行先备份注意锁范围全表清空用TRUNCATE会重置自增ID不可回滚危险操作包在事务里及时COMMIT/ROLLBACK长事务会锁行影响并发9. 一些真实项目中用的补充技巧AUTO_INCREMENT复位、备份与恢复增删改查之外我还想补充几个实操中一定会用到的细节。它们看起来不算“增删改查”但离了它们日常操作很容易卡壳。9.1 手动设置自增ID和自增初始值如果某张表的自增主键被大量删过想重置自增值可以执行ALTER TABLE user AUTO_INCREMENT 1;这会改动自增ID的起点。但这个操作绝不能想当然地用在线上核心表因为如果表里最大ID已经超过1MySQL会取max(id)和指定值中的较大者作为下一个自增ID。所以这条ALTER的实际效果取决于当前表里最大的ID。9.2 清空一张表之后重启自增TRUNCATE TABLE会重置自增ID这个前面已经提过。如果你清空数据后希望ID从1重新开始TRUNCATE是最好的选择。但DELETE FROM不会重置DELETE后想重置还是要配合ALTER TABLE。9.3 在删除前保存备份CREATE TABLE ... SELECT为了防止误删我经常在很关键的数据清理操作前先做一张备份表CREATE TABLE user_bak_20240101 AS SELECT * FROM user;这条命令会把当前所有数据复制到一张新表里同时保留列结构但不复制索引和主键。如果需要完整结构可以先CREATE TABLE user_bak_20240101 LIKE user; INSERT INTO user_bak_20240101 SELECT * FROM user;两条语句配合既复制了表结构包括索引也复制了数据。删除前先这样来一发心安很多。9.4 误删之后的紧急止损思路如果真的发生误删第一时间要做的是立即停止对表的写入操作尽量避免新数据覆盖。检查是否开启了binlog如果有尝试用binlog定位误删位置进行恢复。看是否有备份有备份就选择最近的备份恢复再配合binlog做增量恢复。这些操作需要一定DBA技能不一定初学者能立刻掌握。但至少你要记住误删之后不要慌张先冻结写入再找备份和binlog这是唯一正确的救援路径。10. 写在最后的个人经验从语法熟练到写出让人放心的SQL如果这篇文章只能留下一个观念我想把它放在这里增删改查的难度不在于语法本身而在于你对数据影响范围的理解和控制。语法上INSERT、UPDATE、DELETE、SELECT每一类都只有几种固定写法花几天就能背熟。但让人放心的是那些习惯写UPDATE先SELECT确认范围写DELETE先COUNT一下危险操作前备份大表删除用LIMIT分批执行事务短平快。这些习惯看着琐碎在关键时刻能救命。我自己写SQL多年最深的体会是不要相信自己不会犯错要相信流程能拦住错误。把“先查后改”练成肌肉记忆把“备份先行”当成固定动作你的SQL就从“能跑”进化成了“能让人放心跑”。操作数据是一辈子的事希望你从一开始就成为一个让数据安全落地的人。
