mysql新特性invisible columns

在 MySQL 8.0.23 版本中新添加了一个功能：可以给字段附加不可见属性。对于非指定字段的查询语句默认隐藏不可见字段的内容。该功能可适用于需要给表添加字段，但是需要对已有的业务系统隐藏时使用，或者给表添加主键字段或索引字段时使用。

DDL语句

在未使用invisible属性时，字段默认时visible属性。

create时可以指定invisible属性。

    create table t(id int not null invisible,name varchar(64));

也可以使用alter指令，为列添加invisible或者visible属性。

    alter table t change column id id int visible;
    Query OK, 0 rows affected (0.00 sec)
    Records: 0  Duplicates: 0  Warnings: 0    alter table t change column name name varchar(64) invisible;
    Query OK, 2 rows affected (0.01 sec)
    Records: 2  Duplicates: 0  Warnings: 0

表中的列至少要有一个为visible属性

    alter table t change column id id int invisible;
    Query OK, 0 rows affected (0.00 sec)
    Records: 0  Duplicates: 0  Warnings: 0    alter table t change column name name varchar(64) invisible;
    ERROR 4028 (HY000): A table must have at least one visible column.

invisible属性可以使用desc指令查看，并显示在extra字段中

    desc t;
    +-------+-------------+------+-----+---------+-----------+
    | Field | Type        | Null | Key | Default | Extra     |
    +-------+-------------+------+-----+---------+-----------+
    | id    | int         | NO   |     | NULL    | INVISIBLE |
    | name  | varchar(64) | YES  |     | NULL    |           |
    +-------+-------------+------+-----+---------+-----------+
    2 rows in set (0.00 sec)

使用 show create table ...也可以查看invisible属性

    *************************** 1. row ***************************
           Table: t
    Create Table: CREATE TABLE t (
      id int NOT NULL /*!80023 INVISIBLE */,
      name varchar(64) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
    1 row in set (0.00 sec)

使用invisible属性时可以同时支持NULL,NOT NULL,PRIMARY KEY,UINIQUE,FOREIGN KEY以及约束检测CHECK

create table like...会将源表中invisible的列进行创建，并在新表中具有invisible属性

    select id,name from t;
    +------+------+
    | id   | name |
    +------+------+
    |    1 | ds   |
    |    2 | ww   |
    +------+------+
    2 rows in set (0.00 sec)    create table t1 like t;
    Query OK, 0 rows affected (0.01 sec)    mysql> select id,name from t;
    +------+------+
    | id   | name |
    +------+------+
    |    1 | ds   |
    |    2 | ww   |
    +------+------+
    2 rows in set (0.00 sec)    desc t;
    +-------+-------------+------+-----+---------+-----------+
    | Field | Type        | Null | Key | Default | Extra     |
    +-------+-------------+------+-----+---------+-----------+
    | id    | int         | YES  |     | NULL    | INVISIBLE |
    | name  | varchar(64) | YES  |     | NULL    |           |
    +-------+-------------+------+-----+---------+-----------+
    2 rows in set (0.00 sec)

create table as select ... 在select中必须指定invisible的列，否则无法被查询到以及创建。

    create table t2 as select * from t;
    Query OK, 2 rows affected (0.01 sec)
    Records: 2  Duplicates: 0  Warnings: 0    desc t2 ；
    +-------+-------------+------+-----+---------+-------+
    | Field | Type        | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | name  | varchar(64) | YES  |     | NULL    |       |
    +-------+-------------+------+-----+---------+-------+
    1 row in set (0.00 sec)    create table t3 as select id,name from t;
    Query OK, 2 rows affected (0.01 sec)
    Records: 2  Duplicates: 0  Warnings: 0    desc t3;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type        | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | id    | int         | YES  |     | NULL    |       |
    | name  | varchar(64) | YES  |     | NULL    |       |
    +-------+------------+------+-----+---------+-------+
    2 rows in set (0.00 sec)

即使create table as select..指定的列有invisible属性，创建时仍要指定invisible的属性，否则在新表中的列的属性为visible。

create table t4  (id int invisible,name varchar(64)) as select id,name from t;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0    desc t4;
+-------+-------------+------+-----+---------+-----------+
| Field | Type        | Null | Key | Default | Extra     |
+-------+-------------+------+-----+---------+-----------+
| id    | int         | YES  |     | NULL    | INVISIBLE |
| name  | varchar(64) | YES  |     | NULL    |           |
+-------+-------------+------+-----+---------+-----------+
2 rows in set (0.00 sec)
create view的操作使用方法与create table相同。
create view v as select * from t;
Query OK, 0 rows affected (0.00 sec)    mysql> desc v;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(64) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.01 sec)

