SQL 必知必会

作者:Atom

字数统计:35.4k 字

阅读时长:126 分钟

最近阅读了《SQL 必知必会》，本篇文章算作总结和记录

检索

检索数据语法

DISTINCT:关键词表示去除重复项, 且作用于所有的列
LIMIT: 限制检索的行数
OFFSET: 表示起点从哪条数据开始(从 0 开始)

sql

SELECT (DISTINCT) [字段] FROM [表名] LIMIT [数量] OFFSET [数量]

排序

排序数据语法

ORDER BY: 使用ORDER BY子句进行排序, 应该保证它是 SELECT 语句的最后一条子句

按多列排序

sql

-- 先按价格, 再按名称
SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price, prod_name

按列相对位置排序

sql

-- 按照已选列中的相对位置来排序(prod_price是第2, prod_name是第3)
SELECT prod_id, prod_price, prod_name FROM products ORDER BY 2, 3

指定排序方向

ORDER BY默认采用升序排序ASC(从 A 到 Z), 所以降序需要使用DESC关键字, 且只作用于就近的前面的一个字段

sql

-- 此处只做作用于prod_price降序, prod_name还是升序
SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC, prod_name

过滤

过滤数据语法

WHERE: 使用WHERE子句进行数据过滤, WHERE子句在表名(FROM子句)之后给出, 例如

sql

SELECT prod_id, prod_price, prod_name FROM products WHERE prod_price = 3.49

WHERE 操作符

操作符	说明
=	等于
<>	不等于
!=	不等于
<	小于
<=	小于等于
!<	不小于
>	大于
>=	大于等于
!>	不大于
BETWEEN	在指定的两值之间
IS NULL	为 NULL 值

WHERE 字段引号

如果 WHERE 后面的字段是字符串需要加上引号, 数字不需要

sql

SELECT prod_id, prod_price, prod_name FROM products WHERE prod_price != 'DLL01'

范围值检查

使用BETWEEN [开始] AND [结束]子句

sql

-- 指定价格从5-10之间的数据
SELECT prod_price, prod_name FROM products WHERE prod_price BETWEEN 5 AND 10;

空值检查

使用IS NULL子句, 0, 空字符串, 空格不属于空值

sql

-- 返回所有没有价格字段的数据
SELECT prod_name FROM products WHERE prod_price IS NULL;

高级过滤

如果要实现更强的数据过滤, 那么就需要组合多个WHERE子句, 使用AND 或者 OR子句来组合

AND 操作符

需要满足所有给定的条件

sql

SELECT prod_id, prod_price, prod_name FROM Products WHERE vend_id = 'DLL01' AND prod_price <= 4;

OR 操作符

只需要满足任一条件

sql

SELECT prod_id, prod_price, prod_name FROM Products WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';

混用`AND` 和 `OR`

由于AND优先级高于OR, 所以如果混用, 需要使用括号来确定正确顺序

sql

SELECT prod_id, prod_price, prod_name FROM Products WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01') AND prod_price >= 10;

IN 操作符

用来指定条件范围, 每个条件都可以进行匹配, 类似于OR, 性能更好

sql

SELECT prod_id, prod_price, prod_name FROM Products WHERE  vend_id IN ('DLL01','BRS01') ORDER BY prod_name;

NOT 操作符

用于否定其后所跟的任何条件, 等同于除...之外

sql

SELECT prod_id, prod_price, prod_name FROM Products WHERE NOT vend_id = 'DLL01' ORDER BY prod_name;

通配符过滤

当需要匹配未知的值的时候, 上面的几种方式都不好用了, 此处需要使用LIKE操作符

LIKE 操作符

用来匹配值的一部分的特殊字符

百分号通配符(%) : 表示任何字符出现任意次, 不能匹配 NULL; F%y 表示以 F 开头,y 结尾;

sql

-- 匹配所有prod_name以Fish开头的产品, %之后是任意字符
SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE 'Fish%';

-- 使用多个通配符, %bean  fish%表示之前和之后可以是任意字符
SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE '%bean  fish%';

下划线通配符(_): 只能匹配单个字符, 不能匹配多个字符

sql

-- 此处2个下划线, 匹配2个任意字符
SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE '__ inch teddy bear;';

通配符技巧

不要过度使用通配符(性能问题)
尽量不要把通配符用在搜索模式的开始处(开始处最慢)
注意通配符所处位置

计算字段

数据库中的数据可能不是应用程序所需要的格式, 因此需要做一些处理:

不同表列合并
格式化字段
列大小写混合处理
列计算

拼接(Concatenate)

将多个列中的值联结在一起(将一个值附加到另一个值), 构成单个值

在很多 DBMS 中, 都会使用空格来填充多余的长度, 因此我们一般需要去掉空格, mysql 中可以使用函数来实现

RTRIM(): 去掉右侧空格
LTRIM(): 去掉左侧空格
TRIM(): 去掉左右两边空格

sql

-- mysql中使用Concat函数
SELECT Concat(vend_name, ' (', vend_country, ')')

使用别名

在上述拼接的结果中, 为了能够引用到拼接的值, 因此需要使用别名来实现

实际使用中, 当列明包含不合法的字符时, 可以用来重新命名它

sql

SELECT  Concat(TRIM(vend_name), '(', TRIM(vend_country), ')') AS vend_title FROM Vendors ORDER BY vend_name;

执行算术计算

SQL 支持以下的基本算术操作符, 圆括号可用来区分优先顺序

操作符	说明
+	加
-	加
*	乘
÷	除

sql

-- 汇总物品的价格

SELECT prod_id, quantity, item_price, quantity * item_price AS expanded_price FROM OrderItems WHERE order_num = 20008;

计算测试

一般使用SELECT语句来查看测试、检验函数、计算结果

sql

SELECT 3 * 2;
SELECT TRIM( ' ABC ' )
SELECT Curdate()

函数

DBMS(数据库管理软件)都支持函数, 但是每个 DBMS 实现相同功能的函数并不相同, 所以函数是具有兼容性问题的, 因此是否使用函数, 应明确考虑 SQL 的可移植性

根据函数的功能, 可以分为以下几类:

处理文本
算术操作
处理日期
格式化函数
返回特殊信息的系统函数

处理文本

常用文本处理函数如下

函数	说明
LEFT()	返回字符串左边字符
LENGTH()	返回字符串长度
LOWER()	字符串转化为小写
LTRIM()	去掉字符串左侧空格
RIGHT()	返回字符串右侧字符
RTRIM()	去掉字符串右侧空格
SUBSTR() / SUBSTRING()	提取字符串的组成部分
SOUNDEX()	返回字符串的 SOUNDEX 值(类似发音字符和音节)
UPPER()	字符串转化为大写

例如: 当发音相同的两个字段, 在数据库中拼写错误了, 因此通过字符搜索将无法查到

sql

-- 数据库中存储的错误字段名 Michelle Green

SELECT cust_name, cust_contact FROM Customers WHERE  SOUNDEX(cust_contact) = SOUNDEX('Michael Green')

日期和时间处理

在 DBMS 中, 时间和日期函数的可移植性非常差, SQL Server中用于返回日期的某一部分的函数DATEPART()在Oracle并不支持

数值处理

常用的数值处理函数如下

函数	说明
ABS()	返回一个数的绝对值
COS()	返回一个角度的余弦
EXP()	返回一个数的指数值
PI()	返回圆周率 π 的值
SIN()	返回一个角度的正弦
SQRT()	返回一个数的平方根
TAN()	返回一个角度的正切

汇总数据

我们经常汇总数据, 而不用把他们实际检索出来, 汇总数据一般使用聚集函数, 在各种 DBMS 的中实现也比较一致

聚集函数

确定表列数
获得表中列的和
找出表列

常用聚集函数如下

函数	说明
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值之和(会忽略值为 NULL 的行)

MAX()允许用于任意列, 对于文本数据, 用于返回该列排序后的最后一行

MIN()允许用于任意列, 对于文本数据, 用于返回该列排序后的第一行

sql

-- 求列平均值
SELECT AVG(prod_price) AS avg_price FROM Products;

-- 求所有列的行数
SELECT COUNT(*) AS num_cust FROM Customers;

-- 求具体列的行数
SELECT COUNT(cust_email) AS num_cust FROM Customers;

-- 返回指定列最大值
SELECT MAX(prod_price) AS max_price FROM Products;

-- 返回指定列最小值
SELECT MIN(prod_price) AS min_price FROM Products;

-- 返回指定列值之和
SELECT SUM(quantity) AS items_ordered FROM OrderItems WHERE order_num = 20005;

-- 多列计算
SELECT SUM(item_price * quantity) AS total_price FROM OrderItems WHERE order_num = 20005;

聚集不同值

上一小节的 5 个聚集函数, 还可以指定参数

ALL: 对所有行执行计算(缺省默认值)
DISTINCT: 只包含不同值, 必须使用列名, 不能用于 COUNT(*)

sql

-- 排除了相同值后来进行平均值计算
SELECT AVG(DISTINCT prod_price) AS avg_price FROM Products WHERE vend_id = 'DLL01';

组合聚集函数

SELECT语句可根据需要包含多个聚集函数

sql

SELECT  COUNT(*) AS num_items,
        MIN(prod_price) AS price_min,
        MAX(prod_price) AS price_max,
        AVG(prod_price) AS price_avg
FROM  Products;

分组数据

使用分组可以将数据分为多个逻辑组, 对每个组进行聚集计算

创建分组语法

GROUP BY: 使用GROUP BY进行分组, 该子句返回结果会先进行分组, 然后对每个组进行结果聚集计算, 而不是整个表

sql

-- 按vend_id排序并分组数据, 征对每个vend_id计算一次num_prods
SELECT vend_id, COUNT(*) AS num_prods FROM Products GROUP BY vend_id

输出结果如下:

vend_id	num_prods
BRS01	3
DLL01	4
FNG01	2

过滤分组语法

HAVING: 规定包含哪些分组, 排除哪些分组, 搭配GROUP BY使用; 并且类似于WHERE, 语法使用一致, 都可以用HAVING替代, 但是WHERE用于过滤行, HAVING用于过滤分组

WHERE 在分组前过滤, HAVING 在数据分组后进行过滤
WHERE 排除的行不包括在分组中, 从而影响 HAVING 子句中过滤掉的分组

sql

-- 先通过cust_id进行分组, 再计算每个分组的数量, 最后过滤COUNT(*) >= 2(两个以上订单)的分组
SELECT cust_id, COUNT(*) AS orders FROM Orders GROUP BY cust_id HAVING COUNT(*) >= 2;

sql

SELECT vend_id, COUNT(*) AS num_prods FROM Products WHERE prod_price >= 4 GROUP BY vend_id HAVING COUNT(*) >= 2;

分组排序

一般而言, GROUP BY分组的数据以分组顺序输出, 但DBMS实现可能不一致, 所以保证正确排序, 最好搭配使用ORDER BY, 不要仅依赖GROUP BY排序数据

sql

-- 默认按照了 order_num 排序
SELECT order_num, COUNT(*) AS items FROM OrderItems GROUP BY order_num HAVING COUNT(*) >= 3;

-- 如果需要按照数目排序, 需要加上ORDER BY
SELECT order_num, COUNT(*) AS items FROM OrderItems GROUP BY order_num HAVING COUNT(*) >= 3  ORDER BY items, order_num;

输出结果: 先按照items数量排序完成, 在相同数量里面又按照order_num升序排列

order_num	items
2006	3
2009	3
2007	5
2008	5

SELECT 子句顺序

子句	说明	是否必须使用
SELECT	要返回的列或表达式	是
FROM	从中检索数据的标	仅在从表选择数据时使用
WHERE	行级过滤	否
GROUP BY	分组说明	仅在按组计算聚集时使用
HAVING	组级过滤	否
ORDER BY	输出顺序排序	否

子查询

SQL 允许创建子查询, 嵌套在其他查询中的查询

子查询进行过滤

子查询只能查询单列

假如有三张表, 需要查出订购物品RGAN01的所有顾客

Customers 表: 客户 ID、顾客信息
Orders 表: 订单编号、客户 ID、订单日期
OrderItems 表: 订单编号、物品 ID、物品信息

sql

SELECT cust_name, cust_contact FROM Customers WHERE cust_id IN
  SELECT cust_id FROM Orders WHERE order_num IN
    SELECT order_num FROM OrderItems WHERE prod_id = 'RGAN01'

计算字段使用子查询

完全限定列名: 排除出现冲突列名导致的歧义性, 使用表.列名来避免歧义

sql

SELECT
    cust_name,
    cust_state,
    SELECT COUNT(*)
    FROM Orders
    WHERE Orders.cust_id = Customers.cust_id
FROM Customers
ORDER BY cust_name

联结表

联结的作用

将数据分解为多个表能更有效的存储, 更方便地处理, 并且伸缩性更好, 既然数据存储在多个表中, 所以需要使用联结来关联表

联结的创建

联结两个表时, 实际是将第一个表中的每一行与第二个表中的每一行配对, 因此一般需要使用WHERE子句作为过滤条件

sql

SELECT vend_name, prod_name, prod_price
FROM Vendors, Products
Where Vendors.vend_id = Products.vend_id

内联结分类

基于两个表之间的相等测试, 这种联结称为等值联结, 也叫内联结

语法可以使用如下方式:

简单等值语法

sql

-- 上面的例子
SELECT [字段] FROM [表一], [联结的表二], [联结的表三]... WHERE [条件]

INNER JOIN 语法

sql

SELECT [字段] FROM [表一] INNER JOIN [联结的表二] INNER JOIN [联结的表三] ON [条件]

sql

-- 使用联结解决上面的 "子查询过滤" 问题
SELECT cust_name, cust_contact
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
  AND Orders.order_num = OrderItems.order_num
  AND OrderItems.prod_id = 'RGAN01'

高级联结

表别名

使用AS关键词给表取别名, 表别名可以缩短 SQL 语句, 允许在一条SELECT语句中多次使用相同的表

sql

SELECT cust_name, cust_contact
FROM Customers AS C, Orders AS O, OrderItems AS OI
WHERE C.cust_id = O.cust_id
  AND O.order_num = OI.order_num
  AND OI.prod_id = 'RGAN01'

注意

Oracle 不支持 AS 关键字

联结主要分为以下几类:

内联结(inner join)
自联结(self join)
自然联结(natural join)
外联结(outer join)

上文已讲述了内联结使用, 此处不再赘述

自联结

sql

SELECT C1.cust_id, C1.cust_name, C1.cust_contact
FROM Customers AS C1, Customers AS C2
WHERE C1.cust_name = C2.cust_name
  AND C2.cust_contact = 'Jim Jones'

自然联结

前面我们已经见识过内联结, 它要求我们显式写出联结条件, 在实际查询中还有一种更"省心"的写法, 叫做自然联结(NATURAL JOIN)

无论何时对表进行联结, 应该至少有一列不止出现在一个表中(用来做联结的那一列), 标准的内联结返回所有数据, 甚至相同的列多次出现, 而自然联结排除多次出现, 使每一列只返回一次

自然联结并不需要数据库提供特殊语法, 它本质上是一种约定: 对一个表使用通配符 SELECT *, 而对其他表的列使用明确的子集来保证没有重复列

下面这条语句检索每个订单项所属的供应商信息, 我们对 Products 表使用 * 通配符, 对其他表只取明确需要的列:

sql

SELECT O.*, OI.prod_id, OI.quantity, OI.item_price
FROM Orders AS O
INNER JOIN OrderItems AS OI ON O.order_num = OI.order_num
WHERE OI.prod_id = 'RGAN01';

这里只有 Orders 用了 O.*, 其余表只挑选了不重复的列, 所以结果里 order_num 这种公共列不会重复出现两遍

自然联结与内联结的关系

事实上, 我们迄今为止建立的每个内联结都是自然联结, 多数时候甚至不会注意到这一点, 自然联结不是一种新的联结类型, 而是用列裁剪的方式保证公共列只出现一次的内联结, 真正需要在意的是: 不要用两个 SELECT * 把同一个公共列查两遍

部分数据库(如 Oracle、PostgreSQL)支持显式的 NATURAL JOIN 关键字, 它会自动按同名列进行联结, 但这种隐式匹配在表结构变更时很容易出错, 工程实践中并不推荐, 因此本文不展开

外联结

许多联结将一个表中的行与另一个表中的行相关联, 但有时候我们需要包含没有关联行的那些行, 举几个例子:

对每个顾客下了多少订单进行计数, 包括那些至今尚未下单的顾客
列出所有产品及订购数量, 包括没有人订购的产品
计算平均销售规模, 把尚未下单的顾客也计算在内

在上述例子中, 联结包含了那些在相关表中没有关联行的行, 这种联结称为外联结(OUTER JOIN)

外联结要么保留左表中没有匹配的行, 要么保留右表中没有匹配的行, 通过 LEFT / RIGHT 关键字来指定保留哪一侧

左外联结

LEFT OUTER JOIN(可简写为 LEFT JOIN)保留左表(FROM 关键字后面的表)的所有行, 即使这些行在右表中没有匹配

先看一个对比, 如果用内联结检索所有顾客及其订单:

sql

SELECT Customers.cust_id, Orders.order_num
FROM Customers
INNER JOIN Orders ON Customers.cust_id = Orders.cust_id;

内联结只会返回那些真正下过单的顾客, 从未下单的顾客被直接丢弃了

要把"零订单顾客"也保留下来, 就需要左外联结:

sql

SELECT Customers.cust_id, Orders.order_num
FROM Customers
LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id;

结果示意如下, 注意 1000000002 这位顾客从未下单, 它的 order_num 列被填充为 NULL:

cust_id	order_num
1000000001	20005
1000000001	20009
1000000002	NULL
1000000003	20006
1000000004	20007
1000000005	20008

可以看到, 左外联结保留了左表 Customers 的全部顾客, 右表 Orders 没有匹配上的位置一律用 NULL 补齐, 这正是外联结与内联结最本质的差别

右外联结

