概念描述

  从MySQL8.0.23版本开始，MySQL支持不可见列（invisible columns），在之前版本中MySQL对所有列都是可见的，所谓不可见列就是指在正常默认查询中不会显示出来，用select * from 语句是无法查询出不可见属性的列的。

使用场景

  1. 隐藏字段：刻意隐藏不想展示的字段，比如备注，敏感值，关键信息等
  2. 业务侧需要：业务侧为前期表设计不充分，创建一些隐藏字段来保留业务数据，方便后期扩展需求
  3. 提升容错性：弥补业务初期创建错误表字段问题而开通的一个“后门儿”

测试验证

1. 给现有表加不可见列：

root@localhost:rockdb 04:29:49 >select * from test_table;
+----+------+---------+
| id | name | address |
+----+------+---------+
|  1 | rock | beijing |
+----+------+---------+
1 row in set (0.00 sec)

mysql >show create table test_table\G
*************************** 1. row ***************************
       Table: test_table
Create Table: CREATE TABLE `test_table` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `address` varchar(200) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)

#### 添加一个不可见列 id_no：
mysql >alter table test_table add column id_no varchar(20) default 'xxx' invisible;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

#### 正常select * 查询结果：
mysql >select * from test_table;
+----+------+---------+
| id | name | address |
+----+------+---------+
|  1 | rock | beijing |
+----+------+---------+
1 row in set (0.00 sec)

#### 显示指定不可见列进行查询：
mysql >select id,name,address,id_no from test_table;
+----+------+---------+-------+
| id | name | address | id_no |
+----+------+---------+-------+
|  1 | rock | beijing | xxx   |
+----+------+---------+-------+
1 row in set (0.00 sec)

复制

2. 创建带有不可见列的表

mysql > create table invisible_column_table(id int not null primary key auto_increment, name varchar(100),address varchar(200),ic1 varchar(100) default '' invisible,ic2 varchar(50) not null default '' invisible);
Query OK, 0 rows affected (0.01 sec)

mysql >show create table invisible_column_table\G
*************************** 1. row ***************************
       Table: invisible_column_table
Create Table: CREATE TABLE `invisible_column_table` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `address` varchar(200) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `ic1` varchar(100) COLLATE utf8mb4_general_ci DEFAULT '' /*!80023 INVISIBLE */,
  `ic2` varchar(50) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' /*!80023 INVISIBLE */,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)

root@localhost:rockdb 04:45:26 >insert into invisible_column_table values (1,'tom','tianjin');
Query OK, 1 row affected (0.00 sec)

#### 正常select * 查询结果：
mysql >select * from invisible_column_table;
+----+------+---------+
| id | name | address |
+----+------+---------+
|  1 | tom  | tianjin |
+----+------+---------+
1 row in set (0.00 sec)

#### 显示指定不可见列进行查询：
mysql >select id,name,address,ic1,ic2 from invisible_column_table;
+----+------+---------+------+-----+
| id | name | address | ic1  | ic2 |
+----+------+---------+------+-----+
|  1 | tom  | tianjin |      |     |
+----+------+---------+------+-----+
1 row in set (0.00 sec)

复制

3. 不可见列的几种操作

#### 举例不可见列的一些常用操作
ALTER TABLE t1 CHANGE COLUMN j j DATE VISIBLE;
ALTER TABLE t1 MODIFY COLUMN j DATE INVISIBLE;
ALTER TABLE t1 ALTER COLUMN j SET VISIBLE;

mysql >alter table invisible_column_table change column ic1 ic3 varchar(100) visible;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql >alter table invisible_column_table modify column ic3 varchar(50) default 'zzz' invisible;
Query OK, 6 rows affected (0.02 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql >alter table invisible_column_table alter column ic3 set visible;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql >select * from invisible_column_table;
+----+----------+----------+------+
| id | name     | address  | ic3  |
+----+----------+----------+------+
|  1 | tom      | tianjin  |      |
|  2 | NULL     | NULL     |      |
|  3 | Hubei    | NULL     |      |
|  4 | Xinjiang | NULL     |      |
|  5 | Sam      | Shanghai |      |
|  6 | Tom      | Shenzhen | okr  |
+----+----------+----------+------+
6 rows in set (0.00 sec)

复制

4. 不可见列的相关元数据信息查询

#### 可以通过 show columns进行查询列是否可见
mysql > show columns from invisible_column_table;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int          | NO   | PRI | NULL    | auto_increment |
| name    | varchar(100) | YES  |     | NULL    |                |
| address | varchar(200) | YES  |     | NULL    |                |
| ic1     | varchar(100) | YES  |     |         | INVISIBLE      |
| ic2     | varchar(50)  | NO   |     |         | INVISIBLE      |
+---------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

#### 可以通过information_schema.columns表的 extra字段进行查询列的不可见性
mysql >select table_schema,table_name,column_name,data_type,extra from information_schema.columns where table_name='invisible_column_table';
+--------------+------------------------+-------------+-----------+----------------+
| TABLE_SCHEMA | TABLE_NAME             | COLUMN_NAME | DATA_TYPE | EXTRA          |
+--------------+------------------------+-------------+-----------+----------------+
| rockdb       | invisible_column_table | id          | int       | auto_increment |
| rockdb       | invisible_column_table | name        | varchar   |                |
| rockdb       | invisible_column_table | address     | varchar   |                |
| rockdb       | invisible_column_table | ic1         | varchar   | INVISIBLE      |
| rockdb       | invisible_column_table | ic2         | varchar   | INVISIBLE      |
+--------------+------------------------+-------------+-----------+----------------+
5 rows in set (0.00 sec)
复制

限制和约束

1. 表中必须至少有一个列是可见列，如果表中都是不可见列将会产生错误。

mysql >alter table test_table modify column id int invisible;
ERROR 4028 (HY000): A table must have at least one visible column.
复制

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(…)语句，不包含不可见列的值，否则会报错。

mysql >select table_schema,table_name,column_name,data_type,extra from information_schema.columns where table_name='invisible_column_table';
+--------------+------------------------+-------------+-----------+----------------+
| TABLE_SCHEMA | TABLE_NAME             | COLUMN_NAME | DATA_TYPE | EXTRA          |
+--------------+------------------------+-------------+-----------+----------------+
| rockdb       | invisible_column_table | id          | int       | auto_increment |
| rockdb       | invisible_column_table | name        | varchar   |                |
| rockdb       | invisible_column_table | address     | varchar   |                |
| rockdb       | invisible_column_table | ic1         | varchar   | INVISIBLE      |
| rockdb       | invisible_column_table | ic2         | varchar   | INVISIBLE      |
+--------------+------------------------+-------------+-----------+----------------+
5 rows in set (0.00 sec)

mysql >insert into invisible_column_table values(7,'Tom','Shenzhen','xxx','yyy');
ERROR 1136 (21S01): Column count doesn't match value count at row 1
复制

8. mysqldump 和 mysqlpump 导出数据时使用的是SHOW CREATE TABLE，因此它们在转储表定义中包含不可见列，它们还包括转储数据中不可见列的值。将转储文件重新加载到不支持不可见列的旧版本MySQL中会导致版本特定的注释被忽略，从而将所有不可见列创建为可见列。

参考文档

https://dev.mysql.com/doc/refman/8.0/en/invisible-columns.html