目录
介绍
公用表表达式(CTE)
窗口函数
作为默认值的表达式
降序索引
函数索引关键部件
强制转换的其他目标类型
具有重复键更新的行和列别名
正则表达式支持
结论
介绍尽管MySQL8已在近3年前（2018年4月）发布，但一些开发中的项目仍然以5.7版本为目标，而没有考虑版本8的有用功能，这可以使您的代码更高效。此外，随着次要版本的发布，一些功能会继续出现和改进。
让我们来看看其中最有趣的8个。
公用表表达式(CTE)CTE（也称为WITH查询）是一个命名的临时结果集，它存在于单个语句的范围内，并且可以在该语句中稍后引用，无论是否递归：
WITH
cte1 AS (SELECT a, b FROM table1),
cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2 WHERE cte1.a = cte2.c;递归CTE可用于生成一些数据（相当于Oracle的“ConnectByPrior”）。例如，我们可以生成30行，而无需创建特殊过程或临时表：
WITH RECURSIVE sequence AS (
SELECT 0 AS level
UNION ALL
SELECT level + 1 AS value FROM sequence WHERE sequence.level < 30
)
SELECT level
FROM sequence;我们可以使用这些行来生成一些随机数据：
WITH RECURSIVE sequence AS (
SELECT 1 AS level
UNION ALL
SELECT level + 1 AS value FROM sequence WHERE sequence.level < 10
)
SELECT LEVEL,
REGEXP_REPLACE(CONCAT(SUBSTRING(MD5(UUID()), 1, 20 + rand()*20) , _
'@mail.com'), '\\d', '') random_email,
concat('+', FLOOR(rand() * 100), ' ', FLOOR(rand() * 1000), ' ', _
FLOOR(rand() * 1000), ' ', FLOOR(rand() * 10000)) random_phone,
REGEXP_REPLACE(MAKE_SET(rand()*9999999999999999,
'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', _
'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z'
), ',', '') random_str,
date(date_add(now(), INTERVAL LEVEL DAY)) days_sequence,
date_format(date_add(now(), INTERVAL LEVEL YEAR), '%Y') years_sequence,
date_add(now(), INTERVAL rand() * 999999999 SECOND) rand_future_date,
date_add(now(), INTERVAL - rand() * 999999999 - 16 * 365.25 * 24 * 3600 SECOND) _
rand_past_date_older16
FROM sequence;从MySQL 8.0.19开始，递归CTE的递归SELECT部分支持LIMIT子句，OFFSET也支持：
WITH RECURSIVE sequence AS (
SELECT 1 AS level
UNION ALL
SELECT level + 1 AS value FROM sequence WHERE sequence.level < 100
)
SELECT LEVEL
FROM sequence
LIMIT 10 OFFSET 20;表达式还可用于创建数学序列，例如斐波那契数列或分层遍历数据：
WITH RECURSIVE fibonacci (n, fib_n, next_fib_n) AS
(
SELECT 1, 0, 1
UNION ALL
SELECT n + 1, next_fib_n, fib_n + next_fib_n
FROM fibonacci WHERE n < 20
)
SELECT * FROM fibonacci;
-- WHERE n = 10; -- use WHERE to select n-th Fibonacci number窗口函数对一组行执行一些分组计算，就像分组聚合函数一样。但与将一组行折叠为一行的分组聚合不同，窗口函数为结果集中的每一行执行聚合。
有两种类型的窗口函数：
- 用作窗口函数SQL集合函数：COUNT，SUM，AVG，MIN，MAX，BIT_OR，BIT_AND，BIT_XOR，STDDEV_POP，STDDEV_SAMP，VAR_POP和VAR_SAMP
- 专门窗口函数：RANK，DENSE_RANK，PERCENT_RANK，CUME_DIST，NTILE，ROW_NUMBER，FIRST_VALUE，LAST_VALUE，NTH_VALUE，LEAD和LAG
让我们假设我们有一个table包含由employee组成的sales：
CREATE TABLE sales(employee VARCHAR(50), `date` DATE, sale INT);
INSERT INTO sales VALUES
('Peter', '2020-03-01', 100),
('Peter', '2020-04-01', 200),
('Peter', '2020-05-01', 300),
('Peter', '2020-06-01', 100),
('John', '2020-03-01', 300),
('John', '2020-04-01', 400),
('John', '2020-05-01', 500),
('Jane', '2020-03-01', 600),
('Jane', '2020-04-01', 800)
;在select下面，我们有一个分组聚合，没有GROUPBY子句，但它是隐式的，包含所有行：
SELECT employee, SUM(sale) FROM sales GROUP BY employee;与GROUP BY不同，我们可以让窗口函数只看到总行集的一个子集的行。这称为分区，类似于分组，只是每个销售员的总和不同：
SELECT employee, date, sale, SUM(sale) OVER (PARTITION BY employee) AS sum FROM sales;要查看不同月份的销售额，以及我们的销售人员的贡献如何：
SELECT employee, MONTHNAME(date) AS month, sale, SUM(sale)
OVER (PARTITION BY MONTH(date)) AS sum FROM sales;窗口规范可以包含分区中行的排序子句：
SELECT employee, sale, date, SUM(sale)
OVER (PARTITION by employee ORDER BY date) AS cum_sales FROM sales;上面的窗口规范等价于显式：
(PARTITION by employee ORDER BY date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)也就是说，对于每个已排序的行，SUM应该看到它之前的所有行(UNBOUNDED)，直到并包括当前ROW。要按employee计算总销售额的百分比，我们可以使用：
SELECT employee, sale, sale*100/SUM(sale)
OVER (PARTITION BY employee) AS percent_of_sales FROM sales;MySQL的现在支持使用表达式作为BLOB，TEXT，GEOMETRY和JSON数据类型的默认值，这在以前是根本不会被分配缺省值。
CREATE TABLE table1 (
field1 FLOAT DEFAULT (RAND() * 10),
field2 BINARY(16) DEFAULT (UUID_TO_BIN(UUID())),
field3 DATE DEFAULT (CURRENT_DATE + INTERVAL 1 YEAR),
field4 JSON DEFAULT (JSON_ARRAY()),
field5 BLOB DEFAULT (SUBSTR(UUID(), 1, 18)),
field6 TEXT DEFAULT (CONCAT('UUID: ', UUID())),
field7 POINT DEFAULT (Point(5, 10)),
field8 GEOMETRY DEFAULT (ST_MPointFromText('MULTIPOINT (1 1, 2 2, 3 3)'))
);以前，DESC可用于定义索引，但此标志被忽略。键值现在按降序物理存储。以前，可以按相反顺序扫描索引，但这会导致性能下降。现在可以按前向顺序扫描降序索引，这样效率更高。
通过一个例子可以更好地理解好处：
CREATE TABLE tab1 (
col1 INT, col2 INT,
INDEX idx1 (col1 ASC, col2 ASC),
INDEX idx2 (col1 ASC, col2 DESC),
INDEX idx3 (col1 DESC, col2 ASC),
INDEX idx4 (col1 DESC, col2 DESC)
);优化器可以对每个ORDERBY子句执行前向索引扫描而不使用文件排序操作：
ORDER BY col1 ASC, col2 ASC -- optimizer can use idx1
ORDER BY col1 ASC, col2 DESC -- optimizer can use idx2
ORDER BY col1 DESC, col2 ASC -- optimizer can use idx3
ORDER BY col1 DESC, col2 DESC -- optimizer can use idx4从版本8.0.13开始，MySQL支持索引表达式值而不是列值或列前缀的函数键部分。使用函数键的一部分允许您索引不直接存储在表中的值：
CREATE TABLE tab1 (
col1 VARCHAR(10),
col2 VARCHAR(10),
col3 INT,
col4 INT,
INDEX f_idx1 ((concat(col1, col2))),
INDEX f_idx2 ((ABS(col3))),
INDEX f_idx3 ((col3 + col4)),
INDEX f_idx4 ((col3 + col4), (col3 - col4), col3),
INDEX f_idx5 ((col3 * 40) DESC),
INDEX f_idx6 ((md5(col1)))
);从MySQL 8.0.17开始，CAST和CONVERT函数现在支持转换到DOUBLE，FLOAT和REAL类型：
SELECT CAST('123.45' AS DOUBLE); -- 123.45
SELECT CAST('123.45' AS FLOAT); -- 123.45
SELECT CAST('123.45123123123123' AS FLOAT(10)); -- 123.451
SELECT CAST('123.45123123123123' AS FLOAT(20)); -- 123.451
SELECT CAST('123.45123123123123' AS FLOAT(50)); -- 123.45123123123123
SELECT CAST('123.45123123123123' AS REAL); -- 123.45123123123123从8.0.22开始，服务器允许强制转换为YEAR：
SELECT CONVERT(1994.35, YEAR), CONVERT(1994.50, YEAR); -- 1994, 1995
SELECT CONVERT(20.35, YEAR), CONVERT(20.50, YEAR); -- 2020, 2021
SELECT CONVERT("15:20:00", YEAR), CONVERT(TIME "15:20:00", YEAR); -- 2015, 2021从MySQL 8.0.19开始，可以使用别名引用要插入的行及其列。使用新行的别名new，以及（如果需要）为该行的列使用别名m和n，INSERT语句：
INSERT INTO tab1 SET a=5, b=8
ON DUPLICATE KEY UPDATE a = VALUES(a) + VALUES(b);可以用许多不同的方式重写：
INSERT INTO tab1 SET a = 5, b = 8 AS new
ON DUPLICATE KEY UPDATE a = new.a + new.b;INSERT INTO tab1 VALUES(5, 8) AS new
ON DUPLICATE KEY UPDATE a = new.a + new.b;INSERT INTO tab1 SET a = 5, b = 8 AS new(m, n)
ON DUPLICATE KEY UPDATE a = m + n;INSERT INTO tab1 VALUES(6, 8) AS new(m, n)
ON DUPLICATE KEY UPDATE a = m + n;除了已经存在的函数NOTREGEXP、REGEXP和RLIKE，MySQL 8还提供了四个新函数：
- REGEXP_LIKE——string是否匹配正则表达式
- REGEXP_INSTR——匹配正则表达式的子串的起始索引
- REGEXP_REPLACE——替换匹配正则表达式的子字符串
- REGEXP_SUBSTR——返回匹配正则表达式的子串
REGEXP_LIKE接受一个"subject" string，一个被编译成正则表达式的模式和一个可选参数string，稍后会详细介绍。以最简单的形式，您可以像这样使用它：
SELECT regexp_like('aba', 'b+'); -- 1
SELECT regexp_like('aba', 'b{2}'); -- 0MySQL现在使用International Components for Unicode(ICU)实现正则表达式支持，它提供完整的Unicode支持并且是多字节安全的，因此我们可以整理苹果和樱桃🙂：
SELECT regexp_like('🍎🍎🍎🍒🍒🍒', '🍎🍒'); -- 1
SELECT regexp_like('🍎🍎🍎🍒🍒', '🍒🍎'); -- 0REGEXP_INSTR非常相似，但它不只是返回1或0。它为您提供匹配的位置，或者如果没有则是0，以及更多选项。
例如，您可以选择搜索的起始位置：
SELECT regexp_instr( 'abba', 'b{2}', 2 ); -- 2
SELECT regexp_instr( 'abba', 'b{2}', 3 ); -- 0匹配发生的次数很重要：
SELECT regexp_instr( 'abbabba', 'b{2}', 1, 2 ); -- 5
SELECT regexp_instr( 'abbabba', 'b{2}', 1, 3 ); -- 0以及匹配后的位置：
SELECT regexp_instr( 'abbabba', 'b{2}', 1, 2, 0 ); -- 5
SELECT regexp_instr( 'abbabba', 'b{2}', 1, 3, 1 ); -- 70表示MySQL应该返回匹配的第一个位置，而1表示匹配后的位置。
REGEXP_SUBSTR，除了表达式和模式，还有三个可选参数：位置、出现和匹配类型：
SELECT REGEXP_SUBSTR('abc def ghi', '[a-z]+'); -- abc
SELECT REGEXP_SUBSTR('abc def ghi', '[a-z]+', 1, 3); -- ghiREGEXP_REPLACE执行完整的搜索和替换操作：
SELECT REGEXP_REPLACE('abc def ghi', '[a-z]+', 'X', 1, 3); -- abc def X基于对这些新函数的描述，可以与一些工业DBMS，特别是Oracle进行类比。例如，Common Table Expressions是Oracle中CONNECT BY LEVEL的一种，Window Functions在语法上与Analytic Functions相似，函数集也有重叠。
REGEXP_REPLACE和其他函数现在提供了强大的string操纵机制。UTF8MB4是现在的默认字符集，与5.7相比，SQL性能（例如排序UTF8MB4 string）在8.0中提高了20倍。
除了所涵盖的内容之外，还添加了：NOWAIT和SKIPLOCKED、JSON增强、GROUPING功能、GIS、成本模型和直方图等。
还有一些DBA肯定会感兴趣的架构改进和性能优化。
总而言之，我们可以说MySQL在不断完善和改进的同时，正在逐渐成为一个更加成熟的工业工具。尽管MySQL有许多特定的缺点，但我们可以肯定，新版本更接近于创建一个稳定且功能强大的解决方案，该解决方案将继续在其细分市场中流行。
新功能的完整描述可在以下链接中获得：MySQL参考手册和MySQL团队博客。
https://www.codeproject.com/Tips/5297218/Top-8-New-SQL-Features-of-MySQL-8