RIGHT OUTER JOIN(可简写为 RIGHT JOIN)与左外联结方向相反, 它保留右表的所有行:

sql

SELECT Customers.cust_id, Orders.order_num
FROM Customers
RIGHT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id;

左外联结和右外联结之间唯一的差别是所关联表的顺序, 换句话说, 调整 FROM 和 JOIN 两侧表的位置, 任何左外联结都可以改写成右外联结, 实践中为了可读性, 团队往往统一只用 LEFT JOIN, 通过调整表顺序来表达需求, 避免左右混用造成理解负担

全外联结

还存在一种外联结, 它同时保留左表和右表中没有匹配的行, 称为全外联结(FULL OUTER JOIN)

sql

SELECT Customers.cust_id, Orders.order_num
FROM Customers
FULL OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id;

它的语义是: 既包含没有下单的顾客, 也包含找不到对应顾客的孤儿订单

MySQL 不支持 FULL OUTER JOIN

MySQL 8.0 至今不支持 FULL OUTER JOIN 语法, PostgreSQL、SQL Server、Oracle 则原生支持, 在 MySQL 中需要用 LEFT JOIN 和 RIGHT JOIN 配合 UNION 来模拟:

sql

SELECT Customers.cust_id, Orders.order_num
FROM Customers
LEFT JOIN Orders ON Customers.cust_id = Orders.cust_id
UNION
SELECT Customers.cust_id, Orders.order_num
FROM Customers
RIGHT JOIN Orders ON Customers.cust_id = Orders.cust_id;

UNION 会自动去重, 把"左边多出来的行"和"右边多出来的行"合并成完整的全外联结结果

四种联结的数据范围对比

下面这张图直观展示了内联结、左外联结、右外联结、全外联结分别返回哪一部分数据, 把左表和右表想象成两个集合, 阴影区域就是各联结返回的范围:

绿色代表两表都能匹配上的交集行, 橙色代表左表独有(右侧补 NULL)的行, 蓝色代表右表独有(左侧补 NULL)的行, 结合下表理解会更清晰:

联结类型	返回结果	是否保留左表无匹配行	是否保留右表无匹配行
INNER JOIN	仅两表都匹配的行(交集)	否	否
LEFT OUTER JOIN	左表全部 + 匹配上的右表行	是	否
RIGHT OUTER JOIN	右表全部 + 匹配上的左表行	否	是
FULL OUTER JOIN	左右两表全部行(并集)	是	是

使用带聚集函数的联结

聚集函数可以与联结一起使用, 这种组合在实际报表统计中极为常见

一个典型需求: 检索所有顾客及每个顾客所下的订单数, 如果用内联结, 那些从未下单的顾客会被排除, 统计结果就少了一部分人, 要把"0 单顾客"也统计进来, 必须用左外联结:

sql

SELECT Customers.cust_id,
       Customers.cust_name,
       COUNT(Orders.order_num) AS num_ord
FROM Customers
LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id
GROUP BY Customers.cust_id, Customers.cust_name;

结果示意:

cust_id	cust_name	num_ord
1000000001	Village Toys	2
1000000002	Kids Place	0
1000000003	Fun4All	1
1000000004	Fun4All	1
1000000005	The Toy Store	1

这里有一个关键细节: COUNT(Orders.order_num) 统计的是非 NULL 值的个数, 对于 Kids Place 这位从未下单的顾客, 左外联结把它的 order_num 补成了 NULL, COUNT 遇到 NULL 不计数, 因此结果恰好是 0

不要写成 COUNT(*)

如果这里写成 COUNT(*), 那么即使顾客没有订单, 左外联结产生的那一行(order_num 为 NULL)也会被计为 1, 结果就错了, 统计关联表的数量时, 务必 COUNT(右表的某个具体列) 而不是 COUNT(*)

联结及其使用要点

到这里我们已经覆盖了联结的主要类型, 实践中还有几条经验需要牢记

使用联结的要点

注意所使用的联结类型, 一般我们使用内联结, 但需要保留某一侧无匹配的行时, 使用外联结也是有效的
务必提供联结条件, 漏写 ON 或 WHERE 的联结条件, 会返回两表行数的乘积(笛卡尔积), 行数往往大到失控
保证使用正确的联结条件, 否则将返回不正确的数据
善用表别名, 多表联结时用 AS 给表起短别名, 既能缩短 SQL, 又能在自联结、子查询中消除歧义
关注性能, 联结的表越多, 性能下降越明显, 确保联结列上建立了合适的索引, 不要在大表上做无条件的笛卡尔积

笛卡尔积与交叉联结

由没有联结条件的表关系返回的结果为笛卡尔积(Cartesian product), 检索出的行的数目将是第一个表中的行数乘以第二个表中的行数

它对应的显式语法是交叉联结(CROSS JOIN):

sql

SELECT Customers.cust_name, Orders.order_num
FROM Customers
CROSS JOIN Orders;

如果 Customers 有 5 行、Orders 有 5 行, 这条语句会返回 5 × 5 = 25 行无意义的组合数据, 绝大多数情况下, 笛卡尔积是因为忘记写联结条件而意外产生的错误, 它不仅结果错误, 还可能因为行数爆炸拖垮数据库, 只有在确实需要"两两组合"(例如生成日期与门店的全组合)时, 才会刻意使用 CROSS JOIN

组合查询

多数 SQL 查询只包含从一个或多个表中返回数据的单条 SELECT 语句, SQL 还允许执行多个查询(多条 SELECT 语句), 并将结果作为一个结果集返回, 这些组合查询通常称为并(union)

主要有两种场景会用到组合查询:

在一个查询中从不同的表返回结构一致的数据
对一个表执行多个查询, 按一个查询返回数据

UNION 基本用法

利用 UNION, 可以给出多条 SELECT 语句, 用 UNION 关键字分隔, 数据库会把它们的结果合并成一个结果集

假设需要把 Illinois、Indiana、Michigan 这几个州的顾客, 以及所有名为 Fun4All 的顾客合并到一个列表里, 可以用 OR 条件写在一条语句里, 也可以用 UNION 把两个意图清晰地分开:

sql

SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL', 'IN', 'MI')
UNION
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All';

UNION 会把两条 SELECT 的结果叠加在一起, 并自动去除重复行, 如果某个 Fun4All 恰好也在 Indiana, 它只会出现一次

UNION 的规则

UNION 用起来很简单, 但有几条硬性约束:

UNION 的两条铁律

列数必须相同, 每条 SELECT 查询的列数必须完全一致(包括用别名补齐的列), 否则报错
列的数据类型必须兼容, 各 SELECT 中对应位置的列, 数据类型必须可以隐式转换, 例如第一条的第二列是字符串, 第二条的第二列也应是字符串或可转换为字符串的类型, 不能一个是日期一个是数字

注意: 列名以第一条 SELECT 的列名为准, 后续 SELECT 的列名会被忽略

UNION ALL

UNION 默认会去除重复的行, 如果想返回所有匹配行(包括重复行), 使用 UNION ALL:

sql

SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL', 'IN', 'MI')
UNION ALL
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All';

UNION ALL 与 UNION 的差别在于不做去重, 这看起来只是少了一步, 但对性能影响很大: UNION 为了去重, 需要对合并后的结果做一次排序或哈希比对, 数据量大时开销可观; 而 UNION ALL 直接拼接, 省掉了去重步骤

能用 UNION ALL 就别用 UNION

如果你确定两个结果集之间不会有重复行, 或者业务上允许重复(比如日志归集), 优先使用 UNION ALL, 只有当确实需要消除重复时, 才付出 UNION 去重的代价

对组合查询结果排序

SELECT 语句的输出用 ORDER BY 排序, 在用 UNION 组合查询时, 只能使用一条 ORDER BY 子句, 它必须出现在最后一条 SELECT 之后, 并且作用于整个组合后的结果集:

sql

SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL', 'IN', 'MI')
UNION
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All'
ORDER BY cust_name;

不允许给每条 SELECT 单独写 ORDER BY, 也不能用它只对组合结果的一部分排序, 从语义上理解, ORDER BY 排的是最终那一个结果集, 自然只能写在最后并出现一次

UNION 与 UNION ALL 对比

对比项	UNION	UNION ALL
是否去重	是, 自动去除重复行	否, 保留全部行(含重复)
性能	较低, 需要额外的去重(排序/哈希)	较高, 直接拼接无额外开销
使用场景	需要消除重复、得到不重复并集时	确定无重复或允许重复、追求性能时

插入数据

到目前为止我们一直在 SELECT 数据, 但表里的数据从哪来? 答案就是 INSERT, INSERT 用来向表中插入新行, 看似简单, 真正写好却有不少讲究

插入完整的行

最朴素的写法是只给出 VALUES, 不指定列名:

sql

-- 不推荐的写法
INSERT INTO Customers
VALUES (10006,
        'Pep E. LaPew',
        '100 Main Street',
        'Los Angeles',
        'CA',
        '90046',
        'USA',
        NULL,
        NULL);

这条语句能跑, 但它非常脆弱, 它要求 VALUES 里的值, 顺序和数量必须和表定义中的列完全一致, 一旦有人调整了表结构, 或者把这条 SQL 搬到另一台结构略有差异的库上, 它要么报错, 要么把数据塞进错误的列里, 而且不会有任何提示

更安全的写法是显式给出列名:

sql

INSERT INTO Customers(cust_id,
                      cust_name,
                      cust_contact,
                      cust_email,
                      cust_city,
                      cust_state,
                      cust_zip)
VALUES (10006,
        'Pep E. LaPew',
        'Pep',
        'pep@forta.com',
        'Los Angeles',
        'CA',
        '90046');

这种写法下, 列名和值是一一对应的, 哪个值进哪一列一目了然, 即使表中列的物理顺序发生变化, 这条语句依然正确

始终明确给出列名

不指定列名的 INSERT 高度依赖表中列的定义顺序, 可移植性极差, 养成习惯: 任何 INSERT 都明确写出列名, 多敲几个字, 换来的是结构变更时不会静默写错数据

插入部分行

既然指定了列名, 那么自然可以只插入一部分列, 没给出的列由数据库自行处理:

sql

INSERT INTO Customers(cust_id,
                      cust_name,
                      cust_city,
                      cust_state)
VALUES (10007,
        'Toy Land',
        'New York',
        'NY');

这里只给了四列, cust_contact、cust_email、cust_zip 都没有出现, 能这么省略, 是有前提的, 一个列能被省略, 当且仅当它满足下列条件之一:

列的特征	省略后的行为
定义为 `AUTO_INCREMENT` 自增	数据库自动生成下一个序号
允许 `NULL`(可空列)	该列被填入 `NULL`
定义了 `DEFAULT` 默认值	该列被填入默认值

反过来说, 如果某列既不允许 NULL、又没有默认值、也不是自增列, 那它就是必填的, 省略它会直接报错:

ERROR 1364 (HY000): Field 'xxx' doesn't have a default value

自增主键不要手动赋值

对于 AUTO_INCREMENT 主键, 通常的最佳实践是在 INSERT 时干脆不提它, 让数据库自动分配, 如果想拿回刚生成的自增值, 在 MySQL 中插入后调用 LAST_INSERT_ID() 即可

插入检索出的数据

INSERT 后面跟的不一定是 VALUES, 也可以是一条 SELECT, 这就是 INSERT SELECT, 它能把一个查询的结果批量写入目标表, 是数据迁移、归档、初始化的利器

假设我们有一张结构相同的 CustNew 表, 想把里面的客户全部并入 Customers:

sql

INSERT INTO Customers(cust_id,
                      cust_name,
                      cust_contact,
                      cust_email,
                      cust_city,
                      cust_state,
                      cust_zip)
SELECT cust_id,
       cust_name,
       cust_contact,
       cust_email,
       cust_city,
       cust_state,
       cust_zip
FROM CustNew;

几个要点:

INSERT SELECT 是按列的位置对应的, 而不是按列名, 上面 SELECT 的第一列填入 INSERT 的第一列, 以此类推, 两边列名不需要相同, 但数量和类型要能对应
SELECT 可以带 WHERE、JOIN、聚合等任意子句, 比如只迁移加州的客户, 加个 WHERE cust_state = 'CA' 即可
一条 INSERT SELECT 能插入多行, 有几行结果就插入几行

一条 INSERT 插入多行

如果要插入的多行数据是已知的常量, 没必要写多条 INSERT, 用逗号把多组 VALUES 连起来即可:

sql

INSERT INTO Customers(cust_id, cust_name, cust_city, cust_state)
VALUES (10008, 'Wascals',     'New York',  'NY'),
       (10009, 'Toys Emporium','Chicago',  'IL'),
       (10010, 'Fun4All',      'Phoenix',  'AZ');

执行结果示意:

Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

这比写三条独立的 INSERT 要好得多

批量插入的性能优势

把 N 行合并到一条 INSERT 中, 性能通常远高于执行 N 条单行 INSERT, 原因在于: 每条独立 SQL 都要经历语法解析、网络往返、事务提交、写日志等固定开销, 合并后这些开销只发生一次, 在导入大批量数据时, 单条多值 INSERT(配合适当的批大小, 例如每批 500 ~ 1000 行)往往能带来数倍乃至数十倍的吞吐提升

复制表数据

有时我们想要的不是把数据插入已有表, 而是新建一张表并填入数据, 常见于建临时表、做数据快照, 不同数据库的语法差异较大:

MySQLSQL ServerOracle

sql

-- 新建 CustCopy 表, 结构与数据均来自 Customers
CREATE TABLE CustCopy AS
SELECT * FROM Customers;

-- 或者向已存在的表追加数据
INSERT INTO CustCopy
SELECT * FROM Customers;

sql

-- SELECT ... INTO 会自动创建新表
SELECT *
INTO CustCopy
FROM Customers;

sql

-- Oracle 同样支持 CREATE TABLE AS SELECT(简称 CTAS)
CREATE TABLE CustCopy AS
SELECT * FROM Customers;

SELECT 部分可以是任意查询, 因此完全可以只复制部分列、部分行, 甚至复制一个多表 JOIN 的结果

CREATE TABLE AS 只复制结构与数据

用 CREATE TABLE ... AS SELECT 建出的新表, 只继承列定义和数据, 原表上的主键、索引、外键、AUTO_INCREMENT 属性、触发器、默认值等通通不会被带过来, 新表里所有列都会变成普通的可空列, 如果你需要一张完整等价的表, 应当先用 CREATE TABLE ... LIKE(MySQL)复制完整结构, 再用 INSERT ... SELECT 灌入数据:

sql

CREATE TABLE CustCopy LIKE Customers;   -- 含索引、约束、自增等
INSERT INTO CustCopy SELECT * FROM Customers;

更新数据

UPDATE 用来修改表中已存在的行, 它的基本结构是: 要更新哪张表、把哪些列设成什么值、以及最关键的——更新哪些行

更新单列

把客户 10005 的邮箱改掉:

sql

UPDATE Customers
SET cust_email = 'kim@thetoystore.com'
WHERE cust_id = 10005;

执行结果:

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

WHERE cust_id = 10005 限定了只有这一行被改动

更新多列

一条 UPDATE 可以同时设置多个列, 各赋值之间用逗号分隔(注意: 这里只用一个 SET 关键字):

sql

UPDATE Customers
SET cust_contact = 'Sam Roberts',
    cust_email   = 'sam@toyland.com'
WHERE cust_id = 10005;

UPDATE 省略 WHERE 会更新所有行

这是数据库领域最经典的生产事故之一, 下面这条语句没有 WHERE:

sql

UPDATE Customers
SET cust_email = 'oops@example.com';   -- 灾难

它会把表中每一行的邮箱都改成同一个值, 全表数据瞬间被污染, 且没有"撤销"按钮, 写 UPDATE 时, 先确认 WHERE 是否写了、范围对不对, 再敲回车

配合计算与子查询的 UPDATE

SET 后面的值不必是常量, 可以是基于现有列的计算表达式, 比如给所有商品统一涨价 10%:

sql

UPDATE Products
SET prod_price = prod_price * 1.1;

这里故意不带 WHERE, 因为我们的业务意图确实是"全部商品", 注意区分: 不带 WHERE 是事故还是设计, 取决于你是否真的想动全表

也可以用子查询让更新依赖另一张表的数据, 例如把下过订单的客户的某个标记列更新:

sql

UPDATE Customers
SET cust_state = 'CA'
WHERE cust_id IN (SELECT cust_id
                  FROM Orders
                  WHERE order_num = 20005);

删除某列的值

如果想把某列"清空", 不是删除行, 而是把这一列设为 NULL(前提是该列允许 NULL):

sql

UPDATE Customers
SET cust_email = NULL
WHERE cust_id = 10005;

NULL 表示"无值", 与空字符串 '' 是两回事, 不要混用

删除数据

DELETE 用来删除表中的整行, 它删的是行, 而不是列的值——想清空某列用上面的 UPDATE ... SET 列 = NULL, 而不是 DELETE

sql

DELETE FROM Customers
WHERE cust_id = 10006;

DELETE 省略 WHERE 会删除所有行

和 UPDATE 一样, DELETE 不带 WHERE 会把整张表的数据逐行删光:

sql

DELETE FROM Customers;   -- 删除表中所有行

表结构还在, 但数据全没了, 务必三思而后行

DELETE 与 TRUNCATE TABLE 的区别

如果确实要删除表中所有行, 除了 DELETE FROM 表, 还有更快的 TRUNCATE TABLE 表, 二者结果看似相同(表被清空), 内部机制却差别很大, 这也是高频面试题:

sql

TRUNCATE TABLE Customers;

对比维度	DELETE(不带 WHERE)	TRUNCATE TABLE
操作方式	逐行删除, 记录每行的删除日志	直接释放数据页, 不逐行处理
速度	慢, 数据量越大越明显	极快, 几乎与表大小无关
是否可回滚	DML 操作, 在事务中可 `ROLLBACK`	DDL 操作, 通常不可回滚(MySQL 中隐式提交)
是否重置自增值	否, 自增计数器继续递增	是, 自增计数器归零
是否触发触发器	触发 `DELETE` 触发器	不触发触发器
WHERE 条件	支持, 可按条件删除	不支持, 只能清空整表
返回受影响行数	返回实际删除的行数	一般返回 0

