MySQL 增删改查操作全解_Mysql

增删改查是数据库操作的基础，但不同层次的理解深度决定了 sql 编写的质量和效率。本文以层次递进的方式，从最基础的语法，到执行逻辑、性能优化，再到高级注意事项，系统梳理 mysql 中的 crud 核心知识。

一、基础操作

1.1 insert：插入数据

-- 单行插入
insert into users (name, age) values ('alice', 25);
-- 多行插入
insert into users (name, age) values ('bob', 30), ('carol', 28);
-- 插入查询结果
insert into archive_users select * from users where age > 60;

1.2 select：查询数据

-- 基本查询
select name, age from users where age > 18;
-- 排序与分页
select * from users order by age desc limit 10, 20; -- 跳过10条取20条
-- 聚合与分组
select department, avg(salary) from employees group by department having avg(salary) > 5000;

1.3 update：更新数据

-- 更新单行/多行
update users set age = 26 where name = 'alice';
-- 批量更新（谨慎使用）
update users set status = 1 where age > 60;

1.4 delete：删除数据

-- 条件删除
delete from users where id = 123;
-- 清空全表
truncate table temp_logs;

二、执行逻辑

2.1 select 的逻辑执行顺序

书写顺序 ≠ 执行顺序。数据库引擎按以下顺序处理：

from：确定表及连接方式
where：逐行过滤
group by：分组
having：过滤分组
select：选择列、计算表达式
order by：排序
limit：截取行数

这个顺序解释了为什么 where 不能用 select 中的别名，而 order by 可以。例如：

-- 错误：where 无法识别别名 total
select salary * 12 as total from emp where total > 100000;
-- 正确：使用表达式或子查询
select salary * 12 as total from emp where salary * 12 > 100000;

2.2 索引如何影响 crud

select：索引加速 where、join、order by、group by，且覆盖索引可避免回表。
insert/update/delete：索引虽加速条件定位，但会带来维护成本。每次修改索引列都需要同步更新索引结构。

2.3 事务与锁

在 innodb 中：

select 默认不加锁（快照读），除非使用 for update 或 lock in share mode。
insert 产生插入意向锁，通常不阻塞其他插入。
update/delete 对匹配行加排他锁，并可能加间隙锁，范围越大锁越多。

三、性能与优化

3.1 插入优化

批量插入：insert into ... values (...), (...) 比逐条插入快数倍。
关闭自动提交：将多条插入包在一个事务中，减少磁盘同步。
使用 load data infile：适合大批量文本导入，效率最高。

3.2 查询优化

避免 select *：只取需要的列，减少网络传输和内存开销。
索引列上避免函数或运算：where year(create_time) = 2023 无法走索引，应改为范围查询。
联合索引最左前缀：索引 (a, b, c) 支持 a、a,b、a,b,c 的查询，不支持跳过 a 使用 b。
覆盖索引：查询的列都包含在索引中，无需回表，explain 显示 using index。

3.3 更新与删除优化

使用索引定位：where 条件列有索引时，更新/删除只锁定少量行，否则可能锁全表。
分批操作：大范围更新/删除应拆分成多批，每批处理几千行，减少长事务和锁持有时间。
注意碎片：频繁 delete 后表空间不会释放，可定期 optimize table 整理碎片（低峰期执行）。

四、注意事项

4.1 insert 的主键冲突处理

insert ignore：跳过冲突行，适合批量导入时忽略重复。

on duplicate key update：存在则更新，不存在则插入，常用于幂等写入。

insert into daily_stats (date, pv) values ('2023-01-01', 100)
on duplicate key update pv = pv + values(pv);

4.2 update 与 delete 的“危险”设计

不带 where 会作用于全表，这是 sql 语法允许的，但极易误操作。建议在开发环境开启 sql_safe_updates 模式，禁止无条件的更新删除。
delete 不会重置自增计数器，truncate 会。

4.3 锁与死锁

更新/删除时，如果多个事务以不同顺序锁定多行，可能发生死锁。优化方式：尽量按相同顺序操作，缩短事务，合理设计索引减少锁范围。
间隙锁可能导致并发插入阻塞，在 repeatable read 下尤其明显。评估业务是否需要使用 read committed 隔离级别。

4.4 大数据量下的特殊手段

归档旧数据：定期将历史数据移出主表，减小主表体积。
分区表：按时间等维度分区，便于快速删除整个分区（truncate partition）。
异步批量删除：通过程序分批循环删除，避免长事务。

与 truncate 的区别‌：

表格

特性	delete	truncate
可回滚	是（事务内）	否
重置自增值	否	是
性能	较慢（逐条删除）	极快
删除范围	可带 where 删指定行	只能清空全表

⚠️ ‌注意‌：‌省略 where 会清空全表‌。truncate 不写日志、不可回滚，但速度极快；delete 写日志，可搭配事务回滚。生产环境删除前务必先备份或确认条件。

🛡️ 实用安全建议

‌update/delete 前先 select 确认‌：用相同的 where 条件先查一遍，确认影响范围。
‌重要操作放事务里‌：start transaction;... commit; 出错可 rollback。
‌开启安全模式‌：开发环境设置 set sql_safe_updates=1;，防止漏写 where。
‌注意 null 陷阱‌：null 参与的运算结果都是 null，where col <> 'x' 不会匹配到 null 行，需用 is null 单独处理。
‌大表批量操作‌：用 limit 分批执行，避免锁表时间过长。

总结

掌握增删改查的层次结构：

基础层：能写出正确的 sql。
逻辑层：理解语句执行顺序、索引和锁的影响。
优化层：针对性能瓶颈进行优化。
高级层：应对复杂场景和工程陷阱。

到此这篇关于mysql 增删改查操作全解的文章就介绍到这了,更多相关mysql增删改查内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！

MySQL 增删改查操作全解

2026年09月08日 • Mysql •我要评论