MySQL8.0新特性之不可見主鍵的使用

更新時間：2024年04月24日 09:50:50 作者：不剪發(fā)的Tony老師

MySQL8.0.30版本引入不可見主鍵,它可以自動為沒有顯式指定主鍵的?InnoDB?表創(chuàng)建一個不可見的主鍵,本文主要介紹了MySQL8.0新特性之不可見主鍵的使用,具有一定的參考價值,感興趣的可以了解一下

數(shù)據(jù)庫設計通常需要滿足一定的范式要求，其中主鍵更是最基本的要求。不過，數(shù)據(jù)庫管理系統(tǒng)卻允許我們創(chuàng)建沒有主鍵的表。這樣的表在 MySQL 中會帶來查詢性能低下、復制延遲甚至無法實現(xiàn)高可用配置等問題。

為此，MySQL 8.0.30 版本引入了一個新的功能，叫做不可見主鍵（Generated Invisible Primary Keys），它可以自動為沒有顯式指定主鍵的 InnoDB 表創(chuàng)建一個不可見的主鍵。

不可見主鍵

MySQL 通過系統(tǒng)變量 sql_generate_invisible_primary_key 控制是否啟用 GIPK 特性，該變量的默認設置為 OFF。

以下示例創(chuàng)建了兩個表，都沒有指定主鍵。

mysql> SELECT @@sql_generate_invisible_primary_key;
+--------------------------------------+
| @@sql_generate_invisible_primary_key |
+--------------------------------------+
|                                    0 |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE auto_0 (c1 VARCHAR(50), c2 INT);
Query OK, 0 rows affected (0.02 sec)

mysql> SET sql_generate_invisible_primary_key=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@sql_generate_invisible_primary_key;
+--------------------------------------+
| @@sql_generate_invisible_primary_key |
+--------------------------------------+
|                                    1 |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE auto_1 (c1 VARCHAR(50), c2 INT);
Query OK, 0 rows affected (0.04 sec)

創(chuàng)建 auto_0 表時，sql_generate_invisible_primary_key 設置為 OFF；創(chuàng)建 auto_1 表時，sql_generate_invisible_primary_key 設置為 ON。

使用 SHOW CREATE TABLE 語句查看兩個表的區(qū)別：

mysql> SHOW CREATE TABLE auto_0\G
*************************** 1. row ***************************
       Table: auto_0
