MySQL 8.0新特性之隐藏字段的深入讲解-巨人网络通讯

MySQL 8.0新特性之隐藏字段的深入讲解

前言

MySQL 8.0.23 版本增加了一个新的功能：隐藏字段（Invisible Column），也称为不可见字段。本文给大家介绍一下 MySQL 隐藏字段的相关概念和具体实现。

基本概念

隐藏字段需要在查询中进行显式引用，否则对查询而言是不可见的。MySQL 8.0.23 开始支持隐藏字段，在此之前所有的字段都是可见字段。

考虑以下应用场景，假如一个应用程序使用SELECT *语句访问某个表，并且必需持续不断地进行查询，即使我们为该表增加了一个该应用不需要的新字段时也要求能够正常工作。对于SELECT *查询，星号（*）代表了表中除隐藏字段之外的所有字段，因此我们可以将新加的字段定义为隐藏字段。该隐藏字段对于SELECT *查询是不可见的，因此应用能够继续运行。如果新版本的应用程序需要使用该字段，可以在查询中显式指定。

PS：不推荐使用SELECT *语句查询数据，应该明确指定需要返回的字段。

隐藏字段与 DDL 语句

默认情况下创建的字段属于可见字段。如果想要显式指定字段的可见性，可以在CREATE TABLE或者ALTER TABLE语句中为字段的定义指定VISIBLE 或者 INVISIBLE 关键字。例如：

CREATE TABLE t1 (
 i INT,
 j DATE INVISIBLE
) ENGINE = InnoDB;
ALTER TABLE t1 ADD COLUMN k INT INVISIBLE;

如果想要修改某个字段的可见性，同样可以使用 VISIBLE 或者 INVISIBLE 关键字。例如：

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;

使用隐藏字段时，需要注意以下事项：

一个表至少需要一个可见字段。如果将所有字段都设置为隐藏字段，将会返回错误。
隐藏字段支持常见的字段属性：NULL、NOT NULL 以及 AUTO_INCREMENT 等等。
计算列（Generated column）可以是隐藏字段。
索引可以使用隐藏字段，包括 PRIMARY KEY 和 UNIQUE 索引。虽然一个表至少需要一个可见字段，但是索引定义中可以不包含任何可见字段。
删除某个表中的隐藏字段时，同时会从相关索引中删除该字段。
外键约束可以基于隐藏字段进行定义，同时外键约束也可以引用隐藏字段。
CHECK 约束可以基于隐藏字段进行定义。插入或者更新数据时，如果违反了隐藏字段上的 CHECK 约束将会返回错误。

如果使用CREATE TABLE ... LIKE语句复制表结构，将会复制原表中的隐藏字段，而且它们在新表中仍然是隐藏字段。如果使用CREATE TABLE ... SELECT语句复制表，不会包含隐藏字段，除非显式指定了隐藏字段。尽管如此，即使包含了原表中的隐藏字段，新表中的这些字段将会变成可见字段。例如：

mysql> CREATE TABLE t1 (col1 INT, col2 INT INVISIBLE);
mysql> CREATE TABLE t2 AS SELECT col1, col2 FROM t1;
mysql> SHOW CREATE TABLE t2\G
*************************** 1. row ***************************
  Table: t2