DML语句

insert时必须显式指定有invisible属性的列，否则只能插入visible属性的数据，invisible列会填入默认值（如果定义了默认值）。如果隐式列定义的时候没有默认值，且禁止非空，在插入时没有指定隐式列，会导致插入失败

insert into t values(3,'ss');
ERROR 1136 (21S01): Column count doesn't match value count at row 1  
insert into t (id,name) values(1,'ds');
Query OK, 1 row affected (0.00 sec)
insert into t values('kk');
Query OK, 1 row affected (0.00 sec)
insert into t() values('ii');
Query OK, 1 row affected (0.00 sec)
select id,name from t;
+------+------+
| id   | name |
+------+------+
|    1 | ds   |
|    2 | ww   |
| NULL | kk   |
| NULL | ii   |
+------+------+
4 rows in set (0.00 sec)

不能被select * 查询到,select时必须显式指定有invisible属性的列

mysql> select * from t;
+------+
| name |
+------+
| ds   |
+------+
1 row in set (0.00 sec)    mysql> select id,name from t;
+----+------+
| id | name |
+----+------+
|  1 | ds   |
+----+------+
1 row in set (0.00 sec)

使用 select...outfile 以及 load data 方式导入导出含不可见字段表时，默认对不可见列不做处理。

如果需要导出不可见列的数据或者将数据导入至不可见列中，也需要显式指定字段名。

binlog记录

binlog 会记录 DDL 的不可见列字段属性当 binlog 为 STATEMENT 模式时，原始 DML 语句会被记录. 当 binlog 为 ROW 模式时，不可见列如果有值，也会被记录。

insert into t (id,name) values(5,'ddd');
Query OK, 1 row affected (0.00 sec)

ROW模式读取binlog文件

BEGIN
/!/;
# at 4378
#221021  2:45:02 server id 1  end_log_pos 4434 CRC32 0x7b78bf19     Table_map: my_db.t mapped to number 96
# at 4434
#221021  2:45:02 server id 1  end_log_pos 4478 CRC32 0xfd219e51     Write_rows: table id 96 flags: STMT_END_F    BINLOG '
rgdSYxMBAAAAOAAAAFIRAAAAAGAAAAAAAAEABW15X2RiAAF0AAIDDwLwAAMBAQACA/z/ABm/eHs=
rgdSYx4BAAAALAAAAH4RAAAAAGAAAAAAAAEAAgAC/wAFAAAAA2RkZFGeIf0=
'/!/;
### INSERT INTO my_db.t
### SET
###   @1=5
###   @2='ddd'
# at 4478
#221021  2:45:02 server id 1  end_log_pos 4509 CRC32 0xd1be06a1     Xid = 49
COMMIT/*!*/;

mysql新特性Generated Invisible Primary Key(GIPK)

MySQL8.0.30中新增了特性Generated Invisible Primary Key(GIPK)，自动创建隐式主键my_row_id。通过设置变量

SET SESSION sql_generate_invisible_primary_key = ON;

SQL层可以自动为没有创建主键的表添加隐式主键。

