快捷導(dǎo)航

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

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

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

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

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

不可見主鍵

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

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

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 表時(shí)，sql_generate_invisible_primary_key 設(shè)置為 OFF；創(chuàng)建 auto_1 表時(shí)，sql_generate_invisible_primary_key 設(shè)置為 ON。

使用 SHOW CREATE TABLE 語句查看兩個(gè)表的區(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 自動(dòng)為 auto_1 創(chuàng)建了一個(gè)不可見字段 my_row_id，并且將其設(shè)置為主鍵。

不可見字段也叫做隱藏字段，是 MySQL 8.0.23 版本增加的新功能。作為不可見字段，my_row_id 不會(huì)出現(xiàn)在 SELECT * 或者 TABLE 語句的結(jié)果中，查詢?cè)撟侄伪仨氾@式指定它的名字。

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

修改屬性

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

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)

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

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

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

字典信息

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

此時(shí)，我們查詢系統(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 設(shè)置為 OFF，再次查詢系統(tǒng)表 COLUMNS，不會(huì)顯示不可見主鍵字段：

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)

復(fù)制與備份

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

對(duì)于 CREATE TABLE … SELECT 語句，GIPK 支持基于行的復(fù)制選項(xiàng)，此時(shí)二進(jìn)制日志中包含了 GIPK 定義，可以正確地復(fù)制。如果是基于語句的復(fù)制，CREATE TABLE … SELECT 不支持 sql_generate_invisible_primary_key = ON。

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

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

您可能感興趣的文章: