第177回　MySQL 8.0.30の新機能：不可視プライマリキーについて

MySQLでは、プライマリキー（主キー）がないテーブルが存在すると、運用中に問題になるケースが多々あります。そのため、MySQL運用者はプライマリキーをないテーブルを作成しようとしている開発者に対して、必ずプライマリキーを設定するように注意を促すことがあるかと思います。

プライマリキーを追加したいけれど、塩漬けされたアプリケーションやパッケージ製品のため改修できず、プライマリキーを追加できないこともあるでしょう。今回はそんなときに役に立つ、2022/07/26にリリースされたばかりのMySQL 8.0.30 の新機能「不可視プライマリキー」について主に紹介したいと思います。

プライマリキーについて

プライマリキーがないテーブルが存在するMySQLを運用していると、以下のような問題を潜在的に抱えることになります。

binlog_format=ROWにおけるレプリケーションの遅延
InnoDB ClusterのGroupReplicationはプライマリキー必須

そのため、プライマリキーを作成することをおすすめします。

また、MySQL 8.0ではプライマリキーを必須にさせるための sql_require_primary_key オプションが存在します。このオプションについてはすでにこの連載で紹介していますので、第109回主キーを必須にさせるをご確認ください。

不可視プライマリキーについて

それでは、本題の不可視プライマリキーについて紹介したいと思います。

MySQL 8.0.30からGenerated Invisible Primary Key（GIPK）モードがサポートされました。sql_generate_invisible_primary_keyオプションをONにすることで、GIPKモードに変更されます。デフォルトはOFFです。GIPKモード下では、明示的にプライマリキーを指定せずに作成されたInnoDBのテーブルに対して、自動的に不可視プライマリキーが追加されます。

以下のようなカラムタイプのプライマリキーが生成されます。

my_row_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT INVISIBLE PRIMARY KEY

生成されるカラムはAUTO_INCREMENTの数値型で、カラム名はmy_row_idで固定です。変更はできません。

テーブル作成

では、試してみましょう。まずは、sql_generate_invisible_primary_keyオプションをONに変更します。このオプションのスコープはセッションまたはグローバルです。

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

プライマリキーを指定せずテーブルを作成します。

mysql> CREATE TABLE t0 (col1 varchar(255));
Query OK, 0 rows affected (0.02 sec)

SHOW CREATE TABLE文でテーブルの構造を確認します。

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

このように、不可視プライマリキー（my_row_id）が作成されました。

SELECT

不可視プライマリキーのテーブルをSELECTしてみましょう。

mysql> SELECT * FROM t0;
+------+
| col1 |
+------+
| a    |
+------+

mysql> SELECT my_row_id,col1 FROM t0;
+-----------+------+
| my_row_id | col1 |
+-----------+------+
|         1 | a    |
+-----------+------+

SELECT *ではmy_row_idが出力されませんが、明示的にカラム名を指定することで出力できます。

INSERT

INSERTは以下のような結果になりました。明示的にカラム名を指定するとINSERTできるようになっています。

mysql>  INSERT INTO t0 VALUES ('b');
Query OK, 1 row affected (0.00 sec)

mysql>  INSERT INTO t0(col1) VALUES ('c');
Query OK, 1 row affected (0.00 sec)

mysql>  INSERT INTO t0(my_row_id,col1) VALUES (100,'c');
Query OK, 1 row affected (0.00 sec)

その他

sql_generate_invisible_primary_keyオプションの変更はレプリケーションされないので、ソースでONにしてもレプリカではOFFのままです。ただし、GIPKモードで作成されたテーブル構造やデータは正常にレプリケーションされますので問題ありません。

また、sql_generate_invisible_primary_keyオプションがソースでOFF、およびレプリカでONの場合に、プライマリキーなしのテーブルを作成してもレプリカでは不可視プライマリキーが設定されません。そのため、オプション値がソースとレプリカでズレていたとしても、問題ありません。

続いて、show_gipk_in_create_table_and_information_schemaオプションをOFFに変更すると、GIPKモードで作成されたプライマリキーの情報を、information_schemaのテーブルやSHOW CREATE TABLE文から表示させないことも可能です。

show_gipk_in_create_table_and_information_schemaオプションをOFFにすると、以下のような表示になります。

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

mysql> SHOW CREATE TABLE t0\G
*************************** 1. row ***************************
       Table: t0
Create Table: CREATE TABLE `t0` (
  `col1` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

そして、mysqldumpやmysqlpumpはデフォルトでは出力に不可視プライマリキー情報を含みます。除外するには、--skip-generated-invisible-primary-keyオプションを指定して実行する必要があります。

まとめ

今回は不可視プライマリキーについて紹介しました。不可視プライマリキーは、Invisible Columnsの機能を利用して実装されています。

Invisible Columnsについては第158回 Invisible Columnsの使いどころをご確認ください。

また、この記事は13.1.20.11 Generated Invisible Primary Keysを参考にしています。あわせてご確認ください。