简单记忆: 想有条件地删除部分行、或者删除操作需要能回滚 / 触发触发器, 用 DELETE; 只是想把一张表快速彻底清空且不在乎回滚, 用 TRUNCATE

DELETE / TRUNCATE / DROP 的层次区别

这三个常被一起考查, 删除的"粒度"逐级升高:

DELETE FROM t WHERE ... —— 删除行数据, 表和结构都还在, 可按条件、可回滚
TRUNCATE TABLE t —— 删除全部行数据, 表结构(列、索引定义)保留, 但速度快、自增重置
DROP TABLE t —— 删除整张表, 连同结构、索引、约束一起消失, 表本身不复存在

一句话: DELETE 删行, TRUNCATE 清空, DROP 拆表

更新和删除的最佳实践

UPDATE 和 DELETE 是杀伤力最强的两类语句, 一行 WHERE 的疏忽就可能酿成不可逆的事故, 下面这些准则值得刻进肌肉记忆:

安全操作准则

除非确实打算更新或删除所有行, 否则绝不省略 WHERE 子句, 把"无 WHERE"当成一个需要刻意确认的特例, 而不是默认形态

先用 SELECT 验证 WHERE 条件, 在执行 UPDATE / DELETE 前, 把同样的 WHERE 套到一条 SELECT COUNT(*) 或 SELECT * 上, 确认命中的行数和内容符合预期:

sql

-- 第一步: 先看会影响哪些行
SELECT * FROM Customers WHERE cust_id = 10006;
-- 确认无误后, 再执行删除
DELETE FROM Customers WHERE cust_id = 10006;

在事务中操作以便回滚, 把高危操作包进显式事务, 发现不对就 ROLLBACK:

sql

START TRANSACTION;
UPDATE Products SET prod_price = prod_price * 1.1;
-- 检查结果, 没问题再 COMMIT, 有问题就 ROLLBACK
SELECT prod_id, prod_price FROM Products;
COMMIT;

保证表上有主键 / 索引, 让 WHERE 能精确、高效地定位目标行, 既避免误删, 也避免全表扫描带来的锁与性能问题

MySQL 的 SQL 安全更新模式

MySQL 提供了一道防呆机制 sql_safe_updates, 开启后, 如果一条 UPDATE / DELETE 的 WHERE 没有用到键列(主键或索引列), 数据库会直接拒绝执行, 从源头上挡住"裸 UPDATE / DELETE"误操作:

sql

SET SQL_SAFE_UPDATES = 1;

此后执行一条不带合理 WHERE 的删除:

sql

DELETE FROM Customers;

会得到报错:

ERROR 1175 (HY000): You are using safe update mode and you tried to
update a table without a WHERE that uses a KEY column.

许多图形化客户端(如 MySQL Workbench)默认就开启了该模式, 在生产环境的交互式连接里建议长期开启, 把它当作最后一道安全网, 当你确实需要全表更新时, 再临时 SET SQL_SAFE_UPDATES = 0; 放行, 用完即关

下面用一张图回顾一条高危写操作应有的安全闭环:

创建和操纵表

前面我们一直在和数据打交道, 增删改查围绕的都是表里的行, 但表本身从哪来? 列的类型怎么定? 主键、自增这些又是如何声明的? 这一章我们就从零开始, 把一张表的生命周期走一遍: 创建、修改、删除, 这部分操作属于 DDL(Data Definition Language, 数据定义语言), 它定义的是数据的"骨架"

创建表 CREATE TABLE

创建表使用 CREATE TABLE 语句, 核心是指定表名和一组列定义, 每个列至少要有列名和数据类型, 还可以附带是否允许 NULL、默认值、自增等修饰

我们以电商系统里的商品表 Products 为例:

sql