create table t5 (id int unsigned,name varchar(20));
Query OK, 0 rows affected (0.01 sec)    desc t5;
+-----------+-----------------+------+-----+---------+--------------------------+
| Field     | Type            | Null | Key | Default | Extra                    |
+-----------+-----------------+------+-----+---------+--------------------------+
| my_row_id | bigint unsigned | NO   | PRI | NULL    | auto_increment INVISIBLE |
| id        | int unsigned    | YES  |     | NULL    |                          |
| name      | varchar(20)     | YES  |     | NULL    |                          |
+-----------+-----------------+------+-----+---------+--------------------------+

该功能近支持使用InnoDB引擎的表，不支持分区表，支持临时表，会被binlog记录

GIPK列使用alter时，在开启sql_generate_invisible_primary_key时仅支持修改gipk列的visible和invisible属性，无法删除。关闭sql_generate_invisible_primary_key可以删除

因为my_row_id占据了自增属性，所以无法再创建auto_increment属性字段，不能有其他字段占用my_row_id

系统变量SHOW_GIPK_IN_CREATE_TABLE_AND_INFORMATION_SCHEMA可以控制gipk属性是否能被show create table、show columns、show index等显示，默认为ON。设置为OFF无法查询到gipk属性

gipk相关函数

gipk相关的函数主要定义在sql_gipk.h和sql_gipk.cc文件中,当其他函数使用gipk功能时会将其调用

bool is_generate_invisible_primary_key_mode_active();

检测gipk模式是否开启。在bootstrap以及initialize 线程,无法使用GPIK。包含三种线程，包括数据字典初始化线程SYSTEM_THREAD_DD_INITIALIZE，数据字典重启进程SYSTEM_THREAD_DD_RESTART，初始化线程SYSTEM_THREAD_SERVER_INITIALIZE

bool is_generated_invisible_primary_key_column_name（）

检测列名my_row_id是否可用

bool is_candidate_table_for_invisible_primary_key_generation（）

检测创建表时是否可以创建隐式主键。 1.表中未定义主键 2.存储引擎支持主键

