Mysql 8 正式发布了,新增了很多优秀特性,之后我会挑些重点来分享。
下面和大家一起熟悉下 CTE(Common Table Expressions)通用表表达式。
派生表大家都比较熟悉了,CTE 就是针对派生表来的,可以说是增强的派生表,或者说时派生表的替换。
派生表是 FROM
中的子查询,例如:
SELECT ... FROM (subquery) AS derived, t1 ...
CTE 就像派生表,但它的声明是在查询块儿之前,而不是在 FROM
中,例如:
WITH derived AS (subquery)
SELECT ... FROM derived, t1 ...
看几个简单的示例:
WITH qn AS (SELECT a FROM t1)
SELECT * from qn;
WITH qn AS (SELECT a+2 AS a, b FROM t1)
UPDATE t1, qn SET t1.a=qn.a + 10 WHERE t1.a - qn.a = 0;
WITH qn(a, b) AS (SELECT a+2, b FROM t2)
DELETE t1 FROM t1, qn WHERE t1.a - qn.a = 0;
INSERT INTO t2
WITH qn AS (SELECT 10*a AS a FROM t1)
SELECT * from qn;
SELECT * FROM t1 WHERE t1.a IN
(WITH cte as (SELECT * FROM t1 AS t2 LIMIT 1)
SELECT a + 0 FROM cte);
CTE 相较于派生表有4个明显的优势:
派生表的形式:
SELECT ...
FROM t1 LEFT JOIN ((SELECT ... FROM ...) AS dt JOIN t2 ON ...) ON ...
CTE的形式:
WITH dt AS (SELECT ... FROM ...)
SELECT ...
FROM t1 LEFT JOIN (dt JOIN t2 ON ...) ON ...
派生表不能被引用两次,例如:
SELECT ...
FROM (SELECT a, b, SUM(c) s FROM t1 GROUP BY a, b) AS d1
JOIN (SELECT a, b, SUM(c) s FROM t1 GROUP BY a, b) AS d2 ON d1.b = d2.a;
而 CTE 可以,例如:
WITH d AS (SELECT a, b, SUM(c) s FROM t1 GROUP BY a, b)
SELECT ... FROM d AS d1 JOIN d AS d2 ON d1.b = d2.a;
派生表不能引用其他派生表,例如:
SELECT ...
FROM (SELECT ... FROM ...) AS d1, (SELECT ... FROM d1 ...) AS d2 ...
ERROR: 1146 (42S02): Table ‘db.d1’ doesn’t exist
CTE 可以引用其他的 CTE,例如:
WITH d1 AS (SELECT ... FROM ...),
d2 AS (SELECT ... FROM d1 ...)
SELECT
FROM d1, d2 ...
派生表是具体化的,每个派生表都是一个具体化的存在,就会产生性能问题,例如更多的空间、耗费更多的时间……
CTE 只会被创建一次,不管被引用了多少次
先从一个简单的例子开始,生成 1-10 的数字
WITH RECURSIVE my_cte AS
(
SELECT 1 AS n
UNION ALL
SELECT 1+n FROM my_cte WHERE n<10
)
SELECT * FROM my_cte;
+------+
| n |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+------+
简单分析下这个例子:
my_cte
,需要注意的是名字前面多了一个关键字 RECURSIVE
,说明这个CTE是递归形式的my_cte
的使用SELECT 1 AS n
是初始设置,这一行是用来定义 my_cte
的列,只有一列,类型为 INT
,名字为 n
SELECT 1+n FROM my_cte WHERE n<10
这句的意思是:从 my_cte 中拿 <10 的行,然后产生一行新记录,对 n
进行增加所以 mysql 会做以下步骤:
n<10
的,所以没有产出,并使循环终止S0,S1,...,S9
进行 union
USE test;
CREATE TABLE numbers
WITH RECURSIVE my_cte(n) AS
(
SELECT 1
UNION ALL
SELECT 1+n FROM my_cte WHERE n<6
)
SELECT * FROM my_cte;
Query OK, 6 rows affected (0,40 sec)
SELECT * FROM numbers;
+------+
| n |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+------+
INSERT
INSERT INTO numbers
WITH RECURSIVE my_cte(n) AS
(
SELECT 1
UNION ALL
SELECT 1+n FROM my_cte WHERE n<6
)
SELECT * FROM my_cte;
Query OK, 6 rows affected (0,12 sec)
SELECT * FROM numbers;
+------+
| n |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+------+
UPDATE
中使用:WITH RECURSIVE my_cte(n) AS
(
SELECT 1
UNION ALL
SELECT 1+n FROM my_cte WHERE n<6
)
UPDATE numbers, my_cte
SET numbers.n=0
WHERE numbers.n=my_cte.n*my_cte.n;
Query OK, 4 rows affected (0,01 sec)
SELECT * FROM numbers;
+------+
| n |
+------+
| 0 |
| 2 |
| 3 |
| 0 |
| 5 |
| 6 |
| 0 |
| 2 |
| 3 |
| 0 |
| 5 |
| 6 |
+------+
DELETE
中使用:DELETE FROM numbers
WHERE numbers.n >
(
WITH RECURSIVE my_cte(n) AS
(
SELECT 1
UNION ALL
SELECT 1+n FROM my_cte WHERE n<6
)
# Half the average is 3.5/2=1.75
SELECT AVG(n)/2 FROM my_cte
);
Query OK, 4 rows affected (0,07 sec)
SELECT * FROM numbers;
+------+
| n |
+------+
| 0 |
| 0 |
| 0 |
| 0 |
+------+
我是使用 docker 安装的 Mysql 8.0.11
,我感觉这是最简单的方式。
启动正常,但连接mysql时报错:
MySQL said: Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(/usr/local/lib/plugin/caching_sha2_password.so, 2): image not found
意思是caching_sha2_password
这个认证插件不能被加载。
网上查了一下,原因是mysql8改变了认证模式,解决方式是在启动容器时指定参数:
docker run -p 3306:3306 --name mysql8 -e MYSQL_ROOT_PASSWORD=111111 -d mysql:8.0.11 --default-authentication-plugin=mysql_native_password
重点是添加了:
--default-authentication-plugin=mysql_native_password
之后就可以正常登陆了,使用docker mysql作为客户端登录的命令:
docker run -it --link mysql8:mysql --rm mysql sh -c 'exec mysql -h"$MYSQL_PORT_3306_TCP_ADDR" -P"$MYSQL_PORT_3306_TCP_PORT" -uroot -p"$MYSQL_ENV_MYSQL_ROOT_PASSWORD"'
这是我遇到的一个问题,如果你也是使用docker来实践mysql8,这个经验会帮您节省一些时间。