增删改查四个动作，语法十分钟就能学完，坑全在「影响了几行」上。 UPDATE 和 DELETE 忘写 WHERE，动的是整张表。 查询慢不慢，取决于能不能用上索引；这一点在图形工具的搜索框里最容易踩。
上一篇把表的骨架搭好了，这一篇往里装数据。 四个动作对应四条语句，第 5 章到第 11 章会把它们逐条展开。 这一篇只讲「在图形工具里点的时候，背后发生了什么」。 它是全书用得最多的一篇。 后面所有章节的 Demo，九成时间都在写这四条语句的变体。
一、插入：一条、一批、一个表单
插入有三种粒度，性能差得远。
| 写法 | 一次往返发几条 | 典型场景 |
|---|---|---|
| INSERT INTO 表 VALUES (...) | 1 条 | 手工插几条 |
| INSERT INTO 表 VALUES (...),(...),(...) | 1 条语句多个值 | 批量导入 |
| 逐条 execute 循环 | 每条一次往返 | 最慢，别在大批量上用 |
图形工具的「插入」页对应第一种。 phpMyAdmin 还有一个「通过 form 表单插入数据」的功能，那是网页层面的事： 你在网页上填的表单，提交后由 PHP 拼成一条 INSERT。 所以那条 INSERT 里的值是用户输入，这就带出了 [SQL 注入] 的问题。 防的办法不是过滤字符，是参数化查询——第 22 章会专门讲。
二、修改：WHERE 是命门
UPDATE 表 SET 列 = 值; 不带 WHERE，改的是整张表。
而且不报错，只说「影响了 N 行」。
MySQL 的命令行客户端有个开关叫 --safe-updates。
带上它，不带 WHERE 的 UPDATE 会直接被拒绝。
这个开关默认是关的，很多人不知道它存在。
| 写法 | 动几行 | 风险 |
|---|---|---|
| UPDATE 表 SET 列 = 值 | 全表 | 极高，一行写错全表遭殃 |
| UPDATE 表 SET 列 = 值 WHERE 主键 = ? | 1 行 | 低 |
| UPDATE 表 SET 列 = 值 WHERE 条件 | 命中多少动多少 | 中，得先 SELECT 数一遍 |
一条实用习惯：动手改之前，先把 UPDATE 换成 SELECT COUNT(*) 跑一遍。
数出来的行数跟你预期一致，再改。
三、查询：浏览、搜索、分页
图形工具里的三种看法，代价完全不同。
| 看法 | 发出的 SQL | 能不能用索引 |
|---|---|---|
| 浏览 | SELECT * FROM 表 LIMIT 0,30 | 能（按主键顺序扫） |
| 按主键搜索 | WHERE id = ? | 能 |
| 关键字搜索 | WHERE 列 LIKE '%关键字%' | 不能，全表扫 |
| 翻到第 N 页 | LIMIT 20 OFFSET N | OFFSET 越大越慢 |
第三行是最要命的：搜索框默认两头通配，看着方便，实际是全表扫。
表小的时候没感觉，一百万行就是几秒和几十毫秒的差别。
第四行也有讲究：OFFSET 的语义是「先数过前 N 行再扔掉」。
要翻到很后面，正确做法是「记住上一页最后一行的主键，用它当条件」。
而不是一路加大 OFFSET。
四、删除：删的是行，不是空间
DELETE FROM 表 WHERE 条件; 删掉的是行，文件不一定变小。
腾出来的位置会被后面的插入复用。
要真把空间还给操作系统，MySQL 上得 OPTIMIZE TABLE，SQLite 上是 VACUUM。
这两个动作都会重建文件，大表上要跑很久，还得留出等量的空闲磁盘。
删除还有一个隐形代价：索引里对应的项也要删。
索引越多，删一行越慢——这是「加索引换查询」的另一面。
五、动手 Demo：增删改查的四个动作，在一万行上各跑一遍
这一课用 10000 行数据把四个动作各跑一遍。 重点看两件事：影响行数、以及慢在哪。 六段：批量插入、WHERE 决定动几行、三种查法的执行计划、分页、删除。
import sqlite3
c.execute("CREATE TABLE tb_member (id INTEGER PRIMARY KEY,"
" username TEXT, city TEXT, score INTEGER)")
rows = [(i, "用户%05d" % i, "城市%d" % (i % 20), i % 100)
for i in range(1, 10001)]
c.executemany("INSERT INTO tb_member VALUES (?,?,?,?)", rows)
# 一条一条 vs 一次一批
for i in range(200):
c.execute("INSERT INTO tb_member VALUES (?,?,?,?)", (...))
c.executemany("INSERT INTO tb_member VALUES (?,?,?,?)", [...])
# WHERE 决定动几行
c.execute("UPDATE tb_member SET score = score + 10 WHERE city = '城市3'")
print("带 WHERE 改了", c.execute("SELECT changes()").fetchone()[0])
c.execute("UPDATE tb_member SET city = city")
print("不带 WHERE 改了", c.execute("SELECT changes()").fetchone()[0])
# 执行计划：前缀匹配和两头通配差在哪
c.execute("CREATE INDEX i_name ON tb_member (username)")
for sql in ("SELECT * FROM tb_member WHERE username LIKE '用户0123%'",
"SELECT * FROM tb_member WHERE username LIKE '%0123%'"):
print(c.execute("EXPLAIN QUERY PLAN " + sql).fetchall()[-1][-1])
# 分页：OFFSET 越大越慢
for off in (0, 5000, 9900):
c.execute("SELECT * FROM tb_member ORDER BY id "
"LIMIT 20 OFFSET %d" % off).fetchall()
实跑输出：
--------------------------------------------------------------------
第十二课：增删改查的四个动作，在网页上长什么样
--------------------------------------------------------------------
**3.4 节讲的是在图形工具里插数据、改数据、查数据、删数据。**
**四个动作背后就是 INSERT、UPDATE、SELECT、DELETE。**
**这一课用 10000 行数据把这四个动作各跑一遍，重点看两件事：影响行数、以及慢在哪。**
**先灌 10000 行。**
一、插入：一条一条 vs 一次一批
--------------------------------------------------------------------
写法 耗时 说明
-----------------------------------------------------------
execute 200 次 0.0006 秒 每条一次往返（这里是函数调用）
executemany 一次 0.0004 秒 一次往返，200 条一起发
**这是本沙箱的速度，别当基准。**
**但方向是确定的：行数越多，批量插入的优势越大，因为省的是「往返次数」。**
二、修改：WHERE 决定动几行
--------------------------------------------------------------------
mysql> UPDATE tb_member SET score = score + 10 WHERE city = '城市3'
**带 WHERE 那条改了 500 行。**
mysql> UPDATE tb_member SET city = city
**不带 WHERE 那条「改了」10000 行——虽然值一模一样。**
**这就是图形工具里「编辑」按钮为什么一定带主键条件。**
**它要是忘了带，你点一次保存，全表一起变。**
三、查询：三种看法，三种代价
--------------------------------------------------------------------
mysql> CREATE INDEX i_name ON tb_member (username)
**先给 `username` 建个普通索引，再看三种查法各自的执行计划。**
SELECT *
FROM tb_member
WHERE id = 12345
SELECT *
FROM tb_member
WHERE username LIKE '用户0123%'
SELECT *
FROM tb_member
WHERE username LIKE '%0123%'
查法 执行计划 耗时
---------------------------------------------------------------------------------------
按主键 id 查 SEARCH tb_member USING INTEGER PRIMARY KEY (rowid=?) 0.000003 秒
前缀匹配 '用户0123%' SCAN tb_member 0.001170 秒
两头通配 '%0123%' SCAN tb_member 0.001404 秒
**注意中间那两行的计划完全一样：都是全表扫。**
**这说明「前缀匹配能用索引」这句话，在这台沙箱上不成立。**
四、为什么前缀匹配也没用上索引
--------------------------------------------------------------------
**因为 SQLite 的 LIKE 默认不区分大小写，而索引是按字节顺序排的。**
**比较规则对不上，索引就用不了。**
mysql> PRAGMA case_sensitive_like = ON
打开大小写敏感之后 执行计划 耗时
------------------------------------------------------------------------------------------------
前缀匹配 SEARCH tb_member USING INDEX i_name (username>? AND username<?) 0.000021 秒
两头通配 SCAN tb_member 0.001382 秒
**第一行变成 `SEARCH ... USING COVERING INDEX i_name` 了，这就是走索引。**
**第二行还是 `SCAN`：开头的 `%` 让数据库不知道从哪儿开始找。**
**MySQL 在 `utf8mb4_general_ci` 这类排序规则下，前缀 LIKE 本来就能用索引。**
**因为索引和比较用的是同一套规则。**
**两头通配在哪儿都用不上，这一条两边一样。**
**所以「搜索」功能最常踩的坑是两头通配。**
**图形工具里的搜索框默认就是两头通配，数据一多就慢。**
**一万行还看不出来，一百万行就是几秒和几十毫秒的差别。**
五、分页：越翻到后面越慢
--------------------------------------------------------------------
翻到哪 耗时
-------------------------------------
第 1 页（OFFSET 0） 0.000032 秒
第 251 页（OFFSET 5000） 0.000147 秒
第 496 页（OFFSET 9900） 0.000261 秒
**`OFFSET` 的语义是「先数过前 N 行再扔掉」，所以越往后越慢。**
**一万行上看不出来，是因为它本来就没多少行可数。**
六、删除：删的是行，不是空间
--------------------------------------------------------------------
mysql> DELETE FROM tb_member WHERE score > 90
**删了 1000 行，从 10000 行变成 9000 行。**
**但文件不一定变小——删掉的空间会被后面的插入复用。**
**要真把空间还回去，MySQL 上得 `OPTIMIZE TABLE`，SQLite 上是 `VACUUM`。**
第一段的两个耗时别当基准，看方向：一次一批比一条一条快，省的是「往返次数」。
换成真的 MySQL，往返要走网络，差距会比这里大得多。
第二段是这一节的重点：不带 WHERE 的 UPDATE「改了 10000 行」，而值一模一样。
数据库不会因为「值没变」就不算改动——它照样写了一遍。
第三段那两个执行计划，一开始都是一样的 SCAN。
这不是我写错了，是 SQLite 的 LIKE 默认不区分大小写，索引按字节排，规则对不上。
打开 PRAGMA case_sensitive_like 之后，前缀匹配才变成 SEARCH ... USING INDEX。
MySQL 上前缀 LIKE 本来就能用索引（排序规则一致），两头通配在哪儿都不行。
所以「搜索」这个功能，慢的根因永远是开头那个 %。
第四段分页：第 1 页 0.000032 秒，第 496 页 0.00026 秒，慢了一个数量级。
一万行上看不出疼，但它证明了「越翻越慢」是真的。
30 秒实验
- 把 Demo 里那句
UPDATE ... WHERE city = '城市3'的 WHERE 删掉，再跑一遍。 它会告诉你改了 10000 行，而且一点都不卡。 这就是「误改全表」最可怕的地方：没有报错，没有警告。 - 在分页那一段把
LIMIT 20 OFFSET 9900改成LIMIT 20 OFFSET 9990，看耗时怎么变。 行数一样是 20，耗时却随 OFFSET 涨。 - 试着给
city也建个索引，再跑一次「搜索 city=城市3」。 它会走索引了，但如果你写的是LIKE '%城市3%'，索引又用不上了。
常见误区
- 「UPDATE 不带 WHERE 只是慢一点，不会出事。」
它会把整张表都改掉，而且不报错。
动手之前先把
UPDATE改成SELECT COUNT(*)数一遍，这是最便宜的一道保险。 - 「
LIKE '%关键字%'慢是因为数据库不行。」 是因为开头那个%。 索引按顺序排，开头通配等于「从哪儿开始找都不知道」，只能一行行看。 - 「删了一半数据，文件应该小一半。」
不会。删掉的空间留在文件里等着复用。
要真还回去得
OPTIMIZE TABLE或VACUUM，这两个动作都会重建整个文件。
自测三问（点开对答案）
1. 不带 WHERE 的 `UPDATE` 会怎样？ —— **整张表每一行都被写一遍。** **哪怕值没变，它也照样写，`affected rows` 就是全表行数。** 2. 为什么 `LIKE '%abc%'` 用不上索引？ —— **因为不知道从哪个位置开始找。** **索引是按前缀排好序的，开头通配把这个顺序废掉了。** 3. 翻到很后面很慢，正确的改法是什么？ —— **别加大 `OFFSET`，改成「记住上一页最后一行的主键，用它当条件」。** **`WHERE id > 上一页最后那个 id ORDER BY id LIMIT 20`，这样每页都是索引定位。**下一篇讲数据的搬家：怎么把一整个库导出成脚本、怎么导回去， 以及为什么一定要包在一个事务里跑。
- ☐ （本篇完，下一篇 3.5 导入／导出数据）
相关笔记
- 管理数据表
- 导入／导出数据
- 45 课 MySQL 必知必会：SELECT 的系统讲法
- 🛢️ MySQL从入门到精通 · 索引