Create Table: CREATE TABLE `auto_0` (
  `c1` varchar(50) DEFAULT NULL,
  `c2` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE auto_1\G
*************************** 1. row ***************************
       Table: auto_1
Create Table: CREATE TABLE `auto_1` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
  `c1` varchar(50) DEFAULT NULL,
  `c2` int DEFAULT NULL,
  PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

MySQL 自動為 auto_1 創(chuàng)建了一個不可見字段 my_row_id，并且將其設置為主鍵。

不可見字段也叫做隱藏字段，是 MySQL 8.0.23 版本增加的新功能。作為不可見字段，my_row_id 不會出現(xiàn)在 SELECT * 或者 TABLE 語句的結(jié)果中，查詢該字段必須顯式指定它的名字。

這種情況下，系統(tǒng)默認增加的不可見主鍵字段名稱固定為 my_row_id，因此我們不能在創(chuàng)建表時指定其他字段名為 my_row_id，除非同時將其指定為主鍵。

修改屬性

當我們啟用了 GIPK 功能時，不可見主鍵 my_row_id 不能使用 ALTER TABLE 語句進行修改，只能設置它的可見屬性。以下命令將 auto_1 表的不可見主鍵設置為可見字段：

mysql> ALTER TABLE auto_1 ALTER COLUMN my_row_id SET VISIBLE;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE auto_1\G
*************************** 1. row ***************************
       Table: auto_1
Create Table: CREATE TABLE `auto_1` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `c1` varchar(50) DEFAULT NULL,
  `c2` int DEFAULT NULL,
  PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

當我們啟用了 GIPK 功能時，如果刪除不可見主鍵會導致以下任意情況發(fā)生，都不允許刪除不可見主鍵：

該表沒有主鍵；
刪除主鍵而保留主鍵字段。

另外，GIPK 功能只支持 InnoDB 存儲引擎，當我們使用 ALTER TABLE 語句修改這種表的存儲引擎時，仍然會保留字段和主鍵約束，但是它會變成普通的主鍵字段。

字典信息

默認情況下，SHOW CREATE TABLE、SHOW COLUMNS 以及 SHOW INDEX 命令都會顯示不可見主鍵信息。同時，information_schema 數(shù)據(jù)庫中的 COLUMNS 和 STATISTICS 表中也包含了不可見主鍵字段。這一行為可以通過系統(tǒng)變量 show_gipk_in_create_table_and_information_schema 進行控制，默認值為 ON。

mysql> SELECT @@show_gipk_in_create_table_and_information_schema;
+----------------------------------------------------+
| @@show_gipk_in_create_table_and_information_schema |
+----------------------------------------------------+
|                                                  1 |
+----------------------------------------------------+
1 row in set (0.00 sec)

此時，我們查詢系統(tǒng)表 COLUMNS 可以返回 auto_1 中的不可見主鍵 my_row_id：

mysql> SELECT COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE, COLUMN_KEY
    -> FROM INFORMATION_SCHEMA.COLUMNS
    -> WHERE TABLE_NAME = "auto_1";
+-------------+------------------+-----------+------------+
| COLUMN_NAME | ORDINAL_POSITION | DATA_TYPE | COLUMN_KEY |
+-------------+------------------+-----------+------------+
| my_row_id   |                1 | bigint    | PRI        |
| c1          |                2 | varchar   |            |
| c2          |                3 | int       |            |
+-------------+------------------+-----------+------------+
3 rows in set (0.01 sec)

如果將系統(tǒng)變量 show_gipk_in_create_table_and_information_schema 設置為 OFF，再次查詢系統(tǒng)表 COLUMNS，不會顯示不可見主鍵字段：

mysql> SET show_gipk_in_create_table_and_information_schema = OFF;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@show_gipk_in_create_table_and_information_schema;
+----------------------------------------------------+
| @@show_gipk_in_create_table_and_information_schema |
+----------------------------------------------------+
|                                                  0 |
+----------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE, COLUMN_KEY
    -> FROM INFORMATION_SCHEMA.COLUMNS
    -> WHERE TABLE_NAME = "auto_1";
+-------------+------------------+-----------+------------+
| COLUMN_NAME | ORDINAL_POSITION | DATA_TYPE | COLUMN_KEY |
+-------------+------------------+-----------+------------+
| c1          |                2 | varchar   |            |
| c2          |                3 | int       |            |
+-------------+------------------+-----------+------------+
2 rows in set (0.00 sec)

復制與備份

系統(tǒng)變量 sql_generate_invisible_primary_key 的配置不會被復制，復制應用線程會忽略該變量。這就意味著源節(jié)點的設置不會對副本產(chǎn)生影響。從 MySQL 8.0.32 版本開始，我們可以在 CHANGE REPLICATION SOURCE TO 語句使用 REQUIRE_TABLE_PRIMARY_KEY_CHECK = GENERATE 選項設置副本節(jié)點啟用 GIPK 功能，為指定復制渠道中的那些沒有主鍵的表自動增加不可見主鍵。

對于 CREATE TABLE … SELECT 語句，GIPK 支持基于行的復制選項，此時二進制日志中包含了 GIPK 定義，可以正確地復制。如果是基于語句的復制，CREATE TABLE … SELECT 不支持 sql_generate_invisible_primary_key = ON。

如果啟用了 GIPK 功能，使用 mysqldump 備份或者還原數(shù)據(jù)時，可以通過 --skip-generated-invisible-primary-key 選項排除 GIPK 信息。

到此這篇關于MySQL8.0新特性之不可見主鍵的使用的文章就介紹到這了,更多相關MySQL8.0 不可見主鍵內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: