MySQL :: MySQL 9.0 參考手冊 :: 15.1.20.11 產生的隱形主鍵

版本 9.0

8.4 目前版本
8.0

MySQL 9.0 參考手冊 / ... / 產生的隱形主鍵

15.1.20.11 產生的隱形主鍵

MySQL 9.0 支援為任何沒有明確主鍵的 InnoDB 資料表產生隱形主鍵。當 sql_generate_invisible_primary_key 伺服器系統變數設定為 ON 時，MySQL 伺服器會自動為任何此類資料表新增產生的隱形主鍵 (GIPK)。此設定對於使用 InnoDB 以外的任何其他儲存引擎所建立的資料表沒有影響。

預設情況下，sql_generate_invisible_primary_key 的值為 OFF，這表示自動新增 GIPK 功能已停用。為了說明這如何影響資料表的建立，我們先建立兩個相同的資料表，兩者都沒有主鍵，唯一的區別在於第一個資料表 (表 auto_0) 是在 sql_generate_invisible_primary_key 設定為 OFF 的情況下建立的，而第二個資料表 (auto_1) 是在將其設定為 ON 之後建立的，如下所示

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)

比較這些 SHOW CREATE TABLE 陳述式的輸出，以查看資料表實際建立方式的差異

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)

由於 auto_1 沒有由用於建立它的 CREATE TABLE 陳述式指定任何主鍵，因此設定 sql_generate_invisible_primary_key = ON 會導致 MySQL 將隱藏的欄位 my_row_id 新增到此資料表，並在此欄位上建立主鍵。由於建立 auto_0 時 sql_generate_invisible_primary_key 為 OFF，因此未對該資料表執行此類新增操作。

當伺服器將主鍵新增到資料表時，欄位和索引鍵名稱始終為 my_row_id。因此，當以這種方式啟用產生的隱藏主鍵時，除非資料表建立陳述式也指定了明確的主鍵，否則您無法建立具有名為 my_row_id 的欄位的資料表。（在這種情況下，您不需要將欄位或索引鍵命名為 my_row_id。）

my_row_id 是一個隱藏的欄位，這表示它不會顯示在 SELECT * 或 TABLE 的輸出中；必須透過名稱明確選取該欄位。請參閱第 15.1.20.10 節，「隱藏欄位」。

啟用 GIPK 時，除了在 VISIBLE 和 INVISIBLE 之間切換之外，無法變更產生的主鍵。若要讓 auto_1 上產生的隱藏主鍵可見，請執行此 ALTER TABLE 陳述式

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)

若要再次讓這個產生的主鍵隱藏，請發出 ALTER TABLE auto_1 ALTER COLUMN my_row_id SET INVISIBLE。

產生的隱藏主鍵預設始終處於隱藏狀態。

每當啟用 GIPK 時，如果發生以下 2 種情況中的任何一種，您就無法刪除產生的主鍵

資料表沒有主鍵。
主鍵被刪除，但主鍵欄位未被刪除。

sql_generate_invisible_primary_key 的效果僅適用於使用 InnoDB 儲存引擎的資料表。您可以使用 ALTER TABLE 陳述式來變更具有產生的隱藏主鍵的資料表所使用的儲存引擎；在這種情況下，主鍵和欄位會保留在原位，但資料表和索引鍵不再接受任何特殊處理。

預設情況下，GIPK 會顯示在 SHOW CREATE TABLE、SHOW COLUMNS 和 SHOW INDEX 的輸出中，並且在 Information Schema 的 COLUMNS 和 STATISTICS 資料表中可見。您可以透過將 show_gipk_in_create_table_and_information_schema 系統變數設定為 OFF，來讓產生的隱藏主鍵在這些情況下被隱藏。預設情況下，此變數為 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)

從以下針對 COLUMNS 資料表的查詢中可以看出，my_row_id 在 auto_1 的欄位中可見

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)

在將 show_gipk_in_create_table_and_information_schema 設定為 OFF 之後，my_row_id 將不再顯示在 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)

sql_generate_invisible_primary_key 的設定不會被複寫，並且會被複寫應用程式執行緒忽略。這表示此變數在來源上的設定對複本沒有影響。您可以使用 REQUIRE_TABLE_PRIMARY_KEY_CHECK = GENERATE 作為 CHANGE REPLICATION SOURCE TO 陳述式的一部分，來讓複本為在給定複寫通道上未帶有主鍵而複寫的資料表新增 GIPK。

GIPK 可與 CREATE TABLE ... SELECT 的基於列的複寫一起運作；在這種情況下，為此陳述式寫入二進位記錄檔的資訊包括 GIPK 定義，因此會正確複寫。當 sql_generate_invisible_primary_key = ON 時，不支援基於陳述式的 CREATE TABLE ... SELECT 複寫。

在建立或匯入正在使用 GIPK 的安裝備份時，可以排除產生的隱藏主鍵欄位和值。--skip-generated-invisible-primary-key 選項用於 mysqldump，會導致程式的輸出中排除 GIPK 資訊。