CREATE TABLE Products
(
    prod_id      INT          NOT NULL AUTO_INCREMENT,
    vend_id      INT          NOT NULL,
    prod_name    VARCHAR(255) NOT NULL,
    prod_price   DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    prod_desc    TEXT         NULL,
    prod_created DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (prod_id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

逐行拆解一下:

表名 Products 紧跟在 CREATE TABLE 之后, 后面用一对圆括号包住所有列定义
每一列由 列名 + 类型 + 修饰 构成, 列与列之间用逗号分隔
PRIMARY KEY (prod_id) 单独占一行, 把 prod_id 声明为主键
结尾的 ENGINE = InnoDB 指定存储引擎(MySQL 8.0 默认就是 InnoDB, 支持事务和外键), CHARSET = utf8mb4 指定字符集, 推荐用 utf8mb4 而不是 utf8, 因为前者才能完整存储 emoji 等四字节字符

命名建议

表名、列名建议统一用小写加下划线的风格(如 order_items), 避免大小写混用, MySQL 在 Linux 下默认区分表名大小写, 而在 Windows 下不区分, 跨平台迁移时大小写混用极易踩坑

如果不希望表已存在时报错, 可以加上 IF NOT EXISTS:

sql

CREATE TABLE IF NOT EXISTS Products ( ... );

常用数据类型

选对数据类型, 是建表时最重要的决策之一, 类型选得太大浪费存储, 选得太小则可能溢出, 下面按类别梳理 MySQL 8.0 的常用类型

整数类型

整数类型的区别只在于占用字节数和取值范围, 默认是有符号(SIGNED), 加 UNSIGNED 后范围会翻倍到非负区间

类型	字节	有符号范围	无符号范围	典型用途
TINYINT	1	-128 ~ 127	0 ~ 255	状态位、布尔标志
SMALLINT	2	-32768 ~ 32767	0 ~ 65535	小范围计数
MEDIUMINT	3	约 -839万 ~ 839万	0 ~ 约1677万	中等范围
INT	4	约 -21亿 ~ 21亿	0 ~ 约42亿	主键、外键、普通整数
BIGINT	8	约 ±922京	0 ~ 约1844京	大表主键、雪花算法ID

关于 INT(11)

你可能见过 INT(11) 这种写法, 这里的 11 是显示宽度, 跟存储范围毫无关系, 一个 INT 永远是 4 字节, 它仅在配合 ZEROFILL 时才有意义, MySQL 8.0.17 起已经弃用这个特性, 直接写 INT 即可

浮点与定点类型

类型	字节	特点	精度
FLOAT	4	单精度浮点	约 7 位有效数字, 不精确
DOUBLE	8	双精度浮点	约 15 位有效数字, 不精确
DECIMAL(M, D)	变长	定点数, 精确存储	M 总位数, D 小数位数

DECIMAL(10, 2) 表示总共 10 位数字, 其中小数 2 位, 即最大能存到 99999999.99

金额绝对不要用 FLOAT/DOUBLE

浮点类型在底层用二进制存储, 像 0.1、0.2 这种小数无法被精确表示, 累加运算会产生误差, 下面这个经典现象能说明问题:

sql

SELECT 0.1 + 0.2;                    -- FLOAT 计算可能得到 0.30000000000000004
SELECT CAST(0.1 AS DECIMAL(10,2)) 
     + CAST(0.2 AS DECIMAL(10,2));   -- DECIMAL 精确得到 0.30

涉及金额、余额、价格的字段, 一律用 DECIMAL, 把元转成分用 BIGINT 存整数也是常见做法, 但混合方案要全团队统一, 否则容易出对账事故

字符串类型

类型	长度	存储方式	说明
CHAR(M)	0~255	定长	不足用空格补齐, 检索时去掉尾部空格
VARCHAR(M)	0~65535	变长	实际长度 + 1~2 字节长度前缀
TINYTEXT	~255 字节	变长	短文本
TEXT	~64KB	变长	文章、描述
MEDIUMTEXT	~16MB	变长	大段文本
LONGTEXT	~4GB	变长	超大文本

CHAR 是定长, 适合存储长度固定的内容, 比如手机号、身份证号、MD5 哈希值(固定 32 位), VARCHAR 是变长, 按实际内容占用空间, 适合长度不定的内容, 比如商品名、地址

TEXT 不能设默认值

TEXT 系列类型(以及 BLOB)在 MySQL 中无法指定 DEFAULT 默认值, 写了会报错, 如果业务上需要默认空串, 要么用 VARCHAR, 要么在应用层兜底, 此外, TEXT 字段不能建立完整索引(只能建前缀索引), 大字段也会拖慢查询, 能用 VARCHAR 解决的就别上 TEXT

日期时间类型

这是建表时最容易选错的一类, 我们重点讲

类型	格式	用途
DATE	YYYY-MM-DD	只存日期, 如生日、入职日
TIME	HH:MM:SS	只存时间段
YEAR	YYYY	只存年份
DATETIME	YYYY-MM-DD HH:MM:SS	日期+时间
TIMESTAMP	YYYY-MM-DD HH:MM:SS	日期+时间, 带时区语义

DATETIME 和 TIMESTAMP 看起来很像, 但内部机制差别很大, 选型时一定要分清:

对比项	DATETIME	TIMESTAMP
取值范围	1000-01-01 ~ 9999-12-31	1970-01-01 ~ 2038-01-19
存储空间	8 字节(5.6后为5字节+小数)	4 字节
时区	与时区无关, 存什么取什么	存储时转 UTC, 读取时转回会话时区
自动初始化/更新	8.0 起支持	支持
超范围风险	几乎不会	2038 年问题

2038 年问题

TIMESTAMP 底层是 32 位整数存储自 1970 年以来的秒数, 到 2038-01-19 03:14:07 就会溢出, 对于需要存远期时间(如保险到期、长期合同)的字段, 不要用 TIMESTAMP, 改用 DATETIME

实践中的取舍: 如果业务涉及多时区用户、需要数据库自动按时区转换, 用 TIMESTAMP; 如果只想"存什么读什么"、范围要更大, 用 DATETIME, 多数互联网业务统一用 UTC 存储时间, 此时两者行为接近, 但 DATETIME 的范围优势让它成为更稳妥的默认选择

其他常用类型

类型	说明	示例
ENUM	枚举, 只能取预定义值之一	`ENUM('male','female')`
SET	集合, 可取预定义值的多个	`SET('a','b','c')`
JSON	原生 JSON 文档, 可用函数查询	存储灵活的扩展属性
BLOB	二进制大对象	存图片/文件二进制(不推荐, 通常存 URL)

ENUM 用着方便, 但加减选项要 ALTER TABLE, 不够灵活, 很多团队更倾向于用 TINYINT + 应用层常量映射, JSON 类型在存储不固定结构的扩展字段时很好用, 但别滥用, 频繁查询的字段还是应该拆成独立列

NULL 与 NOT NULL

NULL 表示"没有值"、"未知", 它不等于空字符串 '', 也不等于数字 0

列默认是允许 NULL 的, 显式写 NULL 或什么都不写效果一样
写 NOT NULL 则强制该列必须有值, 插入时不给值且无默认值就会报错
主键列会自动成为 NOT NULL, 即使你没写, 也无法存入 NULL

sql

CREATE TABLE Customers
(
    cust_id    INT          NOT NULL AUTO_INCREMENT,
    cust_name  VARCHAR(50)  NOT NULL,   -- 必填
    cust_email VARCHAR(100) NULL,       -- 可空
    PRIMARY KEY (cust_id)
);

尽量用 NOT NULL

NULL 在比较、聚合、索引中都有特殊行为(如 NULL = NULL 结果是 NULL 而非真), 容易写出隐蔽的 bug, 能确定有值的列, 建议都加 NOT NULL 并配合 DEFAULT, 让数据更干净, 也利于索引优化

DEFAULT 默认值

DEFAULT 用于指定列的默认值, 当插入数据未提供该列时自动填入

sql

CREATE TABLE Orders
(
    order_id     INT       NOT NULL AUTO_INCREMENT,
    order_status TINYINT   NOT NULL DEFAULT 0,                 -- 默认状态0
    order_time   DATETIME  NOT NULL DEFAULT CURRENT_TIMESTAMP, -- 默认当前时间
    updated_time DATETIME  NOT NULL DEFAULT CURRENT_TIMESTAMP 
                                    ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (order_id)
);

MySQL 对时间列提供了两个很实用的特性:

DEFAULT CURRENT_TIMESTAMP: 插入时若未指定该列, 自动填入当前时间, 非常适合"创建时间"字段
ON UPDATE CURRENT_TIMESTAMP: 每当这一行被 UPDATE 时, 该列自动刷新为当前时间, 适合"最后更新时间"字段, 不需要应用层手动维护

这两个特性可以同时用在一列上(如上面的 updated_time), 实现"创建时记录时间, 更新时自动刷新"

AUTO_INCREMENT 自增

AUTO_INCREMENT 让列在每次插入时自动生成递增的数值, 最常用于主键, 保证每行有唯一标识

sql

CREATE TABLE Vendors
(
    vend_id   INT         NOT NULL AUTO_INCREMENT,
    vend_name VARCHAR(50) NOT NULL,
    PRIMARY KEY (vend_id)
);

INSERT INTO Vendors (vend_name) VALUES ('海尔'), ('美的');
-- vend_id 自动生成 1, 2

几个要点:

每张表只能有一个 AUTO_INCREMENT 列, 且必须是索引(通常就是主键)的一部分
插入后可用 SELECT LAST_INSERT_ID(); 获取刚生成的自增值
删除行不会回填空缺, 比如删掉 id=2, 下一条仍是 3(默认行为)

各数据库自增方案对比

自增是个常用需求, 但各家语法差异很大, 迁移时要特别注意:

数据库	方案	写法
MySQL	AUTO_INCREMENT	`id INT AUTO_INCREMENT`
SQL Server	IDENTITY	`id INT IDENTITY(1,1)`
PostgreSQL	SERIAL / IDENTITY	`id SERIAL` 或 `id INT GENERATED ALWAYS AS IDENTITY`
Oracle	SEQUENCE + 触发器	`CREATE SEQUENCE seq; ... seq.NEXTVAL`(12c 后也支持 IDENTITY)

分布式场景下自增主键还会带来分库分表冲突问题, 业界常改用雪花算法(Snowflake)、号段模式等全局唯一 ID 方案

更新表 ALTER TABLE

表建好之后, 业务变化常常要求修改表结构, 这就要用 ALTER TABLE

添加列 用 ADD COLUMN:

sql

-- 在表末尾添加一列
ALTER TABLE Products ADD COLUMN prod_weight DECIMAL(8, 3) NULL;

-- 指定添加位置(放在 prod_name 之后)
ALTER TABLE Products ADD COLUMN prod_brand VARCHAR(50) NULL AFTER prod_name;

删除列 用 DROP COLUMN:

sql

ALTER TABLE Products DROP COLUMN prod_weight;

修改列 有两种方式, 区别在于改不改列名:

sql

-- MODIFY: 只改类型/属性, 列名不变
ALTER TABLE Products MODIFY COLUMN prod_name VARCHAR(300) NOT NULL;

-- CHANGE: 同时改列名和类型, 要写两次列名(旧名 新名)
ALTER TABLE Products CHANGE COLUMN prod_desc prod_detail TEXT NULL;

添加和删除约束:

sql

-- 添加唯一约束
ALTER TABLE Customers ADD CONSTRAINT uq_email UNIQUE (cust_email);

-- 删除约束(唯一约束/索引)
ALTER TABLE Customers DROP INDEX uq_email;

-- 添加外键
ALTER TABLE OrderItems 
    ADD CONSTRAINT fk_order FOREIGN KEY (order_id) REFERENCES Orders (order_id);

-- 删除外键(注意删的是外键约束名)
ALTER TABLE OrderItems DROP FOREIGN KEY fk_order;

大表 ALTER 可能锁表

在 MySQL 中, 对大表执行 ALTER TABLE 时, 某些操作会重建整张表并持有锁, 导致期间无法写入甚至无法读, 一张几千万行的表改个结构可能卡住业务几分钟甚至更久

虽然 MySQL 5.6 起支持 Online DDL(很多操作可 ALGORITHM=INPLACE, LOCK=NONE 在线进行), 但并非所有变更都支持无锁, 生产环境改大表结构, 通常借助第三方工具 pt-online-schema-change(Percona Toolkit)或 gh-ost(GitHub 出品), 它们通过"建影子表 + 同步数据 + 切换"的方式实现近乎无锁的变更, 任何线上 DDL 都应先在低峰期、并评估锁影响后再执行

删除表 DROP TABLE

DROP TABLE 直接删除整张表, 包括结构和所有数据, 不可恢复, 务必谨慎

sql

DROP TABLE Products;

-- 表不存在时不报错
DROP TABLE IF EXISTS Products;

DROP / TRUNCATE / DELETE 的区别

这三个都能"清空数据", 但语义完全不同:

DELETE FROM t: DML, 逐行删除, 可加 WHERE, 可回滚, 触发触发器, 自增值不重置
TRUNCATE TABLE t: DDL, 清空全表数据但保留结构, 速度快, 不可回滚, 自增值重置为初始
DROP TABLE t: DDL, 连表结构一起删除, 表彻底消失

生产环境执行任何一个前, 都先确认操作对象和备份

重命名表 RENAME

MySQL 中重命名表可以用 RENAME TABLE 或 ALTER TABLE ... RENAME:

sql

RENAME TABLE Products TO Goods;

-- 等价写法
ALTER TABLE Products RENAME TO Goods;

-- RENAME 还能一次重命名多张表
RENAME TABLE Products TO Goods, Orders TO Sale_Orders;

不同数据库重命名语法略有差异:

MySQLPostgreSQLSQL ServerOracle

sql

RENAME TABLE Products TO Goods;
-- 或 ALTER TABLE Products RENAME TO Goods;

sql

ALTER TABLE Products RENAME TO Goods;

sql

EXEC sp_rename 'Products', 'Goods';

sql

ALTER TABLE Products RENAME TO Goods;
-- 或 RENAME Products TO Goods;

约束

约束(Constraints)是施加在表或列上的规则, 用来保证数据的完整性和正确性, 比如主键保证每行唯一可识别、外键保证关联数据存在、检查约束保证取值在合理范围, 数据库引擎会在写入时自动校验这些规则, 把不合法的数据挡在门外

主键约束 PRIMARY KEY

主键唯一标识表中的每一行, 它的值必须唯一且非空, 一张表最多只能有一个主键

单列主键 最常见:

sql

CREATE TABLE Customers
(
    cust_id   INT         NOT NULL AUTO_INCREMENT,
    cust_name VARCHAR(50) NOT NULL,
    PRIMARY KEY (cust_id)
);

复合主键(由多列共同构成)用于多对多关联表, 比如订单明细表里, 一个订单的同一商品只应出现一次, 用 (order_id, prod_id) 联合做主键:

sql

CREATE TABLE OrderItems
(
    order_id   INT NOT NULL,
    prod_id    INT NOT NULL,
    quantity   INT NOT NULL DEFAULT 1,
    item_price DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (order_id, prod_id)   -- 复合主键
);

InnoDB 与主键

InnoDB 表的数据是按主键聚簇存储的, 主键即数据的物理排列顺序, 因此主键最好是单调递增的整数(如自增 INT/BIGINT), 这样插入时数据顺序追加, 性能好, 用 UUID 这类随机值做主键会造成频繁的页分裂, 影响写入性能

外键约束 FOREIGN KEY

外键用于建立两张表之间的引用关系, 保证子表中的值必须在父表中存在, 例如 OrderItems.order_id 必须是 Orders 表里真实存在的订单:

sql

CREATE TABLE OrderItems
(
    item_id  INT NOT NULL AUTO_INCREMENT,
    order_id INT NOT NULL,
    prod_id  INT NOT NULL,
    PRIMARY KEY (item_id),
    CONSTRAINT fk_orderitems_order 
        FOREIGN KEY (order_id) REFERENCES Orders (order_id)
        ON DELETE CASCADE
        ON UPDATE RESTRICT
);

ON DELETE 和 ON UPDATE 定义了当父表记录被删除/更新时, 子表如何响应:

选项	含义
RESTRICT	父表有被引用的记录时, 禁止删除/更新(默认行为)
NO ACTION	与 RESTRICT 基本等价(MySQL 中行为相同)
CASCADE	级联, 父表删除/更新时, 子表对应记录一起删除/更新
SET NULL	父表删除/更新时, 把子表外键列置为 NULL(该列须允许 NULL)

大厂为什么常常不建外键

在阿里巴巴《Java 开发手册》等规范中, 明确规定"不得使用外键与级联, 一切外键概念必须在应用层解决", 互联网大厂普遍倾向于不在数据库层建外键, 转而用应用程序代码保证引用完整性, 主要原因有:

性能开销: 每次写入子表都要回查父表校验, 高并发下成为瓶颈; 级联操作还可能引发意料之外的大范围连锁更新
分库分表困难: 数据水平拆分后, 关联的两张表可能落在不同库不同实例上, 外键根本无法跨库生效
耦合与维护: 外键让表结构强耦合, 删数据、做数据迁移、灰度变更时束手束脚

需要权衡: 在数据量不大、对一致性要求高的内部系统(如财务、后台管理), 用外键能省心地兜底; 而在大流量、需弹性扩展的互联网业务里, 牺牲数据库外键、由应用层保证一致性是主流选择

唯一约束 UNIQUE

唯一约束保证某列(或某几列组合)的值不重复, 常用于邮箱、手机号、用户名等业务唯一字段:

sql

CREATE TABLE Customers
(
    cust_id    INT          NOT NULL AUTO_INCREMENT,
    cust_email VARCHAR(100) NULL,
    cust_phone VARCHAR(20)  NOT NULL,
    PRIMARY KEY (cust_id),
    CONSTRAINT uq_email UNIQUE (cust_email),
    CONSTRAINT uq_phone UNIQUE (cust_phone)
);

唯一约束和主键都保证唯一性, 但有几点关键区别:

对比项	PRIMARY KEY	UNIQUE
每表数量	最多一个	可以多个
是否允许 NULL	不允许	允许(且多行 NULL 不算重复)
用途	行的唯一标识	业务字段去重

UNIQUE 允许多个 NULL

在 MySQL 中, 唯一约束列允许出现多行 NULL 值, 因为 NULL 代表"未知", 两个未知不算相等, 所以 cust_email 即使加了唯一约束, 仍可以有多行邮箱为 NULL

检查约束 CHECK

检查约束用于限制列的取值范围, 不满足条件的数据无法写入, 比如商品价格必须大于 0、库存不能为负:

sql

CREATE TABLE Products
(
    prod_id    INT NOT NULL AUTO_INCREMENT,
    prod_price DECIMAL(10, 2) NOT NULL,
    prod_stock INT NOT NULL DEFAULT 0,
    PRIMARY KEY (prod_id),
    CONSTRAINT chk_price CHECK (prod_price > 0),
    CONSTRAINT chk_stock CHECK (prod_stock >= 0)
);

MySQL 8.0.16 才真正支持 CHECK

这是个长期的坑: MySQL 在 8.0.16 之前, 虽然语法上能解析 CHECK 子句, 但会直接忽略, 完全不做校验, 你以为加了约束, 其实毫无作用, 从 8.0.16 开始, CHECK 才被真正强制执行

如果你的 MySQL 低于 8.0.16, 想实现类似效果只能靠触发器或应用层校验, 升级前后务必确认版本, 别让"形同虚设"的约束放进了脏数据

NOT NULL 约束

NOT NULL 是最简单的约束, 前面已经详细讲过, 它强制列必须有值, 它常与其他约束配合使用, 比如业务必填字段、主键列(自动 NOT NULL), 简单回顾:

sql

cust_name VARCHAR(50) NOT NULL   -- 姓名必填

约束总结

最后用一张表把五种约束横向对比, 方便速查:

约束	作用	是否允许 NULL	每表数量
PRIMARY KEY	唯一标识每一行	不允许	最多 1 个
FOREIGN KEY	保证引用的数据在父表存在	允许(取决于列定义)	可多个
UNIQUE	保证列值不重复	允许(多个 NULL 不冲突)	可多个
CHECK	限制列取值范围	取决于条件	可多个
NOT NULL	强制列必须有值	不允许	按列设置

约束是数据库帮我们守住数据质量的第一道防线, 设计表结构时, 把能确定的规则尽量交给约束来表达, 既减少应用层校验负担, 也让数据从源头上更可信, 下一章我们将进入索引的世界, 看看如何让查询飞起来

视图

到目前为止, 我们写的所有查询都是直接面向底层表的, 当一个联结查询要写七八行, 还要在十几个地方重复粘贴时, 维护成本会迅速失控, 视图就是为了解决这类问题而生的

什么是视图

视图(View)本质上是一张虚拟的表, 它和真实的表最大的区别在于, 视图本身不存储任何数据, 它只存储一段 SELECT 查询逻辑, 当你查询视图时, 数据库会在背后执行这段预先定义好的查询, 把结果"当作"一张表返回给你

可以把它理解为一个"保存起来的查询", 或者说是底层表之上的一层抽象

从图中可以看出, 应用层只面对一个干净的 ProductCustomers 视图, 而背后可能联结了三张甚至更多的物理表, 这层抽象屏蔽了底层的复杂性

视图的作用

视图在实际工程中的价值, 可以归纳为以下几点

作用	说明
简化复杂联结	把多表联结、嵌套子查询封装成一个视图, 上层查询像查单表一样简单
权限控制	只暴露视图中的部分列, 隐藏 `cust_email`、薪资等敏感字段, 对外只授予视图权限
复用查询逻辑	同一段统计逻辑在多处使用时, 集中定义一次, 改一处即可全局生效
格式化数据	在视图里做好字段拼接、计算、别名, 上层拿到的就是格式化后的结果
解耦	底层表结构调整时, 只要视图定义跟着改, 上层应用可以不动

创建与使用视图

创建视图使用 CREATE VIEW 语句, 后面跟一段 SELECT 即可, 下面把一个常见的三表联结(客户、订单、订单明细)封装成视图 ProductCustomers, 用来查询"购买了某个产品的客户"

sql

CREATE VIEW ProductCustomers AS
SELECT cust_name,
       cust_contact,
       prod_id
FROM Customers AS c
JOIN Orders AS o ON c.cust_id = o.cust_id
JOIN OrderItems AS oi ON o.order_num = oi.order_num;

视图创建好之后, 就可以像查询普通表一样查询它, 比如要找出所有购买了产品 RGAN01 的客户

sql

SELECT cust_name, cust_contact
FROM ProductCustomers
WHERE prod_id = 'RGAN01';

注意这里的关键点: WHERE 条件是在查询视图时追加的, 它会和视图内部定义的查询逻辑组合后再交给数据库执行, 也就是说, 视图并不会先把所有数据查出来再过滤, 而是把外层的 WHERE 下推, 最终执行一条完整的、带过滤条件的联结查询

如果想看视图的定义, 可以用下面的语句

sql

SHOW CREATE VIEW ProductCustomers;

视图的规则与限制

视图虽然方便, 但有不少容易踩坑的地方

使用视图时需要注意

嵌套与性能: 视图内部可以引用另一个视图(嵌套视图), 嵌套层数过多时, 数据库需要逐层展开查询, 容易生成低效的执行计划, 排查问题也更困难
ORDER BY 会被覆盖: 如果视图定义里写了 ORDER BY, 而查询视图时外层又写了 ORDER BY, 那么外层的排序会覆盖视图内部的排序, 因此不建议在视图定义中依赖 ORDER BY
不是所有视图都可更新: 包含聚集函数、DISTINCT、GROUP BY、UNION、子查询等的视图通常是只读的, 无法对其执行 INSERT / UPDATE / DELETE
命名唯一: 视图名在同一数据库内必须唯一, 不能和表重名
不能加索引: 视图本身不存数据, 无法在视图上单独建立索引、触发器或默认值, 这些只能加在底层表上

可更新视图

在某些条件下, 视图是可以更新的, 也就是说, 对视图执行 INSERT、UPDATE、DELETE, 这些操作会被转换并作用到底层的真实表上

一个视图要可更新, 通常需要满足以下条件

只基于单张表(不含联结)
不包含聚集函数(SUM、COUNT、AVG 等)
不包含 DISTINCT、GROUP BY、HAVING、UNION
不包含子查询(在某些情况下子查询会破坏可更新性)
视图中的列直接对应底层表的列, 没有经过计算或函数处理

例如下面这个简单的单表视图就是可更新的

sql

CREATE VIEW USCustomers AS
SELECT cust_id, cust_name, cust_email, cust_state
FROM Customers
WHERE cust_country = 'USA';

-- 这条更新会真实作用到 Customers 表
UPDATE USCustomers
SET cust_email = 'new@example.com'
WHERE cust_id = 1000000001;

TIP

现实中, 视图的核心价值在于查询和抽象, 而不是更新, 大多数复杂视图都是只读的, 建议把视图当作只读的查询封装来使用, 数据写入仍然直接面向底层表, 这样逻辑更清晰, 也避免可更新视图带来的隐式行为

物化视图

普通视图每次查询都要实时执行一遍底层 SQL, 如果底层查询很重(比如大表的聚合统计), 频繁查询视图就会有性能压力, 物化视图(Materialized View)正是为此设计的: 它会把查询结果真实地存储下来, 查询时直接读取存好的结果, 速度极快, 代价是数据存在延迟, 需要定期刷新

关于 MySQL 与物化视图

MySQL 到 8.0 为止并不原生支持物化视图, 如果你需要类似能力, 常见做法是手动维护一张汇总表(summary table), 通过定时任务(事件调度器 EVENT 或外部 cron)定期把聚合结果写入这张表

而 Oracle 和 PostgreSQL 则原生支持物化视图, 语法如下:

sql

-- PostgreSQL / Oracle
CREATE MATERIALIZED VIEW order_summary AS
SELECT cust_id, COUNT(*) AS order_count, SUM(quantity * item_price) AS total
FROM Orders o
JOIN OrderItems oi ON o.order_num = oi.order_num
GROUP BY cust_id;

-- 刷新数据(PostgreSQL)
REFRESH MATERIALIZED VIEW order_summary;

在 MySQL 中等价的"汇总表"思路:

sql

CREATE TABLE order_summary (
    cust_id    INT PRIMARY KEY,
    order_count INT,
    total       DECIMAL(10, 2)
);

-- 定期清空重算(或用 INSERT ... ON DUPLICATE KEY UPDATE 增量更新)
TRUNCATE order_summary;
INSERT INTO order_summary
SELECT o.cust_id, COUNT(*), SUM(oi.quantity * oi.item_price)
FROM Orders o
JOIN OrderItems oi ON o.order_num = oi.order_num
GROUP BY o.cust_id;

存储过程

视图封装的是"查询逻辑", 而存储过程封装的是"一段可执行的业务流程", 当一个操作需要多条 SQL 配合、还带有判断和循环时, 存储过程就派上用场了

什么是存储过程

存储过程(Stored Procedure)是一组为了完成特定功能而预先编写、并保存在数据库中的 SQL 语句集合, 它经过一次编译后存储在服务器端, 之后可以被多次调用, 调用时只需传入参数即可

你可以把它类比成编程语言里的"函数": 有名字、可以接收参数、内部封装了一系列逻辑、能返回结果, 区别在于, 它运行在数据库服务器上, 直接操作数据

存储过程的利弊

存储过程是一把双刃剑, 用对场景是利器, 用错场景是负担

优点	缺点
性能好: 预编译, 省去每次解析 SQL 的开销	可移植性差: 各数据库语法差异巨大, 几乎无法跨库迁移
封装性: 把复杂业务逻辑封装在一处, 调用方无需了解细节	难维护难调试: 缺乏成熟的版本管理和调试工具, 出错难定位
安全: 可只授予执行权限, 不暴露底层表	增加数据库负载: 业务逻辑压在数据库上, 而数据库是最难横向扩展的一层
减少网络往返: 多条 SQL 一次调用完成, 不必来回通信	与无状态扩展理念冲突: 互联网应用倾向把逻辑放在可水平扩展的应用层

互联网团队的普遍态度

在大型互联网架构中, 数据库往往是整个系统里最难扩展的一层(分库分表已经够头疼了), 把大量业务逻辑写进存储过程, 等于把压力和复杂度都堆到这一层, 还牺牲了可移植性和可维护性, 因此很多互联网团队明确不推荐重度使用存储过程, 主张"把逻辑放在应用层, 数据库只做存取"

但在传统企业系统、金融、报表等场景下, 存储过程依然有它的价值, 是否使用, 取决于你的团队规模、扩展需求和运维能力, 不要一概而论

创建存储过程

创建存储过程会遇到一个语法问题: 存储过程内部本身就用分号 ; 分隔语句, 而我们提交整段定义时, 客户端又会把第一个分号当成"语句结束", 解决办法是先用 DELIMITER 把语句结束符临时改成别的符号(常用 // 或 $$), 定义完再改回来

下面创建一个存储过程, 计算所有产品的平均价格

sql

DELIMITER //

CREATE PROCEDURE GetAvgPrice()
BEGIN
    SELECT AVG(prod_price) AS avg_price
    FROM Products;
END //

DELIMITER ;

这里 BEGIN ... END 包裹的是过程体, 中间的 SQL 仍然用普通分号分隔, 而整个 CREATE PROCEDURE 以 // 结尾, 最后一行把分隔符改回默认的分号

调用存储过程

存储过程用 CALL 关键字调用, 后面跟过程名和参数列表(没有参数也要带括号)

sql

CALL GetAvgPrice();

执行后会返回和直接运行内部 SELECT 一样的结果

参数: IN / OUT / INOUT

存储过程的参数有三种模式, 决定了数据的流向

模式	方向	说明
`IN`	调用方 → 过程	输入参数, 把值传进过程, 过程内对它的修改不影响外部(默认模式)
`OUT`	过程 → 调用方	输出参数, 过程内给它赋值, 调用结束后外部能拿到结果
`INOUT`	双向	既作为输入传入初始值, 过程内又可以修改并传回

下面是一个带 IN 和 OUT 参数的例子: 传入一个订单号, 计算该订单的总金额并通过 OUT 参数返回

sql

DELIMITER //

CREATE PROCEDURE GetOrderTotal(
    IN  the_order_num INT,
    OUT order_total   DECIMAL(10, 2)
)
BEGIN
    SELECT SUM(quantity * item_price)
    INTO order_total
    FROM OrderItems
    WHERE order_num = the_order_num;
END //

DELIMITER ;

调用时, 用一个用户变量(以 @ 开头)来接收 OUT 参数的值, 调用结束后再 SELECT 出来

sql

CALL GetOrderTotal(20005, @total);
SELECT @total AS order_total;

注意 INTO order_total 这个写法: 它把 SELECT 查到的单个值直接赋给了输出参数, 而不是返回结果集

变量与控制流

存储过程内部支持声明局部变量, 以及 IF、CASE、循环等控制流语句, 这让它具备了完整的过程式编程能力

声明变量用 DECLARE, 必须写在 BEGIN 之后、其他语句之前

sql

DECLARE total DECIMAL(10, 2) DEFAULT 0;

IF 条件判断示例: 根据订单总额给出不同的等级标签

sql

DELIMITER //

CREATE PROCEDURE OrderLevel(IN the_order_num INT, OUT level VARCHAR(10))
BEGIN
    DECLARE total DECIMAL(10, 2);

    SELECT SUM(quantity * item_price) INTO total
    FROM OrderItems
    WHERE order_num = the_order_num;

    IF total >= 1000 THEN
        SET level = '大单';
    ELSEIF total >= 100 THEN
        SET level = '中单';
    ELSE
        SET level = '小单';
    END IF;
END //

DELIMITER ;

CASE 语句适合多分支取值, 逻辑比层层 ELSEIF 更清晰

sql

CASE
    WHEN total >= 1000 THEN SET level = '大单';
    WHEN total >= 100  THEN SET level = '中单';
    ELSE                    SET level = '小单';
END CASE;

WHILE 循环示例: 简单累加 1 到 5

sql

DELIMITER //

CREATE PROCEDURE SumToFive(OUT result INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    SET result = 0;

    WHILE i <= 5 DO
        SET result = result + i;
        SET i = i + 1;
    END WHILE;
END //

DELIMITER ;

LOOP 循环则需要配合标签和 LEAVE 手动退出, 灵活度更高

sql

my_loop: LOOP
    SET i = i + 1;
    IF i > 5 THEN
        LEAVE my_loop;
    END IF;
END LOOP my_loop;

删除存储过程

不再需要的存储过程用 DROP PROCEDURE 删除, 加上 IF EXISTS 可以避免目标不存在时报错

sql

DROP PROCEDURE IF EXISTS GetOrderTotal;

不同数据库的语法差异

存储过程是各数据库方言差异最大的地方之一, 几乎无法直接迁移, 下面以"计算订单总额"为例, 对比三种主流数据库的写法

MySQLSQL Server (T-SQL)Oracle (PL/SQL)

sql

DELIMITER //
CREATE PROCEDURE GetOrderTotal(
    IN the_order_num INT,
    OUT order_total DECIMAL(10, 2)
)
BEGIN
    SELECT SUM(quantity * item_price) INTO order_total
    FROM OrderItems
    WHERE order_num = the_order_num;
END //
DELIMITER ;

sql

CREATE PROCEDURE GetOrderTotal
    @the_order_num INT,
    @order_total   DECIMAL(10, 2) OUTPUT
AS
BEGIN
    SELECT @order_total = SUM(quantity * item_price)
    FROM OrderItems
    WHERE order_num = @the_order_num;
END;

sql

CREATE OR REPLACE PROCEDURE GetOrderTotal(
    the_order_num IN  NUMBER,
    order_total   OUT NUMBER
)
AS
BEGIN
    SELECT SUM(quantity * item_price) INTO order_total
    FROM OrderItems
    WHERE order_num = the_order_num;
END;

可以看到, 参数声明、变量前缀(T-SQL 的 @)、是否需要 DELIMITER、赋值方式都各不相同, 这正是前面提到的"可移植性差"的直接体现

游标

前面的查询都是面向"结果集整体"的——一条 SQL 处理一批行, 但有时业务需要对结果集逐行做不同处理, 这时就要用到游标

什么是游标

游标(Cursor)是一种能够逐行遍历查询结果集的机制, 它把一个 SELECT 的结果集"指针化", 让你可以一次取出一行、处理一行、再取下一行, 直到遍历完毕

游标只能在存储过程或函数内部使用, 它有一套固定的生命周期: 声明、打开、逐行读取、关闭

整个流程的关键在于第 4 步的判断: 数据库需要一种方式告诉我们"已经没有更多行了", 这通过 CONTINUE HANDLER FOR NOT FOUND 来实现

使用游标

下面是一个完整的例子, 演示在存储过程中用游标遍历所有订单, 逐行累加每个订单的总金额到一张汇总表里

sql

DELIMITER //

CREATE PROCEDURE ProcessOrders()
BEGIN
    -- 1. 声明用于接收每行数据的局部变量
    DECLARE done   BOOLEAN DEFAULT FALSE;
    DECLARE o_num  INT;
    DECLARE o_total DECIMAL(10, 2);

    -- 2. 声明游标, 绑定一条 SELECT
    DECLARE order_cursor CURSOR FOR
        SELECT order_num FROM Orders;

    -- 3. 声明"结束处理器": 当 FETCH 取不到数据时, 把 done 置为 TRUE
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- 准备一张临时汇总表
    CREATE TABLE IF NOT EXISTS OrderTotals (
        order_num   INT,
        order_total DECIMAL(10, 2)
    );

    -- 4. 打开游标
    OPEN order_cursor;

    -- 5. 循环逐行读取
    order_loop: LOOP
        FETCH order_cursor INTO o_num;

        -- 取不到数据时退出循环
        IF done THEN
            LEAVE order_loop;
        END IF;

        -- 处理这一行: 计算该订单总额
        SELECT SUM(quantity * item_price) INTO o_total
        FROM OrderItems
        WHERE order_num = o_num;

        INSERT INTO OrderTotals(order_num, order_total)
        VALUES (o_num, o_total);
    END LOOP order_loop;

    -- 6. 关闭游标
    CLOSE order_cursor;
END //

DELIMITER ;

调用方式和普通存储过程一样

sql

CALL ProcessOrders();
SELECT * FROM OrderTotals;

这段代码里有几个必须理解的要点

声明顺序有讲究: 在 MySQL 中, 变量(DECLARE 变量) → 游标(DECLARE ... CURSOR) → 处理器(DECLARE ... HANDLER)必须严格按这个顺序声明, 否则会报错
NOT FOUND 处理器是核心: DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; 注册了一个事件——当 FETCH 没有更多行可取时, 自动把标志变量 done 设为 TRUE, 我们靠它判断循环是否该结束
FETCH 把列值塞进变量: FETCH ... INTO o_num 把当前行的列依次赋给 INTO 后面的变量, 变量的数量和顺序要和游标 SELECT 的列对应
务必 CLOSE: 游标会占用服务器资源, 用完一定要关闭

游标的性能问题

游标虽然提供了逐行处理的能力, 但它的代价非常高, 必须谨慎使用

能用集合操作就不要用游标

游标是逐行处理的, 每取一行都要和数据库交互一次, 这与关系型数据库"批量集合操作"的设计初衷背道而驰, 在处理稍大的数据量时, 游标的性能可能比等价的单条 SQL 慢几十倍甚至上百倍

这是一条非常重要的最佳实践: **凡是能用一条 SQL(集合操作)解决的, 就绝对不要用游标逐行处理, **

以上面的 ProcessOrders 为例, 它完全可以用一条 INSERT + SELECT 替代, 既简洁又高效:

sql

INSERT INTO OrderTotals(order_num, order_total)
SELECT order_num, SUM(quantity * item_price)
FROM OrderItems
GROUP BY order_num;

一条 GROUP BY 就完成了整个游标循环做的事, 数据库可以对它做充分的优化, 只有当逐行逻辑确实无法用集合操作表达时(例如每行要调用外部过程、行间有强顺序依赖), 才考虑游标

小结

视图封装查询逻辑, 是虚拟表, 适合简化复杂联结和做权限控制
存储过程封装可执行流程, 性能好但可移植性和可维护性差, 互联网场景需谨慎使用
游标用于逐行处理, 是最后的手段, 优先考虑集合操作

这三者都是把逻辑下沉到数据库的工具, 用之前先想清楚: 这段逻辑究竟该放在数据库, 还是放在应用层

事务处理

到目前为止, 我们执行的都是单条 SQL 语句, 每条语句独立生效, 但真实业务里, 一个完整的操作往往由多条 SQL 组成, 它们必须作为一个整体被对待, 事务就是为此而生的

什么是事务

事务(Transaction)是一组不可分割的数据库操作, 这组操作要么全部成功, 要么全部失败, 不存在中间状态

最经典的例子就是银行转账, 假设账户 A 要转 100 元给账户 B, 这个动作在数据库里其实是两步:

sql

UPDATE Accounts SET balance = balance - 100 WHERE acct_id = 'A';
UPDATE Accounts SET balance = balance + 100 WHERE acct_id = 'B';

问题来了, 如果第一条执行成功, A 被扣了 100 元, 但第二条因为某种原因(宕机、约束冲突、网络中断)失败了, 那么这 100 元就凭空消失了, A 少了钱, B 没收到钱, 数据陷入不一致状态

事务保证了这两条 UPDATE 被绑定成一个原子单元: 要么 A 扣款和 B 加款都成功并提交, 要么任何一步出错就整体回滚, 谁的余额都不会变

ACID 特性

事务必须满足四个特性, 取这四个英文单词的首字母合称 ACID, 这是面试高频考点, 也是理解数据库可靠性的基石

特性	英文	含义	InnoDB 如何保证
原子性	Atomicity	事务内的操作要么全做, 要么全不做, 不可分割	依靠 undo log(回滚日志), 出错时用它把数据回退到事务开始前
一致性	Consistency	事务执行前后, 数据库都必须处于合法状态, 满足所有约束规则	由原子性、隔离性、持久性共同保证, 也依赖用户定义的约束(主键、外键、CHECK 等)
隔离性	Isolation	多个并发事务之间互不干扰, 一个事务的中间状态对其他事务不可见	依靠锁机制和 MVCC 多版本并发控制, 由隔离级别决定隔离强度
持久性	Durability	事务一旦提交, 其结果就永久保存, 即使系统崩溃也不丢失	依靠 redo log(重做日志), 提交时先写日志再刷盘, 崩溃后可重放恢复

这里简单理解两个日志:

undo log: 记录数据被修改前的样子, 用于回滚, 也是 MVCC 实现的基础, 原子性靠它兜底
redo log: 记录数据被修改后的样子, 采用 WAL(Write-Ahead Logging, 预写日志)机制, 先把变更顺序写入 redo log, 再异步刷新到磁盘数据页, 持久性靠它保证

INFO

一致性(Consistency)是事务的最终目的, 而原子性、隔离性、持久性是实现一致性的手段, 可以理解为: 前三者服务于第四者

事务控制语句

MySQL 中控制事务的核心语句有三个:

语句	作用
`START TRANSACTION` 或 `BEGIN`	显式开启一个事务
`COMMIT`	提交事务, 所有变更永久生效
`ROLLBACK`	回滚事务, 撤销本次事务的所有变更

下面用完整的转账案例演示, 先准备账户表和数据:

sql

CREATE TABLE Accounts (
    acct_id   CHAR(10)       NOT NULL,
    balance   DECIMAL(12, 2) NOT NULL DEFAULT 0,
    PRIMARY KEY (acct_id)
);

INSERT INTO Accounts (acct_id, balance) VALUES
('A', 1000.00),
('B', 500.00);

正常提交的转账流程, A 转 100 元给 B:

sql

START TRANSACTION;

UPDATE Accounts SET balance = balance - 100 WHERE acct_id = 'A';
UPDATE Accounts SET balance = balance + 100 WHERE acct_id = 'B';

COMMIT;

执行 COMMIT 后, A 的余额变成 900, B 变成 600, 变更永久落盘

如果中途发现问题需要撤销, 用 ROLLBACK:

sql

START TRANSACTION;

UPDATE Accounts SET balance = balance - 100 WHERE acct_id = 'A';

-- 此时检查发现 A 余额不足, 或者业务校验不通过
-- 撤销刚才的扣款, A 的余额恢复如初
ROLLBACK;

ROLLBACK 之后, A 的余额回到 START TRANSACTION 之前的值, 仿佛这次扣款从未发生

在实际应用代码中(比如 Java/Node.js), 典型写法是用 try-catch 包裹: 操作成功则 COMMIT, 捕获到异常则 ROLLBACK

sql

-- 伪代码逻辑示意
START TRANSACTION;
    UPDATE Accounts SET balance = balance - 100 WHERE acct_id = 'A';
    UPDATE Accounts SET balance = balance + 100 WHERE acct_id = 'B';
-- 如果以上都成功
COMMIT;
-- 如果任何一步抛出异常
ROLLBACK;

WARNING

DDL 语句(如 CREATE TABLE、ALTER TABLE、DROP TABLE)在 MySQL 中会触发隐式提交, 它们无法被 ROLLBACK 回滚, 所以不要在事务中间夹杂 DDL 操作, 否则前面的变更会被意外提交

保存点 SAVEPOINT

有时候事务很长, 我们并不想出错时就回滚整个事务, 而是希望只回退到某个中间节点, 这时候可以用保存点(SAVEPOINT)

保存点相当于在事务内部打了一个书签, ROLLBACK TO SAVEPOINT 可以让事务回退到这个书签处, 而书签之前的操作仍然保留

sql

START TRANSACTION;

UPDATE Accounts SET balance = balance - 100 WHERE acct_id = 'A';

-- 设置一个保存点
SAVEPOINT after_deduct;

UPDATE Accounts SET balance = balance + 100 WHERE acct_id = 'B';

-- 发现给 B 加款的逻辑有问题, 只回退这一步
-- A 的扣款保留, B 的加款被撤销
ROLLBACK TO SAVEPOINT after_deduct;

-- 重新执行正确的加款
UPDATE Accounts SET balance = balance + 100 WHERE acct_id = 'B';

COMMIT;

语句	作用
`SAVEPOINT 名称`	创建一个保存点
`ROLLBACK TO SAVEPOINT 名称`	回滚到指定保存点, 保存点之后的操作被撤销
`RELEASE SAVEPOINT 名称`	删除一个保存点(不回滚)

自动提交 autocommit

MySQL 默认开启自动提交模式, 即 autocommit = 1, 这意味着你执行的每一条单独的 SQL 语句都被当作一个独立的事务, 执行完立刻自动提交, 无法回滚

这就解释了为什么平时直接执行 UPDATE 后, 数据马上就变了

查看当前自动提交状态:

sql

SELECT @@autocommit;
SHOW VARIABLES LIKE 'autocommit';

关闭自动提交有两种方式:

sql

-- 方式一: 关闭自动提交, 之后所有语句都需要手动 COMMIT
SET autocommit = 0;

UPDATE Accounts SET balance = balance - 100 WHERE acct_id = 'A';
COMMIT;  -- 必须手动提交才会生效

sql

-- 方式二: 用 START TRANSACTION 显式开启事务
-- 它会临时覆盖 autocommit 设置, 直到 COMMIT 或 ROLLBACK
START TRANSACTION;
UPDATE Accounts SET balance = balance - 100 WHERE acct_id = 'A';
COMMIT;

INFO

推荐使用 START TRANSACTION 的方式而非全局 SET autocommit = 0, 因为关闭全局自动提交会影响该连接上所有后续语句, 容易遗忘 COMMIT 导致锁长时间不释放, 而 START TRANSACTION 的作用范围只在单个事务内, 更清晰可控

事务隔离级别

上一节讲的是单个事务的完整性, 但数据库是多用户系统, 同一时刻往往有成百上千个事务在并发执行, 如果不加以控制, 这些并发事务相互交叉读写同一份数据, 就会引发各种数据错乱问题, 隔离级别就是用来权衡并发性能与数据正确性的开关

并发问题

并发事务可能引发三类经典问题: 脏读、不可重复读、幻读, 它们的严重程度依次递减

问题	英文	描述
脏读	Dirty Read	一个事务读到了另一个事务尚未提交的数据
不可重复读	Non-Repeatable Read	同一事务内, 两次读取同一行数据, 结果却不一致(被其他已提交事务修改了)
幻读	Phantom Read	同一事务内, 两次执行同样的范围查询, 第二次却多出或少了一些行(被其他已提交事务插入或删除了)

下面逐一看具体场景

脏读场景: 事务 B 读到了事务 A 还没提交、后来又回滚的数据

时间	事务 A	事务 B
T1	修改 A 余额为 900(未提交)
T2		读取 A 余额, 得到 900(脏数据)
T3	ROLLBACK, A 余额回到 1000
T4		此时 B 手里的 900 是个根本不存在的值

事务 B 基于一个最终被撤销的数据做了决策, 这就是脏读的危害

不可重复读场景: 重点在于读到的是已修改的同一行

时间	事务 A	事务 B
T1	读取 A 余额, 得到 1000
T2		修改 A 余额为 900 并 COMMIT
T3	再次读取 A 余额, 得到 900

事务 A 在同一个事务里读了两次, 却得到不同结果, 对依赖数据稳定性的逻辑(如先查余额再做计算)是个隐患

幻读场景: 重点在于读到的是新增或删除的行, 是"行数"层面的变化

时间	事务 A	事务 B
T1	查询余额大于 500 的账户, 得到 3 行
T2		插入一个余额 800 的新账户并 COMMIT
T3	再次查询余额大于 500 的账户, 得到 4 行

事务 A 两次相同的范围查询, 第二次凭空多了一行, 像出现了"幻影", 故称幻读

TIP

不可重复读和幻读容易混淆, 简单区分: 不可重复读针对单行的更新(UPDATE/DELETE 导致内容变了), 幻读针对多行的数量(INSERT 导致行数变了)

四种隔离级别

SQL 标准定义了四种隔离级别, 隔离性由弱到强, 并发性能由高到低, 下表展示每种级别能否解决上述三类问题(打勾表示能避免该问题):

隔离级别	英文	脏读	不可重复读	幻读
读未提交	READ UNCOMMITTED	否	否	否
读已提交	READ COMMITTED	是	否	否
可重复读	REPEATABLE READ	是	是	是(InnoDB)
串行化	SERIALIZABLE	是	是	是

说明:

READ UNCOMMITTED(读未提交): 最低级别, 几乎不做隔离, 一个事务能读到其他事务未提交的数据, 三种问题全都存在, 实践中基本不用
READ COMMITTED(读已提交): 只能读到已提交的数据, 解决了脏读, 但同一事务内多次读取仍可能因其他事务的提交而变化, Oracle、SQL Server、PostgreSQL 默认采用此级别
REPEATABLE READ(可重复读): 保证同一事务内多次读取结果一致, 解决了脏读和不可重复读, MySQL InnoDB 的默认级别
SERIALIZABLE(串行化): 最高级别, 强制事务串行执行, 彻底杜绝所有并发问题, 但并发性能最差, 一般只在对一致性要求极高的场景使用

INFO

按 SQL 标准, REPEATABLE READ 级别下幻读是无法避免的, 但 MySQL InnoDB 比较特殊: 它在 REPEATABLE READ 级别下, 通过 MVCC(解决快照读的幻读)加 Next-Key Lock(解决当前读的幻读)的组合, 基本解决了幻读问题, 所以上表 InnoDB 在 RR 级别的幻读一列打了勾, 这是 InnoDB 区别于标准的重要增强

查看与设置隔离级别

查看当前会话和全局的隔离级别:

sql

-- MySQL 8.0
SELECT @@global.transaction_isolation;   -- 全局级别
SELECT @@session.transaction_isolation;  -- 当前会话级别

设置隔离级别, 可以指定作用范围是当前会话(SESSION)还是全局(GLOBAL):

sql

-- 设置当前会话的隔离级别为读已提交
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 设置全局隔离级别为串行化(对之后新建的连接生效)
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- 可选的四个级别关键字
-- READ UNCOMMITTED / READ COMMITTED / REPEATABLE READ / SERIALIZABLE

下面用时序图还原一次不可重复读的发生过程, 帮助理解隔离级别为什么重要:

MVCC 多版本并发控制

前面多次提到 MVCC, 这里简要介绍它的原理

INFO

MVCC(Multi-Version Concurrency Control, 多版本并发控制)是 InnoDB 实现高并发读写的核心机制, 它的核心思想是: 给数据维护多个历史版本, 读操作读某个时间点的"快照", 写操作创建新版本, 从而让读不阻塞写、写不阻塞读, 大幅提升并发能力

它主要由三部分协作实现:

快照读(Snapshot Read): 普通的 SELECT 语句读的是数据的历史快照版本, 不加锁, 与之相对的"当前读"(如 SELECT ... FOR UPDATE、UPDATE、DELETE)读的是最新版本并加锁
undo log 版本链: 每次修改数据时, 旧版本数据被存入 undo log, 通过隐藏的回滚指针串成一条版本链, 记录了这一行的历史变迁
ReadView(读视图): 事务执行快照读时生成的一致性视图, 记录了当前活跃事务的信息, InnoDB 据此判断版本链中的哪个版本对当前事务可见, 从而保证读到符合隔离级别要求的数据

简单说: REPEATABLE READ 下事务第一次快照读时生成 ReadView 并固定下来, 之后整个事务都用这个视图, 所以多次读结果一致; READ COMMITTED 下则每次快照读都重新生成 ReadView, 所以能读到别人最新提交的数据

锁机制简介

MVCC 解决了快照读的并发问题, 但涉及写操作和当前读时, 还需要锁来保证正确性, InnoDB 的锁体系比较丰富, 这里做个概览

锁类型	说明
共享锁(S 锁)	也叫读锁, 多个事务可同时持有同一资源的 S 锁, 彼此不冲突, 手动加锁: `SELECT ... LOCK IN SHARE MODE`(8.0 推荐 `FOR SHARE`)
排他锁(X 锁)	也叫写锁, 一个事务持有 X 锁时, 其他事务不能再加任何锁, UPDATE/DELETE 自动加 X 锁, 也可 `SELECT ... FOR UPDATE` 手动加
行锁	锁定具体的某一行或某几行, 粒度小, 并发度高, InnoDB 默认基于索引实现行锁
表锁	锁定整张表, 粒度大, 并发度低, 当 SQL 没用到索引时, 行锁可能升级为表锁
间隙锁(Gap Lock)	锁定索引记录之间的"间隙", 不锁记录本身, 用于阻止其他事务在间隙中插入新行, 是防幻读的关键
临键锁(Next-Key Lock)	行锁 + 间隙锁的组合, 既锁住记录本身, 又锁住前面的间隙, InnoDB 在 RR 级别下当前读默认使用它来解决幻读

WARNING

死锁(Deadlock): 当两个事务互相持有对方需要的锁, 并都在等待对方释放时, 就形成死锁, 谁也无法继续

例如事务 A 锁住了行 1 等待行 2, 事务 B 锁住了行 2 等待行 1, 双方僵持

InnoDB 内置了死锁检测机制, 发现死锁后会自动选择一个"代价较小"的事务进行回滚, 让另一个事务得以继续, 并向被回滚的事务返回 Deadlock found 错误, 开发时应注意: 让多个事务以相同的顺序访问资源, 尽量缩短事务持锁时间, 可以有效降低死锁概率

触发器

讲完了事务和并发控制, 我们来看另一类自动化机制 —— 触发器, 它让数据库能在特定数据变更时自动执行一段预定义的逻辑

什么是触发器

触发器(Trigger)是一种特殊的存储过程, 它不需要手动调用, 而是绑定在某张表上, 当对该表执行 INSERT、UPDATE 或 DELETE 操作时, 由数据库自动触发执行

它常用于以下场景:

数据校验与规范化(如插入前统一格式)
维护审计日志(记录谁在什么时候改了什么)
自动维护冗余/统计字段(如更新订单时同步汇总金额)

创建触发器

触发器由两个维度组合定义: 触发时机(BEFORE/AFTER)和触发事件(INSERT/UPDATE/DELETE), 共有六种组合

时机 \ 事件	INSERT	UPDATE	DELETE
BEFORE	插入前	更新前	删除前
AFTER	插入后	更新后	删除后

触发器内部可以用两个特殊关键字访问受影响行的数据:

关键字	可用于	含义
`NEW`	INSERT、UPDATE	代表即将插入或更新后的新行, 可读可改(仅 BEFORE 中可改)
`OLD`	UPDATE、DELETE	代表更新前或删除前的旧行, 只读

记忆方法: INSERT 只有 NEW(没有旧值), DELETE 只有 OLD(没有新值), UPDATE 两者都有

案例一: 插入订单前规范化数据, 在订单写入前, 自动把空的下单时间补上当前时间, 并校验金额, 这里用 BEFORE INSERT:

sql

DELIMITER $$

CREATE TRIGGER trg_orders_before_insert
BEFORE INSERT ON Orders
FOR EACH ROW
BEGIN
    -- 如果没有指定下单日期, 自动填充为当前日期
    IF NEW.order_date IS NULL THEN
        SET NEW.order_date = NOW();
    END IF;
END$$

DELIMITER ;

TIP

DELIMITER $$ 的作用是临时把语句结束符从分号改成 $$, 这样触发器体内部的分号才不会被提前当作结束符, 定义完成后再用 DELIMITER ; 改回来

案例二: 维护审计日志表, 当 Products 表的价格被修改时, 自动往审计表里记录一条变更历史, 这里用 AFTER UPDATE, 同时用到 OLD 和 NEW:

sql

-- 先准备一张审计日志表
CREATE TABLE ProductAudit (
    audit_id   INT          NOT NULL AUTO_INCREMENT,
    prod_id    CHAR(10)     NOT NULL,
    old_price  DECIMAL(8,2),
    new_price  DECIMAL(8,2),
    changed_at DATETIME     NOT NULL,
    PRIMARY KEY (audit_id)
);

DELIMITER $$

CREATE TRIGGER trg_products_after_update
AFTER UPDATE ON Products
FOR EACH ROW
BEGIN
    -- 只有价格真的发生变化时才记录
    IF OLD.prod_price <> NEW.prod_price THEN
        INSERT INTO ProductAudit (prod_id, old_price, new_price, changed_at)
        VALUES (OLD.prod_id, OLD.prod_price, NEW.prod_price, NOW());
    END IF;
END$$

DELIMITER ;

之后任何一次价格更新, 都会自动在 ProductAudit 留下痕迹:

sql

UPDATE Products SET prod_price = 19.99 WHERE prod_id = 'P001';
-- 触发器自动向 ProductAudit 插入一条 old_price -> new_price 的记录

SELECT * FROM ProductAudit;

查看和删除触发器:

sql

-- 查看当前数据库的所有触发器
SHOW TRIGGERS;

-- 删除触发器
DROP TRIGGER IF EXISTS trg_products_after_update;

触发器的弊端

触发器看起来很方便, 但在实际的互联网工程实践中, 大厂往往明确禁止或严格限制使用触发器, 原因如下:

WARNING

触发器虽然能自动化一些逻辑, 但存在明显的弊端, 需要谨慎使用:

隐藏业务逻辑: 触发器在后台默默执行, 不出现在应用代码里, 一条简单的 UPDATE 背后可能连锁触发了一堆看不见的操作, 排查问题时极易遗漏, 维护者常常一脸困惑
难以调试和测试: 触发器没有像应用代码那样完善的调试、日志、单元测试手段, 出了问题难以定位
影响性能: 触发器与触发它的 SQL 在同一个事务中同步执行, 会拉长事务时间、增加锁持有时长, 在高并发写入场景下, 触发器可能成为性能瓶颈
不易迁移和扩展: 业务逻辑分散在数据库层, 不利于服务化拆分和水平扩展

因此, 现代互联网架构更推荐把这类逻辑放到应用层显式处理(如在 Service 代码里记录审计日志、做数据校验), 让逻辑清晰可见、可测可控, 触发器更适合数据量不大、逻辑简单且追求强一致的传统系统

其他数据库的差异

不同数据库的触发器在语法和能力上有所差异:

SQL Server: 触发器内用 INSERTED 和 DELETED 两张虚拟表来访问变更数据, 而非 NEW/OLD, 且默认是语句级触发器(一条语句触发一次), 而非 MySQL 的行级触发器(每影响一行触发一次), SQL Server 还支持 INSTEAD OF 触发器, 用自定义逻辑替代原本的操作
Oracle: 同样使用 :NEW 和 :OLD(注意带冒号前缀), 功能更强大, 支持行级和语句级触发器、INSTEAD OF 触发器, 以及在一个触发器里用 INSERTING/UPDATING/DELETING 谓词区分不同操作
MySQL: 相对简洁, 只支持行级触发器(FOR EACH ROW), 不支持语句级和 INSTEAD OF 触发器, 一张表对同一时机同一事件早期只能有一个触发器(8.0 起放宽了此限制)

理解了触发器的能力边界和代价, 才能在合适的场景做出正确取舍

索引

前面我们写的查询, 数据量小的时候随便怎么写都很快, 可一旦表里有几百万行, 一条 WHERE cust_email = 'a@b.com' 就可能让数据库把整张表从头到尾扫一遍, 索引就是为了解决这个问题而生的

什么是索引

想象一本几百页的技术书, 你想找"索引失效"这一节, 如果没有目录, 你只能一页一页翻, 这叫全表扫描, 但书的前面有目录, 你先在目录里定位到"索引失效, 第 286 页", 直接翻过去就行, 索引在数据库里扮演的就是目录的角色

更准确地说, 索引是一种为加速查询而单独维护的数据结构, 它把表中某一列(或几列)的值, 按照便于查找的方式组织起来, 并记录每个值对应数据行的位置, 查询时数据库先在这个小而有序的结构里快速定位, 再去取真正的数据行, 避免逐行扫描

TIP

索引的本质是空间换时间, 它额外占用磁盘存储, 也会拖慢写入(每次 INSERT/UPDATE/DELETE 都要同步维护索引), 但能把查询从 O(n) 的全表扫描降到接近 O(log n), 所以索引不是越多越好, 而是该建的地方建、不该建的地方别乱建

为什么用 B+ 树

要"快速定位", 能用的数据结构其实不少, 为什么数据库几乎都选了 B+ 树, 而不是二叉树或哈希表? 关键在于数据库的数据存在磁盘上, 而磁盘 IO 是最大的性能瓶颈, 每读一个磁盘页(InnoDB 默认 16KB)就是一次 IO, 我们要做的是尽量减少 IO 次数, 也就是让树尽可能"矮"

来逐个看候选者的问题:

数据结构	核心问题	是否适合数据库
二叉搜索树 (BST)	极端情况下退化成链表, 高度变成 O(n)	否
平衡二叉树 (AVL)	每个节点只有 2 个分叉, 数据量大时树太高, IO 次数多	否
红黑树	同样是二叉, 树高问题依旧, 大数据量下高度可观	否
B 树	多叉、矮胖, 但非叶子节点也存数据, 单页能放的键变少	一般
Hash	等值查询 O(1) 极快, 但不支持范围查询和排序	部分场景
B+ 树	多叉矮胖、非叶子只存键、叶子用链表串联	是

二叉树系列最大的问题就是"瘦高": 每个节点只有两个孩子, 存 2000 万行数据, 树高大约要 24 层, 最坏要 24 次磁盘 IO, 而 B+ 树是多叉的, 一个节点能放上百个键, 同样 2000 万行通常只要 3 层左右就够了, 查一次数据最多 3 次 IO

Hash 索引等值查询确实快, 但它有个致命短板: 哈希之后数据是无序的, 没法做 BETWEEN、>、ORDER BY 这类范围和排序操作, 而这些在业务里太常见了

B+ 树相比 B 树又做了两点关键优化:

非叶子节点只存键(索引), 不存数据, 这样一个磁盘页能塞下更多的键, 树就更矮, IO 更少
所有数据都在叶子节点, 且叶子节点之间用双向链表串联, 这让范围查询变得极其高效: 定位到起点叶子后, 顺着链表往后扫即可, 不用再回到根节点

这两点正好命中了数据库"减少 IO + 高效范围查询"的核心诉求, 所以 B+ 树成了 InnoDB 的默认选择

B+ 树结构图

下面用一棵简化的 B+ 树说明它的结构, 假设叶子节点存的是订单号 order_num, 非叶子节点只存用来导航的键:

以查找 order_num = 45 为例, 走查过程是这样的:

从根节点 [30 | 60] 开始, 45 落在 30 和 60 之间, 走中间那条指针, 到达中间节点 [40 | 50]
在 [40 | 50] 里, 45 落在 40 和 50 之间, 走中间指针, 到达叶子节点 45,50
在叶子里找到 45, 命中, 整个过程只走了 3 层, 也就是最多 3 次磁盘 IO

如果是范围查询 order_num BETWEEN 45 AND 70, 先用上面的方式定位到值为 45 的叶子, 然后顺着叶子节点之间的链表一路往右扫, 经过 55,60、65,70, 取到所有满足条件的值即可, 完全不需要再回到根节点重新查找, 这正是叶子链表带来的好处

聚簇索引与非聚簇索引

理解了 B+ 树, 接下来最重要的概念就是: 叶子节点里到底存的是什么? 这决定了聚簇索引和二级索引的区别, 也是 InnoDB 的核心设计

聚簇索引

聚簇索引(Clustered Index)的叶子节点直接存储整行数据, 在 InnoDB 中, 表数据本身就是按主键组织成一棵 B+ 树存放的, 主键就是聚簇索引, 换句话说, 数据即索引, 索引即数据, 一张表只能有一个聚簇索引

以 Orders 表为例, 主键是 order_num, 它的聚簇索引大致是这样:

因为叶子节点已经是整行数据, 所以用主键查询时, 找到叶子就拿到了所有列, 一步到位, 非常快

INFO

如果建表时没有显式定义主键, InnoDB 会按以下顺序选一个作为聚簇索引: 第一个非空唯一索引(NOT NULL UNIQUE); 如果也没有, 就自动生成一个隐藏的 6 字节 ROWID 作为聚簇索引, 所以 InnoDB 表一定有聚簇索引, 区别只在于你是否能控制它, 建议总是显式定义一个自增主键

二级索引与回表

除主键外, 你在其他列上建的索引都叫二级索引(Secondary Index), 也叫非聚簇索引或辅助索引, 它和聚簇索引最大的不同是: 二级索引的叶子节点不存整行数据, 只存索引列的值 + 对应的主键值

比如我们在 cust_email 上建一个二级索引, 执行:

sql

SELECT * FROM Customers WHERE cust_email = 'tom@example.com';

数据库的实际查找过程是:

这个"先在二级索引查到主键, 再拿主键去聚簇索引取整行"的过程, 就叫回表(Back to Table), 回表意味着要查两棵 B+ 树, 多一次查找开销, 后面讲覆盖索引时, 我们会看到怎么避免它

INFO

MyISAM 和 InnoDB 的索引存储方式截然不同, MyISAM 的索引和数据是分开存储的: 索引文件(.MYI)的叶子节点存的是数据行的物理地址, 数据文件(.MYD)单独存放, 因此 MyISAM 的主键索引和二级索引结构上没有本质区别, 都需要按地址去数据文件取行, 也就不存在 InnoDB 那种"聚簇 + 回表"的概念, InnoDB 则是聚簇索引把数据和主键索引合二为一, 二级索引指向主键

联合索引与最左前缀原则

很多时候查询条件不止一列, 这时可以建联合索引(复合索引), 即一个索引包含多个列:

sql

CREATE INDEX idx_name_state_email
ON Customers (cust_name, cust_state, cust_email);

联合索引 (cust_name, cust_state, cust_email) 在 B+ 树里是先按 cust_name 排序, cust_name 相同再按 cust_state 排序, 再相同才按 cust_email 排序, 就像字典里先按拼音首字母排, 首字母相同再看第二个字母

正因为这个排序规则, 联合索引的使用必须遵守最左前缀原则: 查询条件必须从联合索引的最左列开始, 且不能跳过中间的列, 才能用上索引

下面用这个 (cust_name, cust_state, cust_email) 索引, 列举各种查询能否命中:

查询条件	能否用到索引	说明
`WHERE cust_name = 'Tom'`	能	命中最左列
`WHERE cust_name = 'Tom' AND cust_state = 'NY'`	能	命中前两列
`WHERE cust_name = 'Tom' AND cust_state = 'NY' AND cust_email = 'a@b.com'`	能	三列全命中
`WHERE cust_name = 'Tom' AND cust_email = 'a@b.com'`	部分	只用到 cust_name, 跳过了 cust_state, email 用不上
`WHERE cust_state = 'NY'`	不能	没有最左列 cust_name
`WHERE cust_state = 'NY' AND cust_email = 'a@b.com'`	不能	缺最左列, 整个索引失效
`WHERE cust_name = 'Tom' AND cust_state > 'NY' AND cust_email = 'a@b.com'`	部分	范围列 cust_state 之后的 email 用不上

WARNING

最左前缀有两个高频踩坑点:

跳列: 缺了中间列, 后面的列就用不上, cust_name + cust_email(跳过 cust_state)只能用到 cust_name 这一段
范围列截断: 遇到范围查询(>、<、BETWEEN、LIKE)的那一列还能用索引, 但它右边的列就用不上索引排序了, 所以设计联合索引时, 应把等值查询的列放左边、范围查询的列放右边

需要说明的是, 这里的"列顺序"指的是索引定义里的顺序, 而不是 WHERE 里写的顺序, WHERE cust_state = 'NY' AND cust_name = 'Tom' 和 WHERE cust_name = 'Tom' AND cust_state = 'NY' 是等价的, MySQL 优化器会自动调整, 都能命中索引

覆盖索引

回到前面的回表问题, 如果一个查询所需要的全部列, 都已经包含在索引里了, 那数据库直接从二级索引的叶子节点就能拿到所有数据, 根本不用回表去查聚簇索引, 这种情况叫覆盖索引(Covering Index)

还是用 (cust_name, cust_state, cust_email) 这个联合索引举例:

sql

-- 只查索引里有的列, 构成覆盖索引, 无需回表
SELECT cust_name, cust_state, cust_email
FROM Customers
WHERE cust_name = 'Tom';

-- 查了 cust_id 之外的 cust_address(不在索引里), 需要回表
SELECT cust_name, cust_address
FROM Customers
WHERE cust_name = 'Tom';

第一条 SQL 要的三列全在索引里(顺带一提, 二级索引叶子还存着主键, 所以连 cust_id 一起取也算覆盖), 在索引上就读完了, 这就是覆盖索引

判断是否命中覆盖索引, 看 EXPLAIN 的 Extra 列:

sql

EXPLAIN SELECT cust_name, cust_state
FROM Customers WHERE cust_name = 'Tom';

如果 Extra 里出现 Using index, 就说明这次查询是覆盖索引, 没有回表, 注意别和 Using index condition(索引下推)搞混, 后者是另一回事

TIP

覆盖索引是很实用的优化手段, 当你发现某个高频查询总要回表, 可以考虑把它要查的列加进联合索引, 用空间换掉回表开销, 但也别把太多大字段塞进索引, 那会让索引体积膨胀、写入变慢

其他索引类型

除了普通索引和联合索引, MySQL 还有几种特殊用途的索引, 简单对比一下:

索引类型	作用	典型语法 / 说明
唯一索引 (UNIQUE)	保证列值唯一, 同时具备索引加速能力	`CREATE UNIQUE INDEX idx ON t(col)`
主键索引 (PRIMARY)	特殊的唯一索引, 不允许 NULL, 即聚簇索引	`PRIMARY KEY (id)`
前缀索引	只对长字符串的前 N 个字符建索引, 省空间	`CREATE INDEX idx ON t(col(20))`
全文索引 (FULLTEXT)	对文本做关键词检索, 配合 `MATCH ... AGAINST`	适合文章、描述等长文本搜索
哈希索引 (HASH)	等值查询极快, 不支持范围	InnoDB 不支持显式建, Memory 引擎支持

INFO

InnoDB 有个"自适应哈希索引(Adaptive Hash Index)"特性, 它会自动监控热点页, 在内存里为其建立哈希索引来加速等值查询, 整个过程由引擎自动管理, 不需要你手动创建

创建索引

创建索引主要有三种方式, 用 Products 表演示

1. 单独用 CREATE INDEX 创建

sql

-- 普通索引
CREATE INDEX idx_prod_name ON Products (prod_name);

-- 唯一索引
CREATE UNIQUE INDEX idx_prod_name_uniq ON Products (prod_name);

-- 联合索引
CREATE INDEX idx_vend_price ON Products (vend_id, prod_price);

-- 前缀索引(对 prod_name 前 10 个字符建索引)
CREATE INDEX idx_prod_name_prefix ON Products (prod_name(10));

2. 用 ALTER TABLE 添加

sql

ALTER TABLE Products ADD INDEX idx_vend (vend_id);

ALTER TABLE Products ADD UNIQUE INDEX idx_prod_name_uniq (prod_name);

-- 添加主键(聚簇索引)
ALTER TABLE Products ADD PRIMARY KEY (prod_id);

3. 建表时直接定义

sql

CREATE TABLE Products (
    prod_id    CHAR(10)      NOT NULL,
    vend_id    CHAR(10)      NOT NULL,
    prod_name  VARCHAR(255)  NOT NULL,
    prod_price DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (prod_id),
    INDEX idx_vend (vend_id),
    INDEX idx_vend_price (vend_id, prod_price)
);

不同数据库在创建索引上的语法基本一致, 但全文索引等特性差异较大:

MySQLPostgreSQLSQL Server

sql

-- 普通索引
CREATE INDEX idx_prod_name ON Products (prod_name);
-- 全文索引
CREATE FULLTEXT INDEX idx_ft ON Products (prod_name);

sql

-- 普通索引
CREATE INDEX idx_prod_name ON Products (prod_name);
-- 可指定索引方法, 全文检索用 GIN
CREATE INDEX idx_ft ON Products USING GIN (to_tsvector('english', prod_name));

sql

-- 普通索引
CREATE INDEX idx_prod_name ON Products (prod_name);
-- 全文索引需先建全文目录, 语法差异较大
CREATE FULLTEXT INDEX ON Products (prod_name) KEY INDEX PK_Products;

查看与删除索引

查看一张表上有哪些索引:

sql

SHOW INDEX FROM Products;

输出里几个关键列要会看: Key_name 是索引名, Column_name 是索引列, Seq_in_index 是该列在联合索引里的位置, Non_unique 为 0 表示唯一索引, Cardinality 是基数(该列不重复值的估算数量, 越大越适合建索引)

删除索引同样有两种写法:

sql

DROP INDEX idx_prod_name ON Products;

-- 等价的 ALTER 写法
ALTER TABLE Products DROP INDEX idx_prod_name;

索引失效的常见场景

建了索引不代表查询就一定会走索引, 很多时候是写法本身让优化器没法用上索引, 这就是索引失效, 也是面试里的高频考点, 下面逐个拆解, 每种都给出反例和正确写法, 假设 Customers.cust_name 上有索引

1. 对索引列使用函数或运算

一旦把函数或计算施加在索引列上, B+ 树是按列的原始值排序的, 算完之后的值无序, 索引就用不上了

sql

-- 失效: 对索引列 order_date 用了函数
SELECT * FROM Orders WHERE YEAR(order_date) = 2026;

-- 正确: 改写成范围查询, 让索引列保持"裸露"
SELECT * FROM Orders
WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01';

2. 列参与计算

和上一条同理, 只要索引列出现在表达式里参与运算, 就会失效

sql

-- 失效: prod_price 参与了乘法
SELECT * FROM Products WHERE prod_price * 2 > 100;

-- 正确: 把计算挪到常量一侧
SELECT * FROM Products WHERE prod_price > 50;

3. 隐式类型转换

如果索引列是字符串类型, 查询时却传了数字, MySQL 会把字符串列隐式转成数字再比较, 等价于在列上套了一层函数, 索引失效

sql

-- 假设 cust_id 是 CHAR 类型
-- 失效: 传了数字, 触发隐式转换
SELECT * FROM Customers WHERE cust_id = 10003;

-- 正确: 传字符串, 类型匹配
SELECT * FROM Customers WHERE cust_id = '10003';

4. LIKE 以 % 开头

B+ 树是从左往右按前缀排序的, LIKE 'abc%' 能用索引(本质是前缀匹配), 但 LIKE '%abc' 因为不知道开头是什么, 只能全表扫描

sql

-- 失效: 以 % 开头
SELECT * FROM Customers WHERE cust_name LIKE '%Tom';

-- 可用索引: 前缀匹配
SELECT * FROM Customers WHERE cust_name LIKE 'Tom%';

5. OR 连接了非索引列

用 OR 时, 只要有一个条件列没有索引, 整条查询往往就退化成全表扫描, 因为它得把所有行都检查一遍才能确定

sql

-- 失效: cust_name 有索引, 但 cust_address 没有, 整体走全表
SELECT * FROM Customers
WHERE cust_name = 'Tom' OR cust_address = 'NewYork';

-- 改善: 给 cust_address 也建索引, 或改用 UNION 拆分
SELECT * FROM Customers WHERE cust_name = 'Tom'
UNION
SELECT * FROM Customers WHERE cust_address = 'NewYork';

6. 不符合最左前缀

联合索引 (cust_name, cust_state, cust_email), 如果查询不从最左列开始, 索引就用不上, 这点前面已经详细讲过

sql

-- 失效: 缺最左列 cust_name
SELECT * FROM Customers WHERE cust_state = 'NY';

-- 命中: 从最左列开始
SELECT * FROM Customers WHERE cust_name = 'Tom' AND cust_state = 'NY';

7. 使用 != / NOT IN / IS NULL 等

!=、<>、NOT IN、NOT LIKE 这类否定条件, 往往要扫描大量不匹配的行, 优化器经常判断走索引还不如全表扫描, 于是放弃索引, IS NULL / IS NOT NULL 是否走索引则要看具体数据分布和版本

sql

-- 大概率失效
SELECT * FROM Customers WHERE cust_state != 'NY';
SELECT * FROM Orders WHERE cust_id NOT IN (10001, 10002);

DANGER

索引失效大多不会报错, 查询照样能跑出正确结果, 只是悄悄地从"走索引"退化成了"全表扫描", 数据量小时根本看不出来, 一上线到大表就变成慢查询, 判断有没有走索引, 不要靠猜, 一律用 EXPLAIN 看 type 和 key 列: type 出现 ALL 就是全表扫描, key 为 NULL 就是没用上任何索引, 养成对慢 SQL 先 EXPLAIN 的习惯, 能避开绝大多数索引坑

数据库设计范式

前面我们学了那么多查询和操作语句, 但有个更前置的问题一直没碰: 表到底该怎么设计? 字段怎么拆、怎么放, 直接决定了后面的 SQL 写起来是顺手还是处处掣肘, 范式(Normal Form)就是前人总结出来的一套设计指导, 核心目的只有一个: 减少数据冗余, 避免更新异常

三大范式

范式是逐级递进的, 满足第二范式的前提是先满足第一范式, 以此类推, 实际工作中我们主要关注前三个

第一范式 1NF: 列的原子性

第一范式要求每一列都是不可再分的原子值, 不能在一个字段里塞多个值

来看一个反例, 假设我们要存订单的收货信息:

order_id	user_name	address
1001	张三	浙江省, 杭州市, 西湖区文三路 100 号
1002	李四	江苏省, 南京市, 玄武区中山路 50 号

这里的 address 把省、市、区、详细地址全揉在一个字段里, 如果业务上需要"统计杭州市的订单量", 就只能用 LIKE '%杭州市%' 去模糊匹配, 既慢又不可靠, 这就违反了 1NF

修正的办法是把它拆成原子列:

order_id	user_name	province	city	district	detail
1001	张三	浙江省	杭州市	西湖区	文三路 100 号
1002	李四	江苏省	南京市	玄武区	中山路 50 号

现在按省、市筛选都能直接用等值条件, 也能加索引了

WARNING

"原子性"要结合业务判断, 不是越细越好, 如果你的业务从来不会单独查询省市区, 那把地址整体存成一个字段反而更简单, 设计永远服务于查询场景

第二范式 2NF: 消除部分依赖

第二范式建立在 1NF 之上, 要求非主键字段必须完全依赖于整个主键, 而不能只依赖主键的一部分, 这个问题只在联合主键时才会出现

看一个订单明细的反例, 主键是 (order_id, product_id) 联合主键:

order_id	product_id	product_name	quantity	order_time
1001	2001	机械键盘	2	2026-06-10
1001	2002	无线鼠标	1	2026-06-10
1002	2001	机械键盘	3	2026-06-11

问题出在哪? product_name 只依赖 product_id(商品名只跟商品有关), order_time 只依赖 order_id(下单时间只跟订单有关), 它们都只依赖联合主键的一部分, 这就是"部分依赖"

带来的麻烦: 机械键盘改名为"机械键盘 Pro", 你得把所有出现过这个商品的明细行全部更新; 商品名在多行重复存储, 纯属浪费

修正办法是拆表, 让每张表的非主键字段都完全依赖各自的主键:

sql

-- 订单表: order_time 完全依赖 order_id
CREATE TABLE `order` (
  order_id   BIGINT UNSIGNED PRIMARY KEY,
  order_time DATETIME
);

-- 商品表: product_name 完全依赖 product_id
CREATE TABLE product (
  product_id   BIGINT UNSIGNED PRIMARY KEY,
  product_name VARCHAR(128)
);

-- 订单明细表: quantity 才是真正依赖 (order_id, product_id) 的
CREATE TABLE order_item (
  order_id   BIGINT UNSIGNED,
  product_id BIGINT UNSIGNED,
  quantity   INT,
  PRIMARY KEY (order_id, product_id)
);

第三范式 3NF: 消除传递依赖

第三范式建立在 2NF 之上, 要求非主键字段之间不能存在传递依赖, 也就是非主键字段不能依赖另一个非主键字段

反例, 一张订单表里冗余了用户的所属城市信息:

order_id	user_id	user_city	city_manager
1001	501	杭州	王经理
1002	502	杭州	王经理
1003	503	南京	赵经理

依赖链是: order_id → user_id → user_city → city_manager, city_manager(城市负责人)依赖 user_city, 而 user_city 又依赖 user_id, 主键是通过 user_city 间接决定 city_manager 的, 这就是传递依赖

问题: 杭州换了负责人, 你要更新所有杭州用户的订单行; 城市和负责人的对应关系被反复冗余存储

修正办法是把传递依赖的部分抽出去单独建表:

sql

-- 订单表只保留 user_id
CREATE TABLE `order` (
  order_id BIGINT UNSIGNED PRIMARY KEY,
  user_id  BIGINT UNSIGNED
);

-- 城市与负责人的关系独立维护
CREATE TABLE city (
  city_name    VARCHAR(32) PRIMARY KEY,
  city_manager VARCHAR(32)
);

把三大范式串起来看, 它们本质上都在做同一件事: 让每个事实只在一个地方存储一次, 这样更新时只改一处, 不会出现数据自相矛盾的情况

反范式化

范式让数据干净无冗余, 但它有个代价: 数据被拆得越散, 查询时需要的联结(JOIN)就越多, 在高并发的互联网场景下, 多表 JOIN 往往是性能杀手, 于是就有了反范式化(Denormalization)——有意地引入冗余字段, 用空间换时间

INFO

最典型的例子就是订单表冗余商品名, 严格按范式设计, 订单明细只存 product_id, 展示订单时需要 JOIN 商品表才能拿到商品名, 但商品名一旦确定, 历史订单里它就不该再变(用户买的时候叫什么, 就该一直显示什么), 所以实践中我们会在订单明细表里直接冗余一份下单时的 product_name 和 product_price 快照:

性能收益: 查订单列表不用再 JOIN 商品表, 单表查询直接出结果
业务正确性: 商品后续改名、调价, 都不影响历史订单的展示, 这恰恰是业务需要的"留痕"
代价: 商品名存了多份, 占用额外空间; 写入时要多维护一个字段

权衡下来, 这点冗余完全值得

反范式化不是否定范式, 而是在理解范式的基础上做有意识的取舍, 一般的原则是: 先按三范式把表设计干净, 再针对明确的性能瓶颈做局部反范式, 冗余字段一旦引入, 就要承担数据一致性的维护责任(比如靠应用层双写、或定时任务校准), 切忌为了图省事而到处冗余

建表设计最佳实践

范式解决的是"表怎么拆", 这一节解决的是"字段怎么定", 下面这套规约大量参考了阿里巴巴《Java 开发手册》的数据库规约, 是经过大规模生产环境验证的经验, 建议当成团队规范来执行

字段设计规约

编号	规约	说明
1	主键用 `BIGINT UNSIGNED` 自增	单表自增主键写入有序, 不会引起页分裂; 分布式场景用雪花算法等有序 ID
2	金额用 `DECIMAL`	精确存储, 绝不用 FLOAT/DOUBLE
3	字符串用 `VARCHAR(n)`, 字符集 `utf8mb4`	n 按业务预估上限设定; utf8mb4 支持 emoji 和完整 Unicode
4	字段尽量 `NOT NULL` 并给 `DEFAULT`	NULL 占用额外空间、影响索引和聚合统计
5	布尔语义用 `TINYINT(1)`	0 表示否, 1 表示是; 不要用 BIT
6	时间用 `DATETIME`	需要毫秒精度用 `DATETIME(3)`
7	表必备三字段	`id`、`created_at`、`updated_at`

下面逐条展开几个容易踩坑的点

主键不要用 UUID

WARNING

InnoDB 的主键索引是聚簇索引, 数据行就是按主键顺序物理存储的, 自增主键意味着每次插入都追加在最后, 顺序写入, 效率很高, 而 UUID 是无序的, 新插入的主键值可能落在已有数据的中间, InnoDB 就得在已经写满的数据页里腾位置, 触发页分裂(page split), 导致大量随机 I/O 和空间碎片, 写入性能随数据量增长急剧下降

所以单库单表场景首选 BIGINT UNSIGNED AUTO_INCREMENT; 确实需要全局唯一 ID 的分布式场景, 用**雪花算法(Snowflake)**这类趋势递增的 ID, 而不是无序的 UUID

金额禁用 FLOAT/DOUBLE

FLOAT 和 DOUBLE 是浮点数, 底层用二进制近似表示十进制小数, 会丢精度, 经典的 0.1 + 0.2 != 0.3 问题在金额计算里是灾难, 一律用 DECIMAL(M, D), 比如 DECIMAL(10, 2) 表示总共 10 位、小数 2 位, 精确存储分

sql

-- 错误示范
price FLOAT,
-- 正确做法
price DECIMAL(10, 2) NOT NULL DEFAULT 0.00,

尽量 NOT NULL

NULL 的麻烦比想象中多: 它在索引里需要额外存储, 在 COUNT、SUM 等聚合中会被跳过导致结果出乎意料, WHERE col = NULL 还永远不成立(必须用 IS NULL), 能用 NOT NULL + 一个有业务含义的默认值(比如空字符串 ''、0)表达的, 就别用 NULL

时间字段的取舍

DATETIME 存的是字面时间, 与时区无关, 范围大(1000 到 9999 年), 占 8 字节; TIMESTAMP 存的是 UTC 时间戳, 自动随时区转换, 但范围只到 2038 年(著名的 2038 问题), 占 4 字节, 绝大多数业务用 DATETIME 更省心, 不用担心时区和年限的坑, 需要记录精确到毫秒的, 用 DATETIME(3)

命名规约

好的命名能让 SQL 自我解释, 省下大量沟通成本

表名、字段名一律小写字母 + 下划线分隔, 如 order_item、created_at, MySQL 在某些系统上大小写敏感, 全小写可以避免跨平台迁移的坑
见名知意, 杜绝拼音、缩写黑话, user_name 而不是 u_nm
布尔语义字段统一用 is_ 前缀, 如 is_deleted、is_paid, 一眼就知道是 0/1 标志位
禁用数据库保留字作字段名, 如 order、desc、status 中的部分关键字, order 这种实在要用就得反引号包起来, 很别扭, 不如直接叫 order_info
索引命名也要有规范: 主键 PRIMARY, 唯一索引 uk_字段名, 普通索引 idx_字段名

索引规约

索引用好了是查询利器, 用滥了拖垮写入, 几条核心原则:

单表索引数量控制在 5 个以内, 每个索引都是一棵 B+ 树, 写入时都要同步维护, 索引越多写越慢
联合索引遵循最左前缀, 字段顺序很关键: 区分度高的列放前面, 区分度是指该列不重复值的比例, 比如 user_id 区分度高, gender 区分度极低(只有几种值), 把 user_id 放前面, 索引能快速缩小范围
超长 VARCHAR 用前缀索引, 给一个 VARCHAR(255) 的列建全列索引太占空间, 可以只取前 N 个字符: KEY idx_name (name(20)), 在区分度和体积间取平衡
区分度极低的列(如状态、性别)单独建索引意义不大, 因为优化器可能直接选择全表扫描更快

一张规范的建表 DDL 范例

把上面所有规约落到实处, 下面是一张可以直接拿去参考的标准电商订单表, 注意它的每个细节: 字段类型、NOT NULL、DEFAULT、COMMENT 注释、三字段、以及索引设计

sql

CREATE TABLE `order` (
  `id`             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `order_no`       VARCHAR(32)     NOT NULL                COMMENT '订单编号, 业务唯一',
  `user_id`        BIGINT UNSIGNED NOT NULL                COMMENT '下单用户ID',
  `total_amount`   DECIMAL(10, 2)  NOT NULL DEFAULT 0.00   COMMENT '订单总金额, 单位元',
  `pay_amount`     DECIMAL(10, 2)  NOT NULL DEFAULT 0.00   COMMENT '实付金额, 单位元',
  `status`         TINYINT         NOT NULL DEFAULT 0      COMMENT '订单状态: 0待支付 1已支付 2已发货 3已完成 4已取消',
  `is_deleted`     TINYINT(1)      NOT NULL DEFAULT 0      COMMENT '逻辑删除: 0未删除 1已删除',
  `remark`         VARCHAR(255)    NOT NULL DEFAULT ''     COMMENT '订单备注',
  `pay_time`       DATETIME                 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_order_no` (`order_no`),
  KEY `idx_user_status` (`user_id`, `status`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='订单表';

这张表里值得细品的几处设计:

id 是自增主键, order_no 是业务唯一编号, 二者分离——主键管物理存储有序, 业务编号给外部系统使用, 各司其职
所有字段几乎都 NOT NULL 且带 DEFAULT, 只有 pay_time 在未支付时确实没有值, 才允许 NULL
created_at 和 updated_at 用了 CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP, 数据库自动维护, 应用层完全不用操心时间字段
idx_user_status 是联合索引, 把高区分度的 user_id 放前面, 既能支持"查某用户的所有订单", 也能支持"查某用户某状态的订单"
每个字段、每张表都有 COMMENT, 半年后回来看也不会一脸茫然

SQL 优化

表建好了, 接下来谈查询性能, SQL 优化不是玄学, 它有清晰的方法论: 先理解一条 SQL 在 MySQL 内部是怎么跑的, 再学会用 EXPLAIN 看清它的执行计划, 最后对症下药

查询执行流程

一条 SELECT 语句从客户端发出到返回结果, 在 MySQL 内部要经过下面这条流水线:

各环节做的事:

连接器: 负责建立连接、校验权限、管理连接状态
分析器: 做词法和语法分析, 判断 SQL 写得对不对, 表和字段存不存在
优化器: 关键角色, 同一条 SQL 可能有多种执行方式(走哪个索引、多表 JOIN 的顺序), 优化器基于成本估算选出它认为最优的执行计划, 我们后面看的 EXPLAIN, 看的就是优化器的决策结果
执行器: 拿着优化器选定的计划, 调用存储引擎的接口去真正读写数据
存储引擎: InnoDB 在这一层, 负责数据的实际存取和索引(B+ 树)的维护

理解这条链路的意义在于: 我们做 SQL 优化, 本质上就是引导优化器选到更好的执行计划, 比如让它走上索引、避免全表扫描

EXPLAIN 执行计划

EXPLAIN 是 SQL 优化最重要的工具, 没有之一, 在任何 SELECT 前面加上 EXPLAIN, MySQL 就会告诉你它打算怎么执行这条语句, 而不会真正执行

sql

EXPLAIN SELECT * FROM `order` WHERE user_id = 501 AND status = 1;

输出是一张表, 关键列含义如下:

列	含义
`id`	查询的序号, 标识执行顺序; id 越大越先执行, id 相同从上往下执行
`select_type`	查询类型, 如 SIMPLE(简单查询)、PRIMARY(主查询)、SUBQUERY(子查询)
`table`	当前行涉及的表
`type`	访问类型, 最重要的列之一, 反映查询走没走索引、效率如何
`possible_keys`	可能用到的索引
`key`	实际选用的索引, 为 NULL 说明没走索引
`key_len`	使用的索引长度, 可判断联合索引用上了几个字段
`rows`	优化器估算需要扫描的行数, 越小越好
`filtered`	按条件过滤后剩余行数的百分比
`Extra`	附加信息, 藏着很多优化线索

重点看 type 列

type 反映了访问数据的方式, 从好到坏有明确的等级排序:

system > const > eq_ref > ref > range > index > ALL

type	含义	评价
`system`	表只有一行(系统表), 极特殊	最优
`const`	通过主键或唯一索引一次命中, 最多返回一行	极好
`eq_ref`	多表 JOIN 时, 对前表每一行在后表用主键/唯一索引精确匹配一行	很好
`ref`	用普通索引等值查询, 可能返回多行	好
`range`	索引范围扫描, 如 `BETWEEN`、`>`、`IN`	还行
`index`	扫描整棵索引树(比全表扫描略好, 但仍扫了全部)	较差
`ALL`	全表扫描, 一行行读	最差, 重点优化对象

DANGER

看到 type = ALL 且 rows 很大, 基本就是性能问题的根源, 这意味着 MySQL 没用上任何索引, 把整张表从头扫到尾, 优化的首要目标, 就是通过加合适的索引、改写 SQL, 把 type 从 ALL 提升到 range、ref 甚至 const

重点看 Extra 列

Extra 是个信息量极大的列, 几个高频值必须认识:

Extra 值	含义	好坏
`Using index`	覆盖索引, 查询所需字段全在索引里, 不用回表读数据行	好, 追求的目标
`Using where`	在存储引擎取回数据后, server 层还要再用 WHERE 过滤	中性
`Using filesort`	额外的排序操作, 无法利用索引完成 ORDER BY, 在内存或磁盘里排序	坏, 需优化
`Using temporary`	用了临时表, 常见于 GROUP BY、DISTINCT、复杂 ORDER BY	坏, 需优化

看到 Using filesort 或 Using temporary, 说明 MySQL 做了额外的重活, 通常可以通过调整索引(让排序/分组字段也走索引)来消除, 而 Using index 则是好消息, 说明命中了覆盖索引, 连数据行都不用读

慢查询定位

线上系统不可能对每条 SQL 都手动 EXPLAIN, 我们需要工具自动揪出慢的那些

慢查询日志(slow query log) 是 MySQL 内置的功能, 开启后会把执行时间超过阈值的 SQL 记录下来:

sql

-- 查看是否开启
SHOW VARIABLES LIKE 'slow_query_log';
-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
-- 设置阈值为 1 秒, 超过的都记录
SET GLOBAL long_query_time = 1;

日志攒一段时间后, 配合 mysqldumpslow 或 pt-query-digest 工具做聚合分析, 就能找出最值得优化的几条 SQL

SHOW PROFILE 可以分析单条 SQL 各阶段的耗时(如发送数据、排序、锁等待), 帮你定位时间到底花在哪一步:

sql

SET profiling = 1;
SELECT * FROM `order` WHERE user_id = 501;
SHOW PROFILES;             -- 列出执行过的语句及总耗时
SHOW PROFILE FOR QUERY 1;  -- 查看指定语句的各阶段耗时

INFO

SHOW PROFILE 在新版本中已标记为废弃, 官方推荐用 performance_schema 来做更精细的性能剖析, 但作为快速排查的小工具, 它依然好用

SQL 优化实战清单

下面这些是可以直接落地的优化手段, 几乎每条都能在日常开发中用到

优化建议

只查需要的列, 不要 SELECT *, 多查的列既浪费网络传输, 又可能让本可走覆盖索引的查询被迫回表
善用覆盖索引避免回表, 如果查询字段都在某个索引里, MySQL 直接从索引返回数据, 不用再去聚簇索引读完整行
小表驱动大表, JOIN 时让数据量小的表作为驱动表, 减少被驱动表的扫描次数
特定场景用 EXISTS 替代 IN, 当子查询结果集很大、外层表较小时, EXISTS 通常更高效; 反之 IN 更好
批量操作代替循环单条, INSERT INTO ... VALUES (...), (...), (...) 一次插入多行, 远快于循环执行多条单行 INSERT
避免在 WHERE 列上做运算或用函数, 否则索引失效(下面详述)
优化深分页, 别让 LIMIT 100000, 10 这种翻页拖垮数据库(下面详述)

避免索引失效: WHERE 列上别做运算

这是最常见的索引失效场景, 一旦在索引列上套了函数或做了运算, MySQL 就没法用索引了, 只能全表扫描

sql

-- 坏: 对 created_at 用了函数, idx_created_at 索引失效
SELECT * FROM `order` WHERE DATE(created_at) = '2026-06-10';

-- 好: 改写成范围查询, 索引正常生效
SELECT * FROM `order`
WHERE created_at >= '2026-06-10 00:00:00'
  AND created_at <  '2026-06-11 00:00:00';

同理, WHERE amount + 100 > 1000 也会失效, 应改写成 WHERE amount > 900, 把运算挪到常量那一侧, 别碰索引列

深翻页优化

分页是个隐藏的性能陷阱, LIMIT 100000, 10 看着只要 10 行, 但 MySQL 的做法是先扫描出前 100010 行, 再把前 100000 行丢掉, 只留最后 10 行, 翻得越深, 扫描的行数越多, 越慢

优化思路有两种

方案一: 延迟关联, 先用覆盖索引快速定位到那 10 行的主键, 再回表取完整数据, 把回表的代价从 10 万行降到 10 行

sql

-- 优化前: 扫描并回表 100010 行
SELECT * FROM `order` ORDER BY id LIMIT 100000, 10;

-- 优化后: 子查询只走索引拿到 10 个 id, 再 JOIN 回表取数据
SELECT o.* FROM `order` o
INNER JOIN (
  SELECT id FROM `order` ORDER BY id LIMIT 100000, 10
) AS t ON o.id = t.id;

方案二: 游标分页(基于上一页的最大 id), 如果业务允许, 这是最优解, 直接利用主键索引定位, 彻底告别 OFFSET

sql

-- 记住上一页最后一条的 id (假设是 100000), 下一页这样查
SELECT * FROM `order` WHERE id > 100000 ORDER BY id LIMIT 10;

游标分页的查询效率与翻到第几页无关, 永远很快, 代价是只能"上一页/下一页"顺序翻, 没法直接跳到任意页, 但这对信息流、瀑布流这类场景完全够用

COUNT 优化

统计总数时, COUNT(*)、COUNT(1)、COUNT(主键) 在 InnoDB 里性能基本一致, 优化器会选择最小的可用索引来扫描, 不必纠结写法, 要避免的是 COUNT(普通列), 因为它要判断该列是否为 NULL, 略慢, 如果业务对实时性要求不高, 对千万级大表的总数统计, 可以考虑用一张计数表单独维护, 或者接受估算值(EXPLAIN 里的 rows)

大数据量优化策略

当单表数据量涨到千万、上亿级别, 再精妙的索引和 SQL 也会力不从心, 这时候要从架构层面想办法

INFO

下面这些是应对海量数据的常见思路, 每一个展开都是一个大话题, 这里只点到为止, 建立认知

垂直拆分: 把一张宽表按字段拆成多张表, 比如把订单表里不常用的大字段(备注、扩展信息)拆到 order_ext 副表, 让主表更"瘦", 单页能装下更多行, 查询更快
水平拆分(分库分表): 把一张大表的数据按某种规则(如 user_id 取模)分散到多张结构相同的表、甚至多个库里, 单表数据量降下来, 查询和写入压力随之分摊, 代价是跨分片查询、分布式事务会变复杂, 通常借助 ShardingSphere 这类中间件实现
读写分离: 一主多从架构, 写操作走主库, 读操作分摊到多个从库, 适合读多写少的场景, 能成倍提升读吞吐, 但要接受主从同步的延迟
冷热分离: 把高频访问的"热数据"(如近三个月订单)和低频的"冷数据"(历史归档订单)分开存储, 热数据保持小而快, 冷数据归档到成本更低的存储

这些策略不是越早上越好, 绝大多数性能问题, 在加索引、优化 SQL、加缓存这三步就能解决, 分库分表会显著增加系统复杂度, 应该是数据量确实触达单机瓶颈后, 经过测算的最后手段, 而不是一上来就过度设计

结语

到这里, 《SQL 必知必会》就算讲完了, 从最基础的查询, 一路走到范式设计、建表规约和性能优化, 你已经具备了独立设计一张规范的表、写出高效 SQL 并定位性能瓶颈的能力, 这些规约和技巧看着多, 但真正内化它们靠的不是背诵, 而是在一次次 EXPLAIN、一次次踩坑和优化里慢慢长出来的手感, 打开你的数据库, 把这些 SQL 都敲一遍、改一遍, 比读十篇文章都管用

SQL 必知必会 ​

检索 ​

检索数据语法 ​

排序 ​

排序数据语法 ​

按多列排序 ​

按列相对位置排序 ​

指定排序方向 ​

过滤 ​

过滤数据语法 ​

WHERE 操作符 ​

WHERE 字段引号 ​

范围值检查 ​

空值检查 ​

高级过滤 ​

AND 操作符 ​

OR 操作符 ​

混用AND 和 OR ​

IN 操作符 ​

NOT 操作符 ​

通配符过滤 ​

LIKE 操作符 ​

通配符技巧 ​

计算字段 ​

拼接(Concatenate) ​

使用别名 ​

执行算术计算 ​

计算测试 ​

函数 ​

处理文本 ​

日期和时间处理 ​

数值处理 ​

汇总数据 ​

聚集函数 ​

聚集不同值 ​

组合聚集函数 ​

分组数据 ​

创建分组语法 ​

过滤分组语法 ​

分组排序 ​

SELECT 子句顺序 ​

子查询 ​

子查询进行过滤 ​

计算字段使用子查询 ​

联结表 ​

联结的作用 ​

联结的创建 ​

内联结分类 ​

高级联结 ​

表别名 ​

自联结 ​

自然联结 ​

外联结 ​

左外联结 ​

右外联结 ​

全外联结 ​

四种联结的数据范围对比 ​

使用带聚集函数的联结 ​

联结及其使用要点 ​

笛卡尔积与交叉联结 ​

组合查询 ​

UNION 基本用法 ​

UNION 的规则 ​

UNION ALL ​

对组合查询结果排序 ​

UNION 与 UNION ALL 对比 ​

插入数据 ​

插入完整的行 ​

插入部分行 ​

插入检索出的数据 ​

一条 INSERT 插入多行 ​

复制表数据 ​

更新数据 ​

更新单列 ​

更新多列 ​

配合计算与子查询的 UPDATE ​

删除某列的值 ​

删除数据 ​

DELETE 与 TRUNCATE TABLE 的区别 ​

更新和删除的最佳实践 ​

SQL 必知必会

检索

检索数据语法

排序

排序数据语法

按多列排序

按列相对位置排序

指定排序方向

过滤

过滤数据语法

WHERE 操作符

WHERE 字段引号

范围值检查

空值检查

高级过滤

AND 操作符

OR 操作符

混用`AND` 和 `OR`

IN 操作符

NOT 操作符

通配符过滤

LIKE 操作符

通配符技巧

计算字段

拼接(Concatenate)

使用别名

执行算术计算

计算测试

函数

处理文本

日期和时间处理

数值处理

汇总数据

聚集函数

聚集不同值

组合聚集函数

分组数据

创建分组语法

过滤分组语法

分组排序

SELECT 子句顺序

子查询

子查询进行过滤

计算字段使用子查询

联结表

联结的作用

联结的创建

内联结分类

高级联结

表别名

自联结

自然联结

外联结

左外联结

右外联结

全外联结

四种联结的数据范围对比

使用带聚集函数的联结

联结及其使用要点

笛卡尔积与交叉联结

组合查询

UNION 基本用法

UNION 的规则

UNION ALL

对组合查询结果排序

UNION 与 UNION ALL 对比

插入数据

插入完整的行

插入部分行

插入检索出的数据

一条 INSERT 插入多行

复制表数据

更新数据

更新单列

更新多列

配合计算与子查询的 UPDATE

删除某列的值

删除数据

DELETE 与 TRUNCATE TABLE 的区别

更新和删除的最佳实践