MySQL 8.0 新特性之隱藏字段

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構(gòu)設(shè)計。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net


文章目錄

        基本概念
        隱藏字段與 DDL 語句
        隱藏字段與 DML 語句
        隱藏字段相關(guān)的元數(shù)據(jù)
        隱藏字段與二進(jìn)制日志

大家好，我是只談技術(shù)不剪發(fā)的 Tony 老師。

MySQL 8.0.23 版本增加了一個新的功能：隱藏字段（Invisible Column），也稱為不可見字段。本文給大家介紹一下 MySQL 隱藏字段的相關(guān)概念和具體實現(xiàn)。

如果你覺得文章有用，歡迎評論??、點(diǎn)贊??、推薦??
基本概念

隱藏字段需要在查詢中進(jìn)行顯式引用，否則對查詢而言是不可見的。MySQL 8.0.23 開始支持隱藏字段，在此之前所有的字段都是可見字段。

考慮以下應(yīng)用場景，假如一個應(yīng)用程序使用SELECT *語句訪問某個表，并且必需持續(xù)不斷地進(jìn)行查詢，即使我們?yōu)樵摫碓黾恿艘粋€該應(yīng)用不需要的新字段時也要求能夠正常工作。對于SELECT *查詢，星號（*）代表了表中除隱藏字段之外的所有字段，因此我們可以將新加的字段定義為隱藏字段。該隱藏字段對于SELECT *查詢是不可見的，因此應(yīng)用能夠繼續(xù)運(yùn)行。如果新版本的應(yīng)用程序需要使用該字段，可以在查詢中顯式指定。

PS：不推薦使用SELECT *語句查詢數(shù)據(jù)，應(yīng)該明確指定需要返回的字段。
隱藏字段與 DDL 語句

默認(rèn)情況下創(chuàng)建的字段屬于可見字段。如果想要顯式指定字段的可見性，可以在CREATE TABLE或者ALTER TABLE語句中為字段的定義指定VISIBLE 或者 INVISIBLE 關(guān)鍵字。例如：

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



如果想要修改某個字段的可見性，同樣可以使用 VISIBLE 或者 INVISIBLE 關(guān)鍵字。例如：

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;



使用隱藏字段時，需要注意以下事項：

    一個表至少需要一個可見字段。如果將所有字段都設(shè)置為隱藏字段，將會返回錯誤。
    隱藏字段支持常見的字段屬性：NULL、NOT NULL 以及 AUTO_INCREMENT 等等。
    計算列（Generated column）可以是隱藏字段。
    索引可以使用隱藏字段，包括 PRIMARY KEY 和 UNIQUE 索引。雖然一個表至少需要一個可見字段，但是索引定義中可以不包含任何可見字段。
    刪除某個表中的隱藏字段時，同時會從相關(guān)索引中刪除該字段。
    外鍵約束可以基于隱藏字段進(jìn)行定義，同時外鍵約束也可以引用隱藏字段。
    CHECK 約束可以基于隱藏字段進(jìn)行定義。插入或者更新數(shù)據(jù)時，如果違反了隱藏字段上的 CHECK 約束將會返回錯誤。

如果使用CREATE TABLE ... LIKE語句復(fù)制表結(jié)構(gòu)，將會復(fù)制原表中的隱藏字段，而且它們在新表中仍然是隱藏字段。如果使用CREATE TABLE ... SELECT語句復(fù)制表，不會包含隱藏字段，除非顯式指定了隱藏字段。盡管如此，即使包含了原表中的隱藏字段，新表中的這些字段將會變成可見字段。例如：

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 語句，除非在查詢列表中顯式指定了隱藏字段，否則查詢結(jié)構(gòu)中不會包含隱藏字段。查詢列表中的 * 和 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（* 不包含隱藏字段），因此查詢結(jié)果中沒有返回 col2 字段。第二個 SELECT 語句顯式指定了 col2 字段，因此查詢結(jié)果返回了該字段。

對于查詢語句，如果沒有為隱藏字段指定數(shù)據(jù)，使用隱式默認(rèn)值規(guī)則進(jìn)行賦值。

