概念描述
从MySQL8.0.23版本开始，MySQL支持不可见列（invisible columns），在之前版本中MySQL对所有列都是可见的，所谓不可见列就是指在正常默认查询中不会显示出来，用select * from 语句是无法查询出不可见属性的列的。
使用场景
1. 隐藏字段：刻意隐藏不想展示的字段，比如备注，敏感值，关键信息等
2. 业务侧需要：业务侧为前期表设计不充分，创建一些隐藏字段来保留业务数据，方便后期扩展需求
3. 提升容错性：弥补业务初期创建错误表字段问题而开通的一个“后门儿”
测试验证
- 给现有表加不可见列：
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_tableG
*************************** 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)
- 创建带有不可见列的表
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_tableG
*************************** 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)
- 不可见列的几种操作
#### 举例不可见列的一些常用操作
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)
- 不可见列的相关元数据信息查询
#### 可以通过 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)
限制和约束
- 表中必须至少有一个列是可见列，如果表中都是不可见列将会产生错误。
mysql >alter table test_table modify column id int invisible;
ERROR 4028 (HY000): A table must have at least one visible column.
- 不可见列支持常用的列属性： NULL，NOT NULL，DEFAULT，AUTO_INCREMENT等。
- 可以在不可见列上定义外键约束，外键约束可以引用不可见列。
- CHECK可以在不可见列上定义约束。对于新增或修改的行，违反CHECK不可见列上的约束会产生错误。
- CREATE TABLE … LIKE 包括不可见的列，它们在新表中依然是不可见的。
- CREATE TABLE … SELECT 不包括不可见列，除非在 SELECT部件中明确指定，但是即使显式指定，现有表中不可见的列在新表中也是可见的。
- 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
- mysqldump 和 mysqlpump 导出数据时使用的是SHOW CREATE TABLE，因此它们在转储表定义中包含不可见列，它们还包括转储数据中不可见列的值。将转储文件重新加载到不支持不可见列的旧版本MySQL中会导致版本特定的注释被忽略，从而将所有不可见列创建为可见列。
参考文档
https://dev.mysql.com/doc/refman/8.0/en/invisible-columns.html
