很多人在学数据库的时候都有一种感觉SQL语句看着简单好像就是增删改查四个字但真到了写业务代码或者接手项目的时候才发现自己连一条像样的查询都写不利索。尤其是MySQL作为最流行的开源关系型数据库之一搞清楚它最基础的INSERT、SELECT、UPDATE、DELETE比背一堆高深理论有用得多。这篇我打算从实际开发的角度把MySQL的增删改查CRUD完整拆开讲一遍不绕弯子直接说人话给你一套能直接用到项目里的操作思路和避坑经验。这篇文章适合谁看刚入门数据库的学生、做后端开发但SQL基础不牢的工程师、以及那些用惯了ORM框架比如MyBatis-Plus、Hibernate却很少手写SQL的朋友。你放心就算你之前完全没写过SQL跟着这篇文章一步步操作也能很快上手如果你已经有经验那里面关于WHERE条件陷阱、批量插入效率、误操作恢复的内容也值得花几分钟扫一遍。1. 先把地基打好建库建表阶段的几个关键决定增删改查的前提是你得有表而表建得好不好直接决定你后面写CRUD是舒服还是难受。很多新人喜欢拿到需求就写SQL跳过设计这一步结果数据冗余、查询缓慢、更新异常最后全成了屎山。这里我分享一下我建表时心里默认过的一套流程。1.1 数据库字符集和排序规则别乱选建库的时候字符集尽量用utf8mb4排序规则用utf8mb4_unicode_ci或者utf8mb4_0900_ai_ci。为什么不用utf8因为MySQL的utf8其实是阉割版最多存3个字节像一些生僻字、emoji表情根本存不进去到时候往表里插数据报Incorrect string value错误你查半天都不一定想到是字符集的问题。我踩过一次坑早期做一个小系统建库用了utf8上线后用户注册昵称带了个emoji接口直接500日志一看就是字符集不支持。后来把库、表、字段全部改成utf8mb4才解决。注意改了库的默认字符集原来已经建好的表不会自动跟着改你需要ALTER TABLE手动去调整这又是一个大坑。1.2 主键和常用索引的规划主键我几乎无条件推荐自增整数INT UNSIGNED或BIGINT业务字段做主键比如身份证号、手机号有时候听着合理实际坑很多一是身份证号涉及隐私没必要全表到处带二是字符串主键会让二级索引变大检索性能下降。索引也不是越多越好。很多新人喜欢在查询条件的每个字段上都建索引但索引太多会拖慢写入速度。我一般的原则是高频查询的WHERE条件字段、JOIN关联字段、ORDER BY排序字段优先建索引其他的先不加等真的出现慢查询再说。1.3 字段类型够用就好别什么都上大字段能用TINYINT就别用INT能用VARCHAR(50)就别用TEXT。字段类型太大一方面浪费存储空间另一方面InnoDB在内存里缓存的数据页就变少间接影响查询性能。举个例子状态字段明明只有0、1、2三个值用TINYINT就够了有人非要建个VARCHAR(20)存字符串。MySQL的InnoDB对变长字段处理起来更复杂等数据量上来之后表空间膨胀得很明显。我提供一个参考建表语句你新建一个用户表可以照这个思路来CREATE TABLE user ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键ID, username VARCHAR(50) NOT NULL COMMENT 用户名, nickname VARCHAR(50) DEFAULT NULL COMMENT 昵称, age TINYINT UNSIGNED DEFAULT NULL COMMENT 年龄, email VARCHAR(100) DEFAULT NULL COMMENT 邮箱, status TINYINT NOT NULL DEFAULT 0 COMMENT 状态0-正常1-禁用, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, PRIMARY KEY (id), KEY idx_username (username) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci COMMENT用户表;这里有几个细节你们在建表时可以先养习惯create_time和update_time直接给默认值这样插入时不写这两个字段也能自动填充省去应用层手动处理的时间。status这类枚举型字段用数字而不是字符串应用层再翻译成对应的业务含义查询更快也避免拼写不统一。2. INSERT插入数据从单行到批量效率差在哪里插入是CRUD的第一步。很多人写INSERT就只写最简单的单行插入数据量小的时候没感觉一旦要初始化几万条数据或者接口并发写入才发现性能差得离谱。2.1 基础插入语法和逻辑单行插入的语法相信你眼熟INSERT INTO user (username, nickname, age, email) VALUES (zhangsan, 张三, 25, zhangsanexample.com);这里有几个细节你们在建表时可以先养习惯强烈建议列出字段名再VALUES不要直接INSERT INTO user VALUES (...)。一旦表结构中间加了个字段不带字段名的SQL直接就崩了而且阅读代码的人根本不知道每个值对应什么列。VALUES里的字符串注意别漏引号数字可以不加引号但建议都加上引号避免隐式类型转换的问题。插入时不要插入主键让自增主键自己生成。有些新手喜欢在主键里填业务含义的数字短时间没问题时间一长ID就乱了还会把自增游标搞乱。2.2 批量插入的正确姿势如果要插入多条数据最直观的想法是一条一条INSERT但在循环里拼命执行单条INSERT是很低效的做法。每一次INSERT都是一次独立的SQL操作要经过连接、解析、优化、执行、提交这一整套流程非常浪费。批量插入就是把多条记录合并到一条SQL里INSERT INTO user (username, nickname, age, email) VALUES (lisi, 李四, 26, lisiexample.com), (wangwu, 王五, 27, wangwuexample.com), (zhaoliu, 赵六, 28, zhaoliuexample.com);一条SQL插几百条甚至上千条都可以但也不是越大越好。单条SQL太大会导致网络传输包过大、锁持有时间过长、事务日志暴涨我一般控制在500到1000条一批分批提交。比如你要插一万条数据可以分成10个批次每批1000条不要一口气全塞进去。批量插入的原理说白了就是减少SQL解析和网络往返次数把多次小事务合并成少数几次大事务。在MyBatis中你可以用foreach标签动态拼接VALUES也可以自定义一个executeBatch工具方法效果都不错。2.3 一个真实的批量插入性能对比我做过一个数据迁移任务旧的用户表有大概50万条数据要搬到新表。刚开始用最粗暴的逐条插入程序跑了快半小时才插了5万条这速度根本没法接受。后来改成批量插入每批1000条50万条数据总共也就几十秒。差别在哪逐条插入相当于你我在工厂流水线上每加工一个零件就重新开一次机器批量插入则是一次开机连续加工几十个零件开销集中摊销了自然快得多。如果遇到大批量初始化数据的场景还有几招可以用先删除索引再批量插入最后重建索引因为插入过程中维护索引有额外代价。将事务手动提交改为自动一次性提交减少fsync次数。中间穿插SELECT COUNT(*)验证数据条数防止部分失败。3. SELECT查询数据WHERE条件的执行逻辑和索引的默契SELECT是增删改查里最常用也最容易写出花样的操作但很多人对它的理解停留在查出结果就行完全不考虑查询条件和索引的关系。这里我把最核心的查询逻辑拆开讲。3.1 基础查询与列的取舍最简单的查询是查全表SELECT * FROM user;注意SELECT *在开发调试时可以上生产环境我是坚决反对的。为什么因为你不可能需要一张表的所有字段。查出来的列越多网络传输的数据量越大内存和CPU的负担越重。并且如果表结构后续加了一个重量级字段比如TEXT类型的简介用SELECT *的应用层代码就无端背上这个包袱。规范的做法是明确列出需要的字段SELECT id, username, nickname, age FROM user WHERE status 0;这个习惯在ORM框架里往往被忽略因为写实体类映射的时候会自动把全部字段查出来。但手写SQL的时候要有这个意识尤其是表字段多、数据量大之后少查一个字段就少一分开销。3.2 WHERE条件的底层执行顺序WHERE条件的本质是从表中筛选出满足条件的行。这里有一个非常重要的知识点SQL中WHERE条件的执行顺序不是按照你书写顺序来的而是优化器决定的。很多人以为虚构的例子AND前面的条件先执行、后面的后执行其实MySQL的优化器会基于统计信息选择它认为最优的执行路径。举个例子SELECT * FROM user WHERE age 20 AND username zhangsan;你可能会觉得age 20先过滤再匹配username但MySQL优化器评估后发现username有索引单独用username zhangsan能快速定位到少量行再在结果集里过滤age 20整体代价更低于是执行计划就反过来了。所以不要自以为聪明地调整WHERE条件的顺序来优化SQL优化器比你更懂数据分布。三个常见的WHERE细节经常有人在这里翻车字符串字段别和数字比较。WHERE mobile 13800138000会让MySQL把字符串转成数字再比较一旦字段里有非数字字符结果可能不对索引也用不上。NULL判断要用IS NULL或IS NOT NULL不要写成 NULL在MySQL里 NULL的结果永远是UNKNOWN查出来的行数会和你预期差很多。我之前排查过一个数据对不上的问题最后发现就是有人写了一整排xxx NULL。IN里面的元素别太多几百上千个IN条件会让优化器非常纠结甚至放弃索引走全表扫描。3.3 分页查询与排序分页是项目里几乎躲不开的需求。MySQL最常用的分页写法是LIMITSELECT id, username FROM user ORDER BY id LIMIT 10 OFFSET 20;这个写法的小问题是偏移量大的时候很慢比如LIMIT 100000, 10MySQL依然要扫描前面十万行再跳过性能是线性下降的。深分页优化我一般用延迟关联或基于游标的方式-- 先查主键再回表查详情 SELECT u.id, u.username, u.nickname FROM user u INNER JOIN ( SELECT id FROM user ORDER BY id LIMIT 100000, 10 ) tmp ON u.id tmp.id;分页和排序是对好兄弟但排序字段一定要有索引否则数据量一大MySQL就要建临时文件做filesort。一般我把排序字段放在索引的末尾并注意排序方向和索引方向的匹配这样order by就能直接走索引顺序避免额外排序。3.4 聚合查询和GROUP BY的常见坑统计类需求离不开聚合函数和分组最基础的是查数量、最大值、最小值、平均值SELECT status, COUNT(*) AS cnt, AVG(age) AS avg_age FROM user GROUP BY status;写GROUP BY时有一个非常容易踩的坑SELECT后面出现的非聚合列必须出现在GROUP BY里。在MySQL里如果关掉了ONLY_FULL_GROUP_BY这个SQL模式你甚至可以SELECT一些没分组的列这时返回的数据是随机的、不可控的。我建议在配置里保持ONLY_FULL_GROUP_BY开启避免写出潜藏问题的SQL。另外HAVING是分组后的过滤条件它和WHERE不同不能替代WHERE。能用WHERE提前过滤的数据就尽量在WHERE里过滤减少分组时的数据量。4. UPDATE更新数据影响行数和你想象中的不一样更新操作是最容易出事故的环节。删库跑路只是段子但一条忘加WHERE的UPDATE绝对能让一个团队忙活一整天。4.1 基础UPDATE语法和影响行数UPDATE的基本写法UPDATE user SET age 26 WHERE username zhangsan;这里我想强调影响行数这个概念。很多人在执行UPDATE之后下意识以为返回的行数就是实际被修改的行数。但实际上MySQL默认返回的是被匹配的行数而不是真正发生变更的行数。如果age本来就是26你执行上面这条SQLMySQL会告诉你影响1行但它内部其实什么都没改。在一些ORM框架里如果你拿这个影响行数来判定更新是否成功会发现明明数据没变化却返回成功。这种需求下你应该检查的是实际变更行数MySQL可以通过在连接上设置s Found Rows或者你在SQL里做一些额外的判断来实现。最简单的方式是更新前先查一次旧值比对成本高点但逻辑清晰。4.2 更新时最容易忽略的事务问题UPDATE一旦出事往往需要事务来回滚。MySQL的InnoDB引擎默认支持事务但要注意自动提交模式下每条SQL都是一个独立事务。如果你连续执行多条UPDATE中间某条失败前面的并不会自动回滚。我建议在需要多条更新保持一致性的场景显式开启事务START TRANSACTION; UPDATE account SET balance balance - 100 WHERE id 1; UPDATE account SET balance balance 100 WHERE id 2; COMMIT;如果第二条UPDATE报错直接ROLLBACK第一条的修改也会撤销。这个习惯在线上变更数据时尤为重要千万别靠一条条单SQL执行后手动补数据去修复。4.3 更新语句的防呆习惯更新语句的防呆说起来就一句WHERE条件务必写严谨。但真到了手速快的时刻谁都可能翻车。我见过最惨的一次有人在测试环境执行UPDATE user SET status 0 WHERE id 100结果id写成了1 1那种裸奔条件整个表的用户全部被禁用要不是有备份数据根本救不回来。我自己养成的一个习惯是先SELECT确认要更新的数据范围再写UPDATE。不是不信任自己而是多花一秒钟确认比出事之后花几天恢复要划算得多。另外UPDATE语句里尽量用主键或唯一索引字段做条件这样能精确定位到目标行。如果只能用普通字段先用一条SELECT看下这个条件下有多少行确认是预期数量再执行更新这招看起来笨但真的很管用。4.4 大批量更新时的分批技巧如果需要更新几万行数据一次性UPDATE全表会持有大量的行锁长事务还会导致主从复制延迟和undo膨胀。我有一次批量更新配置表直接UPDATE了几万行结果从库延迟了快十分钟业务查询都受到了影响。后来我养成了分批更新的习惯UPDATE user SET status 1 WHERE status 0 AND id 0 ORDER BY id LIMIT 1000;一批一批地更新每次只锁一小部分行既避免了长事务也让主从延迟控制在很小范围内。有些场景不能简单LIMIT那就按ID区间循环比如以1万为步长每次更新一个ID区间循环跑完为止。5. DELETE删除数据你以为的删除可能不是真删除DELETE看起来比UPDATE安全毕竟删错了从结果上就能看出来。但DELETE的坑在于它的执行机制和数据恢复难度。5.1 DELETE语法与TRUNCATE的本质区别DELETE的基础写法DELETE FROM user WHERE id 100;不加WHERE相当于清空全表DELETE FROM user;很多人分不清DELETE和TRUNCATE的区别。DELETE是DML数据操作语言逐行删除、走事务、可以回滚、不会重置自增IDTRUNCATE是DDL数据定义语言直接重建表、速度快得多、不可按行回滚在事务里可以回滚但通常不建议依赖、会重置自增ID。如果你只是想清空一张表的数据并让ID从1重新开始TRUNCATE更合适如果只想删掉其中的一部分数据或者需要保留删除记录以便恢复用DELETE。5.2 大表DELETE的效率问题和方案大表DELETE有一个很恶心的点即使你只删除其中20%的数据如果表里数据量很大这个DELETE可能执行得非常慢还会导致主从延迟和大量磁盘碎片。这是因为DELETE不只是删数据还要记录undo日志、维护二级索引、在数据页上打删除标记。删除千万级大表的一部分数据我一般不会直接一次性DELETE而是先查出要删除的主键范围然后分批DELETEDELETE FROM user WHERE id BETWEEN 100000 AND 200000;每批删几千行删完一批暂停几十毫秒让主库和从库都有喘息的机会。这样总耗时会拉长但对生产环境的影响最小。还有一种做法是软删除这也是大量业务系统的常态加一个deleted字段0表示存在1表示已删除删除操作变成UPDATE。这样做的好处很明显——数据还在表里误删可以恢复坏处是查询时所有SQL都要额外加WHERE deleted 0写起来烦而且容易漏。5.3 误删除之后的急救思路如果不小心把一张表的数据删了第一步是深呼吸不要慌然后立刻停止对这个表的一切写入操作防止已删除的数据占用的数据页被后续写入覆盖减少恢复难度。第二步看备份。有备份的话用备份把丢失的数据恢复到一个临时表再通过INSERT ... SELECT把数据找回来。如果你用的是云数据库比如RDS通常有自动备份和按时间点恢复的功能可以恢复到删除之前的那个时间点。如果没有备份那就要靠binlog了。前提是你开启了binlog一般生产环境都要开日志格式最好是ROW。你可以通过mysqlbinlog工具解析binlog找到删除前的INSERT或UPDATE记录把丢失的数据拼出来。整个过程比较费劲但至少给了你一线生机。我特别想强调别把希望寄托在自己的手速和运气上定期备份才是防止误删事故的根本手段。在生产环境至少要做到每日全量备份加实时binlog增量备份有条件的话做跨机房容灾。6. 写CRUD时那些让我印象深刻的翻车现场这一节我挑几个真实的坑来复盘。知道正确的写法是一回事亲眼看一遍错误是怎么产生的才能真正长记性。6.1 忘了WHERE条件的UPDATE事故复盘之前公司有个运营后台运营同学需要给一批用户加积分程序执行的SQL是UPDATE user_score SET score score 100 WHERE user_id 12345;看起来没问题对吧但有一次一个开发同学在做数据订正时复制了这条SQL把WHERE给删了变成UPDATE user_score SET score score 100;结果全表几十万用户的积分都加了100。这件事因为涉及金额积分可以兑换商品最后动用了备份binlog一点点恢复耗费了整整一个晚上。复盘下来的核心教训就是生产环境执行UPDATE或DELETE一定要先在测试环境试过完整的SQL手写时先写WHERE再写SET。6.2 字符集不一致导致的插入错误两个系统对接A系统的库是utf8mb4B系统的库是latin1往B系统插中文标题时直接插入失败。排查很久最后发现是两张表的字符集不一样数据在连接层做了错误的转码。解决方式是把两边统一成utf8mb4并且连接字符串显式指定characterEncodingutf8。这个问题的隐蔽性在于不是每条插入都报错而是当字符串里出现了某种特殊字符时才报错导致排查方向经常跑偏。6.3 分页深翻页卡死线上服务一个后台管理页面上有全部用户列表管理员习惯性点最后一页SQL是SELECT * FROM user ORDER BY id LIMIT 500000, 20;数据量上了百万之后这条查询直接把数据库CPU打满整个库的查询全部被拖慢。后来我优化成先查主键再回表的延迟关联方案同样翻到50万页从原来的5秒多降到0.1秒不到。优化点不在于少查了数据而在于避免了让数据库扫描并丢弃大量无关行。7. 从手写SQL到ORM框架思想相通但别做甩手掌柜现在很多项目用MyBatis-Plus、Hibernate这类ORM框架写代码时根本不用手拼SQL。这里我想说ORM让你省事但不代表你可以不懂SQL。框架生成SQL的能力终究有限一旦遇到复杂查询、性能问题你还是要回到SQL层面去分析。7.1 框架里的CRUD和手写SQL的对照拿MyBatis-Plus举例它内置的BaseMapper已经提供了selectById、insert、updateById、deleteById这些方法。你调用userMapper.selectById(100)的时候本质上执行的等价SQL就是SELECT id, username, nickname, age, email, status, create_time, update_time FROM user WHERE id 100;也就是说框架只是在帮你拼接SQL。如果你对底层SQL的执行逻辑没有概念那当框架生成的SQL出现性能问题时你连该从哪个索引入手都不知道。7.2 框架生成SQL的典型性能陷阱MyBatis-Plus的selectList方法很好用但它默认会查出这个实体映射的所有字段。如果你的表字段很多又不幸包含了几个大的TEXT字段那么列表页每次查询都会携带大量无用数据。这时候我就建议你用select方法指定列或者干脆写自定义SQL。还有LambdaQueryWrapper里的like方法很多新手拿来当模糊查询用一查发现慢得离谱。原因很简单LIKE %keyword%的前置通配符让索引直接失效全表扫描。这种问题在框架里隐藏得尤其深因为代码看着人畜无害。7.3 建议即使有框架也要保持手写SQL的能力我的建议并不是让你抛弃ORM而是你要能看懂框架生成的SQL能在必要时果断手写。手段可以是打印SQL日志或者用工具截获执行的SQL语句一行行拆解。这样你在用框架时心里始终明白它背后到底做了什么数据库有没有在帮你好好干活。8. 增删改查的“道”与“术”几条掏心窝的经验总结走到这里MySQL的基础增删改查其实讲得差不多了。我再分享几条这些年写SQL攒下的原则纯个人经验不是教科书上写的但非常实用。8.1 数据库不是给你跑全表扫描的每次写查询之前先问自己这条SQL大概会扫描多少行如果数据量到百万千万这个写法还能扛住吗好的SQL不是功能正确就完事而是要在合理的数据量下尽量走索引、减少扫描行数、避免无谓的排序和临时表。检查索引的方式很简单用EXPLAINEXPLAIN SELECT * FROM user WHERE username zhangsan;看输出里type这一列如果是ALL说明是全表扫描要考虑加索引如果是ref、const这类说明命中索引心里就有底了。8.2 每一条生产环境的CRUD都要有演练意识在大公司线上数据变更要有审批单、有执行窗口、有回滚预案。个人开发者可能没这么严格的流程但至少有先在测试库跑一遍、备份再做变更、变更后确认数据的意识。8.3 SQL可读性也是一种尊重尽量用缩进和统一大小写来写SQL别把一条几百行的SQL全部堆积在一行。这不是形式主义而是未来维护你代码的人很可能就是三个月后的你会感激你。8.4 关于MySQL增删改查的进阶方向掌握了基础的CRUD之后接着可以往这些方向走索引优化、事务隔离级别、锁机制、SQL执行计划分析、慢查询优化、主从复制、分库分表。这些内容我后面的文章会逐步展开。就当前这篇来说你只要把INSERT、SELECT、UPDATE、DELETE这四类操作的语法、性能和习惯都理顺了后面的进阶内容才有地基可扎。最后再聊一个非常贴身的技巧。每次部署完一个系统我会顺手往数据库里塞一条测试数据然后用默认的CRUD接口走一遍新增、查询、修改、删除再直连数据库用SQL验证一遍。这个动作不是多余的它能最快暴露字符集、自增主键、字段类型映射等基础问题避免上线后让真实用户当小白鼠踩坑。数据库的功夫永远不嫌多。