bool validate_and_generate_invisible_primary_key(）

验证并生成隐式主键给创建的表。 1.表是未分区表，gipk目前不支持分区表上使用 2.表未含有auto_increment属性的列 3.表未含有列名为my_row_id的列 4.不能在binlog_format=STATEMENT下，使用CREATE TABLE ... SELECT（在复制时，使用statement不安全）

bool adjust_generated_invisible_primary_key_column_position()

在使用alter的情况下，调整表中的列顺序 1.当调整gipk在表中的顺序，产生报错 2.当调整其他列的位置置于gipk所在列之前，仍会保留gipk列的在第一位置，被调整的列会放于gipk列之后的位置

bool check_primary_key_alter_restrictions(）

检测alter在主键和列上的约束 1.sql_generate_invisible_primary_key启用后，删除主键的前提是在新表同时定义了主键 2.无论sql_generate_invisible_primary_key是否启用，删除gipk只能是其所在列被删除时才发生 3.无论sql_generate_invisible_primary_key是否启用，CHANGE、MODIFY、ALTER只能修改gipk的invisible和visible属性

bool table_def_has_generated_invisible_primary_key()

检测正在创建和修改的表是否定义gipk

bool table_has_generated_invisible_primary_key(TABLE *table);

检测表是否有gipk

主从复制

从库复制时不会将sql_generate_invisible_primary_key 的值复制到从库中。当主库使用SET SESSION sql_generate_invisible_primary_key = ON时，从库的变量依然会保持OFF状态，但是会将隐式主键的列复制到从库中。

看个例子，在主库上

mysql> SET SESSION sql_generate_invisible_primary_key = ON;
 Query OK, 0 rows affected (0.00 sec) 
mysql> create table my_table (id int,phone int); 
Query OK, 0 rows affected (0.09 sec) 
mysql> insert into my_table values (5,999); 
Query OK, 1 row affected (0.01 sec)

在从库上

mysql> show variables like 'sql_generate_invisible_primary_key';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| sql_generate_invisible_primary_key | OFF   |
+------------------------------------+-------+
1 row in set (0.00 sec)    mysql> select *from my_table;
+------+-------+
| id   | phone |
+------+-------+
|    5 |   999 |
+------+-------+
1 row in set (0.00 sec)    mysql> select *,my_row_id from my_table;
+------+-------+-----------+
| id   | phone | my_row_id |
+------+-------+-----------+
|    5 |   999 |         1 |
+------+-------+-----------+
1 row in set (0.00 sec)

若主库SET SESSION sql_generate_invisible_primary_key = OFF，而从库SET SESSION sql_generate_invisible_primary_key = ON时，从库不会为没有隐式主键的表添加隐式主键

逻辑备份

mysql 8.0.30 版本的 mysqldump 提供的 --skip-generated-invisible-primary-key 选项会忽略 GIPK 信息。mysqldump 时，不带该参数，逻辑导出的数据会包含隐式主键。如果带上该参数，则不带隐式主键。

mysql新特性invisible columns

DDL语句

在未使用invisible属性时，字段默认时visible属性。

create时可以指定invisible属性。

    create table t(id int not null invisible,name varchar(64));

也可以使用alter指令，为列添加invisible或者visible属性。

    alter table t change column id id int visible;
    Query OK, 0 rows affected (0.00 sec)
    Records: 0  Duplicates: 0  Warnings: 0    alter table t change column name name varchar(64) invisible;
    Query OK, 2 rows affected (0.01 sec)
    Records: 2  Duplicates: 0  Warnings: 0

表中的列至少要有一个为visible属性

    alter table t change column id id int invisible;
    Query OK, 0 rows affected (0.00 sec)
    Records: 0  Duplicates: 0  Warnings: 0    alter table t change column name name varchar(64) invisible;
    ERROR 4028 (HY000): A table must have at least one visible column.

invisible属性可以使用desc指令查看，并显示在extra字段中

    desc t;
    +-------+-------------+------+-----+---------+-----------+
    | Field | Type        | Null | Key | Default | Extra     |
    +-------+-------------+------+-----+---------+-----------+
    | id    | int         | NO   |     | NULL    | INVISIBLE |
    | name  | varchar(64) | YES  |     | NULL    |           |
    +-------+-------------+------+-----+---------+-----------+
    2 rows in set (0.00 sec)

使用 show create table ...也可以查看invisible属性

    *************************** 1. row ***************************
           Table: t
    Create Table: CREATE TABLE t (
      id int NOT NULL /*!80023 INVISIBLE */,
      name varchar(64) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
    1 row in set (0.00 sec)

使用invisible属性时可以同时支持NULL,NOT NULL,PRIMARY KEY,UINIQUE,FOREIGN KEY以及约束检测CHECK

create table like...会将源表中invisible的列进行创建，并在新表中具有invisible属性

    select id,name from t;
    +------+------+
    | id   | name |
    +------+------+
    |    1 | ds   |
    |    2 | ww   |
    +------+------+
    2 rows in set (0.00 sec)    create table t1 like t;
    Query OK, 0 rows affected (0.01 sec)    mysql> select id,name from t;
    +------+------+
    | id   | name |
    +------+------+
    |    1 | ds   |
    |    2 | ww   |
    +------+------+
    2 rows in set (0.00 sec)    desc t;
    +-------+-------------+------+-----+---------+-----------+
    | Field | Type        | Null | Key | Default | Extra     |
    +-------+-------------+------+-----+---------+-----------+
    | id    | int         | YES  |     | NULL    | INVISIBLE |
    | name  | varchar(64) | YES  |     | NULL    |           |
    +-------+-------------+------+-----+---------+-----------+
    2 rows in set (0.00 sec)

create table as select ... 在select中必须指定invisible的列，否则无法被查询到以及创建。

    create table t2 as select * from t;
    Query OK, 2 rows affected (0.01 sec)
    Records: 2  Duplicates: 0  Warnings: 0    desc t2 ；
    +-------+-------------+------+-----+---------+-------+
    | Field | Type        | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | name  | varchar(64) | YES  |     | NULL    |       |
    +-------+-------------+------+-----+---------+-------+
    1 row in set (0.00 sec)    create table t3 as select id,name from t;
    Query OK, 2 rows affected (0.01 sec)
    Records: 2  Duplicates: 0  Warnings: 0    desc t3;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type        | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | id    | int         | YES  |     | NULL    |       |
    | name  | varchar(64) | YES  |     | NULL    |       |
    +-------+------------+------+-----+---------+-------+
    2 rows in set (0.00 sec)

即使create table as select..指定的列有invisible属性，创建时仍要指定invisible的属性，否则在新表中的列的属性为visible。

create table t4  (id int invisible,name varchar(64)) as select id,name from t;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0    desc t4;
+-------+-------------+------+-----+---------+-----------+
| Field | Type        | Null | Key | Default | Extra     |
+-------+-------------+------+-----+---------+-----------+
| id    | int         | YES  |     | NULL    | INVISIBLE |
| name  | varchar(64) | YES  |     | NULL    |           |
+-------+-------------+------+-----+---------+-----------+
2 rows in set (0.00 sec)
create view的操作使用方法与create table相同。
create view v as select * from t;
Query OK, 0 rows affected (0.00 sec)    mysql> desc v;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(64) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.01 sec)

DML语句

insert into t values(3,'ss');
ERROR 1136 (21S01): Column count doesn't match value count at row 1  
insert into t (id,name) values(1,'ds');
Query OK, 1 row affected (0.00 sec)
insert into t values('kk');
Query OK, 1 row affected (0.00 sec)
insert into t() values('ii');
Query OK, 1 row affected (0.00 sec)
select id,name from t;
+------+------+
| id   | name |
+------+------+
|    1 | ds   |
|    2 | ww   |
| NULL | kk   |
| NULL | ii   |
+------+------+
4 rows in set (0.00 sec)

不能被select * 查询到,select时必须显式指定有invisible属性的列

mysql> select * from t;
+------+
| name |
+------+
| ds   |
+------+
1 row in set (0.00 sec)    mysql> select id,name from t;
+----+------+
| id | name |
+----+------+
|  1 | ds   |
+----+------+
1 row in set (0.00 sec)

使用 select...outfile 以及 load data 方式导入导出含不可见字段表时，默认对不可见列不做处理。

如果需要导出不可见列的数据或者将数据导入至不可见列中，也需要显式指定字段名。

binlog记录

binlog 会记录 DDL 的不可见列字段属性当 binlog 为 STATEMENT 模式时，原始 DML 语句会被记录. 当 binlog 为 ROW 模式时，不可见列如果有值，也会被记录。

insert into t (id,name) values(5,'ddd');
Query OK, 1 row affected (0.00 sec)

ROW模式读取binlog文件

BEGIN
/!/;
# at 4378
#221021  2:45:02 server id 1  end_log_pos 4434 CRC32 0x7b78bf19     Table_map: my_db.t mapped to number 96
# at 4434
#221021  2:45:02 server id 1  end_log_pos 4478 CRC32 0xfd219e51     Write_rows: table id 96 flags: STMT_END_F    BINLOG '
rgdSYxMBAAAAOAAAAFIRAAAAAGAAAAAAAAEABW15X2RiAAF0AAIDDwLwAAMBAQACA/z/ABm/eHs=
rgdSYx4BAAAALAAAAH4RAAAAAGAAAAAAAAEAAgAC/wAFAAAAA2RkZFGeIf0=
'/!/;
### INSERT INTO my_db.t
### SET
###   @1=5
###   @2='ddd'
# at 4478
#221021  2:45:02 server id 1  end_log_pos 4509 CRC32 0xd1be06a1     Xid = 49
COMMIT/*!*/;

mysql新特性Generated Invisible Primary Key(GIPK)

MySQL8.0.30中新增了特性Generated Invisible Primary Key(GIPK)，自动创建隐式主键my_row_id。通过设置变量

SET SESSION sql_generate_invisible_primary_key = ON;

SQL层可以自动为没有创建主键的表添加隐式主键。

create table t5 (id int unsigned,name varchar(20));
Query OK, 0 rows affected (0.01 sec)    desc t5;
+-----------+-----------------+------+-----+---------+--------------------------+
| Field     | Type            | Null | Key | Default | Extra                    |
+-----------+-----------------+------+-----+---------+--------------------------+
| my_row_id | bigint unsigned | NO   | PRI | NULL    | auto_increment INVISIBLE |
| id        | int unsigned    | YES  |     | NULL    |                          |
| name      | varchar(20)     | YES  |     | NULL    |                          |
+-----------+-----------------+------+-----+---------+--------------------------+

该功能近支持使用InnoDB引擎的表，不支持分区表，支持临时表，会被binlog记录

GIPK列使用alter时，在开启sql_generate_invisible_primary_key时仅支持修改gipk列的visible和invisible属性，无法删除。关闭sql_generate_invisible_primary_key可以删除

因为my_row_id占据了自增属性，所以无法再创建auto_increment属性字段，不能有其他字段占用my_row_id

gipk相关函数

gipk相关的函数主要定义在sql_gipk.h和sql_gipk.cc文件中,当其他函数使用gipk功能时会将其调用

bool is_generate_invisible_primary_key_mode_active();

bool is_generated_invisible_primary_key_column_name（）

检测列名my_row_id是否可用

bool is_candidate_table_for_invisible_primary_key_generation（）

检测创建表时是否可以创建隐式主键。 1.表中未定义主键 2.存储引擎支持主键

bool validate_and_generate_invisible_primary_key(）

bool adjust_generated_invisible_primary_key_column_position()

bool check_primary_key_alter_restrictions(）

bool table_def_has_generated_invisible_primary_key()

检测正在创建和修改的表是否定义gipk

bool table_has_generated_invisible_primary_key(TABLE *table);

检测表是否有gipk

主从复制

看个例子，在主库上

mysql> SET SESSION sql_generate_invisible_primary_key = ON;
 Query OK, 0 rows affected (0.00 sec) 
mysql> create table my_table (id int,phone int); 
Query OK, 0 rows affected (0.09 sec) 
mysql> insert into my_table values (5,999); 
Query OK, 1 row affected (0.01 sec)

在从库上

mysql> show variables like 'sql_generate_invisible_primary_key';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| sql_generate_invisible_primary_key | OFF   |
+------------------------------------+-------+
1 row in set (0.00 sec)    mysql> select *from my_table;
+------+-------+
| id   | phone |
+------+-------+
|    5 |   999 |
+------+-------+
1 row in set (0.00 sec)    mysql> select *,my_row_id from my_table;
+------+-------+-----------+
| id   | phone | my_row_id |
+------+-------+-----------+
|    5 |   999 |         1 |
+------+-------+-----------+
1 row in set (0.00 sec)

若主库SET SESSION sql_generate_invisible_primary_key = OFF，而从库SET SESSION sql_generate_invisible_primary_key = ON时，从库不会为没有隐式主键的表添加隐式主键

应用商城

合作伙伴

开发者

支持与服务

了解天翼云

mysql新特性invisible columns

mysql新特性invisible columns

DDL语句

DML语句

binlog记录

mysql新特性Generated Invisible Primary Key(GIPK)

gipk相关函数

主从复制

逻辑备份

mysql新特性invisible columns

mysql新特性invisible columns

DDL语句

DML语句

binlog记录

mysql新特性Generated Invisible Primary Key(GIPK)

gipk相关函数

主从复制

逻辑备份

活动

应用商城

合作伙伴

开发者

支持与服务

了解天翼云

mysql新特性invisible columns

mysql新特性invisible columns

DDL语句

DML语句

binlog记录

mysql新特性Generated Invisible Primary Key(GIPK)

gipk相关函数

主从复制

逻辑备份

mysql新特性invisible columns

mysql新特性invisible columns

DDL语句

DML语句

binlog记录

mysql新特性Generated Invisible Primary Key(GIPK)

gipk相关函数

主从复制

逻辑备份