本文已参与「新人创作礼」活动，一起开启掘金创作之路。
mysql8 sql新特性
本文总结了一些 mysql8 中出现的新的sql语法和特性
有序更新
if an UPDATE statement includes an ORDER BY clause, the rows are updated in the order specified
by the clause. This can be useful in certain situations that might otherwise result in an
error. Suppose that a table t contains a column id that has a unique index. The following
statement could fail with a duplicate-key error, depending on the order in which rows are
updated:
UPDATE t SET id = id + 1;
For example, if the table contains 1 and 2 in the id column and 1 is updated to 2 before 2 is
updated to 3, an error occurs. To avoid this problem, add an ORDER BY clause to cause the rows
with larger id values to be updated before those with smaller values:
UPDATE t SET id = id + 1 ORDER BY id DESC;
跨表更新
UPDATE items,month SET items.price=month.price WHERE items.id=month.id;
跨表删除
Multi-Table Deletes You can specify multiple tables in a DELETE statement to delete rows from one or more tables
depending on the condition in the WHERE clause. You cannot use ORDER BY or LIMIT in a
multiple-table DELETE. The table_references clause lists the tables involved in the join,
as described in Section 13.2.10.2, “JOIN Clause”.
For the first multiple-table syntax, only matching rows from the tables listed before
the FROM clause are deleted. For the second multiple-table syntax, only matching rows from the
tables listed in the FROM clause (before the USING clause) are deleted. The effect is that you
can delete rows from many tables at the same time and have additional tables that are used only
for searching:
DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;
Or:
DELETE FROM t1, t2 USING t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;
These statements use all three tables when searching for rows to delete, but delete matching
rows only from tables t1 and t2.
The preceding examples use INNER JOIN, but multiple-table DELETE statements can use other types
of join permitted in SELECT statements, such as LEFT JOIN. For example, to delete rows that
exist in t1 that have no match in t2, use a LEFT JOIN:
DELETE t1 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;
函数索引
opensource.actionsky.com/20190505-my… 函数索引 MySQL8.0的函数索引内部其实也是依据虚拟列来实现的 所以添加函数索引会引起表重建
用 show extended columns from 表名; 显示隐藏列 可以查看隐藏列
ALTER TABLE t_func ADD INDEX idx_log_time ( ( date( log_time ) ) ),
ADD INDEX idx_u1 ( ( rank1 + rank2 ) ),
ADD INDEX idx_suffix_str3 ( ( RIGHT ( str3, 9 ) ) ),
ADD INDEX idx_substr_str1 ( ( substr( str1, 5, 9 ) ) ),
ADD INDEX idx_str2 ( ( CAST( str2 ->> '$.name' AS CHAR ( 9 ) ) ) );
生成数据
oracle 中生成数据
select level from dual connect by level<10;
select rownum from dual connect by rownum<10;
mysql 中生成数据
WITH RECURSIVE cte (n) AS
(
SELECT 1
UNION ALL
SELECT n + 1 FROM cte WHERE n < 10
)
SELECT /*+ SET_VAR(cte_max_recursion_depth = 1M) */ * FROM cte;
CREATE TABLE demotree (
id INT PRIMARY KEY NOT NULL,
name VARCHAR(100) NOT NULL,
pid INT not null ,
INDEX (pid)
);
INSERT INTO `demotree` (`id`, `name`, `pid`) VALUES (1, '01', 0);
INSERT INTO `demotree` (`id`, `name`, `pid`) VALUES (2, '02', 0);
INSERT INTO `demotree` (`id`, `name`, `pid`) VALUES (3, '0101', 1);
INSERT INTO `demotree` (`id`, `name`, `pid`) VALUES (4, '0102', 1);
INSERT INTO `demotree` (`id`, `name`, `pid`) VALUES (5, '010101', 3);
INSERT INTO `demotree` (`id`, `name`, `pid`) VALUES (6, '0201', 2);
INSERT INTO `demotree` (`id`, `name`, `pid`) VALUES (7, '01010101', 5);
WITH RECURSIVE tree AS (
#初始部分 id=1 的节点
SELECT t.* FROM demotree t WHERE t.id =1
UNION ALL
#递归部分 pid=初始部分.id
SELECT t1.* FROM demotree t1,tree t WHERE t1.pid = t.id
)
select * from tree;
WITH RECURSIVE tree AS (
#初始部分 id=7 的节点
SELECT t.* FROM demotree t WHERE t.id=7
UNION ALL
#递归部分 id=初始部分.pid
SELECT t1.* FROM demotree t1,tree t WHERE t1.id=t.pid
)
select * from tree;
WITH RECURSIVE tree AS (
SELECT 1 lev ,t.* FROM demotree t WHERE t.id =1
UNION ALL
SELECT lev+1 lv, t1.* FROM demotree t1,tree t WHERE t1.pid = t.id and lev<= 2-1
)
select * from tree;
WITH RECURSIVE tree AS (
SELECT 1 lev, t.* FROM demotree t WHERE t.id=7
UNION ALL
SELECT lev+1 lv, t1.* FROM demotree t1,tree t WHERE t1.id=t.pid and lev<= 3-1
)
select * from tree;
用户自定义变量
#@方法已经在mysql8中不太支持 select (@i:=@i+5) as rownum, t.file_time, t.event_type from github_events t , (select @i:=100) as init limit 10;
You can store a value in a user-defined variable in one statement and refer to it later in
another statement. This enables you to pass values from one statement to another.
User-defined variables are session specific. A user variable defined by one client cannot be
seen or used by other clients.
#动态拼接sql SET @var_name = expr [, @var_name = expr] ...
SET @c = "id,name as name1 ";
SET @s = CONCAT("SELECT ", @c, " FROM demo");
# PREPARE s1 FROM 'SELECT * FROM t1';
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
