MySQL这玩意儿干开发的绕不开做运维的绕不开搞数据分析的同样绕不开。你去看招聘要求十个岗位里八个写着熟悉MySQL但真正问起来很多人对增删改查的理解还停留在会写几条SQL的层面。实际上数据的增删改查是整个数据库操作的骨架你后面学的索引优化、事务隔离、读写分离、分库分表全都是在为这四类操作服务。这篇文章我就从实操角度把INSERT、SELECT、UPDATE、DELETE这四类操作掰开揉碎讲清楚包括语句怎么写、参数怎么选、坑在哪里、出了问题怎么排查。适合刚入行的开发者也适合那些写了好几年SQL但从来没深究过执行细节的朋友。1. 增删改查到底在解决什么问题1.1 为什么说CRUD是数据库的基石数据进数据库就四件事新增、查询、修改、删除。英文缩写CRUD对应Create、Read、Update、Delete。你做一个订单系统下单是新增列表是查询改收货地址是修改取消订单是删除绕来绕去就这四件事。但你把会写SQL和真正理解CRUD划等号那就亏了。我见过一个真实案例某同事写了一条UPDATE语句忘记带WHERE条件一执行整张表的单价全部变成了同一个值。几万条数据花了半天时间从备份里恢复。这种事故不是个例几乎每个MySQL踩坑合集里都有类似的故事。所以我一直觉得学习增删改查最重要的是建立三个意识第一边界意识。你的操作会影响多少行你心里得有数。UPDATE和DELETE尤其如此WHERE条件决定了影响范围条件写得不够精确代价就是整张表。第二代价意识。SELECT * 看起来很省事但在大表上每多查一列就多一分IO开销。你自己本地跑着没什么感觉放到生产环境、放到几百万行的表上差距就出来了。第三一致性意识。你的操作不是孤立的一次修改可能牵扯到其他表、其他业务逻辑。事务就是为了保证这种一致性而存在的。这三个意识说白了就是别只盯着语句本身要盯着数据。1.2 一条SQL语句是怎么跑起来的理解了增删改查要解决什么问题再看执行过程会轻松很多。你敲一条SELECTMySQL内部要经历这么几个阶段客户端把SQL文本发给服务端服务端先做语法解析检查你的语句写得对不对。通过之后做语义分析检查表存在不存在、字段存在不存在、有没有权限。然后是优化器登场它决定这条语句怎么执行最划算——走哪个索引、先连接哪张表、采用哪种执行方式。最后是执行器真正去存储引擎里读取或修改数据。我为什么要提这个过程因为排查问题的时候很有用。比如你发现一条查询特别慢第一步不应该是猜而是用EXPLAIN看它的执行计划看看优化器到底选了哪条路。是走了全表扫描还是用了索引一目了然。对于增删改而言执行阶段的区别在于SELECT只读取INSERT、UPDATE、DELETE都要修改数据。而修改数据就涉及事务日志和锁这也是为什么同样的语句在并发环境下表现千差万别。提示所有增删改查的排查都建议先分清楚是语句本身的问题还是执行环境的问题。语句问题看EXPLAIN环境问题看状态变量和锁等待。2. 动手之前建库建表和字符集的坑2.1 建表时的字段设计决定了后面所有的路很多人一上来就写增删改查但忽略了一个前提数据是存在表里的表结构设计得合不合理直接影响你后面每条SQL好不好写、跑得快不快。以用户表为例我见过太多烂设计用户名用VARCHAR(500)、手机号用VARCHAR存数字、时间字段用字符串、没有任何索引。等数据量上来一条简单的SELECT都要好几秒。建表有几个基本点需要注意第一字段类型宁小勿大。能TINYINT不INT能INT不BIGINT能VARCHAR(32)不VARCHAR(255)。类型越小索引占的空间越少查询的时候IO越少。但别走极端比如手机号虽然是一串数字但用BIGINT存会把开头的0丢掉所以还是得用VARCHAR。第二NOT NULL优先。很多开发喜欢给字段留NULL觉得没值就是NULL。但在MySQL里NULL会带来一系列麻烦索引效率降低、COUNT统计容易出错、查询条件容易写错。能用空字符串或者0代替就不要用NULL除非业务语义上必须区分没有值和值为空。第三时间字段建议用DATETIME或TIMESTAMP别用字符串。字符串比较大小是按照字典序来的跨年、跨月的区间查询一旦格式不统一结果就是错的。DATETIME和TIMESTAMP可以直接用比较运算符做区间过滤也支持函数处理。第四主键尽量用自增ID或者雪花ID别用自然主键。自然主键比如身份证号、手机号往往存在变更需求一旦变更就要级联更新所有引用它的表。拿一个简单的用户表举例CREATE TABLE t_user ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键, username VARCHAR(32) NOT NULL COMMENT 用户名, phone VARCHAR(20) NOT NULL DEFAULT COMMENT 手机号, email VARCHAR(64) NOT NULL DEFAULT COMMENT 邮箱, status TINYINT NOT NULL DEFAULT 1 COMMENT 状态1启用0禁用, 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), KEY idx_phone (phone) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户表;这张表我把注释写清楚了索引也建上了。UNIQUE KEY保证用户名不重复普通索引加速手机号查询。字段全部NOT NULL时间字段用默认值自动填充。这样的表结构后面写增删改查会很顺手。2.2 字符集为什么推荐utf8mb4字符集的问题属于那种不出事则已一出事就是大事的类型。最常见的表现就是中文乱码或者插入emoji表情的时候报错Incorrect string value。MySQL早期默认字符集是latin1后来很多项目改成utf8但utf8在MySQL里最多存3字节而emoji这类字符需要4字节。所以存emoji一定要用utf8mb4它才是真正完整的UTF-8编码。建库的时候统一指定CREATE DATABASE demo_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;建表的时候也可以在末尾指定。连接层面最好在JDBC连接串里加上characterEncodingutf8避免客户端和服务端字符集不一致导致的乱码。检查当前连接的字符集可以执行SHOW VARIABLES LIKE character_set%;我实际排查过很多乱码问题最后发现根源都不是表或库的字符集设置错了而是连接层的字符集没对齐。客户端用一种编码发过去服务端用另一种编码解析两边鸡同鸭讲数据就乱了。你记住一个原则库、表、连接三个环节的字符集要保持一致推荐全部utf8mb4。3. 核心操作逐个拆解INSERT、SELECT、UPDATE、DELETE3.1 INSERT插入的三种姿势和容易忽略的细节INSERT就是把数据写进表里看起来简单但写法上也有讲究。我归纳为三种姿势。姿势一单条插入INSERT INTO t_user (username, phone, email) VALUES (zhangsan, 13800001111, zhangsanexample.com);指定字段名插入是最推荐的写法。好处是字段和值一一对应可读性强后面表结构增加字段时这条SQL也不会因为字段顺序变化而出错。姿势二批量插入INSERT INTO t_user (username, phone, email) VALUES (lisi, 13800002222, lisiexample.com), (wangwu, 13800003333, wangwuexample.com), (zhaoliu, 13800004444, zhaoliuexample.com);批量插入的核心价值是减少SQL的执行次数。同样插1000条数据一条一条插需要执行1000次批量插可能10条SQL就够了网络往返和日志刷盘次数大幅降低。生产环境实测下来批量插入比单条循环插入快一个数量级。不过批量也别太大我一般控制在500到1000条一批。单条INSERT最大能接受的数据量有个max_allowed_packet参数约束默认是64MB但批太大事务日志和锁持有时间都会变长容易拖垮其他请求。姿势三INSERT ... ON DUPLICATE KEY UPDATEINSERT INTO t_user (username, phone) VALUES (zhangsan, 13900001111) ON DUPLICATE KEY UPDATE phone VALUES(phone);这条语句解决的是一个高频需求数据存在就更新不存在就插入。它的依据是唯一键或者主键冲突。比如用户表username有唯一索引插入时如果username已经存在就走UPDATE分支更新phone字段。第一次用这个语法的时候得注意VALUES()函数在新版本里已经标记为废弃未来可能会换成别名语法但当前主流版本用着没毛病。INSERT还有几个容易忽略的细节第一自增ID不会因为插入失败而回退。比如你插入一条违反唯一约束的数据报错后自增主键已经消费掉了一个值你再插入成功的数据ID会跳跃。这是正常现象别以为是主键错乱了。第二大量INSERT之后自增ID可能不是连续分配的。InnoDB在批量插入时是按块预分配自增值的如果事务回滚预分配的ID就浪费了。第三指定自增列的值要谨慎。除非你在做数据迁移否则不要手动给自增主键赋值一旦赋值超过了当前自增序列MySQL会强制把自增序列跳到那个值很可能把后续的自增ID顶到一个很大的数。3.2 SELECT查询不只是SELECT *SELECT是增删改查里出现频率最高的操作也是优化空间最大的操作。我把日常工作中最常用的查询场景拆成几个层次。基础查询与条件过滤SELECT id, username, phone FROM t_user WHERE status 1 AND created_at 2024-01-01;这里两个点要强调。第一**不要用SELECT ***按需取列能少一列就少一列因为取出的数据要经过网络传输回客户端列越多传输量越大。第二WHERE条件的写法直接影响索引命中。像WHERE created_at 2024-01-01这样的范围条件只要created_at上建了索引就能用上。排序与分页SELECT id, username, created_at FROM t_user WHERE status 1 ORDER BY created_at DESC LIMIT 20 OFFSET 0;LIMIT 20 OFFSET 0表示从第0行开始取20条。注意ORDER BY的字段如果有索引排序就不用额外消耗内存和CPU如果没索引MySQL就要把数据放到临时文件里做排序数据量大了会非常慢。分页还有一个经典深坑深分页。当OFFSET到了十万、百万级别MySQL还是会把前面所有行都扫描一遍再扔掉性能急剧下降。常见解法是延迟关联先查出主键再关联原表取完整数据SELECT t.id, t.username, t.created_at FROM t_user t INNER JOIN ( SELECT id FROM t_user ORDER BY created_at DESC LIMIT 20 OFFSET 500000 ) tmp ON t.id tmp.id;我实测过百万级数据下这种写法比分页直查快十倍以上。聚合查询SELECT status, COUNT(*) AS cnt FROM t_user GROUP BY status;GROUP BY和聚合函数是报表类需求的标配。COUNT()、SUM()、MAX()、MIN()这些函数在InnoDB下的表现各有不同比如COUNT()在InnoDB里并不是O(1)的它要遍历满足条件的行去统计所以大表上做COUNT要谨慎。多表关联SELECT u.username, o.order_no FROM t_user u INNER JOIN t_order o ON u.id o.user_id WHERE u.status 1;关联查询要注意的是驱动表的选择。一般原则是小表驱动大表优化器会自动选择但你也可以通过STRAIGHT_JOIN强制指定顺序。实际工作中我碰到过很多因为字段字符集不一致导致关联走不了索引的案例两边都是VARCHAR但一个utf8一个utf8mb4关联的时候索引就失效了非常坑。3.3 UPDATE更新数据的安全边界UPDATE恐怕是引发生产事故最多的操作因为它的破坏性是隐蔽的数据被改了但不会立即报错等你发现的时候后悔都来不及。基础语法UPDATE t_user SET phone 13900009999 WHERE username zhangsan;重点是WHERE条件一定要写全、写准。执行之前先跑一条等价的SELECT确认影响范围SELECT id, username FROM t_user WHERE username zhangsan;这条SELECT返回多少行UPDATE就改多少行。养成这个习惯能规避掉90%的误操作。多字段更新UPDATE t_user SET status 0, updated_at NOW() WHERE id 123;同时更新多个字段时用逗号分隔而不是AND。我在代码评审里见过有人写成SET status 0 AND phone xxx语法直接报错这种低级错误反而不会造成事故怕的是逻辑上没错但条件写得宽泛。不要让UPDATE去修改主键或唯一键除非有强需求否则不要在主键上做UPDATE。修改主键意味着行要移动到新位置索引要重建还会引发外键关联问题。我见过一个同学为了修数据把某张表的主键从1改成10001结果所有关联子表的外键全部失联业务数据一片混乱。关于UPDATE的原子性在MySQL默认的自动提交模式下每个UPDATE语句是原子的要么全部生效要么全部不生效。但如果你在一个事务里连续执行多条UPDATE这些操作就是一个整体要么全部提交要么全部回滚。这就是为什么说事务是UPDATE类操作的保命符。START TRANSACTION; UPDATE t_account SET balance balance - 100 WHERE id 1; UPDATE t_account SET balance balance 100 WHERE id 2; COMMIT;如果第二条语句失败可以ROLLBACK账就不会算错。转账场景就是最典型的事务应用。3.4 DELETE删除数据的两种思路DELETE从语义上分两种硬删除和软删除。硬删除DELETE FROM t_user WHERE id 123;这就是物理删除数据从表里彻底消失。一旦删除普通查询查不到除非有备份否则找不回来。生产环境硬删除要非常谨慎尤其是核心业务表。我的建议是任何DELETE操作之前先备份或者至少先把要删的数据SELECT出来确认。软删除很多业务表会加一个is_deleted字段删除就是执行UPDATEUPDATE t_user SET is_deleted 1, deleted_at NOW() WHERE id 123;这样数据还在只是业务上不可见。好处很明显数据可回溯、可恢复坏处是所有查询都要记得加AND is_deleted 0条件漏了就把已删除的数据查出来了这是软删除最常见的坑。清空表 vs 删除表DELETE FROM t_user逐行删除不会释放表空间只是标记为可复用而且会记录每一行的删除日志大表上极慢。TRUNCATE TABLE t_user直接把整个表的数据页标记为可重用速度极快但自增ID会重置。注意TRUNCATE会隐式提交事务一旦执行无法回滚。DROP TABLE t_user连表结构带数据一起删除是最彻底的。三者的区别建议你亲手建个表分别试一下感受一下速度和数据表现上的差异。特别是TRUNCATE之后自增ID从1重新开始这个行为很多项目因为这个被坑过原来主键到了1000TRUNCATE之后新数据主键从1开始如果其他地方缓存了旧ID就会出现主键冲突或者引用错乱。提示任何删除操作我个人的铁律是三查一删先查数量再查明细再查关联最后才执行删除。宁可多花两分钟不要赌那一下手速。4. 事务、锁与隔离级别增删改查背后的可靠性4.1 为什么事务能让增删改查更安全单独一条INSERT、UPDATE、DELETE是原子的这在MySQL默认的autocommit1模式下成立。但真实业务里一个操作往往涉及多条语句。比如下单要扣库存、要写订单、要记流水这三步要么全成功要么全失败不能只扣了库存没写订单。事务就是来解决这个问题的。ACID四个特性原子性事务内的操作全部执行或全部不执行。InnoDB通过undo log实现执行过程中如果出错用undo log回滚到事务开始前。一致性事务执行前后数据完整性约束不能被破坏。比如转账前后总金额不变。隔离性一个事务未提交的修改对其他事务不可见取决于隔离级别。通过锁和MVCC实现。持久性事务提交后修改永久生效。InnoDB通过redo log保证即使数据库崩溃重启后也能通过redo log重放。我在实际项目里见过最典型的错误是开发把多条SQL拆成单独执行每个连接都自动提交结果中间一步失败数据就处于半成品状态。要么后续流程大量报错要么需要人工修数据。正确的做法是加事务START TRANSACTION; -- 扣库存 UPDATE t_stock SET num num - 1 WHERE sku_id 100 AND num 0; -- 影响行数为0说明库存不足 -- 这时应该回滚或者中断 INSERT INTO t_order (user_id, sku_id, num) VALUES (1, 100, 1); INSERT INTO t_order_log (order_id, action) VALUES (LAST_INSERT_ID(), CREATE); COMMIT;注意扣库存那条WHERE条件里带了num 0这样即使并发情况下也最多只有一个事务能扣成功从源头上规避了超卖问题。这也是用UPDATE解决并发问题的一个经典手法。4.2 隔离级别和锁理解并发场景下的不一致MySQL有四种隔离级别READ UNCOMMITTED能读到其他事务未提交的数据脏读几乎不用。READ COMMITTED只能读到已提交的数据解决了脏读。Oracle默认隔离级别。REPEATABLE READ同一事务内多次读取结果一致解决不可重复读。MySQL InnoDB默认隔离级别。SERIALIZABLE最强隔离所有事务串行执行性能最差。InnoDB在REPEATABLE READ级别下通过**MVCC多版本并发控制**让读操作不阻塞写操作、写操作不阻塞读操作。快照读普通的SELECT看到的是事务开始时的数据版本当前读UPDATE、DELETE、SELECT ... FOR UPDATE读到的是最新已提交数据。这个特性在写代码时影响很大。举一个实际场景你在一个事务里先查询订单金额再根据金额去做判断那前后的两次SELECT结果是否一致取决于隔离级别。在REPEATABLE READ下是一致的在READ COMMITTED下可能不一致如果中间有其他事务提交了修改。锁方面我建议至少搞清楚三种锁行锁InnoDB对单行记录加锁只在有索引的查询条件下才有效如果更新条件没有索引会升级为表锁实际上是对所有行加锁这是性能杀手。间隙锁REPEATABLE READ下为了防止幻读InnoDB会对索引间隙加锁锁住一个区间而不是具体某行。这就是为什么两个事务分别插入数据时明明插入的行不重复还是会互相阻塞。意向锁表级别的标记用于判断表上是否有行锁提升锁冲突检测效率。死锁是并发场景最容易遇到的问题。两个事务各自持有部分锁又要去获取对方持有的锁互相等待InnoDB检测到死锁会回滚其中一个事务。遇到死锁报错不要慌先看错误信息里的两个事务持有哪些锁多半是加锁顺序不一致导致的。统一所有事务对资源的加锁顺序比如都先锁A再锁B就能规避大部分死锁。5. 高频问题与排查技巧实录5.1 慢查询排查先看执行计划慢查询是最常见的问题。生产环境遇到一条SQL执行要5秒的情况排查路径一般是这样的第一步确认是不是真的慢。用EXPLAIN看执行计划EXPLAIN SELECT id, username, created_at FROM t_user WHERE phone 13800001111 ORDER BY created_at DESC;EXPLAIN输出的关键列有type访问类型、key使用的索引、rows预估扫描行数。type从好到差大概是system const eq_ref ref range index ALL。如果看到type为ALL说明全表扫描基本可以确定问题就在这里。第二步检查索引。SHOW INDEX FROM t_user;看phone字段有没有索引。没有的话用ALTER TABLE t_user ADD INDEX idx_phone (phone);加上。第三步检查是不是索引失效。常见的索引失效场景包括在索引列上做函数运算、隐式类型转换、LIKE前置通配符LIKE %abc、OR连接非索引条件。比如WHERE phone 138和手机号字段是VARCHAR但传了数字MySQL会做隐式转换导致索引失效这个我踩过好多次。EXPLAIN里的Extra列如果出现Using filesort意思是排序没用上索引文件排序本身不慢但数据量一大就会慢出现Using temporary说明用了临时表GROUP BY、DISTINCT、UNION这类操作容易触发。5.2 锁等待和死锁的处理锁等待的报错一般是Lock wait timeout exceeded; try restarting transaction。意思是当前事务等待获取锁超过innodb_lock_wait_timeout默认50秒的阈值。排查路径-- 查看当前所有事务和锁情况 SELECT * FROM information_schema.INNODB_TRX; -- 查看锁等待关系 SELECT * FROM sys.innodb_lock_waits;重点看哪个事务已经运行了很久还没提交。常见原因是某个事务开启后执行了UPDATE但后续逻辑报错没有回滚也没有COMMIT/ROLLBACK事务一直挂着导致其他事务全部阻塞。你只要找到那个老事务把它对应的会话杀掉或者等它回滚锁等待就解除了。死锁的报错是Deadlock found when trying to get lock。InnoDB会自动回滚其中一个事务你的应用代码要捕获死锁异常并重试。从设计上尽量保持事务短小加锁顺序一致能在代码层避免的问题就不要留给数据库去处理。5.3 字符集乱码和数据丢失的预防乱码问题排查思路是从客户端往服务端逐层检查-- 查看服务端字符集设置 SHOW VARIABLES LIKE character_set%; -- 查看库的字符集 SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME demo_db; -- 查看表的字符集 SELECT TABLE_NAME, TABLE_COLLATION FROM information_schema.TABLES WHERE TABLE_SCHEMA demo_db;一般乱码八成是连接层字符集不对。在应用侧连接串里要明确指定编码比如JDBC的characterEncodingutf8或者MySQL命令行启动后执行SET NAMES utf8mb4。如果确认都用了utf8mb4还乱码检查是不是表里的历史数据本身已经是错误编码损坏了这种只能用程序清洗靠改字符集设置救不回来。数据丢失的预防我提两个实操建议。一是定期备份至少每天全量备份加上binlog增量备份这样即使误删也能用备份binlog恢复到误操作之前的时间点。二是所有批量UPDATE/DELETE前先SELECT COUNT(*)确认范围操作后立即SELECT抽样检查。我在实际工作中因为一次裸UPDATE误伤数据后就给自己定了个硬规矩凡是要在生产环境执行非SELECT操作必须走审批流程SQL必须附带影响行数预估执行时先用LIMIT或WHERE限制样本验证再逐步放开。5.4 常见问题速查表现象可能原因排查方法中文乱码连接层字符集与库表不一致检查character_set_client、character_set_connection插入emoji报错字符集是utf8而非utf8mb4改库表字符集为utf8mb4查询缓慢索引缺失或索引失效EXPLAIN看type和keyUPDATE很慢条件无索引导致行锁扩散为表锁给条件字段加索引确认WHERE命中索引锁等待超时长事务未提交查看INNODB_TRX找到并处理未提交事务死锁多个事务加锁顺序不一致统一加锁顺序捕获异常重试自增ID不连续插入失败或事务回滚消耗了自增值正常现象无需处理DELETE后表空间不缩小DELETE只标记删除不释放物理空间需要时用OPTIMIZE TABLE重建表6. 一些实践中的规范和心得最后分享几条我在实际项目里沉淀下来的增删改查规范都是拿教训换来的。第一SQL全部小写关键词字段用反引号包裹。小写因为可读性统一反引号是为了避免字段名和保留字冲突。比如desc、order这种词如果不加反引号很容易在不经意间触发语法错误。虽然反引号不是必须的但统一加上可以省很多心。第二所有查询必须有明确条件禁止裸SELECT FROM大表。哪怕是要全表导出数据也应该先用LIMIT 100验证一下数据内容然后分批处理。我见过有人连SELECT * FROM t_log这种语句直接在生产库上执行几百万行数据全捞出来把数据库IO打满业务全挂。第三UPDATE、DELETE语句必须写WHERE且WHERE必须基于主键或唯一键。如果业务上确实需要非唯一键条件先SELECT确认范围带上LIMIT抽样检查。第四小步提交、及时提交。事务别开太大一个事务处理的数据量控制在千行以内比较稳妥。事务开着的时间越长锁持有的时间越长冲突概率越高。第五线上变更尽量放在低峰期。批量UPDATE大表即使有索引一次更新几十万行也会产生大量redo log主从同步延迟会飙升。分批执行每批几百行中间sleep一下对系统影响小得多。还有一个小技巧日常调试SQL时记得给MySQL加上--safe-updates选项启动。这个模式下不带WHERE条件的UPDATE和DELETE会被直接拒绝等于给手滑上了一道保险。开发环境强烈推荐开启生产环境虽然一般不让开但你可以把常用语句写成模板把WHERE条件固定下来降低失误概率。增删改查这事儿看起来入门门槛极低写几句SQL谁都会但把它写稳、写快、写出不出事故靠的是对数据边界的敬畏和对执行原理的理解。我见过三年经验的开发依然因为一条DELETE没有WHERE而被通报也见过实习生靠着一套规范的排查流程救回了一次生产事故。数据库不是神仙它只是老老实实执行你给它的指令你给的条件不精确它就老老实实地把整张表改了——这就是为什么我觉得增删改查这门课值得每个写SQL的人重新认真过一遍。下次动手之前先问自己三个问题这条语句影响多少行有没有备份出错了怎么回滚三个问题都答得上来你手里的SQL才算真正有了分寸。
