MySQL 8.0 新特性之隱藏欄位

大家好，我是只談技術不剪發的 Tony 老師，

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 命令中包含了欄位元資料中的可見屬性，