對于 INSERT 語句（包括 REPLACE 語句的數(shù)據(jù)插入），如果沒有指定字段列表、指定空白列表或者沒有在字段列表中指定隱藏字段時，使用隱式默認(rèn)值賦值。例如：

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



對于前兩個 INSERT 語句，VALUES() 列表必須為每個可見字段和隱藏字段提供一個數(shù)值。對于第三個 INSERT 語句，VALUES() 列表必須為每個指定的字段提供一個數(shù)值。

對于 LOAD DATA 和 LOAD XML 語句，如果沒有指定字段列表或者沒有在字段列表中指定隱藏字段時，使用隱式默認(rèn)值賦值。輸入數(shù)據(jù)中不能包含隱藏字段的值。

如果想要為上面的語句提供一個非默認(rèn)的數(shù)據(jù)，可以在字段列表中顯式指定隱藏字段并且在 VALUES() 列表中指定一個數(shù)值。

INSERT INTO … SELECT * 和 REPLACE INTO … SELECT * 不會包含隱藏字段，因為 * 不會返回隱藏字段。此時同樣會使用隱式默認(rèn)值規(guī)則進(jìn)行賦值。

基于 PRIMARY KEY 或者 UNIQUE 索引執(zhí)行插入或者忽略插入、替換或者修改數(shù)據(jù)的語句中，MySQL 對隱藏字段的處理方式和可見字段相同：隱藏字段同樣會用于鍵值的比較。準(zhǔn)確來說，如果某個新的數(shù)據(jù)行和已有數(shù)據(jù)行的唯一鍵字段值相同，無論索引字段是否可見，都會使用以下處理方式：

    如果指定了 IGNORE 修飾符，INSERT、LOAD DATA 以及 LOAD XML 都會忽略新的數(shù)據(jù)行。
    REPLACE 使用新的數(shù)據(jù)行替換原有的數(shù)據(jù)行。如果指定了 REPLACE 修飾符，LOAD DATA 和 LOAD XML 也是如此。
    INSERT … ON DUPLICATE KEY UPDATE 更新原有的數(shù)據(jù)行。

如果想要使用 UPDATE 語句更新隱藏字段，像可見字段一樣顯式進(jìn)行賦值即可。
隱藏字段相關(guān)的元數(shù)據(jù)

我們可以通過 INFORMATION_SCHEMA.COLUMNS 系統(tǒng)表的 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 |
+------------+-------------+-----------+



默認(rèn)情況下字段是可見的，此時 EXTRA 字段為空。對于隱藏字段，EXTRA 顯式為 INVISIBLE。

SHOW CREATE TABLE 命令可以顯式表中的隱藏字段，字段定義中包含一個基于版本的注釋，其中包含了一個 INVISIBLE 關(guān)鍵字：

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 命令，因此它們導(dǎo)出的表定義中包含可隱藏字段。同時，它們在導(dǎo)出的數(shù)據(jù)中包含了隱藏字段的值。如果將導(dǎo)出文件加載到不支持的隱藏字段的低版本 MySQL 中，將會忽略基于版本的注釋信息，從而將隱藏字段作為可見字段使用。
隱藏字段與二進(jìn)制日志

對于二進(jìn)制日志中的事件，MySQL 使用以下方式處理隱藏字段：

    創(chuàng)建表的事件中包含了隱藏字段的 INVISIBLE 屬性。
    數(shù)據(jù)行事件中的隱藏字段和可見字段處理方式相同。它們會根據(jù)系統(tǒng)變量 binlog_row_image 的設(shè)置進(jìn)行處理。
    當(dāng)數(shù)據(jù)行事件被應(yīng)用時，隱藏字段和可見字段處理方式相同。其中，使用的算法和索引基于系統(tǒng)變量 slave_rows_search_algorithms 的設(shè)置進(jìn)行選擇。
    計算寫入集（writeset）時隱藏字段和可見字段處理方式相同。寫入集中包含了基于隱藏字段定義的索引。
    mysqlbinlog 命令中包含了字段元數(shù)據(jù)中的可見屬性。