MySQL 你可能不知道的 SQL 使用技巧

pengxb
2017-04-06 13:49:29
MySQL
MySQL

本文将主要介绍一些我认为有用的 SQL 语法和技巧，并通过适当案例说明，但案例本身做了简化处理，只希望通过案例让读者更好的理解。

使用 UNION | UNION ALL 语法

UNION 用于合并多个查询的结果集，我目前遇到的主要有如下两个场景用起来比较有效：

1. 同表的复杂查询，很难通过一个 SELECT 语句搞定
2. 多表查询，但返回的数据一致，常见一些聚合数据统计需求

UNION 也可以加 limit、order by 子句，用于对 UNION 后的结果集进行排序和过滤。

通过 UNION 的这些特性，我们可以把原本需要编写代码才能处理的一些工作交给数据库，同时还减少SQL数量，提高性能。

比如说，下面这个例子：

SELECT
	'product' AS type,
	count(*) AS count
FROM
	`products`
UNION
	SELECT
		'comment' AS type,
		count(*) AS count
	FROM
		`comments`
	ORDER BY
		count;

我们通过 UNION 语法同时查询了 products 表和 comments 表的总记录数，并且按照 count 排序。

结合 UPDATE | DELETE 与 JOIN

一直以来，我们得益于联合查询（SELECT + JOIN）给我们带来的便利，但无形之中确形成了思维定势（至少对于我而言是这样的），殊不知 UPDATE DELETE 也能与 JOIN 联合使用，从而简化 SQL 编写。

在使用 UPDATE | DELETE + JOIN 之前，我们可能做法要么是先查询出待删除记录的 ID 然后再根据 ID 进行删除，要么是使用 IN 子查询。前者需要写两个 SQL 语句，在程序中处理逻辑，后者有时并不能正常工作。

就后者而言，应该有人遇到过这样的错误：

ERROR 1093 (HY000): You can't specify target table 'xxx' for update in FROM clause

这样的错误产生的原因是：MySQL 不支持同一个 SQL 语句尝试对同一个表进行查询和修改两个操作。

比如，删除没有评论的文章这条语句：

DELETE
FROM
	articles
WHERE
	id IN (
		SELECT
			a.id
		FROM
			articles AS a
		LEFT JOIN comments AS c ON a.id = c.article_id
		WHERE
			c.id IS NULL
	)

articles 表既被查询，也被更新，将出现上面的错误。

但是，如果 DELETE 结合 JOIN，则可以直接写出这样的 SQL 语句，简洁许多：

DELETE 
FROM
	articles AS a
LEFT JOIN comments AS c ON a.id = c.article_id
WHERE
	c.id IS NULL

当然，UPDATE 也是同理：

UPDATE articles AS a
LEFT JOIN comments AS c ON a.id = c.article_id
SET a.deleted = 1
WHERE
	c.id IS NULL

CASE 语法

CASE 语法可以在 SQL 内做简单的分支判断，根据不同的条件返回不同的值。比如考虑这样的需求：

一个商品有多个订单，订单有已付款和未付款两个状态，现在给定一个商品列表，返回每个商品已付款和未付款订单的数量。

这个时候我们可以通过 CASE 语句和 GROUP BY 通过一条 SQL 实现：

SELECT
	product_id,
	count(
		CASE is_paid
		WHEN 1 THEN
			1
		ELSE
			NULL
		END
	) AS total_paid,
	count(
		CASE is_paid
		WHEN 0 THEN
			1
		ELSE
			NULL
		END
	) AS total_not_paid
FROM
	orders
GROUP BY
	product_id
WHERE
	product_id IN (1, 2, 3, 4);

配合 ORM 库，这样的写法可以帮助我们实现 eager loading，避免 n + 1 查询。

因为这个场景比较简单，我们也可以使用MySQL提供的流程控制函数(Control Flow Functions)使得该SQL更简洁：

SELECT
	product_id,
	count(IF(is_paid = 1, 1, NULL)) AS total_paid,
	count(IF(is_paid = 0, 1, NULL)) AS total_not_paid
FROM
	orders
GROUP BY
	product_id
WHERE
	product_id IN (1, 2, 3, 4);

使用 INSERT INTO ... SELECT 语法

通过 INSERT INTO ... SELECT 语法，我们可以把 SELECT 的结果集直接写入另一张表中，而不需要程序处理。通过这个语法，外加一些变通，我们可以很方便的实现更多的需求场景。

比如说，我们要给所有购买了某一商品的用户发放一张元价值10元的优惠券，我们可以这样写：

INSERT INTO tickets (user_id, price, expires_in) SELECT
	user_id,
	10 AS price,
	'2017-09-09' AS expires_in
FROM
	orders
WHERE
	product_id = 123
AND is_paid = 1;

又比如说，在选课的场景中，我们要给一批人分配一批课，假设要给1班的人分配体育课和美术课，我们可以通过该语法加 CROSS JOIN 实现：

INSERT INTO class_members (class_id, user_id, STATUS) SELECT
	c.id AS class_id,
	u.id AS user_id,
	1 AS STATUS
FROM
	classes AS c
CROSS JOIN users AS u
WHERE
	c. NAME IN ('体育课', '美术课')
AND u.class_name = '1班';

本文就这些了，你还知道有哪些技巧呢?