数据库研发人员必看的MySQL 8.0新特性

头像
数库信息技术
     福建
    阅读 31 分钟

    本文汇总了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<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;
    idname
    1a
    2b

    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;
    idnamerow_num
    1a1
    2b2

    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;
    idnamebirth_date
    1a2024-01-15
    2b2024-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','http://www.baidu.com');

    mysql> select * from test.nsy_test6;
    idnameurl
    1ahttp://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;
    idnameurl
    1ahttp://www.baidu.com

    1 row in set (0.00 sec)

    -- 显示指定不可见列进行查询：

    mysql> select id,name,url,id_no from test.nsy_test6;
    idnameurlid_no
    1ahttp://www.baidu.comabc

    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;
    TableNon_uniqueKey_nameSeq_in_indexColumn_nameCollationCardinalitySub_partPackedNullIndex_typeCommentIndex_commentVisibleExpression
    nsy_test80PRIMARY1idA0NULLNULL BTREE YESNULL
    nsy_test81idx_name1nameA0NULLNULLYESBTREE NONULL

    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_NAMEIS_VISIBLE
    idx_nameNO
    PRIMARYYES

    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;
    idcid
    10
    30
    40
    55
    61
    75

    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;
    idcreate_datecreate_byupdate_dateupdate_bysortversion
    32024-01-19 00:00:00NULL2024-01-20 00:00:00NULL11

    1 row in set (0.00 sec)

    mysql> explain select * from test.nsy_test11 where day(create_date)=19;
    idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
    1SIMPLEnsy_test11NULLrefidx_create_date_funidx_create_date_fun5const1100.00NULL

    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_0column_1
    1a
    2b

    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;
    idnameurl
    1ahttp://www.baidu.com
    2bhttp://www.163.com

    2 rows in set (0.00 sec)

    mysql> select * from nsy_test2;
    idnameurl
    1ahttp://www.baidu.com
    2bhttp://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;
    idnameurl
    1ahttp://www.baidu.com
    2bhttp://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_nameValue
    sql_require_primary_keyON

    1 row in set (0.05 sec)

    mysql> show variables like '%sql_generate_invisible_primary_key%';
    Variable_nameValue
    sql_generate_invisible_primary_keyON

    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','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;
    idnameurl
    2ahttp://www.baidu.com
    3bhttp://www.163.com

    2 rows in set (0.00 sec)
    指定列名查询：

    mysql> select my_row_id,id,name,url from test.nsy_test10;
    my_row_ididnameurl
    12ahttp://www.baidu.com
    23bhttp://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;
    idnameurl
    3bhttp://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_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

    参考文档
    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 第一时间一起学习新知识！

    mysql
    数库信息技术
    1 声望0 粉丝

    MySQL DBA

    « 上一篇
    MySQL-8.3.0 innovation 创新版本YUM安装配置
    下一篇 »
    PHP无法连接MySQL8.0数据库问题处理 报错如下： SQLSTATE[HY000] [2054]

    引用和评论

    推荐阅读
    头像
    PHP无法连接MySQL8.0数据库问题处理 报错如下： SQLSTATE[HY000] [2054]

    数库信息技术阅读 1.3k

    头像
    MySQL慢查询日志：性能优化的终极指南

    晚上不吃饭赞 2阅读 461

    头像
    MySQL 备份 Shell 脚本：支持远程同步与阿里云 OSS 备份

    苏琢玉赞 1阅读 727

    头像
    《SQL应用场景解析：如何通过SQL解决实际业务问题》

    瘦瘦的企鹅赞 1阅读 717

    头像
    Devin 发布 DeepWiki，2 星的项目直接装出万星的气场

    Bytebase赞 1阅读 543

    头像
    php+mysql 搭建一个在线游戏网站目前已有2000+游戏【代码解析 一】

    海拥阅读 1.1k

    头像
    一键实现 Oracle 数据整库同步至 Apache Doris

    SelectDB技术团队阅读 1.1k

    0 条评论
    得票最新