本文汇总了MySQL8.0 面向开发的新特性，总共有12个新特性，有想快速了解8.0新特性的朋友，可以看一下哈
文章目录：
1.公用表达式支持-CTE
2.窗口函数
3.表达式作为默认值:
4.CHECK支持
5.隐藏列-MySQL8.0.23
6.隐藏索引、降序索引、函数索引 (MySQL-8.0.19)
7.VALUES语法
8.INTERSECT和EXCEPT支持
9.表上必须有主键及隐式主键参数
10.NOWAIT和SKIP LOCKED
11.set_var hint支持
12.字段快速加列
1.公用表达式支持-CTE
创建测试表：MySQL5.7-8.0
CREATE TABLE IF NOT EXISTS `test`.`nsy_test` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(100) NULL DEFAULT NULL COLLATE ‘utf8mb4_unicode_ci’,
`url` VARCHAR(150) NOT NULL COMMENT ‘url’ COLLATE ‘utf8mb4_unicode_ci’,
PRIMARY KEY (`id`),
INDEX `idx_name` (`name`)
)
COMMENT=’测试表’
DEFAULT CHARSET = utf8mb4
COLLATE=’utf8mb4_unicode_ci’
ENGINE=InnoDB;
插入测试数据：MySQL5.7-8.0
insert into test.nsy_test(id,name,url) values(1,’a’,’http://www.baidu.com’),(2,’b’,’http://www.163.com’);
复制一张新表：MySQL5.7-8.0
create table test.nsy_test2 as select * from test.nsy_test;
MySQL5.7:
— with xx as 语法 5.7 不支持
# MySQL 5.7
mysql> with
-> t1 as (select * from test.nsy_test),
-> t2 as (select * from test.nsy_test2)
-> select t1.id,t1.name from t1 join t2
-> where t1.id=t2.id;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘t1 as (select * from test.nsy_test),
t2 as (select * from test.nsy_test2)
select’ at line 2
mysql> with recursive t (n) as
-> ( select 1
-> union all
-> select id + 1 from test.nsy_test where id
-> )
-> select * from t;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘recursive t (n) as
( select 1
union all
select id + 1 from test.nsy_test where i’ at line 1
# MySQL 8.0
mysql> with
-> t1 as (select * from test.nsy_test),
-> t2 as (select * from test.nsy_test2)
-> select t1.id,t1.name from t1 join t2
-> where t1.id=t2.id;
+—-+——+
| id | name |
+—-+——+
| 1 | a |
| 2 | b |
+—-+——+
2 rows in set (0.01 sec)
mysql> with recursive t (n) as
-> ( select 1
-> union all
-> select id + 1 from test.nsy_test where id
-> )
-> select * from t;
+—+
| n |
+—+
| 1 |
| 2 |
| 3 |
+—+
3 rows in set (0.02 sec)
从上面介绍可以知道，CTE一方面可以非常方便进行SQL开发，另一方面也可以提升SQL执行效率。
2.窗口函数
— 5.7 不支持 row_number() over() 窗口函数
mysql> select id,name,row_number() over(order by name) as row_num from test.nsy_test;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘(order by name) as row_num from test.nsy_test’ at line 1
# MySQL 8.0
mysql> select id,name,row_number() over (order by name) as row_num from test.nsy_test;
+—-+——+———+
| id | name | row_num |
+—-+——+———+
| 1 | a | 1 |
| 2 | b | 2 |
+—-+——+———+
2 rows in set (0.01 sec)
3.表达式作为默认值:
— 字段birth_date 的默认值设置为表达式 current_date + interval 1 day
CREATE TABLE IF NOT EXISTS `test`.`nsy_test3` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(100) NULL DEFAULT NULL COLLATE ‘utf8mb4_unicode_ci’,
`birth_date` date default (current_date + interval 1 day),
PRIMARY KEY (`id`),
INDEX `idx_name` (`name`)
)
COMMENT=’测试表’
DEFAULT CHARSET = utf8mb4
COLLATE=’utf8mb4_unicode_ci’
ENGINE=InnoDB;
# MySQL 5.7
mysql> CREATE TABLE IF NOT EXISTS `test`.`nsy_test3` (
-> `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
-> `name` VARCHAR(100) NULL DEFAULT NULL COLLATE ‘utf8mb4_unicode_ci’,
-> `birth_date` date default (current_date + interval 1 day),
-> PRIMARY KEY (`id`),
-> INDEX `idx_name` (`name`)
-> )
-> COMMENT=’测试表’
-> DEFAULT CHARSET = utf8mb4
-> COLLATE=’utf8mb4_unicode_ci’
-> ENGINE=InnoDB;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘(current_date + interval 1 day),
PRIMARY KEY (`id`),
INDEX `idx_name` (`name`)
)’ at line 4
# MySQL 8.0
mysql> CREATE TABLE IF NOT EXISTS `test`.`nsy_test3` (
-> `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
-> `name` VARCHAR(100) NULL DEFAULT NULL COLLATE ‘utf8mb4_unicode_ci’,
-> `birth_date` date default (current_date + interval 1 day),
-> PRIMARY KEY (`id`),
-> INDEX `idx_name` (`name`)
-> )
-> COMMENT=’测试表’
-> DEFAULT CHARSET = utf8mb4
-> COLLATE=’utf8mb4_unicode_ci’
-> ENGINE=InnoDB;
Query OK, 0 rows affected, 1 warning (0.04 sec)
插入数据：
insert into test.nsy_test3(id,name,birth_date) values(1,’a’,’2024-01-15′);
mysql> insert into test.nsy_test3(id,name) values(2,’b’);
Query OK, 1 row affected (0.01 sec)
mysql> select * from test.nsy_test3;
+—-+——+————+
| id | name | birth_date |
+—-+——+————+
| 1 | a | 2024-01-15 |
| 2 | b | 2024-01-16 |
+—-+——+————+
2 rows in set (0.00 sec)
4.CHECK支持
检查约束（CHECK ）。
SQL 中的检查约束属于完整性约束的一种，可以用于约束表中的某个字段或者一些字段必须满足某个条件。例如用户名必须大写、余额不能小于零等。
MySQL 5.7 创建表带check不报错，实际上没有生效；
— 5.7 创建不报错，但是查看约束表并没用约束信息
CREATE TABLE IF NOT EXISTS `test`.`nsy_test4` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`id1` INT(10) not null default 0 check(id1>10),
`id2` INT(10) not null default 0 check(id2>10),
check (id1>id2),
PRIMARY KEY (`id`)
)
COMMENT=’测试表’
DEFAULT CHARSET = utf8mb4
COLLATE=’utf8mb4_unicode_ci’
ENGINE=InnoDB;
# MySQL 5.7
mysql> CREATE TABLE IF NOT EXISTS `test`.`nsy_test4` (
-> `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
-> `id1` INT(10) not null default 0 check(id1>10),
-> `id2` INT(10) not null default 0 check(id2>10),
-> check (id1>id2),
-> PRIMARY KEY (`id`)
-> )
-> COMMENT=’测试表’
-> DEFAULT CHARSET = utf8mb4
-> COLLATE=’utf8mb4_unicode_ci’
-> ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)
mysql> show create table test.nsy_test4G;
*************************** 1. row ***************************
Table: nsy_test4
Create Table: CREATE TABLE `nsy_test4` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`id1` int(10) NOT NULL DEFAULT ‘0’,
`id2` int(10) NOT NULL DEFAULT ‘0’,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT=’测试表’
1 row in set (0.01 sec)
ERROR:
No query specified
mysql> select * from information_schema.TABLE_CONSTRAINTS where table_name=’nsy_test4’G
*************************** 1. row ***************************
CONSTRAINT_CATALOG: def
CONSTRAINT_SCHEMA: test
CONSTRAINT_NAME: PRIMARY
TABLE_SCHEMA: test
TABLE_NAME: nsy_test4
CONSTRAINT_TYPE: PRIMARY KEY
1 row in set (0.01 sec)
#MySQL8.0
mysql> CREATE TABLE IF NOT EXISTS `test`.`nsy_test4` (
-> `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
-> `id1` INT(10) not null default 0 check(id1>10),
-> `id2` INT(10) not null default 0 check(id2>10),
-> check (id1>id2),
-> PRIMARY KEY (`id`)
-> )
-> COMMENT=’测试表’
-> DEFAULT CHARSET = utf8mb4
-> COLLATE=’utf8mb4_unicode_ci’
-> ENGINE=InnoDB;
Query OK, 0 rows affected, 3 warnings (0.06 sec)
mysql> show create table nsy_test4G;
*************************** 1. row ***************************
Table: nsy_test4
Create Table: CREATE TABLE `nsy_test4` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`id1` int NOT NULL DEFAULT ‘0’,
`id2` int NOT NULL DEFAULT ‘0’,
PRIMARY KEY (`id`),
CONSTRAINT `nsy_test4_chk_1` CHECK ((`id1` > 10)),
CONSTRAINT `nsy_test4_chk_2` CHECK ((`id2` > 10)),
CONSTRAINT `nsy_test4_chk_3` CHECK ((`id1` > `id2`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT=’测试表’
1 row in set (0.00 sec)
mysql> select * from information_schema.TABLE_CONSTRAINTS where table_name=’nsy_test4’G;
*************************** 1. row ***************************
CONSTRAINT_CATALOG: def
CONSTRAINT_SCHEMA: test
CONSTRAINT_NAME: PRIMARY
TABLE_SCHEMA: test
TABLE_NAME: nsy_test4
CONSTRAINT_TYPE: PRIMARY KEY
ENFORCED: YES
*************************** 2. row ***************************
CONSTRAINT_CATALOG: def
CONSTRAINT_SCHEMA: test
CONSTRAINT_NAME: nsy_test4_chk_1
TABLE_SCHEMA: test
TABLE_NAME: nsy_test4
CONSTRAINT_TYPE: CHECK
ENFORCED: YES
*************************** 3. row ***************************
CONSTRAINT_CATALOG: def
CONSTRAINT_SCHEMA: test
CONSTRAINT_NAME: nsy_test4_chk_2
TABLE_SCHEMA: test
TABLE_NAME: nsy_test4
CONSTRAINT_TYPE: CHECK
ENFORCED: YES
*************************** 4. row ***************************
CONSTRAINT_CATALOG: def
CONSTRAINT_SCHEMA: test
CONSTRAINT_NAME: nsy_test4_chk_3
TABLE_SCHEMA: test
TABLE_NAME: nsy_test4
CONSTRAINT_TYPE: CHECK
ENFORCED: YES
4 rows in set (0.01 sec)
ERROR:
No query specified
–带上名称 constraint id2_c 后 5.7 创建报错，不支持此语法
CREATE TABLE IF NOT EXISTS `test`.`nsy_test5` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`id1` INT(10) not null default 0 check(id1>10),
`id2` INT(10) not null default 0 constraint id2_c check(id2>10),
check (id1>id2),
PRIMARY KEY (`id`)
)
COMMENT=’测试表’
DEFAULT CHARSET = utf8mb4
COLLATE=’utf8mb4_unicode_ci’
ENGINE=InnoDB;
#MySQL5.7
mysql> CREATE TABLE IF NOT EXISTS `test`.`nsy_test5` (
-> `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
-> `id1` INT(10) not null default 0 check(id1>10),
-> `id2` INT(10) not null default 0 constraint id2_c check(id2>10),
-> check (id1>id2),
-> PRIMARY KEY (`id`)
-> )
-> COMMENT=’测试表’
-> DEFAULT CHARSET = utf8mb4
-> COLLATE=’utf8mb4_unicode_ci’
-> ENGINE=InnoDB;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘constraint id2_c check(id2>10),
check (id1>id2),
PRIMARY KEY (`id`)
)
COMMENT=” at line 4
#MySQL8.0
mysql> CREATE TABLE IF NOT EXISTS `test`.`nsy_test5` (
-> `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
-> `id1` INT(10) not null default 0 check(id1>10),
-> `id2` INT(10) not null default 0 constraint id2_c check(id2>10),
-> check (id1>id2),
-> PRIMARY KEY (`id`)
-> )
-> COMMENT=’测试表’
-> DEFAULT CHARSET = utf8mb4
-> COLLATE=’utf8mb4_unicode_ci’
-> ENGINE=InnoDB;
Query OK, 0 rows affected, 3 warnings (0.05 sec)
mysql> show create table test.nsy_test5G;
*************************** 1. row ***************************
Table: nsy_test5
Create Table: CREATE TABLE `nsy_test5` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`id1` int NOT NULL DEFAULT ‘0’,
`id2` int NOT NULL DEFAULT ‘0’,
PRIMARY KEY (`id`),
CONSTRAINT `id2_c` CHECK ((`id2` > 10)),
CONSTRAINT `nsy_test5_chk_1` CHECK ((`id1` > 10)),
CONSTRAINT `nsy_test5_chk_2` CHECK ((`id1` > `id2`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT=’测试表’
1 row in set (0.00 sec)
5.隐藏列-MySQL8.0.23
概念描述
从MySQL8.0.23版本开始，MySQL支持不可见列（invisible columns），在之前版本中MySQL对所有列都是可见的，所谓不可见列就是指在正常默认查询中不会显示出来，用select * from 语句是无法查询出不可见属性的列的。
使用场景
1. 隐藏字段：刻意隐藏不想展示的字段，比如备注，敏感值，关键信息等
2. 业务侧需要：业务侧为前期表设计不充分，创建一些隐藏字段来保留业务数据，方便后期扩展需求
3. 提升容错性：弥补业务初期创建错误表字段问题而开通的一个“后门儿”
测试验证：
CREATE TABLE IF NOT EXISTS `test`.`nsy_test6` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(100) NULL DEFAULT NULL COLLATE ‘utf8mb4_unicode_ci’,
`url` VARCHAR(150) NOT NULL COMMENT ‘url’ COLLATE ‘utf8mb4_unicode_ci’,
PRIMARY KEY (`id`),
INDEX `idx_name` (`name`)
)
COMMENT=’测试表’
DEFAULT CHARSET = utf8mb4
COLLATE=’utf8mb4_unicode_ci’
ENGINE=InnoDB;
插入数据：
insert into test.nsy_test6(id,name,url) values(1,’a’,’http://www.baidu.com’);
mysql> select * from test.nsy_test6;
+—-+——+———————-+
| id | name | url |
+—-+——+———————-+
| 1 | a | http://www.baidu.com |
+—-+——+———————-+
1 row in set (0.00 sec)
— 添加一个不可见列 id_no：
alter table `test`.`nsy_test6` add column id_no varchar(20) default ‘abc’ invisible;
— 正常的select * 查询结果：
mysql> select * from test.nsy_test6;
+—-+——+———————-+
| id | name | url |
+—-+——+———————-+
| 1 | a | http://www.baidu.com |
+—-+——+———————-+
1 row in set (0.00 sec)
— 显示指定不可见列进行查询：
mysql> select id,name,url,id_no from test.nsy_test6;
+—-+——+———————-+——-+
| id | name | url | id_no |
+—-+——+———————-+——-+
| 1 | a | http://www.baidu.com | abc |
+—-+——+———————-+——-+
1 row in set (0.00 sec)
查看表结构：
mysql> show create table test.nsy_test6G;
*************************** 1. row ***************************
Table: nsy_test6
Create Table: CREATE TABLE `nsy_test6` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`url` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT ‘url’,
`id_no` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT ‘abc’ /*!80023 INVISIBLE */,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT=’测试表’
1 row in set (0.00 sec)
ERROR:
No query specified
不可见列的几种操作:
— 将现有的字段修改为不可见：
alter table test.nsy_test6 modify column url varchar(150) invisible;
mysql> show create table test.nsy_test6G;
*************************** 1. row ***************************
Table: nsy_test6
Create Table: CREATE TABLE `nsy_test6` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`url` varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT NULL /*!80023 INVISIBLE */,
`id_no` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT ‘abc’ /*!80023 INVISIBLE */,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT=’测试表’
1 row in set (0.00 sec)
ERROR:
No query specified
— 将不可见的字段修改为可见
mysql> alter table test.nsy_test6 modify column url varchar(150) visible;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table test.nsy_test6G;
*************************** 1. row ***************************
Table: nsy_test6
Create Table: CREATE TABLE `nsy_test6` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`url` varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`id_no` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT ‘abc’ /*!80023 INVISIBLE */,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT=’测试表’
1 row in set (0.00 sec)
ERROR:
No query specified
— 修改字段名时指定字段不可见
mysql> alter table test.nsy_test6 change column url urls varchar(150) INVISIBLE;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table test.nsy_test6G;
*************************** 1. row ***************************
Table: nsy_test6
Create Table: CREATE TABLE `nsy_test6` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`urls` varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT NULL /*!80023 INVISIBLE */,
`id_no` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT ‘abc’ /*!80023 INVISIBLE */,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT=’测试表’
1 row in set (0.00 sec)
ERROR:
No query specified
不可见列限制和约束
1.表中必须至少有一个列是可见列，如果表中都是不可见列将会产生错误。
CREATE TABLE IF NOT EXISTS `test`.`nsy_test7` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(100) NULL DEFAULT NULL COLLATE ‘utf8mb4_unicode_ci’,
PRIMARY KEY (`id`),
INDEX `idx_name` (`name`)
)
COMMENT=’测试表’
DEFAULT CHARSET = utf8mb4
COLLATE=’utf8mb4_unicode_ci’
ENGINE=InnoDB;
mysql> alter table test.nsy_test7 modify column name VARCHAR(100) invisible;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table test.nsy_test7 modify column id int(10) invisible;
ERROR 4028 (HY000): A table must have at least one visible column.
mysql> show create table test.nsy_test7G;
*************************** 1. row ***************************
Table: nsy_test7
Create Table: CREATE TABLE `nsy_test7` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL /*!80023 INVISIBLE */,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT=’测试表’
1 row in set (0.00 sec)
ERROR:
No query specified
2.不可见列支持常用的列属性： NULL，NOT NULL，DEFAULT，AUTO_INCREMENT等。
3.可以在不可见列上定义外键约束，外键约束可以引用不可见列。
4.CHECK可以在不可见列上定义约束。对于新增或修改的行，违反CHECK不可见列上的约束会产生错误。
5.CREATE TABLE … LIKE 包括不可见的列，它们在新表中依然是不可见的。
6.CREATE TABLE … SELECT 不包括不可见列，除非在 SELECT部件中明确指定，但是即使显式指定，现有表中不可见的列在新表中也是可见的。
7.INSERT INTO t1 VALUES(…)语句，不包含不可见列的值，否则会报错。
8.mysqldump 和 mysqlpump 导出数据时使用的是SHOW CREATE TABLE，因此它们在转储表定义中包含不可见列，它们还包括转储数据中不可见列的值。将转储文件重新加载到不支持不可见列的旧版本MySQL中会导致版本特定的注释被忽略，从而将所有不可见列创建为可见列。
6.隐藏索引、降序索引、函数索引 (MySQL-8.0.19)
MySQL8.0版本带来了3大索引新功能：隐藏索引,降序索引,函数索引，看字面意义，大致也都能猜到那些功能，下面测试实际了解一下。
隐藏索引:
MySQL 8.0 支持隐藏索引（invisible index），也称为不可见索引。隐藏索引不会被优化器使用。主键不能设置为隐藏（包括显式设置或隐式设置）
— 创建测试表：
CREATE TABLE IF NOT EXISTS `test`.`nsy_test8` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(100) NULL DEFAULT NULL COLLATE ‘utf8mb4_unicode_ci’,
`url` VARCHAR(150) NOT NULL COMMENT ‘url’ COLLATE ‘utf8mb4_unicode_ci’,
PRIMARY KEY (`id`),
INDEX `idx_name` (`name`) INVISIBLE
)
COMMENT=’测试表’
DEFAULT CHARSET = utf8mb4
COLLATE=’utf8mb4_unicode_ci’
ENGINE=InnoDB;
— 查看不可见索引：
mysql> show create table test.nsy_test8G;
*************************** 1. row ***************************
Table: nsy_test8
Create Table: CREATE TABLE `nsy_test8` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`url` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT ‘url’,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`) /*!80000 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT=’测试表’
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> show index from test.nsy_test8;
+———–+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+———+————+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+———–+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+———+————+
| nsy_test8 | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
| nsy_test8 | 1 | idx_name | 1 | name | A | 0 | NULL | NULL | YES | BTREE | | | NO | NULL |
+———–+————+———-+——–服务器托管网——+————-+———–+————-+———-+——–+——+————+———+—————+———+————+
2 rows in set (0.04 sec)
mysql> select index_name,is_visible from information_schema.statistics where table_schema=’test’ and table_name=’nsy_test8′;
+————+————+
| INDEX_NAME | IS_VISIBLE |
+————+————+
| idx_name | NO |
| PRIMARY | YES |
+————+————+
2 rows in set (0.00 sec)
— 隐藏和可见操作
mysql> alter table test.nsy_test8 alter index idx_name visible;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table test.nsy_test8 alter index idx_name invisible;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
除了主键，其他索引都可以配置为可见或不可见。
对于唯一键：
例外情况: 没有主键的情况下，第一个唯一建 不可隐藏，第二个唯一建可隐藏。
从这里可以了解到 MySQL在没有主键的情况下 是把第一个唯一建做为主键。
CREATE TABLE IF NOT EXISTS `test`.`nsy_test9` (
`id` INT(10) UNSIGNED NOT NULL,
`name` VARCHAR(100) NULL DEFAULT NULL COLLATE ‘utf8mb4_unicode_ci’,
`order_num` VARCHAR(150) NOT NULL COMMENT ‘url’ COLLATE ‘utf8mb4_unicode_ci’,
`addr` VARCHAR(100) NOT NULL COMMENT ‘url’ COLLATE ‘utf8mb4_unicode_ci’,
unique unique_name(`name`),
unique unique_order_num(`order_num`),
INDEX idx_addr (`addr`) INVISIBLE
)
COMMENT=’测试表’
DEFAULT CHARSET = utf8mb4
COLLATE=’utf8mb4_unicode_ci’
ENGINE=InnoDB;
mysql> CREATE TABLE IF NOT EXISTS `test`.`nsy_test9` (
-> `id` INT(10) UNSIGNED NOT NULL,
-> `name` VARCHAR(100) NULL DEFAULT NULL COLLATE ‘utf8mb4_unicode_ci’,
-> `order_num` VARCHAR(150) NOT NULL COMMENT ‘url’ COLLATE ‘utf8mb4_unicode_ci’,
-> `addr` VARCHAR(100) NOT NULL COMMENT ‘url’ COLLATE ‘utf8mb4_unicode_ci’,
-> unique unique_name(`name`),
-> unique unique_order_num(`order_num`),
-> INDEX idx_addr (`addr`) INVISIBLE
-> )
-> COMMENT=’测试表’
-> DEFAULT CHARSET = utf8mb4
-> COLLATE=’utf8mb4_unicode_ci’
-> ENGINE=InnoDB;
Query OK, 0 rows affected, 1 warning (0.06 sec)
mysql> alter table test.nsy_test9 alter index unique_name invisible;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table test.nsy_test9 alter index unique_order_num invisible;
ERROR 3522 (HY000): A primary key index cannot be invisible
mysql> show create table `test`.`nsy_test9`G;
*************************** 1. row ***************************
Table: nsy_test9
Create Table: CREATE TABLE `nsy_test9` (
`id` int unsigned NOT NULL,
`name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`order_num` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT ‘url’,
`addr`服务器托管网 varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT ‘url’,
UNIQUE KEY `unique_order_num` (`order_num`),
UNIQUE KEY `unique_name` (`name`) /*!80000 INVISIBLE */,
KEY `idx_addr` (`addr`) /*!80000 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT=’测试表’
1 row in set (0.01 sec)
总结：
不可见索引特性可以用于测试删除某个索引对于查询性能的影响，同时又不需要真正删除索引，也就避免了错误删除之后的索引重建。对于一个大表上的索引进行删除重建将会非常耗时，而将其设置为不可见或可见将会非常简单快捷。
如果某个设置为隐藏的索引实际上仍然需要或者被优化器所使用，可以通过以下多种方法发现缺少该索引带来的影响：
索引提示中使用了该索引的查询将会产生错误。
性能模式（Performance Schema）中的数据显示受影响查询的负载升高。
EXPLAIN 语句显示了不同的查询执行计划。
慢查询日志中出现了新的查询语句。
需要思考的问题：
增删改操作，本身会进行索引的维护，隐藏索引是否有必要创建。
MySQL8.0不再对group by操作进行隐式排序。
如果需要排序，必须显式加上order by 子句：
1.创建表：
CREATE TABLE IF NOT EXISTS `test`.`nsy_test12` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`cid` INT(11) NOT NULL DEFAULT ‘0’ COMMENT ‘排序’,
PRIMARY KEY (`id`)
)
COMMENT=’nsy_test12′
DEFAULT CHARSET = utf8mb4
COLLATE=’utf8mb4_unicode_ci’
ENGINE=InnoDB;
插入数据：
mysql> insert into test.nsy_test12 values(1,0),(3,0),(4,0),(5,5),(6,1),(7,5);
Query OK, 6 rows affected (0.03 sec)
Records: 6 Duplicates: 0 Warnings: 0
查看数据情况：
mysql> select * from nsy_test12;
+—-+—–+
| id | cid |
+—-+—–+
| 1 | 0 |
| 3 | 0 |
| 4 | 0 |
| 5 | 5 |
| 6 | 1 |
| 7 | 5 |
+—-+—–+
6 rows in set (0.00 sec)
#mysql8.0
mysql> select version();
+———–+
| version() |
+———–+
| 8.0.33 |
+———–+
1 row in set (0.00 sec)
mysql> select cid from test.nsy_test12 group by cid;
+—–+
| cid |
+—–+
| 0 |
| 5 |
| 1 |
+—–+
3 rows in set (0.00 sec)
#mysql5.7
mysql> select version();
+———–+
| version() |
+———–+
| 5.7.43 |
+———–+
1 row in set (0.00 sec)
mysql> select cid from test.nsy_test12 group by cid;
+—–+
| cid |
+—–+
| 0 |
| 1 |
| 5 |
+—–+
3 rows in set (0.01 sec)
由上可见，两个版本的执行结果是不一样的，MySQL5.7中的group by 默认有排序功能，MySQL8.0则默认只分组不排序，需要加order by 子句排序
降序索引
MySQL8.0开始真正支持降序索引，只有InnoDB引擎支持降序索引，且必须是BTREE降序索引，
查看官方：
MySQL支持降序索引:索引定义中的DESC不再被忽略，而是按降序存储键值。以前，可以以相反的顺序扫描索引，但是会导致性能损失。下行索引可以按前向顺序扫描，效率更高。当最有效的扫描顺序混合了某些列的升序和其他列的降序时，降序索引也使优化器能够使用多列索引。
注意：
descending索引只支持InnoDB存储引擎
具有降序关键部分的索引不用于对调用聚合函数但没有GROUP BY子句的查询进行MIN()/MAX()优化
降序索引:BTREE支持，但不支持哈希索引,全文索引或空间索引
DISTINCT可以使用包含匹配列(包括降序关键部分)的任何索引。
对于所有可用升序索引的数据类型，都支持降序索引。
语法如下，用法简单，需要考虑索引维护的成本和实际使用场景。对于MAX，MIN，DISTINCT 降序索引进行优化。
CREATE TABLE t (
c1 INT, c2 INT,
INDEX idx1 (c1 ASC, c2 ASC),
INDEX idx2 (c1 ASC, c2 DESC),
INDEX idx3 (c1 DESC, c2 ASC),
INDEX idx4 (c1 DESC, c2 DESC)
);
函数索引：
MySQL版本需要是5.7及以上版本才支持建立函数索引(虚拟列方式),MySQL 8.0.13 以及更高版本支持函数索引（functional key parts），也就是将表达式的值作为索引的内容，而不是列值或列值前缀。 将函数作为索引键可以用于索引那些没有在表中直接存储的内容。
#创建表：
CREATE TABLE IF NOT EXISTS `test`.`nsy_test11` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`create_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,
`create_by` VARCHAR(45) NULL DEFAULT NULL COMMENT ‘创建者’ COLLATE ‘utf8mb4_unicode_ci’,
`update_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新时间’,
`update_by` VARCHAR(45) NULL DEFAULT NULL COMMENT ‘更新者’ COLLATE ‘utf8mb4_unicode_ci’,
`sort` INT(11) NOT NULL DEFAULT ‘0’ COMMENT ‘排序’,
`version` BIGINT(20) NOT NULL DEFAULT ‘0’ COMMENT ‘版本号’,
PRIMARY KEY (`id`)
)
COMMENT=’test’
DEFAULT CHARSET = utf8mb4
COLLATE=’utf8mb4_unicode_ci’
ENGINE=InnoDB;
插入数据：
insert into test.nsy_test11(create_date,update_date,sort,version) values(‘2024-01-15′,’2024-01-16’,1,1);
insert into test.nsy_test11(create_date,update_date,sort,version) values(‘2024-01-17′,’2024-01-18’,1,1);
insert into test.nsy_test11(create_date,update_date,sort,version) values(‘2024-01-19′,’2024-01-20’,1,1);
insert into test.nsy_test11(create_date,update_date,sort,version) values(‘2024-01-21′,’2024-01-22’,1,1);
insert into test.nsy_test11(create_date,update_date,sort,version) values(‘2024-01-23′,’2024-01-24’,1,1);
insert into test.nsy_test11(create_date,update_date,sort,version) values(‘2024-01-25′,’2024-01-26’,1,1);
insert into test.nsy_test11(create_date,update_date,sort,version) values(‘2024-01-27′,’2024-01-28’,1,1);
insert into test.nsy_test11(create_date,update_date,sort,version) values(‘2024-01-29′,’2024-01-30’,1,1);
insert into test.nsy_test11(create_date,update_date,sort,version) values(‘2024-02-01′,’2024-02-02’,1,1);
insert into test.nsy_test11(create_date,update_date,sort,version) values(‘2024-02-03′,’2024-02-04’,1,1);
insert into test.nsy_test11(create_date,update_date,sort,version) values(‘2024-02-05′,’2024-02-06’,1,1);
insert into test.nsy_test11(create_date,update_date,sort,version) values(‘2024-02-07′,’2024-02-08’,1,1);
#MySQL 8.0
创建索引：
mysql> alter table test.nsy_test11 add index idx_create_date_fun((day(create_date)));
mysql> select * from test.nsy_test11 where day(create_date)=19;
+—-+———————+———–+———————+———–+——+———+
| id | create_date | create_by | update_date | update_by | sort | version |
+—-+———————+———–+———————+———–+——+———+
| 3 | 2024-01-19 00:00:00 | NULL | 2024-01-20 00:00:00 | NULL | 1 | 1 |
+—-+———————+———–+———————+———–+——+———+
1 row in set (0.00 sec)
mysql> explain select * from test.nsy_test11 where day(create_date)=19;
+—-+————-+————+————+——+———————+———————+———+——-+——+———-+——-+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+————+————+——+———————+———————+———+——-+——+———-+——-+
| 1 | SIMPLE | nsy_test11 | NULL | ref | idx_create_date_fun | idx_create_date_fun | 5 | const | 1 | 100.00 | NULL |
+—-+————-+————+————+——+———————+———————+———+——-+——+———-+——-+
1 row in set, 1 warning (0.01 sec)
查看表结构：
mysql> show create table test.nsy_test11G;
*************************** 1. row ***************************
Table: nsy_test11
Create Table: CREATE TABLE `nsy_test11` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`create_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,
`create_by` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT ‘创建者’,
`update_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新时间’,
`update_by` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT ‘更新者’,
`sort` int NOT NULL DEFAULT ‘0’ COMMENT ‘排序’,
`version` bigint NOT NULL DEFAULT ‘0’ COMMENT ‘版本号’,
PRIMARY KEY (`id`),
KEY `idx_create_date_fun` ((dayofmonth(`create_date`)))
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT=’test’
1 row in set (0.00 sec)
ERROR:
No query specified
#MySQL 5.7
mysql> alter table test.nsy_test11 add index idx_create_date_fun((day(create_date)));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘(day(create_date)))’ at line 1
注意：
函数索引支持UNIQUE选项。但是，主键不能包含函数列。主键只能使用存储的计算列，但是函数索引使用虚拟计算列实现，而不是存储计算列。
空间SPATIAL 索引和 全文FULLTEXT 索引不支持函数索引。
如果某个表中没有主键，InnoDB 存储引擎自动将第一个 UNIQUE NOT NULL 索引提升为主键。但是对于包含函数列的 UNIQUE NOT NULL 索引不会进行提升。
如果要删除的字段上有函数索引，必须先删除函数索引，才能删除字段。
mysql> alter table test.nsy_test11 drop column create_date;
ERROR 3837 (HY000): Column ‘create_date’ has a functional index dependency and cannot be dropped or renamed.
mysql> alter table test.nsy_test11 drop index idx_create_date_fun;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table test.nsy_test11 drop column create_date;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
7.VALUES语法
VALUES 是 MySQL 8.0.19开始支持的语法，它会以表的形式返回一行或多行数据
# MySQL 5.7
mysql> values row(1,’a’),row(2,’b’);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘values row(1,’a’),row(2,’b’)’ at line 1
# MySQL 8.0
mysql> values row(1,’a’),row(2,’b’);
+———-+———-+
| column_0 | column_1 |
+———-+———-+
| 1 | a |
| 2 | b |
+———-+———-+
2 rows in set (0.07 sec)
8.INTERSECT和EXCEPT支持
MySQL 8.0.31 开始支持 INTERSECT 和 EXCEPT，分别用来取两个集合的交集和差集。
— INTERSECT 交集
# MySQL 5.7
mysql> select * from nsy_test;
+—-+——+———————-+
| id | name | url |
+—-+——+———————-+
| 1 | a | http://www.baidu.com |
| 2 | b | http://www.163.com |
+—-+——+———————-+
2 rows in set (0.00 sec)
mysql> select * from nsy_test2;
+—-+——+———————-+
| id | name | url |
+—-+——+———————-+
| 1 | a | http://www.baidu.com |
| 2 | b | http://www.163.com |
+—-+——+———————-+
2 rows in set (0.00 sec)
mysql> select * from test.nsy_test intersect select * from test.nsy_test2;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘select * from test.nsy_test2’ at line 1
# MySQL 8.0
mysql> select * from test.nsy_test intersect select * from test.nsy_test2;
+—-+——+———————-+
| id | name | url |
+—-+——+———————-+
| 1 | a | http://www.baidu.com |
| 2 | b | http://www.163.com |
+—-+——+———————-+
2 rows in set (0.01 sec)
— EXCEPT 差集
# MySQL 5.7
mysql> select * from test.nsy_test except select * from test.nsy_test2;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘select * from test.nsy_test2’ at line 1
# MySQL 8.0
mysql> select * from test.nsy_test except select * from test.nsy_test2;
Empty set (0.00 sec)
9.表上必须有主键及隐式主键参数
# MySQL 5.7
mysql> show variables like ‘%sql_require_primary_key%’;
Empty set (0.05 sec)
mysql> show variables like ‘%sql_generate_invisible_primary_key%’;
Empty set (0.01 sec)
# MySQL 8.0
— sql_require_primary_key 、sql_generate_invisible_primary_key 默认值都是 OFF
将以上两个参数配置为ON后：
mysql> show variables like ‘%sql_require_primary_key%’;
+————————-+——-+
| Variable_name | Value |
+————————-+——-+
| sql_require_primary_key | ON |
+————————-+——-+
1 row in set (0.05 sec)
mysql> show variables like ‘%sql_generate_invisible_primary_key%’;
+————————————+——-+
| Variable_name | Value |
+————————————+——-+
| sql_generate_invisible_primary_key | ON |
+————————————+——-+
1 row in set (0.07 sec)
— 表中没用主键自动创建了一个 my_row_id 列的主键
CREATE TABLE IF NOT EXISTS `test`.`nsy_test10` (
`id` INT(10) UNSIGNED NOT NULL,
`name` VARCHAR(100) NULL DEFAULT NULL COLLATE ‘utf8mb4_unicode_ci’,
`url` VARCHAR(150) NOT NULL COMMENT ‘url’ COLLATE ‘utf8mb4_unicode_ci’,
INDEX `idx_name` (`name`)
)
COMMENT=’测试表’
DEFAULT CHARSET = utf8mb4
COLLATE=’utf8mb4_unicode_ci’
ENGINE=InnoDB;
mysql> show create table test.nsy_test10G;
*************************** 1. row ***************************
Table: nsy_test10
Create Table: CREATE TABLE `nsy_test10` (
`my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
`id` int unsigned NOT NULL,
`name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`url` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT ‘url’,
PRIMARY KEY (`my_row_id`),
KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT=’测试表’
1 row in set (0.00 sec)
ERROR:
No query specified
插入数据：
insert into test.nsy_test10( my_row_id,id,name,url) values(1,2,’a’,’http://www.baidu.com’);
insert into test.nsy_test10( my_row_id,id,name,url) values(2,3,’b’,’http://www.163.com’);
正常查询：
mysql> select * from test.nsy_test10;
+—-+——+———————-+
| id | name | url |
+—-+——+———————-+
| 2 | a | http://www.baidu.com |
| 3 | b | http://www.163.com |
+—-+——+———————-+
2 rows in set (0.00 sec)
指定列名查询：
mysql> select my_row_id,id,name,url from test.nsy_test10;
+———–+—-+——+———————-+
| my_row_id | id | name | url |
+———–+—-+——+———————-+
| 1 | 2 | a | http://www.baidu.com |
| 2 | 3 | b | http://www.163.com |
+———–+—-+——+———————-+
2 rows in set (0.00 sec)
10.NOWAIT和SKIP LOCKED
SELECT … FOR SHARE 和 SELECT … FOR UPDATE 语句中引入 NOWAIT 和 SKIP LOCKED 选项
# MySQL 5.7
mysql> select * from test.nsy_test10 for update nowait;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘nowait’ at line 1
mysql> select * from test.nsy_test10 for update skip locked;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘skip locked’ at line 1
# MySQL 8.0
— session1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update test.nsy_test10 set name=’aa’ where my_row_id=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
— session2
# 指定 NOWAIT 会立即报错，不会等到锁超时
mysql> select * from test.nsy_test10 for update nowait;
ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.
# 指定 SKIP LOCKED 则会跳过锁定行,如果没有索引会返回0行
mysql> select * from test.nsy_test10 for update skip locked;
+—-+——+——————–+
| id | name | url |
+—-+——+——————–+
| 3 | b | http://www.163.com |
+—-+——+——————–+
1 row in set (0.01 sec)
11.set_var hint支持
— set_var 语句级设置参数；5.7 中看成是注释，执行不会报错；8.0 作为hint使用
CREATE TABLE IF NOT EXISTS `test`.`nsy_test11` (
`id` INT(10) UNSIGNED NOT NULL auto_increment,
`name` VARCHAR(100) NULL DEFAULT NULL COLLATE ‘utf8mb4_unicode_ci’,
`url` VARCHAR(150) NOT NULL COMMENT ‘url’ COLLATE ‘utf8mb4_unicode_ci’,
INDEX `idx_name` (`name`),
primary key(id)
)
COMMENT=’测试表’
DEFAULT CHARSET = utf8mb4
COLLATE=’utf8mb4_unicode_ci’
ENGINE=InnoDB;
插入测试数据：
mysql> insert into test.nsy_test11(id,name,url) values(1,’a’,’http://www.baidu.com’);
mysql> insert into test.nsy_test11(name,url) select name,url from test.nsy_test11;
…
mysql> insert into test.nsy_test11(name,url) select name,url from test.nsy_test11;
Query OK, 262144 rows affected (12.37 sec)
Records: 262144 Duplicates: 0 Warnings: 0
# MySQL 5.7
mysql> SELECT /*+ SET_VAR(max_execution_time = 1) */ count(*) FROM test.nsy_test11;
+———-+
| count(*) |
+———-+
| 524288 |
+———-+
1 row in set, 1 warning (0.17 sec)
— max_execution_time =1 ，表示执行时间超过 1ms 报错
# MySQL 8.0 因为查询时间超过了1ms，所以报错了，没有返回结果
mysql> SELECT /*+ SET_VAR(max_execution_time = 1) */ count(*) FROM test.nsy_test11;
ERROR 1317 (70100): Query execution was interrupted
12.字段快速加列
mysql> show create table test.fba_inbound_shipment_itemG;
*************************** 1. row ***************************
Table: fba_inbound_shipment_item
Create Table: CREATE TABLE `fba_inbound_shipment_item` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`shipment_id` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`seller_sku` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`erp_sku` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” COMMENT ‘erpSku’,
`quantity_plan_shipped` int(11) DEFAULT NULL,
`quantity_shipped` int(11) DEFAULT NULL,
`quantity_real_shipped` int(11) DEFAULT NULL,
`quantity_in_case` int(11) DEFAULT NULL,
`quantity_received` int(11) DEFAULT NULL,
`fulfillment_network_sku` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`fnsku` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`asin` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`shipment_status` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT ‘normal, diff’,
`arrival_status` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT ‘normal, diff, processed’,
`arrival_status_remark` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`receive_complete_date` datetime DEFAULT NULL COMMENT ‘完成接收时间’,
`mode` int(8) NOT NULL DEFAULT ‘0’ COMMENT ‘0:旧接口，1:新接口’,
`difference_quantity` int(8) NOT NULL DEFAULT ‘0’ COMMENT ‘差异数量’,
`push_difference_quantity_status` tinyint(1) NOT NULL DEFAULT ‘-1’ COMMENT ‘到金蝶的状态(-1:不需要推送,0:未推送,1:已推送)’,
`sec_quantity_received` int(11) DEFAULT NULL COMMENT ‘两个月后的拉到的亚马逊数量’,
`sec_cal_date` datetime DEFAULT NULL COMMENT ‘两个月后的差异计算时间’,
`sec_difference_quantity` int(11) DEFAULT NULL COMMENT ‘两个月后的差异数量’,
`sec_push_difference_quantity_status` int(8) NOT NULL DEFAULT ‘0’ COMMENT ‘两个月差异推送状态’,
`is_stick_label` tinyint(1) NOT NULL DEFAULT ‘0’ COMMENT ‘是否贴T标(0:否 1:是)’,
`create_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,
`create_by` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT ‘创建者’,
`update_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新时间’,
`update_by` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT ‘更新者’,
PRIMARY KEY (`id`),
KEY `shipment_id` (`shipment_id`),
KEY `asin` (`asin`),
KEY `shipment_status` (`shipment_status`),
KEY `arrival_status` (`arrival_status`),
KEY `seller_sku` (`seller_sku`),
KEY `idx_pushdifferencequantitystatus_updatedate` (`push_difference_quantity_status`,`update_date`),
KEY `idx_erp_sku` (`erp_sku`)
) ENGINE=InnoDB AUTO_INCREMENT=4379357 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT=’入库货件item详情’
1 row in set (0.00 sec)
#5.7
create database test;
导入一张大表：
mysql> select count(*) from test.fba_inbound_shipment_item;
+———-+
| count(*) |
+———-+
| 4212198 |
+———-+
1 row in set (4.02 sec)
#添加列
mysql> alter table test.fba_inbound_shipment_item add column `seller_spec` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL after seller_sku;
Query OK, 0 rows affected (3 min 38.62 sec)
Records: 0 Duplicates: 0 Warnings: 0
#删除列
mysql> alter table test.fba_inbound_shipment_item drop column `seller_spec`;
Query OK, 0 rows affected (3 min 37.24 sec)
Records: 0 Duplicates: 0 Warnings: 0
#修改列：
mysql> alter table test.fba_inbound_shipment_item modify column `erp_sku` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” COMMENT ‘erpSku’ after seller_sku;
Query OK, 4212198 rows affected (16 min 10.03 sec)
Records: 4212198 Duplicates: 0 Warnings: 0
| 10 | root | localhost | test | Query | 293 | copy to tmp table | alter table test.fba_inbound_shipment_item modify column `erp_sku` varchar(250) COLLATE utf8mb4_unic |
| 11 | root | localhost | NULL | Query | 0 | init | show processlist
#8.0
create database test;
导入一张大表：
mysql> select count(*) from test.fba_inbound_shipment_item;
+———-+
| count(*) |
+———-+
| 4212198 |
+———-+
1 row in set (7.39 sec)
#添加列
mysql> alter table test.fba_inbound_shipment_item add column `seller_spec` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL after seller_sku;
Query OK, 0 rows affected (0.82 sec)
Records: 0 Duplicates: 0 Warnings: 0
#删除列
mysql> alter table test.fba_inbound_shipment_item drop column `seller_spec`;
Query OK, 0 rows affected (0.23 sec)
Records: 0 Duplicates: 0 Warnings: 0
#修改列：
mysql> alter table test.fba_inbound_shipment_item modify column `erp_sku` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” COMMENT ‘erpSku’ after seller_sku;
Query OK, 4212198 rows affected (18 min 15.90 sec)
Records: 4212198 Duplicates: 0 Warnings: 0
| 5 | root | localhost | test | Query | 300 | copy to tmp table | alter table test.fba_inbound_shipment_item modify column `erp_sku` varchar(250) COLLATE utf8mb4_unic |
| 7 | root | localhost | NULL | Query | 0 | starting | show processlist
参考文档
https://dev.mysql.com/doc/refman/8.0/en/sql-statements.html
作者：阮胜昌 MySQL 8.0 OCP、Oracle 11G OCP,人大金仓KCP PostgreSQL PCA,Oceanbase OBCA、PingCAP PCTA、软考中级数据库系统工程师、RHCE7.0、cisco CCNA认证
擅长主流数据库MySQL、Oracle、PostgreSQL运维与开发、数据恢复、安装配置、数据迁移、集群容灾、SQL性能优化、高可用架构设计、故障应急处理等
可提供技术业务：
1.DB故障处理/疑难杂症远程支援
2.Mysql/PG/Oracle/SQLSERVER数据库技术服务
欢迎关注我的博客:http://www.linuxmysql.com 第一时间一起学习新知识！
服务器托管，北京服务器托管，服务器租用 http://www.fwqtg.net
机房租用，北京机房租用，IDC机房托管， http://www.fwqtg.net
相关推荐: 将Agent技术的灵活性引入RPA，清华等发布自动化智能体ProAgent
近日，来自清华大学的研究人员联合面壁智能、中国人民大学、MIT、CMU 等机构共同发布了新一代流程自动化范式 “智能体流程自动化” Agentic Process Automation（APA），结合大模型智能体帮助人类进行工作流构建，并让智能体自主处理工作流…
