刚接手一个项目的时候我第一件事不是翻业务代码而是把数据库打开挨个看表结构和索引。为什么因为代码写得再离谱只要数据是干净的系统就还有救但要是表结构稀烂、数据被写坏了那真是欲哭无泪。MySQL最基础的四个操作——增删查改——是每个后端开发每天都要敲的东西。网上教程一搜一大把但真正在生产环境里趟过雷的人都知道这几个操作里面全是细节稍不注意就是线上事故。今天我把这些年做MySQL增删查改的经验从头捋一遍不堆概念直接上干货。1. 为什么增删查改这几个操作值得你认真对待说实话我刚入行那会儿也觉得增删查改有什么好学的INSERT、SELECT、UPDATE、DELETE四条语句背下来就完事了。直到有一次我一条UPDATE忘写WHERE把整个用户表的状态字段全改错了被领导叫去办公室喝茶才意识到这个想法有多天真。后来做了几年数据相关的开发和维护更是深有体会这四条语句占了一个业务系统所有数据库请求的99%写得好不好直接决定系统的稳定性、响应速度和数据安全性。增删查改在MySQL里统称DML也就是Data Manipulation Language数据操作语言。与之对应的是DDLData Definition Language建表、改表结构这种和DCLData Control Language权限控制。日常开发你写的SQL绝大多数都是DML。一个系统好不好用很多时候不取决于业务逻辑多花哨而取决于这些基础语句用得怎么样。用得好查询响应快数据一致性好数据库负载低运维省心。用不好慢查询成堆数据被误改误删线上事故一件接一件半夜起来捞数据。这篇文章不打算讲高深的东西就围绕增删查改这四个操作把语法、适用场景、性能注意点和容易踩的坑一个个说清楚。既有最基础的语法也有我在实际项目里总结出来的经验。不管你是刚入门还是写了几年应该都能找到点有用的东西。2. 动手指之前先把表建明白很多人一上来就埋头写增删查改很容易忽略一个前提数据是放在表里的表结构设计得好不好直接影响增删查改的写法、性能和正确性。所以我先花点篇幅说说建表——这是后面所有操作的地基。2.1 字段类型别乱选见过太多人图省事所有字段一律varchar(255)不管是日期、数字还是布尔值。这样写短期没问题时间长了全是坑日期没法用时间函数比较数字字段没法做聚合计算存储空间白白浪费索引的体积还特别大查询性能跟着遭殃。我实际项目里会遵循这么几个简单原则数字用数字类型比如int、bigint、decimal。整型根据范围选别上来就bigint一张表几亿行的时候每个字段多几个字节都是成本。日期用date、datetime、timestamp。date只存年月日datetime存年月日时分秒timestamp有时区概念还受2038年问题限制自己按业务选。定长字符串用char比如身份证号、手机号这种长度固定的变长字符串用varchar比如用户名、备注这种长度不定的。金额一律用decimal千万别用float或double。浮点数在二进制里无法精确表示算着算着就出现0.30000000000000004这种鬼东西财务数据一旦出现精度问题后果很严重。布尔值用tinyint(1)0和1表示MySQL没有专门的boolean类型。以一张用户表为例我一般会这样建CREATE TABLE user ( id bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 主键ID, username varchar(50) NOT NULL COMMENT 用户名, password varchar(64) NOT NULL COMMENT 密码哈希值, email varchar(100) DEFAULT NULL COMMENT 邮箱, age tinyint unsigned DEFAULT NULL COMMENT 年龄, status tinyint NOT NULL DEFAULT 1 COMMENT 状态: 1启用 0禁用, is_deleted tinyint NOT NULL DEFAULT 0 COMMENT 软删除标记: 0未删除 1已删除, created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, PRIMARY KEY (id), UNIQUE KEY uk_username (username) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci COMMENT用户表;这张表既是后面增删查改的演示对象也是我在大多数业务系统里会用到的标准模板。有几个细节说一下id用bigint unsigned因为int在数据量大以后会溢出一旦主键溢出整个表就废了username加了唯一索引保证用户名不重复status用tinyint默认1created_at和updated_at都设了默认值其中updated_at还带ON UPDATE CURRENT_TIMESTAMP每次UPDATE会自动刷新时间省得你在代码里手动维护。2.2 字符集和引擎的选择建表时不指定字符集很容易踩中乱码的坑。尤其是老版本MySQL默认字符集可能是latin1插进去的中文直接变问号查出来也是一堆乱码。我统一推荐utf8mb4。注意不是utf8utf8mb4才是真正的四字节UTF-8支持emoji也完美兼容所有中文。引擎选InnoDB。这是MySQL 5.7之后默认的存储引擎支持事务、支持行级锁、支持崩溃恢复是绝大多数业务场景的正确选择。有些老项目还在用MyISAM并发写一高就是整表锁死性能惨不忍睹。没有特殊理由——比如纯只读的归档表——就别用MyISAM。提示字符集在建库的时候就要定好否则建完表再改编码转换过程很容易搞出数据异常尤其是已有的中文数据。3. INSERT插入数据一次性说清各种姿势插入数据看起来是最简单的操作但里面有不少值得抠的细节。从语法写法到批量插入再到重复数据处理每一项都有讲究。3.1 基础语法和两种写法最基础的插入写法是这样的INSERT INTO user (username, password, email, age) VALUES (zhangsan, abc123, zsexample.com, 25);这种写法明确指定了要插入的列好处是以后表结构增加字段这条语句不用改可读性强一眼就能看出每个值对应哪个列。这是我在生产环境里最推荐的方式。还有一种省略列名的写法INSERT INTO user VALUES (1, lisi, xxx, lsexample.com, 30, 1, 0, 2025-01-01 00:00:00, 2025-01-01 00:00:00);这种写法要求VALUES里的值必须和表里的字段顺序完全一致少一个多一个都会直接报错。一旦表结构调整SQL就跟着炸。我强烈不建议在业务代码里写这种SQL维护成本太高了谁改表谁想骂人。再提一嘴自增主键。id是AUTO_INCREMENT插入的时候可以不传MySQL会自动分配。分配机制是取当前最大id加1或者取内存里维护的自增值。你也可以显式指定id但要注意指定过大id后自增计数器会跟着跳到那个值后续插入的主键可能不是你猜的数。没事别乱指定。3.2 批量插入能一条绝不N条业务里经常需要一次性插入几千甚至几万条数据。这时候千万不能在循环里一条一条INSERT。为什么因为每一条INSERT都是独立的SQL执行都要经历语法解析、权限检查、执行计划生成、事务提交这些完整链路网络往返和MySQL内部开销被成倍放大。更好的方式是用一条语句插入多行INSERT INTO user (username, password, email, age) VALUES (user1, pass1, u1example.com, 20), (user2, pass2, u2example.com, 21), (user3, pass3, u3example.com, 22);这种方式在几百到几千行的量级性能提升非常明显。我之前处理过一个批量导入任务从循环单条插入改成批量插入耗时直接从几分钟降到了几秒钟效果堪称立竿见影。但批量插入也不是越多越好。一次插一万行会产生一个很大的事务占用大量undo log和锁资源binlog也会被撑大主从复制的延迟也可能被拉高。我一般把每批控制在1000到2000行分批次提交。数据量再大就换LOAD DATA INFILE这种专门的高速导入方案。3.3 重复数据怎么办IGNORE、ON DUPLICATE KEY UPDATE、REPLACE业务上经常遇到数据存在就更新不存在就插入的需求。MySQL提供了三种玩法INSERT IGNORE插入时遇到唯一键冲突就忽略保留原数据返回一个warning。INSERT ... ON DUPLICATE KEY UPDATE冲突时执行更新操作。REPLACE INTO冲突时先删除原记录再插入新记录。举个例子username是唯一键现在要保证同一个用户名重复提交时只更新密码不新增记录INSERT INTO user (username, password, email, age) VALUES (zhangsan, newpass, zsexample.com, 26) ON DUPLICATE KEY UPDATE password VALUES(password), email VALUES(email), age VALUES(age);不过要注意MySQL 8.0.20之后的版本官方已经弃用了VALUES()函数在ON DUPLICATE KEY UPDATE里的写法推荐用别名方式INSERT INTO user (username, password, email, age) VALUES (zhangsan, newpass, zsexample.com, 26) AS new ON DUPLICATE KEY UPDATE password new.password, email new.email, age new.age;这三个操作里我日常用得最多的是ON DUPLICATE KEY UPDATE它在同步数据、导入数据的场景里非常好用。REPLACE INTO因为会先删后插导致自增id变化、外键关联被破坏、触发器执行两次没有特殊需求不建议用。4. SELECT查询写得好不好性能差距巨大SELECT是四个操作里最复杂、最值得花时间研究的。复杂的报表统计、多表关联、子查询归根结底都是SELECT的变种。这里我只讲基础但会把最实用、最容易踩坑的细节都讲到。4.1 基础查询与WHERE条件最基础的查询SELECT * FROM user;平时开发做调试可以这么查但上了生产环境我建议尽量避免SELECT *。原因有三点表字段很多时全字段查询浪费IO和网络带宽特别是有些字段是TEXT、BLOB这种大对象一查全带出来接口响应速度肉眼可见地变慢。如果后续表结构加了新字段SELECT *的结果集也跟着变可能导致程序解析出错。无法利用覆盖索引做优化明明索引里已经有你要的字段却非得回表再查一遍。所以生产环境写查询时把需要的列明确列出来SELECT id, username, email, age FROM user WHERE status 1;WHERE后面可以跟各种条件比较运算、!、、、、范围BETWEEN ... AND ...集合IN (...)模糊LIKE空值IS NULL/IS NOT NULL一组常用写法-- 年龄在20到30之间 SELECT id, username FROM user WHERE age BETWEEN 20 AND 30; -- 用户名在指定集合内 SELECT id, username FROM user WHERE username IN (zhangsan, lisi, wangwu); -- 邮箱不为空的用户 SELECT id, username FROM user WHERE email IS NOT NULL;这里有个特别容易踩的坑判断空值时千万不要写email ! NULL或者email NULL。因为在SQL里NULL和任何值做比较结果都是NULL也就是未知条件永远不成立。必须用IS NULL或IS NOT NULL来判断。这个问题我在不少工作了三五年的开发写的SQL里都见过。4.2 排序和分页的细节排序用ORDER BY默认升序ASC降序用DESCSELECT id, username, age FROM user WHERE status 1 ORDER BY age DESC;分页用LIMITSELECT id, username FROM user ORDER BY id LIMIT 10 OFFSET 20;LIMIT 10 OFFSET 20的意思是跳过前20条取10条等价于LIMIT 20, 10。这是列表页最常见的分页写法。但这里有个经典性能问题深分页的时候OFFSET越大查询越慢。因为MySQL必须扫描并丢弃前面的所有行才能拿到你需要的部分。数据量一上去翻到第100页的时候查询速度可能已经慢到让人抓狂。我一般改成基于游标的分页方式-- 记录上一次查询的最大id下一页从这里开始 SELECT id, username FROM user WHERE status 1 AND id 10000 ORDER BY id LIMIT 10;这种基于id游标的分页方式不管翻到第几页性能都稳定比OFFSET可靠得多。唯一的要求就是排序字段必须是id这种单调递增的列。4.3 LIKE模糊查询的索引失效问题LIKE是模糊查询的常用手段SELECT id, username FROM user WHERE username LIKE zhang%;这个查询会命中所有以zhang开头的用户名比如zhangsan、zhangwei。这里有个细节如果是前缀匹配zhang%并且username列上有索引这个查询大概率能走索引但如果是%zhang或者%zhang%这种后缀匹配或包含匹配索引基本失效只能全表扫描。这一点在高频搜索场景下尤其要命。比如商城的商品搜索很多业务方喜欢在前端拼一个LIKE %关键词%丢到后端。数据量小的时候无所谓数据量大了数据库CPU直接被打满。对这种需求真正靠谱的方案是引入ESElasticsearch或者用MySQL的全文索引而不是靠LIKE硬扛。4.4 顺手提一句聚合统计列表页和报表页总要有统计功能COUNT、SUM、MAX、MIN、AVG这几个聚合函数属于必会SELECT COUNT(*) AS total, AVG(age) AS avg_age FROM user WHERE status 1;COUNT(*)和COUNT(1)性能差别不大COUNT(column)会忽略NULL值统计逻辑上要注意。按维度分组统计用GROUP BYSELECT status, COUNT(*) FROM user GROUP BY status;如果GROUP BY的字段没走索引大数据量下会产生临时表和文件排序性能让人头疼。这些属于进阶内容基础阶段先会用就行。5. UPDATE修改数据先摸清影响范围再动手UPDATE是我眼中最危险的一个操作没有之一。因为INSERT最多影响几条新插入的数据DELETE好歹意图明确而UPDATE常常是本想改一条结果改了一片而且改完之后你未必第一时间发现。5.1 基本语法和WHERE的重要性UPDATE user SET email newexample.com WHERE id 1;这句的执行逻辑是先找到id1那一行然后把email字段改成新值。WHERE条件决定了你要改哪些行。如果你把WHERE忘了UPDATE user SET email newexample.com;那结果就是全表所有用户的邮箱都变成了同一个值而且普通MySQL客户端默认不会弹窗拦住你除非你提前开了安全模式。注意生产环境的UPDATE语句尤其跑在自动化发布脚本里的建议在UPDATE前面先跑一条等价的SELECT看清楚WHERE条件到底命中多少行确认无误再执行UPDATE。我个人的习惯是先SELECT COUNT(*)看命中行数然后加LIMIT 1先试更新一条确认数据没问题再放开全量执行。这套操作下来就算出问题影响也被压到了最小。5.2 多行更新的两种姿势多行批量更新最简单的做法是在业务代码里循环逐条UPDATE。但这和插入数据一个道理循环次数一多就是灾难网络往返和事务开销都受不了。我一般用两种方式。第一种是CASE WHEN一条SQL更新多行UPDATE user SET age CASE id WHEN 1 THEN 20 WHEN 2 THEN 30 WHEN 3 THEN 40 END WHERE id IN (1, 2, 3);这种写法一条语句原子完成不会有更新到一半程序崩溃、数据前后不一致的情况。第二种是JOIN另一张表来更新UPDATE user u JOIN temp_age t ON u.id t.id SET u.age t.age;这种方式特别适合从临时表或者批量导入的数据来更新主表。比如从Excel导入一批用户的年龄先把数据导进临时表再一次性UPDATE主表不管是性能还是代码可维护性都比逐条更新舒服得多。5.3 大表更新最怕锁和事务假设你要更新一张几百万行的表比如把所有status1的用户的积分加10一条UPDATE就能让数据库长时间持锁期间所有涉及这张表的读写请求都可能被阻塞主从复制的延迟也会被拉高甚至拖垮整个集群。这种大规模更新我建议拆分成批次执行UPDATE user SET points points 10 WHERE status 1 ORDER BY id LIMIT 10000;每次只更新一万行事务提交后释放锁然后循环再执行下一批直到处理完所有目标行。这种方式SQL执行次数变多了但对系统稳定性来说是完全值得的。我之前处理过一个凌晨跑批任务一开始一条UPDATE更新全表数据库锁了十几分钟没人敢碰改成分批之后系统稳得一批。还有一点必须强调UPDATE修改的是已经存在的数据是不可逆的。虽然MySQL有binlog但要从binlog里恢复整行数据非常麻烦。生产环境凡是涉及批量UPDATE的重要数据先备份CREATE TABLE user_bak_20250101 AS SELECT * FROM user;这一句花不了几秒钟但能救你无数次手滑。5.4 UPDATE和事务的化学反应再说一句事务的问题。多个表的UPDATE或者一次要更新多条相互关联的记录最好放在一个事务里START TRANSACTION; UPDATE account SET balance balance - 100 WHERE id 1; UPDATE account SET balance balance 100 WHERE id 2; COMMIT;这样保证要么都成功要么都回滚不会出现转账扣了钱对方没收到这种尴尬情况。注意事务里如果某一步出错了一定要记得ROLLBACK否则连接关闭时MySQL会自动回滚但你的业务代码可能拿着一个半完成的状态继续往后跑那才是真的坑。6. DELETE删除删数据之前先想好能不能找回删除操作和UPDATE一样危险DELETE没写WHERE就是清空全表而且比UPDATE更狠——数据可能彻底找不回来。6.1 DELETE基本语法和TRUNCATE的区别DELETE FROM user WHERE id 1;可以删一行也可以删多行DELETE FROM user WHERE age 60;DELETE和TRUNCATE都能让表变空但二者根本不是一回事我整理了一个对比表对比项DELETETRUNCATE是否可带WHERE可以能删部分行不可以只能清空全部事务支持可以ROLLBACKDDL操作隐式提交不可回滚自增ID不重置继续累加重置为初始值性能逐行删除慢直接释放空间快触发器会触发不会触发是否逐条记录日志是binlog量大否日志量极小实际项目里清空一个临时表用TRUNCATE没问题但清空一个业务表前必须三思尤其是自增ID重置可能导致后续新数据主键重复或者和别的系统对接时出现ID冲突。6.2 物理删除和软删除我站软删除这是我想多啰嗦几句的地方。很多新手写删除就是DELETE FROM但在真正的业务系统里我强烈建议用软删除。什么叫软删除就是给表加一个is_deleted字段删除时只更新这个标记而不是真的DELETEUPDATE user SET is_deleted 1 WHERE id 1;查询的时候过滤掉已删除的数据SELECT id, username FROM user WHERE is_deleted 0;这样做的好处非常明显数据可恢复误删了只要把is_deleted改回0就行不用翻备份。保留数据痕迹运营、审计、对账的场景都需要历史数据。避免外键关联断裂比如订单表关联用户表用户被物理删除后订单就成了无主数据所有关联查询都出问题。我在一个电商系统里遇到过真实案例运营手滑删了一批优惠券因为没有软删除数据直接没了几个人折腾了一晚上才从备份里找回一部分。从那以后凡是核心业务表我都坚持加is_deleted删除一律变更新。当然软删除也有它的代价每个查询都要记得带is_deleted0的条件唯一索引也要考虑这个字段否则会出现删了一条再插入同一条时唯一键冲突的尴尬。通用的做法是在唯一索引里把is_deleted也带上或者干脆用deleted_at存删除时间NULL表示未删除时间非空表示已删除这样同一行数据只能被删一次的问题也顺带解决了。6.3 删除大表数据最怕锁和复制延迟DELETE一大片数据和UPDATE大表一个道理会带来长时间锁表、大量binlog写入主从架构下从库可能延迟好几个小时。所以删除大表数据同样建议分批蠕动DELETE FROM user_log WHERE created_at 2024-01-01 ORDER BY id LIMIT 5000;每删完一批等一拍再删下一批直到没有需要删的行。这个习惯我是在一次清理历史日志时养成的——当时头铁想一条SQL删掉几百万行日志结果线上主库卡了近十分钟监控告警响成一片。另外提醒一句如果整张表都不需要了直接用DROP TABLE或TRUNCATE TABLE千万别用不带WHERE的DELETE FROM。DELETE全表几百万行事务日志和binlog直接爆炸而DROP、TRUNCATE秒级完成。6.4 DELETE与主键自增的坑顺带说一下DELETE删掉了数据自增主键的计数器不会回退。假设最大id是100你把id100那条数据删了再插入一条新数据它的id是101不是100。有些业务方会误以为删了最后一条数据再插就是原来的id在MySQL里这是不成立的。这个特性在某些场景下会带来困扰但它是InnoDB的设计目的就是避免主键冲突。7. 我踩过的几个坑都写在这里最后这部分分享几个真实的教训。每个都是线上事故级别的经验希望你别再踩一遍。7.1 忘记WHERE的UPDATE这事发生在我工作第二年。当时要给一批测试账号改状态SQL写好了UPDATE user SET status 1 WHERE username LIKE test%;但在测试库先跑的时候我想看看全表效果就把WHERE去掉执行了一次。结果切到生产环境执行时忘了把WHERE加回来一遍UPDATE下去全库所有用户的status都变成了1。用户量虽然不算大但客户那边发现所有账号状态都变了还是炸了锅。从那以后我给自己立了几条铁规矩生产环境的UPDATE/DELETE语句必须经过同事二次确认。先在测试库把完整SQL执行一遍确认无误再上生产。数据库客户端工具开启安全模式。比如Navicat和MySQL Workbench都可以设置UPDATE/DELETE必须带WHERE才允许执行这条设置能拦住大部分手滑。7.2 SELECT * 把接口拖垮有个报表接口本来只查三四个字段响应时间50毫秒左右。后来不知道哪次改需求把SQL改成了SELECT *而表里后来又加了好几个TEXT类型的大字段接口响应时间直接飙到3秒以上数据库CPU也跟着涨。后面定位到就是SELECT *导致的把SQL改回只查询需要的字段接口瞬间恢复正常。这个坑真不是小事。一张表的字段越多SELECT *的破坏力就越大。尤其是那种顺手加字段加出来的表二三十个字段里可能有一半是业务上根本用不到的大块头。7.3 字符集不一致导致乱码还有一次是导入数据时遇到乱码。源库是utf8目标库建表的时候没指定字符集用了默认的latin1结果中文导入后全变成了问号。排查了半天最后把表改成utf8mb4重新导数据才解决。这个坑在建表那节已经强调过这里再补充一点除了表字符集连接数据库的连接串上也要显式指定字符集比如在JDBC连接串里加上characterEncodingutf8在命令行客户端里加--default-character-setutf8mb4这样才能整条链路保持一致。7.4 隐式类型转换让索引失效这是排查慢查询时经常遇到的坑。假设id是int类型你写SELECT * FROM user WHERE id 123;MySQL会自动把字符串123转成数字123来比较这种从字符串转数字的情况有时还能走索引。但反过来如果索引字段是varchar你传了数字SELECT * FROM user WHERE username 123;MySQL会把username这一列隐式转换成数字来和123比较也就是说索引字段上发生了函数操作索引直接失效全表扫描跑不掉。所以写SQL时字段类型和参数类型保持一致永远不要依赖MySQL的隐式转换。7.5 大事务里批量操作回滚也遭罪有一次在一个事务里一次性插入、更新了几万条记录提交之后才发现有一小部分数据的逻辑不对想回滚结果发现事务太大ROLLBACK也执行了很长时间。后来我学乖了大操作拆成小批次每一批单独提交出错时最多回滚一小部分不至于全盘重来。当然拆批次的前提是你明确知道业务上可以接受部分成功。比如日志清理、历史数据迁移都很适合分批提交。而对于必须强一致的多表更新比如转账、下单扣库存还是老老实实放进一个事务里该锁就锁该等就等数据一致性永远要排在效率前面。关于MySQL增删查改的内容就先写到这儿。这些操作看着基础但基础不代表简单。建表设计影响后面所有操作的好坏INSERT重点是批量SELECT重点是别乱查、别查多UPDATE和DELETE时刻记住带WHERE能软删就别硬删能分批就不一把梭。这些都是我在生产环境里用真金白银换出来的教训希望对你有用。如果你也踩过什么有意思的坑欢迎在评论区聊聊我也顺便学两手。