本文汇总了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','www.baidu.com'),(2,'b','http://www.163.co…');

复制一张新表：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<5 -> ) -> 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<5 -> ) -> 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_test4\G; *************************** 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_test4\G; *************************** 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_test5\G; *************************** 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','www.baidu.com');

mysql> select * from test.nsy_test6; +----+------+----------------------+ | id | name | url | +----+------+----------------------+ | 1 | a | 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 | 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 | www.baidu.com | abc | +----+------+----------------------+-------+ 1 row in set (0.00 sec)

查看表结构： mysql> show create table test.nsy_test6\G; *************************** 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_test6\G; *************************** 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_test6\G; *************************** 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_test6\G; *************************** 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_test7\G; *************************** 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_test8\G; *************************** 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_test11\G; *************************** 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 | www.baidu.com | | 2 | b | www.163.com | +----+------+----------------------+ 2 rows in set (0.00 sec)

mysql> select * from nsy_test2; +----+------+----------------------+ | id | name | url | +----+------+----------------------+ | 1 | a | www.baidu.com | | 2 | b | 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 | www.baidu.com | | 2 | b | 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_test10\G; *************************** 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','www.baidu.com'); insert into test.nsy_test10( my_row_id,id,name,url) values(2,3,'b','www.163.com');

正常查询： mysql> select * from test.nsy_test10; +----+------+----------------------+ | id | name | url | +----+------+----------------------+ | 2 | a | www.baidu.com | | 3 | b | 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 | www.baidu.com | | 2 | 3 | b | 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 | 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','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_item\G; *************************** 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

参考文档 dev.mysql.com/doc/refman/…

作者：阮胜昌 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数据库技术服务 欢迎关注我的博客:www.linuxmysql.com 第一时间一起学习新知识！