搞后端写了这么多年要说哪个操作最绕不开MySQL里的增删查改绝对排第一。专业术语叫CRUDCreate、Read、Update、Delete四个英文单词看着简单但里面的门道比想象中多得多。不少人觉得CRUD不就是几条SQL吗背下来就完事了可一上了真实业务一条简单的INSERT可能因为字符集或自增值翻车一条SELECT可能因为漏了判断条件把整个表扫一遍删除和修改更是出过不少线上事故。这篇指南先讲“上”半部分重点是增和查也就是INSERT和SELECT顺便把改和删的基础操作一并带出来。适合刚接触数据库的新手也适合写过一段时间SQL但对细节不够敏感的同学。我会把日常开发中最常用、最容易被忽略的细节拆开来看每一步都给出可以直接上手的写法以及我实际踩过的坑。1. 先把CRUD的逻辑搞明白再动手1.1 为什么CRUD会被称为“艺术”很多初学者把增删查改当成四个孤立操作背完语法就觉得自己会了。但实际上CRUD是一套完整的数据操作闭环它们之间的相互关系才是核心。插入数据是为了让系统有内容查询数据是为了让内容产生价值更新数据是修正和优化删除数据是清理和回收这四个动作互相配合构成了业务系统的一切数据流转。要真正理解CRUD的“艺术性”建议先建立两个认知第一每条SQL的本质都是对数据做一次“状态转移”数据从一种形态变成另一种形态这期间要保证状态的一致性第二CRUD的操作成本是递增的查询最便宜插入次之更新更谨慎删除要格外小心。理解了成本和风险写出的SQL自动就会变得更克制、更严谨。1.2 数据库表和数据的准备为了后面的示例方便看这里准备一张学员信息表后续所有CRUD操作都围绕这张表展开。CREATE TABLE student ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 自增主键, student_no VARCHAR(20) NOT NULL COMMENT 学号, name VARCHAR(50) NOT NULL COMMENT 姓名, gender TINYINT NOT NULL DEFAULT 0 COMMENT 性别 0未知 1男 2女, age INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 年龄, class_name VARCHAR(50) NOT NULL DEFAULT COMMENT 班级, enroll_date DATE DEFAULT NULL COMMENT 入学日期, 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_student_no (student_no) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci COMMENT学员信息表;这张表的设计有几个细节可以留意一下id用了自增主键这是最常见的单表主键策略学号加了唯一索引因为业务上学号不能重复字符集用了utf8mb4这是MySQL里目前最推荐使用的字符集能存下所有Unicode字符包括emoji。如果项目还用utf8后续遇到特殊字符存储迟早要后悔。2. INSERT增数据也有方法优劣2.1 最基础的INSERT写法INSERT是往表里加数据最直接的方式基本语法就两行-- 指定列名推荐写法 INSERT INTO student (student_no, name, gender, age, class_name, enroll_date) VALUES (20240001, 张三, 1, 20, 计算机1班, 2024-09-01); -- 不指定列名简写方式 INSERT INTO student VALUES (NULL, 20240002, 李四, 2, 21, 计算机2班, 2024-09-01, NOW(), NOW());两种方式里强烈推荐第一种指定列名。原因很直接数据库表结构经常会调整今天加一列明天调一下顺序如果SQL里不指定列名值的位置和表结构必须严格对应一旦表结构变了这种SQL就静默出错最怕是数据插到了错误的列里排查起来极其痛苦。不指定列名时必须给表里所有字段按顺序赋值包括自增主键通常填NULL让数据库自己生成。这种方式看着省事实际维护成本很高业务代码里不建议用。还有一种INSERT ... SET写法适合单条插入的场景INSERT INTO student SET student_no 20240003, name 王五, gender 1, age 22;这种写法的好处是可读性非常强一眼就能看出哪个字段对应哪个值代码审查的时候能节省不少时间。2.2 批量插入的三种方式真实业务里经常要批量写入数据比如导入Excel、同步接口数据、初始化测试数据。批量插入有三种常见姿势第一种循环逐条执行INSERT语句。代码里最直观但性能是最差的每条INSERT都是一次单独的SQL执行网络往返和事务开销都被放大。一百条数据还好一万条数据就会出现明显的性能瓶颈。第二种一条INSERT带多个VALUES这是最推荐的普通业务做法。INSERT INTO student (student_no, name, gender, age, class_name) VALUES (20240004, 赵六, 1, 20, 计算机1班), (20240005, 钱七, 2, 21, 计算机2班), (20240006, 孙八, 1, 19, 计算机3班);一条SQL里塞多个值数据库只需要解析和执行一次速度提升非常明显。注意不要超过MySQL的max_allowed_packet限制一般建议每个批次500到1000条比较稳妥。第三种使用LOAD DATA INFILE从本地文件导入适合那种几十万上百万级别的大批量导入速度远快于任何INSERT方式。从我的经验看90%的批量写入场景用第二种方式就够了不要一上来就搞LOAD DATA那是运维级手段普通业务没必要整得那么重。2.3 插入时容易踩的暗坑插入操作看起来无脑实际有几个坑很容易踩。自增主键“跳号”问题。InnoDB的自增锁有自己的分配策略事务回滚或者插入失败自增ID不会回滚所以ID中间可能会出现空洞。这不是错误不需要处理但如果有人拿着“ID中间有断号”要求你修复你得能解释清楚原因。另一个坑是字符集问题。如果表是utf8mb4但连接字符集是latin1插入中文数据大概率会乱码。连接建立后最好执行一次SET NAMES utf8mb4或者在连接串里配置好characterEncoding这一步能在源头避免很多乱码问题。还有一个是NOT NULL字段不传值导致的报错。很多新手在INSERT里只写了部分字段如果有的字段既没有默认值又没在INSERT里体现比如表里的enroll_date如果是NOT NULL且无默认值这条插入就会失败。设计表结构时建议给非核心字段都配上合理的默认值这样业务侧插入时就不必每次凑齐全部字段。2.4 冲突时可以这样处理插入数据时最常见的冲突场景是唯一键冲突比如学号已经存在了再插入同号学员就会报错。处理这种冲突有三种做法-- 忽略冲突静默跳过 INSERT IGNORE INTO student (student_no, name, gender, age) VALUES (20240001, 张三丰, 1, 30); -- 冲突时更新指定字段 INSERT INTO student (student_no, name, gender, age) VALUES (20240001, 张三丰, 1, 30) ON DUPLICATE KEY UPDATE name VALUES(name), age VALUES(age); -- 先删除后插入谨慎使用 REPLACE INTO student (student_no, name, gender, age) VALUES (20240001, 张三丰, 1, 30);三者的区别值得说清楚。INSERT IGNORE适合那种“有就不管了”的幂等写入场景比如日志去重。ON DUPLICATE KEY UPDATE适合那种“有就更新、没有就新增”的同步场景比如用户资料同步。而REPLACE虽然也能处理冲突但它的机制是先删除旧行再插入新行这意味着主键ID会变同时如果有外键引用还可能导致关联删除实际业务中我很少用它除非能接受ID变化。3. SELECT查数据真的不只是SELECT *3.1 SELECT基础写法查询是CRUD里使用频率最高的操作但很多人写的SELECT带着一股粗糙感。最基本的结构是SELECT student_no, name, age FROM student WHERE age 20;最需要纠正的一个习惯是线上业务查询禁止无脑SELECT *。别觉得这是小事一张表字段少则十几个多则几十个SELECT * 会把所有字段的数据都拉出来网络传输多了好几倍还会额外触发相关行的解析工作。更重要的是如果表结构后面加了字段SELECT *的结果集结构也会变可能会把业务代码里按索引取字段的逻辑弄乱。正确的做法是只查需要的列查出来的列少IO和网络开销都小逻辑也更清晰。如果一个表字段确实很多但你只需要其中几个就明确写出来不要偷懒。3.2 WHERE条件过滤的细节WHERE是查询的灵魂数据过滤的核心都在这。常用的比较和逻辑运算符有等值比较、不等、范围BETWEEN、集合IN、模糊匹配LIKE、空值判断IS NULL以及逻辑运算符AND、OR。一个容易出错的点是运算符优先级。AND的优先级高于OR所以下面这条查询SELECT * FROM student WHERE class_name 计算机1班 OR class_name 计算机2班 AND age 20;实际执行的是“计算机2班且年龄大于20或者计算机1班全部”如果想表达“两个班里年龄大于20的”必须加括号SELECT * FROM student WHERE (class_name 计算机1班 OR class_name 计算机2班) AND age 20;这种优先级问题在复杂条件里经常遇到排查SQL问题时可以先往括号上怀疑。LIKE模糊查询也是一个细节高地。LIKE %张三% 会用非前缀通配符这种写法会导致索引失效数据量大时性能非常差。而LIKE 张三% 是前缀匹配能走索引查询速度会快很多。业务里能用前缀匹配就尽量用前缀匹配实在不行再考虑全文索引别让一个模糊查询拖垮整个库。3.3 排序和分页容易忽略的部分ORDER BY排序是最常用的扩展功能细节不少。多列排序时每一列可以独立指定升序或降序逻辑是从左到右逐列生效。比如ORDER BY class_name ASC, age DESC先按班级排班级相同的再按年龄从大到小排。别写成ORDER BY class_name ASC, age因为默认是升序很容易让人觉得它跟前面一样是升序。分页的常见写法是LIMIT offset, count或者LIMIT count OFFSET offset。第一个数字是起始偏移量第二个是返回的最大行数。-- 第1页每页20条 SELECT id, student_no, name FROM student ORDER BY id LIMIT 0, 20; -- 第2页每页20条 SELECT id, student_no, name FROM student ORDER BY id LIMIT 20, 20;分页隐藏着一个性能坑偏移量越大查询越慢。LIMIT 100000, 20 时MySQL要先把前面十万行都扫过才能返回第20行后端的排序和扫描成本很高。优化的思路是改用“基于游标”的分页方式比如记住上一次查询返回的最大ID然后用WHERE id last_id LIMIT 20这样能极大提升翻页性能。前端那种点击页号跳转的模式在大数据量下是不太行的应该引导用户使用“加载更多”或“上一篇、下一篇”模式。3.4 聚合函数与分组查询统计需求在业务里非常常见聚合函数是这个问题最直接的解药。-- 统计人数 SELECT COUNT(*) FROM student; -- 统计指定字段非NULL数量 SELECT COUNT(age) FROM student; -- 各班平均年龄 SELECT class_name, AVG(age) AS avg_age FROM student GROUP BY class_name;COUNT()和COUNT(具体字段)有区别COUNT()统计的是行数不会忽略任何行COUNT(age)只统计age不为NULL的行。这个区别在业务里很容易踩统计口径不一样上报数据就错了。GROUP BY配合HAVING能做分组后的二次过滤SELECT class_name, AVG(age) AS avg_age FROM student GROUP BY class_name HAVING avg_age 20;很多人会把WHERE和HAVING混着用。WHERE是分组前过滤HAVING是分组后过滤。写SQL时的顺序是WHERE先过滤原始行GROUP BY再分组最后HAVING过滤分组结果。这个执行顺序别弄反否则逻辑会乱。3.5 JOIN多表查询真实的业务数据很少只存在一张表里用户表、订单表、商品表分散设计查询时就要把表关联起来。最常见的JOIN是内连接只返回两张表中匹配上的行SELECT s.name, sc.score FROM student s INNER JOIN student_score sc ON s.id sc.student_id;还有左连接返回左表所有行右表没有匹配的行就补NULLSELECT s.name, sc.score FROM student s LEFT JOIN student_score sc ON s.id sc.student_id;JOIN的时候有一个很实用的建议小表驱动大表用数据量小的表作为驱动表去匹配数据量大的表整体性能更好。另外JOIN条件上的字段两边都要建索引否则连接查询会退化成嵌套循环扫描性能会差到难以接受。4. UPDATE和DELETE改与删都要带敬畏心4.1 UPDATE语法与更新前必做的两件事UPDATE和DELETE操作直接影响数据本身风险远高于查询和插入。基础语法是UPDATE student SET age 21 WHERE student_no 20240001;执行UPDATE前我建议养成两个习惯。第一先SELECT一遍确认要更新哪些行用同样的WHERE条件先查出来看一遍确认影响范围再执行更新。很多事故都用一条误更新的UPDATE收尾就是因为没确认更新范围。第二更新前把当前的WHERE条件里涉及的数据先备份或者记录一下万一更新逻辑有问题还能恢复。UPDATE还有一个容易忽视的问题默认情况下字段值等于原值时MySQL返回的影响行数会是0。有些框架会根据影响行数判断是否成功这时可能误判为操作失败需要在业务代码里理解这个现象不要把它当成异常。4.2 DELETE与TRUNCATE不是一回事DELETE语法DELETE FROM student WHERE student_no 20240001;不带WHERE条件的DELETE会把整张表清空这是数据安全事故的高发操作。DELETE删除的是行数据自增主键不会重置下次插入新数据ID会接着删除前的值往后走。TRUNCATE和DELETE之间完全不是同一级别。TRUNCATE是直接清空表数据并重置自增计数性能比DELETE快很多但它有更严格的限制不能加WHERE是DDL操作所以在绝大多数情况下不能被回滚。生产环境里如果只想清空部分数据一定要用DELETE配合WHERE想让整表数据归零并重置自增TRUNCATE更合适。4.3 让改删操作多一道保险真正的线上经验改删操作绝对不能裸奔。有这几层保险可以上第一层事务包裹。把SELECT和UPDATE或者DELETE放进同一个事务里先查询确认再执行修改最后提交。发现问题时ROLLBACK可以直接撤回到事务开始前的状态这是最简单高效的保险。START TRANSACTION; SELECT * FROM student WHERE age 100; -- 确认数据无误后执行 DELETE FROM student WHERE age 100; -- 操作正确则提交 COMMIT; -- 操作有问题则回滚 ROLLBACK;第二层影响行数检查。像MySQL命令行或客户端工具执行UPDATE/DELETE后会返回影响行数比如Rows matched和Changed。如果影响行数远超预期说明WHERE条件没有发挥应有的过滤作用这时候先不要提交事务赶紧排查。第三层生产环境限制账号权限。让应用账号只拥有INSERT和SELECT权限UPDATE和DELETE用另一个专用账号并且只授权给指定的表或库。很多公司开发阶段不重视权限结果测试环境一条DELETE连生产库也被误删。虽然在数据库这种系统层面我没有深入地讲过权限细节但这个思路是对的方向。5. 实战复盘那些年我在CRUD上踩过的坑5.1 常见问题速查表问题现象常见原因处理建议插入后中文乱码连接字符集不是utf8mb4连接参数配置characterEncodingutf8mb4或SET NAMES utf8mb4插入报Duplicate entry错误唯一键冲突用INSERT IGNORE或ON DUPLICATE KEY UPDATE处理自增ID中间有空洞InnoDB自增机制属于正常现象不用处理模糊查询非常慢LIKE %xxx%导致索引失效改用前缀匹配或使用全文索引分页越翻越慢LIMIT大偏移量性能差改用WHERE id last_id 游标分页UPDATE影响行数为0新值和原值一样业务代码别依赖影响行数判断成功与否DELETE不带WHERE误操作清空全表养成先SELECT预览再执行的习惯5.2 几个很实用的排查思路有一次我遇到一个诡异的情况一条INSERT语句偶尔报错不是每次都错。排查下来发现是某个字段的长度超出了VARCHAR定义的最大长度数据在一部分场景下会变得特别长直接被MySQL拒了。这类问题通常不是SQL写法的问题而是数据质量不达标的问题。处理办法是明确字段的业务长度上限在应用层做校验拦截。还有个印象很深的案例某条SELECT查询本地执行只要几十毫秒线上却要好几秒。EXPLAIN一看发现WHERE条件里的字段没加索引全表扫描了几百万行。解决办法很简单给字段加上索引查询时间瞬间降下来。所以遇到慢查询第一步永远是EXPLAIN看执行计划不要瞎猜。5.3 给新手的一条真诚建议如果你是刚接触MySQL的开发者我建议多抄多练任何CRUD操作都先写在本地环境里用真实结构的数据跑一遍。特别是UPDATE和DELETE先在事务里执行确认影响范围正确再提交。这个习惯前期看着慢但能避免无数次线上事故。另外要学着看执行计划EXPLAIN它就像一张战术地图告诉你SQL执行过程是快还是慢哪里走了全表扫描哪里用了索引。学会读EXPLAIN你的SQL能力就会有一个明显的跃升。我个人在这几年使用MySQL的过程中越来越确定一件事CRUD从来不只是一堆语法拼凑它是对数据所有权的理解对修改风险的敬畏。把“增删查改”这四个基本功用得足够扎实后续再去接触索引优化、事务隔离级别、锁机制这些进阶内容的时候会顺畅非常多。这篇只讲到了CRUD的上半部分基础语法和最常见的场景覆盖了大半剩下的高阶技巧——比如子查询的优化、JOIN的深层原理、复杂事务下的并发控制后面有机会再填坑。先把增和查练熟把改和删用稳就算入门了。
