MySQL增删改查全解析：从SQL语法到JDBC与MyBatis-Plus的落地避坑

干这行这么多年最容易被忽略的往往是最基础的 MySQL 增删改查。很多人用上 MyBatis-Plus 之后写lambdaQuery()比写 SQL 还溜真到了排查慢查询、数据对不上的时候反而要回头补 SQL 的课。这篇就把增删改查从头到尾捋一遍从 SQL 语句本身讲到 JDBC、MyBatis-Plus 里的落地姿势再把我踩过的那些坑一并倒出来。不管你是刚入行的实习生还是写了好几年业务代码的老手只要天天跟数据库打交道这篇文章都值得你花几分钟过一遍。1. 增删改查的整体认知先想清楚“数据生命周期”再动手1.1 CRUD 不只是四句话而是数据流转的四个阶段增删改查对应着数据从进入到退出系统的完整生命周期INSERT是数据诞生SELECT是数据被消费UPDATE是数据状态变化DELETE是数据消亡。很多人写 CRUD 只盯着单表那几行 SQL却没想过这一步操作会牵连哪些关联数据、要不要保留历史记录、并发情况下会不会互相覆盖。比如一个订单系统用户下单走 INSERT支付回调走 UPDATE对账查询走 SELECT退款和作废走 DELETE 或状态翻转。如果只把增删改查当“四个按钮”很容易在写业务的时候漏掉关键约束。我的习惯是拿到需求先画一张“数据流转图”数据从哪里来、到哪里去、中间经过哪几个状态字段、哪些操作需要加事务、哪些删除要留痕。这个习惯帮我避免过很多次上线事故。框架能帮你生成 SQL但生成不了业务判断。换句话说SQL 功底决定了你能不能看懂框架在干什么、能不能判断它生成的 SQL 是否高效。1.2 写增删改查之前先确认表结构和约束我见过太多人拿到表就开写连字段类型、默认值、唯一索引都没看。比如用户表phone字段建了唯一索引你还在代码里先查后插做“防重复”其实数据库层面早就帮你兜住了。反过来如果表里没有唯一索引你就算查了再插并发下也可能插出重复数据。所以动手前花一分钟看SHOW CREATE TABLE重点确认四件事主键策略是自增还是雪花 ID、哪些字段有唯一索引、哪些字段有默认值、字符集是不是utf8mb4。这些信息直接决定你 INSERT 要不要指定字段、UPDATE 的 WHERE 怎么拼接、DELETE 会不会误伤。增删改查看似是 DML 的事但实际上 DDL 的设计质量决定了 DML 好不好写。表结构设计得烂CRUD 写起来就处处别扭。2. INSERT 插入操作从单条插入到批量写入的完整姿势2.1 INSERT 基础语法与字段策略INSERT 的基本句式很简单INSERT INTO user (username, phone, age) VALUES (张三, 138xxxx, 25);这里有个关键选择到底是指定字段列表还是省略字段直接INSERT INTO user VALUES (...)。我强烈建议永远显式指定字段列表。原因很实际——表结构几乎一定会加字段你写INSERT INTO user VALUES (...)这种全字段写法一旦表里新增一列这个 SQL 立刻报错或者数据错位。显式指定字段后新加的列只要允许为空或有默认值老 SQL 依然能跑。另一个容易忽略的点是默认值。MySQL 里如果字段没传值会自动用DEFAULT表达式填充。比如常见的create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP你 INSERT 时不传这个字段数据库会自动写入当前时间。这比在应用层手动塞时间更稳妥因为应用服务器的时间和数据库时间可能不一致。填表这个动作也一样你不填的格子要按表格规则自动处理而不是你在代码里硬编码一个时间。2.2 INSERT 的三个变体ON DUPLICATE KEY UPDATE、INSERT IGNORE、REPLACE INTO业务里经常遇到“有则更新无则插入”的需求比如同步第三方用户数据、上报埋点计数。MySQL 提供了三个经典方案-- 存在唯一键冲突时更新否则插入 INSERT INTO user (id, phone, nickname) VALUES (1, 138xxxx, 张三) ON DUPLICATE KEY UPDATE nickname VALUES(nickname); -- 冲突时忽略不报错也不更新 INSERT IGNORE INTO user (id, phone, nickname) VALUES (2, 139xxxx, 李四); -- 冲突时先删除再插入注意会改变自增 ID 和触发 delete 级联 REPLACE INTO user (id, phone, nickname) VALUES (3, 137xxxx, 王五);三种语义差别很大。我用得最多的是ON DUPLICATE KEY UPDATE因为它能保留原有行的其他字段只更新指定列。INSERT IGNORE适合导入数据时想跳过脏数据。REPLACE INTO看起来方便但底层是先 DELETE 再 INSERT如果表上有外键或自增主键会带来意想不到的连锁反应我基本只在清数场景用。注意VALUES()函数在新版本可能弃用可以用别名写法INSERT INTO ... VALUES (...) AS new ON DUPLICATE KEY UPDATE nickname new.nickname。2.3 批量插入的正确打开方式逐条 INSERT 在数据量小的时候没什么一旦上千条就会明显变慢。原因在于每条 SQL 都要经过完整的 SQL 解析、权限校验、执行、日志写入和网络往返。一条 SQL 插多组值可以大幅减少这些开销INSERT INTO user (username, phone, age) VALUES (张三, 138xxxx, 25), (李四, 139xxxx, 26), (王五, 137xxxx, 27);经验值是一批 500 到 1000 条比较合适。太少省不了几次往返太多会撑爆max_allowed_packet参数导致报错。如果用的是 MyBatis 的foreach标签要格外小心拼接出来的 SQL 超过包大小限制我的做法是在代码里手动分批每批 500 条。批量插入还有几个细节一是尽量保证插入顺序与主键索引顺序一致减少页分裂二是在导入大量历史数据时可以先ALTER TABLE ... DISABLE KEYS关闭非唯一索引更新导入完再开启但这招在 InnoDB 下收益有限别盲目用三是大批量插入时关注innodb_flush_log_at_trx_commit如果允许数据丢失窗口可以临时调到 2 或 0 加快写入但生产环境要慎重。2.4 插入操作的高频坑字符集、主键冲突与 SQL 注入插入中文变成问号是最常见的字符集问题。排查方向是字符集链路客户端 → 连接 → 数据库 → 表 → 字段。你可以在插入前执行SHOW VARIABLES LIKE character_set%看当前值确保character_set_client、character_set_connection、character_set_results都是utf8mb4。JDBC 连接串要加useUnicodetruecharacterEncodingutf8mb4否则即使表和库都是 utf8mb4连接层也可能把人家的中文转成乱码。主键冲突这个坑更多出现在“先查后插”的伪幂等方案里。你查到没有就插入但两个请求同时进来可能都查不到然后一个插成功一个报Duplicate entry。正解是用唯一索引加ON DUPLICATE KEY UPDATE或者用分布式锁把“查 插”锁住。SQL 注入不用多说任何用户输入拼进 SQL 都要用参数化查询PreparedStatement 和 MyBatis 的#{}都能兜住。3. SELECT 查询操作最考验功底的环节远不止 SELECT *3.1 SELECT 书写顺序与执行顺序的差异SELECT 的完整句式是SELECT ... FROM ... JOIN ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT ...但 MySQL 真正的执行顺序是FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT。这个差异两个实战影响。第一你在 WHERE 里不能直接引用 SELECT 中定义的别名因为 WHERE 执行时别名还没算出来第二看执行计划时要按执行顺序去理解——为什么WHERE能过滤掉大部分行时GROUP BY的负担就小因为数据是先过滤再聚合的。理解执行顺序后很多查询性能问题一眼就能看明白不是 SQL 写错了而是数据在哪个环节膨胀了。SELECT 的列选择也是门学问。我从来不在生产代码里写SELECT *。一个是网络传输浪费二个是如果表加了text或blob大字段整条查询会被拖慢。只查需要的列配合覆盖索引性能差别非常明显。索引里已经包含了你需要的列MySQL 就不需要回表这就是“覆盖索引优化”日常查询优化最划算的手段之一。3.2 WHERE 条件与索引失效的几个典型场景索引不是建了就一定生效。最常见的失效场景有三个函数包裹、隐式类型转换、前导模糊查询。-- 函数导致索引失效 SELECT * FROM user WHERE DATE(create_time) 2024-01-01; -- 优化写成范围条件 SELECT * FROM user WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00; -- 隐式类型转换phone 是字符串却用数字比较 SELECT * FROM user WHERE phone 13800138000; -- 前导模糊查询 SELECT * FROM user WHERE nickname LIKE %张%;DATE(create_time)这种写法索引树里存的是原始时间值你拿函数处理后再拿去索引树匹配MySQL 只能全字段扫描。范围查询则能充分利用 B 树的顺序特性精准定位到区间内的数据。判断一个 SQL 有没有走索引最直接的方式是EXPLAIN看type字段。如果能看到const、ref、range一般没问题如果出现ALL说明全表扫描尤其大表上出现这个要立刻处理。3.3 排序与分页从基础 ORDER BY 到深分页优化ORDER BY 看着简单其实暗藏不少细节。默认排序规则跟字段的 collation 有关字符串字段如果用的utf8mb4_general_ci和utf8mb4_unicode_ci排序结果可能不一样。比如有些字符在两种排序规则下的排序位置不同导致同一个 ORDER BY 在不同环境结果不一致。如果需要指定规则可以写ORDER BY name COLLATE utf8mb4_bin。另一个常见问题是 ORDER BY 字段如果没走索引MySQL 会使用 filesort数据量大时非常慢。让排序字段和 WHERE 条件组成联合索引通常能同时解决过滤和排序。分页查询的经典写法是LIMIT offset, size例如第 3 页每页 20 条SELECT * FROM user ORDER BY id LIMIT 40, 20;但这个写法在深分页时有大坑LIMIT 100000, 20意味着 MySQL 要先把前面 10 万行找出来丢弃再返回 20 行越往后越慢。我在后台列表和日志查询里尤其遭过罪。优化方案是“延迟关联”先查主键再回表取完整数据。-- 延迟关联先查 id 再取数据 SELECT u.* FROM user u INNER JOIN (SELECT id FROM user ORDER BY id LIMIT 100000, 20) tmp ON u.id tmp.id;另一个更彻底的方案是基于游标的分页记住上一页最后一条记录的 id下一页用WHERE id 100000 LIMIT 20彻底消灭 offset。前提是排序字段稳定且 id 递增。如果是按创建时间排序可以把 (create_time, id) 作为游标位置。这个方案在移动端下拉加载场景里几乎是标配。3.4 JOIN、聚合查询与常见的统计误区关联查询是 SELECT 里的重头戏。核心原则是小表驱动大表用 MySQL 的说法就是驱动表要小。比如查订单和用户一般让结果集小的订单表做驱动表。LEFT JOIN 时有个经典误解ON 和 WHERE 过滤时机不一样。ON 决定左表是否全保留WHERE 会过滤 JOIN 后的结果。如果你在 WHERE 里写了右表的条件左连接其实已经变成内连接效果了。聚合统计也有不少误区。COUNT(1)和COUNT(*)在 MySQL 8.0 里性能基本没差别COUNT(字段)如果字段为 NULL 则不计数。日常统计订单金额要用SUM(amount)注意 amount 字段为 NULL 时 SUM 返回 NULL 而不是 0需要IFNULL(SUM(amount), 0)。GROUP BY 时还要注意ONLY_FULL_GROUP_BY这个 sql_modeSELECT 的非聚合字段必须出现在 GROUP BY 中否则直接报错。对慢查询来说EXPLAIN 看Extra里出现Using temporary或Using filesort就要考虑是不是要建联合索引来优化。4. UPDATE 与 DELETE 操作删改现场最容易出事故谨慎再谨慎4.1 UPDATE 的基本用法与不带 WHERE 的惨痛教训UPDATE 的基本语法不多说核心是 WHERE。我必须要强调一句不带 WHERE 的 UPDATE就是全表更新这是生产环境事故高发中的高发。别以为“就改一条”忘了拼接条件或者条件拼出来永远为真整张表瞬间被改掉。我的习惯是任何 UPDATE 上线前先执行等价的 SELECT-- 先查后改 SELECT * FROM user WHERE phone 138xxxx; UPDATE user SET age 26 WHERE phone 138xxxx;数量对得上再执行 UPDATE。在 MySQL 客户端里可以设置SET sql_safe_updates 1这样不带 WHERE 的 UPDATE 和 DELETE 会被直接拒绝新手期就把它开着能挡住一大批事故。UPDATE 的另一个底层机制是加锁。InnoDB 执行 UPDATE 会锁定命中的行如果 WHERE 条件能走唯一索引锁的就是行如果 WHERE 条件没走索引可能升级到锁大量行甚至表。这就是为什么偶尔一条 UPDATE 能把线上卡死——它锁的范围比你想象的大得多。排查方法后面讲这里先记住更新操作尽量走主键或唯一索引。4.2 DELETE 与逻辑删除、TRUNCATE 的取舍DELETE 的语法和 UPDATE 类似核心风险也是 WHERE。很多业务场景不适合物理删除数据比如订单作废、用户注销删了就查不到历史记录。互联网行业普遍用逻辑删除加一个deleted字段删除变成 UPDATEdeleted 1查询自动拼接deleted 0。MyBatis-Plus 里用TableLogic注解就能自动实现不需要你每条 SQL 手工拼接。大量数据清理时DELETE和TRUNCATE要分清。TRUNCATE TABLE是清空整张表速度极快但不能加 WHERE也不能回滚还会重置自增 ID。它会把 InnoDB 的表中数据页直接标记为可复用相当于把表“格式化”了。生产环境里 TRUNCATE 基本要经过审批才能执行。而 DELETE 逐行删除会记录 binlog、走事务可以回滚代价是慢。如果你要清掉某张表 90% 的数据另一种思路是“保留少量数据删表重插”但这是一个相对复杂的操作需要在停机或低峰期操作别在业务高峰期贸然尝试。4.3 事务控制让增删改查安全落地增删改查和事务是天然绑定的尤其是一个业务操作要更新多张表的时候。经典例子转账。A 扣钱、B 加钱、插入流水三步必须在一个事务里否则中间任何一步失败都会导致钱对不上。START TRANSACTION; UPDATE account SET balance balance - 100 WHERE user_id A; UPDATE account SET balance balance 100 WHERE user_id B; INSERT INTO transfer_log (from_user, to_user, amount) VALUES (A, B, 100); COMMIT;事务隔离级别默认是REPEATABLE READ它保证同一事务里多次 SELECT 结果一致但也带来间隙锁、幻读等一系列特性。对于大多数业务默认隔离级别已经够用关键是控制事务的粒度。我见过最典型的线上事故一个事务里循环更新几万条数据还夹杂着远程接口调用结果事务长时间不提交锁越积越多直接把数据库拖垮。事务里不要做远程调用、不要循环执行太多 SQL、不要人为等待。跨系统的分布式事务是另一个大话题单机事务能短则短能小则小。5. JDBC 与 MyBatis-Plus 场景下的增删改查落地5.1 JDBC 手写 CRUD理解底层才能用好框架JDBC 是 Java 操作数据库最原始的姿势虽然现在很少有人直接这么写但理解它的流程对理解连接池、MyBatis 都有帮助。核心就六步加载驱动、获取连接、创建 PreparedStatement、执行 SQL、处理结果集、关闭资源。String url jdbc:mysql://localhost:3306/demo?useUnicodetruecharacterEncodingutf8mb4serverTimezoneAsia/Shanghai; String sql INSERT INTO user (username, phone) VALUES (?, ?); try (Connection conn DriverManager.getConnection(url, user, password); PreparedStatement ps conn.prepareStatement(sql)) { ps.setString(1, 张三); ps.setString(2, 138xxxx); int rows ps.executeUpdate(); }注意这里必须用PreparedStatement而不是字符串拼接 SQL。它有预编译缓存更关键的是参数以占位符传递天然防 SQL 注入。JDBC 还有个容易踩的坑用完的连接要归还给连接池否则连接泄漏最终Too many connections。实际项目中自己手写 JDBC 的场景很少但连接池参数调优、事务边界等问题不懂 JDBC 底层的同学往往两眼一抹黑。5.2 基于 MyBatis-Plus 的通用 CRUD 服务无状态增删改查怎么设计MyBatis-Plus 最大的价值是把单表 CRUD 写成了模板代码。你的 Mapper 接口继承BaseMapperUser就自动获得insert、selectById、updateById、deleteById等方法Service 继承IServiceUser又多了save、updateById、removeById等方法。这其实就是许多人说的“通用 CRUD 服务”一个无状态的 Service 只依赖 Mapper不持有会话状态天然适合横向扩容。用代码举个例子public interface UserMapper extends BaseMapperUser {} Service public class UserServiceImpl extends ServiceImplUserMapper, User implements UserService { // 直接使用 IService 提供的 save、updateById、removeById }条件构造器也很顺手。按手机号和状态查用户LambdaQueryWrapperUser wrapper new LambdaQueryWrapper(); wrapper.eq(User::getPhone, 138xxxx) .eq(User::getStatus, 1) .orderByDesc(User::getCreateTime); ListUser list userService.list(wrapper);这套东西的确把开发效率拉满了。但我建议仍然要理解每个方法底层生成的 SQL。比如saveBatch底层也不是一条 SQL 插入所有数据而是分批INSERTremoveById如果是逻辑删除配置底层就是UPDATE。框架只是把 SQL 藏起来了不是把 SQL 消灭了。真遇到慢查询你还得定位到具体生成的 SQL 去调优。5.3 从 SQL 到框架的映射事务注解与批量操作的几个注意事项用 MyBatis-Plus 写 CRUD 时有几个细节极其容易翻车。第一个是Transactional失效。最常见的是同类内部方法调用比如 Service 里方法 A 调用方法 BB 标了Transactional但事务不生效——因为 Spring 事务基于代理内部调用绕过了代理。第二个是注解默认只在 RuntimeException 时回滚你抓异常后自己吞掉或抛 checked 异常事务就会悄悄提交。第三个是别把大查询放进事务事务里持有数据库连接时间长连接池容易被打满。批量操作方面MyBatis-Plus 的saveBatch有默认 batchSize你可以按实际情况调整。批量更新别一次性传几千条建议分批。我通常把批大小控制在几百条既保证速度又避免包大小超限。另外如果业务很复杂不要硬用 Wrapper 拼直接写 XML 自定义 SQL 更清晰。Wrapper 适合单表简单查询多表 JOIN 和复杂统计手写 SQL 的可读性、可调优性都强得多。6. 常见问题排查与避坑技巧实录6.1 增删改查高频报错速查表下面这些是我在实际项目里遇到并且处理过的问题整理成表格方便你直接对照排查。现象大概率原因快速处理思路插入中文变成??连接字符集或字段字符集不对检查character_set_client确保连接串指定 utf8mb4字段类型为 varchar utf8mb4Duplicate entry xx for key主键或唯一键冲突业务上用 ON DUPLICATE KEY UPDATE或先确认唯一约束是否合理Lock wait timeout exceeded行锁被其他事务持有SHOW PROCESSLIST查阻塞事务SELECT * FROM information_schema.innodb_trx看长事务必要时 kill 阻塞会话Too many connections连接池打满或连接泄漏检查应用连接池最大连接数查无释放连接的代码路径调大max_connections前先确认不是泄漏Unknown column报错SQL 字段名与表结构不一致SHOW CREATE TABLE核对字段注意大小写和隐藏字符Data too long for column xx字段长度不够调整字段长度或检查是否误把整段文本塞进了 varcharORDER BY排序结果和预期不一样字段 collation 或字符集问题显式指定COLLATE utf8mb4_bin或确认排序字段的数据类型查询突然变慢慢 SQL 在高峰时段出现EXPLAIN看执行计划重点检查是否全表扫描、filesort、临时表我觉得最值得留意的是锁等待超时。线上偶尔一条 UPDATE 卡住通常就是有另一个事务一直没提交。我处理过最夸张的一次是同事在一个事务里循环调了外部接口每个请求等 30 秒整个表的更新全部堵死。后续的规范里明确要求事务内禁止远程调用这类问题才彻底减少。6.2 安全与性能习惯把“防事故”做在写 SQL 之前增删改查的稳定性七分靠习惯三分靠运维。下面这几个习惯我是用事故换来的。第一DELETE和UPDATE之前先写 SELECT。这是最便宜、最有效的防呆操作。你甚至可以让代码评审阶段强制要求凡是 DML 语句必须附带等价的 SELECT 条件。第二开启sql_safe_updates。至少在测试和预发环境开启生产环境如果条件允许也建议开。它不允许不带 WHERE 的 UPDATE/DELETE能挡住手滑和程序 bug 导致的全表更新。第三批量操作尽量分批加LIMIT。比如历史数据清理DELETE FROM log WHERE create_time 2024-01-01 LIMIT 1000循环执行避免一次性删几十万行导致主从延迟和锁范围过大。第四所有 DML 上线前看执行计划。别嫌麻烦EXPLAIN一秒钟就能看到typeALL、rows1000000这时候就该停下来想索引。很多慢查询事故如果上线前看过 EXPLAIN根本不会发生。第五大表结构变更和大量数据操作必须走审批和备份。mysqldump做备份虽然朴素但在大多数场景下比花里胡哨的方案靠谱。数据无价备份做在前面出事不慌。我在实际操练中还有一个小习惯关键业务表的所有 DML 操作都尽量在代码里打日志至少记录操作人、操作条件和影响行数。真出了问题可以从日志倒推是哪个环节改坏了数据而不是翻 binlog 大海捞针。这个习惯不复杂但能让你在排查问题时省下几个通宵。最后再分享一个实战小技巧如果你发现某张表的增删改查越来越卡不要只盯着 SQL 本身先看表数据量、索引情况、有没有长时间未提交事务。八成问题出在索引失效或者事务堆积而不是你写的那条语句。先定位瓶颈在哪一层再去优化对应层比盲目加索引、乱调参数有效得多。