Create Table: CREATE TABLE `t2` (
 `col1` int DEFAULT NULL,
 `col2` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

如果想要保留这些字段的隐藏属性，可以在 CREATE TABLE 之后为它们指定隐藏属性。例如：

mysql> CREATE TABLE t1 (col1 INT, col2 INT INVISIBLE);
mysql> CREATE TABLE t2 (col2 INT INVISIBLE) AS SELECT col1, col2 FROM t1;
mysql> SHOW CREATE TABLE t2\G
*************************** 1. row ***************************
  Table: t2
Create Table: CREATE TABLE `t2` (
 `col1` int DEFAULT NULL,
 `col2` int DEFAULT NULL /*!80023 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

视图可以引用隐藏字段，需要在定义中显式指定这些字段。在视图定义之后修改字段的可见性不会影响视图。

隐藏字段与 DML 语句

对于 SELECT 语句，除非在查询列表中显式指定了隐藏字段，否则查询结构中不会包含隐藏字段。查询列表中的 * 和 tbl_name.* 不会包含隐藏字段。自然连接不会包含隐藏字段。

对于以下语句：

mysql> CREATE TABLE t1 (col1 INT, col2 INT INVISIBLE);
mysql> INSERT INTO t1 (col1, col2) VALUES(1, 2), (3, 4);

mysql> SELECT * FROM t1;
+------+
| col1 |
+------+
| 1 |
| 3 |
+------+

mysql> SELECT col1, col2 FROM t1;
+------+------+
| col1 | col2 |
+------+------+
| 1 | 2 |
| 3 | 4 |
+------+------+

第一个 SELECT 语句没有引用隐藏字段 col2（* 不包含隐藏字段），因此查询结果中没有返回 col2 字段。第二个 SELECT 语句显式指定了 col2 字段，因此查询结果返回了该字段。

对于查询语句，如果没有为隐藏字段指定数据，使用隐式默认值规则进行赋值。

对于 INSERT 语句（包括 REPLACE 语句的数据插入），如果没有指定字段列表、指定空白列表或者没有在字段列表中指定隐藏字段时，使用隐式默认值赋值。例如：

CREATE TABLE t1 (col1 INT, col2 INT INVISIBLE);
INSERT INTO t1 VALUES(...);
INSERT INTO t1 () VALUES(...);
INSERT INTO t1 (col1) VALUES(...);

对于前两个 INSERT 语句，VALUES() 列表必须为每个可见字段和隐藏字段提供一个数值。对于第三个 INSERT 语句，VALUES() 列表必须为每个指定的字段提供一个数值。

对于 LOAD DATA 和 LOAD XML 语句，如果没有指定字段列表或者没有在字段列表中指定隐藏字段时，使用隐式默认值赋值。输入数据中不能包含隐藏字段的值。

如果想要为上面的语句提供一个非默认的数据，可以在字段列表中显式指定隐藏字段并且在 VALUES() 列表中指定一个数值。

INSERT INTO … SELECT * 和 REPLACE INTO … SELECT * 不会包含隐藏字段，因为 * 不会返回隐藏字段。此时同样会使用隐式默认值规则进行赋值。

基于 PRIMARY KEY 或者 UNIQUE 索引执行插入或者忽略插入、替换或者修改数据的语句中，MySQL 对隐藏字段的处理方式和可见字段相同：隐藏字段同样会用于键值的比较。准确来说，如果某个新的数据行和已有数据行的唯一键字段值相同，无论索引字段是否可见，都会使用以下处理方式：

如果指定了 IGNORE 修饰符，INSERT、LOAD DATA 以及 LOAD XML 都会忽略新的数据行。
REPLACE 使用新的数据行替换原有的数据行。如果指定了 REPLACE 修饰符，LOAD DATA 和 LOAD XML 也是如此。
INSERT … ON DUPLICATE KEY UPDATE 更新原有的数据行。

如果想要使用 UPDATE 语句更新隐藏字段，像可见字段一样显式进行赋值即可。

隐藏字段相关的元数据

我们可以通过 INFORMATION_SCHEMA.COLUMNS 系统表的 EXTRA 字段或者 SHOW COLUMNS 命令查看字段的可见属性。例如：

mysql> SELECT TABLE_NAME, COLUMN_NAME, EXTRA
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't1';
+------------+-------------+-----------+
| TABLE_NAME | COLUMN_NAME | EXTRA  |
+------------+-------------+-----------+
| t1   | i   |   |
| t1   | j   |   |
| t1   | k   | INVISIBLE |
+------------+-------------+-----------+

默认情况下字段是可见的，此时 EXTRA 字段为空。对于隐藏字段，EXTRA 显式为 INVISIBLE。

SHOW CREATE TABLE 命令可以显式表中的隐藏字段，字段定义中包含一个基于版本的注释，其中包含了一个 INVISIBLE 关键字：

mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
  Table: t1
Create Table: CREATE TABLE `t1` (
 `i` int DEFAULT NULL,
 `j` int DEFAULT NULL,
 `k` int DEFAULT NULL /*!80023 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

mysqldump 和 mysqlpump 使用 SHOW CREATE TABLE 命令，因此它们导出的表定义中包含可隐藏字段。同时，它们在导出的数据中包含了隐藏字段的值。如果将导出文件加载到不支持的隐藏字段的低版本 MySQL 中，将会忽略基于版本的注释信息，从而将隐藏字段作为可见字段使用。

隐藏字段与二进制日志

对于二进制日志中的事件，MySQL 使用以下方式处理隐藏字段：

创建表的事件中包含了隐藏字段的 INVISIBLE 属性。
数据行事件中的隐藏字段和可见字段处理方式相同。它们会根据系统变量 binlog_row_image 的设置进行处理。
当数据行事件被应用时，隐藏字段和可见字段处理方式相同。其中，使用的算法和索引基于系统变量 slave_rows_search_algorithms 的设置进行选择。
计算写入集（writeset）时隐藏字段和可见字段处理方式相同。写入集中包含了基于隐藏字段定义的索引。
mysqlbinlog 命令中包含了字段元数据中的可见属性。