刚入行那会儿我以为MySQL的“增删改查”就是四条SQL语句的事不就是INSERT、SELECT、UPDATE、DELETE嘛背下来就能应付面试。直到真正参与生产项目被线上慢查询、死锁、数据不一致轮番教育之后我才意识到增删改查这四个字字面简单背后牵扯的却是索引原理、事务隔离、锁机制、SQL优化一整条知识链。今天这篇博客我就把自己这些年折腾MySQL增删改查的经验捋一遍从语法细节到实战落地再到排查技巧一次性讲透。这篇文章适合刚学会MySQL基础语法、准备进入后端开发的新人也适合已经写过一段时间CRUD、但总感觉“差点意思”的初级开发。我会用大量实际场景和踩坑记录来讲不整虚的你看完能直接用。1. MySQL增删改查的本质你写的不只是SQL是数据守恒1.1 为什么增删改查是后端开发的核心基本功很多人在学习阶段会问一个问题后端开发除了增删改查还有什么我的回答是增删改查本身就不简单你之所以觉得简单是因为你在玩具项目里只用到了它的皮毛。在一个真实的业务系统里增删改查承载的是数据的全生命周期管理。“增”涉及唯一约束、默认值校验、批量插入的性能取舍“删”要考虑物理删除还是逻辑删除、级联策略“改”要面对并发更新、乐观锁冲突、更新子查询的正确写法“查”更是一整个性能优化的大类索引、执行计划、回表、覆盖索引全在这里。所以增删改查不是四个动词而是四种数据操作范式。你把它们吃透了后面学存储过程、触发器、读写分离、分库分表都是在这些基础上的延展。反之如果只停留在“能跑”的层面那到了真实项目里一条UPDATE就能把整张表锁死一条SELECT就能把数据库CPU拉满。1.2 工欲善其事一套舒服的MySQL环境怎么搭讲增删改查之前先聊聊环境。因为很多新手卡在增删改查之前不是语法不会而是MySQL装不上、连不上、密码不知道。我个人最推荐的方式是Docker部署MySQL干净、可重复、不污染宿主机。一个简单的docker-compose配置就能拉起一个8.0版本的实例version: 3.8 services: mysql: image: mysql:8.0 container_name: mysql-dev restart: always environment: MYSQL_ROOT_PASSWORD: root123456 MYSQL_DATABASE: school ports: - 3306:3306 volumes: - ./mysql-data:/var/lib/mysql - ./my.cnf:/etc/mysql/conf.d/my.cnf command: - --character-set-serverutf8mb4 - --collation-serverutf8mb4_general_ci启动之后连接工具有两种选择。一种是官方免费的MySQL Workbench功能全适合学习另一种是Navicat界面友好但收费。新手阶段用Workbench就够了它能看执行计划、能跑SQL脚本对理解基础操作帮助很大。提示MySQL 8.0默认的认证插件是caching_sha2_password如果你用老版本的客户端工具连接会报认证失败。这时候要么升级客户端要么在创建用户时指定mysql_native_password。还有一个高频问题MySQL的初始密码是什么。如果你是用官方安装包在本地装的安装过程中会让你设置root密码如果你用Docker密码就是在环境变量里指定的那个。如果你忘了密码可以在配置文件里加skip-grant-tables跳过认证但这是应急手段别在生产环境用。2. 四大操作核心语法拆解从会用到用对2.1 INSERT不只是插入数据一致性从入库就开始INSERT是最基础的写操作但它有几个细节值得单独拎出来讲。第一显式列出字段名。我见过很多新手喜欢写这种精简写法-- 不推荐字段顺序一旦变化数据就错位了 INSERT INTO student VALUES (1, 张三, 男, 20, 2024-01-01);这种写法在表结构稳定的时候没问题但一旦表加了字段、删了字段这条SQL就可能报错或者插错数据。我建议养成习惯永远显式列出字段名-- 推荐字段明确可读性和稳定性都好 INSERT INTO student (id, name, gender, age, create_time) VALUES (1, 张三, 男, 20, 2024-01-01);第二批量插入的效率问题。假设你要在初始化数据时插入一万条学生记录一条一条INSERT会反复提交事务性能极差。正确的做法是多值批量插入INSERT INTO student (name, gender, age, class_id) VALUES (李四, 男, 21, 101), (王五, 女, 20, 102), (赵六, 男, 22, 103);同样是一万条记录批量插入比循环单条插入快一个数量级。原因很简单每一条INSERT都有SQL解析、网络传输、事务提交的开销批量插入把这些开销摊薄到每一条记录上了。第三INSERT与唯一约束的冲突处理。业务里最常见的场景是先查一下这条记录存不存在不存在就插入存在就更新。这种“查了再插”的方式在并发场景下会出大问题两个请求同时查到不存在然后同时插入其中一个就会因为唯一索引报错。正确做法是使用ON DUPLICATE KEY UPDATEINSERT INTO student (id, name, age) VALUES (1001, 钱七, 19) ON DUPLICATE KEY UPDATE name VALUES(name), age VALUES(age);或者用INSERT IGNORE忽略冲突记录INSERT IGNORE INTO student (id, name, age) VALUES (1001, 钱七, 19);这两种方式都能把“查插”合并成一次原子操作避免并发下的重复插入问题。2.2 SELECT查询性能的分水岭索引从这里开始SELECT是增删改查里最常写、也最能拉开水平差距的操作。很多人写SELECT只关心结果对不对不关心性能好不好结果就是同一个功能别人的接口200毫秒你的接口3秒钟。先说最简单的全表查询。SELECT * FROM student;这条SQL在实际生产里几乎不会这么写。*号会把所有字段都查出来包括你根本不需要的大字段白白增加网络传输和内存消耗。我建议只查你需要的字段SELECT id, name, age FROM student;再说WHERE条件。SELECT * FROM student WHERE class_id 101 AND age 20;很多人不知道WHERE条件的写法会直接影响索引是否生效。比如你在索引列上做了函数运算-- 索引失效对索引列用了YEAR函数 SELECT * FROM student WHERE YEAR(create_time) 2024; -- 索引生效直接对比日期范围 SELECT * FROM student WHERE create_time 2024-01-01 AND create_time 2025-01-01;这是一个非常典型的索引失效场景。如果你在SQL里对索引列做了运算、函数、隐式类型转换MySQL的优化器就没办法使用索引了只能全表扫描。最后说排序。ORDER BY是容易让人忽略性能的操作。如果排序字段没有索引MySQL会在临时文件里做排序数据量一大简直是一场灾难。-- 如果age字段没有索引这条SQL在大表上会非常慢 SELECT id, name, age FROM student ORDER BY age DESC LIMIT 20;解决办法是给排序字段加上索引。MySQL的InnoDB索引本身就是按B树有序存储的走索引排序就不需要额外的filesort了。2.3 UPDATE更新子查询与事务边界最容易踩坑的地方UPDATE是增删改查里风险最高的操作因为一旦WHERE条件写错后果是灾难性的。我见过最严重的事故是这样的同事要把某个学生的成绩改掉本来应该写UPDATE score SET value 95 WHERE student_id 1001 AND course_id 5;结果漏了course_id条件变成了UPDATE score SET value 95 WHERE student_id 1001;这个学生所有课程的成绩全部被改成了95分。所以UPDATE有一个铁律WHERE条件必须能在执行前明确圈定影响范围。如果条件不够确定先SELECT确认一下再执行这不是胆小是职业素养。更新子查询是另一个高频考点。MySQL的UPDATE语句里子查询不能直接指向被更新的表这算是MySQL的一个限制。比如你想把学生表里每个学生的年龄更新为同班同学的平均年龄-- 报错You cant specify target table student for update in FROM clause UPDATE student SET age (SELECT AVG(age) FROM student WHERE class_id student.class_id);MySQL不允许在UPDATE的FROM子句中直接依赖目标表。解决办法是包一层虚拟表把子查询结果先查出来UPDATE student s JOIN ( SELECT class_id, AVG(age) AS avg_age FROM student GROUP BY class_id ) t ON s.class_id t.class_id SET s.age t.avg_age;这是MySQL中更新子查询的标准解法用JOIN代替直接的FROM子查询。UPDATE还有一个容易忽略的点没有WHERE条件的UPDATE会更新全表。这个看着像废话但很多人真的会犯。生产环境更新全表轻则锁表阻塞业务重则数据全部被覆盖直接导致事故。2.4 DELETE删除数据真的“删”了吗DELETE是增删改查里最需要谨慎的操作。我先说一个很多新手不知道的事实DELETE只是把数据标记为删除并不会立刻释放磁盘空间。InnoDB引擎的DELETE实际上是给记录打一个删除标记真正的物理清理要等后台的purge线程来做。这也是为什么你删了大量数据之后表文件大小并没有明显变化。DELETE的几种常见场景和应对方案场景一删除全部数据。DELETE FROM student;这条SQL会逐条标记删除记录binlog速度慢而且如果表很大事务日志会非常庞大。如果你确认要清空整张表最好用TRUNCATETRUNCATE TABLE student;TRUNCATE是直接重建表速度快得多而且会重置自增ID。代价是它不会逐条触发删除相关的业务逻辑所以只在确定场景下使用。场景二条件删除。DELETE FROM student WHERE id 1001;条件删除的首要原则和UPDATE一样WHERE条件必须精准。另外如果你要删除的数据量很大比如超过一万条建议分批删除防止一次删除导致锁持有时间过长拖垮其他业务。DELETE FROM student WHERE id IN ( SELECT id FROM student WHERE create_time 2023-01-01 LIMIT 1000 );场景三逻辑删除。现在的企业项目里比较规范的做法是用逻辑删除替代物理删除。也就是给表加一个deleted字段ALTER TABLE student ADD COLUMN deleted TINYINT NOT NULL DEFAULT 0;删除操作变成UPDATEUPDATE student SET deleted 1 WHERE id 1001;查询时统一加条件SELECT id, name FROM student WHERE deleted 0;逻辑删除的好处是数据可回溯、不会因为误删造成不可逆损失缺点是你写的每条SQL都要记得带deleted 0条件忘了就是事故。3. 完整实战一个学生管理系统里的增删改查落地3.1 建表设计字段类型、默认值与索引的一次到位光讲语法太干了我带你看一个实际项目。假设我们要用Java Web做一个学生管理系统核心是维护学生和课程成绩这两类数据。学生表的建表语句CREATE TABLE student ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键ID, 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 NOT NULL DEFAULT 0 COMMENT 年龄, class_id BIGINT NOT NULL COMMENT 班级ID, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, deleted TINYINT NOT NULL DEFAULT 0 COMMENT 逻辑删除标记0-未删除1-已删除, PRIMARY KEY (id), UNIQUE KEY uk_student_no (student_no), KEY idx_class_id (class_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci COMMENT学生表;课程成绩表CREATE TABLE score ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键ID, student_id BIGINT NOT NULL COMMENT 学生ID, course_id BIGINT NOT NULL COMMENT 课程ID, score DECIMAL(5,2) NOT NULL DEFAULT 0 COMMENT 成绩分数, exam_time DATETIME NOT NULL COMMENT 考试时间, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, PRIMARY KEY (id), UNIQUE KEY uk_student_course (student_id, course_id), KEY idx_course_id (course_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci COMMENT课程成绩表;这里有几个值得留意的点。id字段我用BIGINT UNSIGNED而不是INT因为INT最大才21亿生产环境很容易撞顶。gender、age、deleted这类字段直接用TINYINT设置默认值避免插入时因为缺字段而报错。score用DECIMAL(5,2)因为浮点数FLOAT在金额、成绩这类需要精确比较的场景下会出精度问题。关于“MySQL设置默认值为0”很多新手会写成ALTER TABLE student ALTER COLUMN deleted SET DEFAULT 0;这个语法是合法的但更推荐在建表时就定义好默认值。如果表已经建好了用MODIFY COLUMN更直接ALTER TABLE student MODIFY COLUMN deleted TINYINT NOT NULL DEFAULT 0;3.2 增删改查的SQL编写与执行计划验证表结构设计完毕我们来写具体的学生信息增删改查。新增学生INSERT INTO student (student_no, name, gender, age, class_id) VALUES (20240001, 李雷, 1, 18, 101);根据ID查询学生SELECT id, student_no, name, gender, age, class_id FROM student WHERE id 1 AND deleted 0;修改学生信息UPDATE student SET name 韩梅梅, age 19 WHERE id 1 AND deleted 0;删除学生这里我们采用逻辑删除UPDATE student SET deleted 1 WHERE id 1;条件查询学生列表SELECT id, student_no, name, gender, age, class_id FROM student WHERE deleted 0 AND class_id 101 ORDER BY age DESC LIMIT 10;写完SQL之后我强烈建议你用EXPLAIN看一眼执行计划。这条查询是否走索引一眼就能看出来EXPLAIN SELECT id, student_no, name FROM student WHERE class_id 101 AND deleted 0;执行计划里重点看两个字段type和key。type是all说明全表扫描是ref或range说明走了索引key显示的是实际使用的索引名。如果class_id有索引但你发现执行计划里的key是NULL那就要检查是不是SQL写法有问题了。注意deleted 0这个条件加进去之后如果你单独给class_id建了索引MySQL可能会因为deleted字段不在索引里导致部分场景下选择全表扫描。实际项目中这就是“逻辑删除字段对索引选择的影响”需要根据数据分布具体分析必要时使用组合索引(class_id, deleted)。3.3 从SQL到业务代码Java Web项目中的增删改查封装SQL写对了还要落到业务代码里。很多Java后端项目的增删改查都是三层结构Controller接收请求Service处理业务逻辑Mapper操作数据库。以一个经典的添加学生接口为例简化后的代码逻辑是这样的PostMapping(/student) public Result addStudent(RequestBody StudentDTO dto) { // 1. 参数校验 if (StringUtils.isBlank(dto.getStudentNo()) || StringUtils.isBlank(dto.getName())) { return Result.error(学号和姓名不能为空); } // 2. 检查学号是否已存在 Student existing studentMapper.selectByStudentNo(dto.getStudentNo()); if (existing ! null) { return Result.error(学号已存在); } // 3. 插入新学生 Student student new Student(); student.setStudentNo(dto.getStudentNo()); student.setName(dto.getName()); student.setGender(dto.getGender()); student.setAge(dto.getAge()); student.setClassId(dto.getClassId()); studentMapper.insert(student); return Result.success(student.getId()); }这段代码里有一个典型的并发问题第2步检查学号是否存在和第3步插入之间是有时间窗口的。两个请求同时通过检查然后同时插入学号唯一约束就会被触发抛异常。真正的解决方案是直接在Mapper层捕获唯一键冲突或者用INSERT配合ON DUPLICATE KEY UPDATEInsert(INSERT INTO student (student_no, name, gender, age, class_id) VALUES (#{studentNo}, #{name}, #{gender}, #{age}, #{classId}) ON DUPLICATE KEY UPDATE name VALUES(name), age VALUES(age)) int insertOrUpdate(Student student);这就是从“SQL能跑”到“SQL在并发场景下依然正确”的区别。增删改查看着简单真正考验功力的是这些边界情况。4. 增删改查背后的硬功夫事务、锁与MVCC4.1 事务边界为什么一条UPDATE可能锁住整张表增删改查的四个操作里最需要警惕的是并发场景下的锁问题。很多人写UPDATE只关心有没有WHERE条件却忽略了事务隔离级别和锁范围。MySQL InnoDB的锁机制简单说分三种行锁、间隙锁、表锁。正常情况下的UPDATE是行锁锁住的只是你修改的那一行。但有几个情况会导致锁升级或者锁范围扩大。情况一没有索引的WHERE条件。UPDATE student SET age 20 WHERE name 李雷;如果name字段没有索引InnoDB为了找到符合条件的记录只能全表扫描。而在扫描过程中它会对扫描到的每一行都加上锁看起来就像是锁住了整张表。情况二范围条件触发的间隙锁。UPDATE student SET age age 1 WHERE age BETWEEN 18 AND 22;在可重复读隔离级别下InnoDB不仅会给符合条件的现有记录加锁还会给范围之内的“空隙”加间隙锁防止其他事务在这个范围插入新数据。这是为了确保事务隔离性但代价就是并发度下降。情况三事务没有及时提交。如果你在事务里执行了一条UPDATE然后不提交也不回滚锁就会一直持有。其他事务修改同一行只能等待。线上很多“卡死”的SQL不是SQL本身的问题而是前面的事务迟迟不提交。所以我给新人的建议是事务要短、要快用完立刻提交。尤其是这种事务代码Transactional public void updateStudent(Student student) { studentMapper.updateById(student); // 这里不要放耗时的远程调用、文件操作 }Transactional注解别滥用事务里别放无关的耗时操作否则锁的持有时间会被无限放大。4.2 MVCC与隔离级别查询性能的“隐形引擎”MVCCMulti-Version Concurrency Control多版本并发控制是MySQL实现高并发读的核心机制。它让普通的SELECT不用加锁就能读取到一个一致性的快照从而让读操作和写操作互不阻塞。我打个比方。MVCC就像图书馆里的一本书每个人借阅的时候都拿到一个复印版本大家同时看书互不影响。只有当有人要修改书里的内容时才会临时拿笔改原稿而其他人看到的仍然是自己手里的复印件。MVCC和增删改查的关系体现在一个典型场景一个事务里先SELECT一条数据然后在另一个事务里更新这条数据第一个事务再SELECT一次读到的还是旧值。-- 事务A BEGIN; SELECT age FROM student WHERE id 1; -- 返回 18 -- 事务B BEGIN; UPDATE student SET age 20 WHERE id 1; COMMIT; -- 事务A再次查询 SELECT age FROM student WHERE id 1; -- 返回 18不是20这个现象在可重复读隔离级别下是正常的它靠的就是MVCC的版本快照机制。正因为有这个机制我们才能在业务里放心地执行大量SELECT查询而不用担心它们被正在执行的UPDATE阻塞。理解了MVCC再看四个隔离级别就豁然开朗了。读未提交READ UNCOMMITTED会被其他事务的未提交修改影响读已提交READ COMMITTED每次读都是最新快照可重复读REPEATABLE READ事务内快照一致是MySQL的默认配置串行化SERIALIZABLE完全加锁性能最差。绝大多数业务系统用MySQL默认的可重复读就够了。5. 常见问题与排查技巧实录5.1 新手最容易犯的几个增删改查错误我把这些年看到的、自己踩过的新手错误整理成一张表你可以对照自查。错误场景错误写法正确做法插入时省略字段名INSERT INTO student VALUES (1, 张三)显式列出字段名增强可读性和稳定性更新时忘记WHEREUPDATE student SET age 20先SELECT确认范围再执行UPDATE删除用DELETE FROM全表DELETE FROM student确认意图必要时用TRUNCATE或逻辑删除等值查询索引失效WHERE YEAR(create_time) 2024改为create_time的范围条件SELECT * 查询多余字段SELECT * FROM student只查询需要的字段隐式类型转换导致索引失效WHERE phone 13800138000phone是varchar写成WHERE phone 13800138000这里特别要说一下隐式类型转换。手机号字段是VARCHAR类型你写WHERE phone 13800138000MySQL会把字段转成数字再比较索引就失效了。反过来如果你字段是数值类型你传了字符串MySQL也会做隐式转换同样可能导致索引失效。规则很简单字段是什么类型条件就传什么类型。5.2 线上问题排查思路慢查询、锁等待与索引失效遇到增删改查性能问题我的排查顺序是这样的。第一步打开慢查询日志。先看哪些SQL是慢的。SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;设置之后执行时间超过1秒的SQL会被记录到慢查询日志里。这是性能排查的第一步你得先知道是哪些SQL在拖后腿。第二步用EXPLAIN分析执行计划。EXPLAIN SELECT * FROM score WHERE student_id 1001;重点看typesystem、const、eq_ref、ref、range属于比较好的访问类型index和all就危险了说明走了全索引扫描或全表扫描。第三步检查是不是锁等待。如果一条UPDATE一直执行不完先看是不是在等锁SELECT * FROM performance_schema.data_lock_waits;或者用SHOW ENGINE INNODB STATUS看事务的锁信息。锁等待最常见的场景是一个事务修改了某行但不提交另一个事务要改同一行就一直卡着。处理办法是找到持有锁的事务确认业务没问题后让事务提交或者杀掉它。第四步确认是不是批量操作引发的锁范围扩大。前面提到的无索引UPDATE锁全表是最坑的一种。排查方法很简单看这条UPDATE的WHERE条件字段有没有索引。没有索引就老老实实加索引别让数据库替你买单。5.3 一张表读懂增删改查场景下的优化清单权限控制之后我把增删改查不同操作对应的优化要点整理成清单照着做基本不会有大问题。操作类型优化重点具体手段INSERT批量插入使用多值VALUES、合并事务减少提交次数SELECT索引覆盖避免SELECT *、合理创建组合索引、避免索引列函数运算UPDATE精准定位WHERE条件用索引、事务短小、避免无索引全表更新DELETE控制范围分批删除、尽量逻辑删除、避免一次删除全表通用监控与预案开启慢查询日志、定期分析慢SQL、备份数据表最后再分享一个我自己很受用的习惯每写一条增删改查SQL先跑一次EXPLAIN再决定要不要放行。这个习惯不需要花多少时间但能提前拦截掉绝大部分性能隐患。有一次我排查一个同事写的报表SQL单条查询跑了30秒EXPLAIN一看三个表关联有三个全表扫描加了两个索引之后直接降到300毫秒。这种成就感比你会写多复杂的SQL都实在。增删改查是一条越走越宽的路。开始你以为这是终点走进去才发现是一个起点。希望这篇内容能帮你把基础打牢固在之后深入MySQL架构、性能优化、分布式场景时少踩一些不必要的坑。