MySQL5.7 与 MySQL8.0的差异

MySQL5.7 与 MySQL8.0的差异

MySQL8.0官网

字符集支持。 默认字符集已从 更改 latin1为utf8mb4。

JSON 增强。从MySQL 5.7.8 开始支持JSON，8.0对此进行增强。

**数据类型支持。**MySQL 现在支持在数据类型规范中使用表达式作为默认值。

优化器。

• MySQL 现在支持不可见索引。优化器根本不使用不可见索引，但可以正常维护。默认情况下，索引是可见的。不可见索引可以测试删除索引对查询性能的影响，而无需进行破坏性更改，如果需要索引，则必须撤消该更改。该功能适用于主键以外的索引。

  ALTER TABLE t1 ALTER INDEX i_idx INVISIBLE;
  ALTER TABLE t1 ALTER INDEX i_idx VISIBLE;
  

• MySQL 现在支持降序索引：支持 InnoDB存储引擎， DESC在索引中定义不再被忽略，而是导致键值按降序存储。

• MySQL 8.0.13 和更高版本支持创建索引表达式值而不是列值的功能索引键部分。功能键部分可以索引其他无法索引的值，例如 JSON值。

  -- 允许
  ALTER TABLE t1 ADD INDEX ((col1 * 40) DESC);
  INDEX ((col1 + col2), (col3 - col4))
  INDEX (col1, col2)
  -- 不允许
  INDEX (col1 + col2, col3 - col4)
  INDEX ((col1), (col2))
  

公用表表达式。 MySQL 现在支持非递归和递归的公共表表达式。

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;
-- 递归
WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;

窗口函数。 MySQL 现在支持窗口函数，对于查询中的每一行，使用与该行相关的行执行计算。

<窗口函数> over (partition by <用于分组的列名>
                order by <用于排序的列名>)

横向派生表。 现在可以在派生表前面加上 关键字，以指定允许在同一子句LATERAL中引用（依赖）前面表的列。

SELECT
  salesperson.name,
  max_sale.amount,
  max_sale_customer.customer_name
FROM
  salesperson,
  -- calculate maximum size, cache it in transient derived table max_sale
  LATERAL
  (SELECT MAX(amount) AS amount
    FROM all_sales
    WHERE all_sales.salesperson_id = salesperson.id)
  AS max_sale,
  -- find customer, reusing cached maximum size
  LATERAL
  (SELECT customer_name
    FROM all_sales
    WHERE all_sales.salesperson_id = salesperson.id
    AND all_sales.amount =
        -- the cached maximum size
        max_sale.amount)
  AS max_sale_customer;
  
SELECT
  salesperson.name,
  max_sale.amount,
  max_sale.customer_name
FROM
  salesperson,
  -- find maximum size and customer at same time
  LATERAL
  (SELECT amount, customer_name
    FROM all_sales
    WHERE all_sales.salesperson_id = salesperson.id
    ORDER BY amount DESC LIMIT 1)
  AS max_sale;

单表 DELETE 语句中的别名。 在 MySQL 8.0.16 及更高版本中，单表 DELETE语句支持使用表别名。

正则表达式支持。

解释分析语句。 MySQL 8.0.18 引入了EXPLAIN ANALYZE，它运行一个语句并产生 EXPLAIN输出以及时间和额外的、基于迭代器的信息，关于优化器的期望如何与实际执行相匹配。

EXPLAIN ANALYZE SELECT * FROM t1 JOIN t2 ON (t1.c1 = t2.c2)\G

带有 ON DUPLICATE KEY UPDATE 的行和列别名。

-- 违反唯一索引时执行表达式
INSERT INTO t1 (a,b,c) VALUES (1,1,3),(4,5,6)
  ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);
-- MySQL 8.0.19  
INSERT INTO t1 (a,b,c) VALUES (1,1,3),(4,5,6) AS new
  ON DUPLICATE KEY UPDATE c = new.a+new.b;