MySQLでは、プライマリキー
プライマリキーを追加したいけれど、塩漬けされたアプリケーションやパッケージ製品のため改修できず、プライマリキーを追加できないこともあるでしょう。今回はそんなときに役に立つ、2022/
プライマリキーについて
プライマリキーがないテーブルが存在するMySQLを運用していると、以下のような問題を潜在的に抱えることになります。
- binlog_
format=ROWにおけるレプリケーションの遅延 - InnoDB ClusterのGroupReplicationはプライマリキー必須
そのため、プライマリキーを作成することをおすすめします。
また、MySQL 8.sql_
オプションが存在します。このオプションについてはすでにこの連載で紹介していますので、第109回 主キーを必須にさせるをご確認ください。
不可視プライマリキーについて
それでは、本題の不可視プライマリキーについて紹介したいと思います。
MySQL 8.sql_
オプションをONにすることで、GIPKモードに変更されます。デフォルトはOFFです。GIPKモード下では、明示的にプライマリキーを指定せずに作成されたInnoDBのテーブルに対して、自動的に不可視プライマリキーが追加されます。
以下のようなカラムタイプのプライマリキーが生成されます。
my_row_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT INVISIBLE PRIMARY KEY
生成されるカラムはAUTO_
の数値型で、カラム名はmy_
で固定です。変更はできません。
テーブル作成
では、試してみましょう。まずは、sql_
オプションを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_
)
SELECT
不可視プライマリキーのテーブルをSELECTしてみましょう。
mysql> SELECT * FROM t0; +------+ | col1 | +------+ | a | +------+ mysql> SELECT my_row_id,col1 FROM t0; +-----------+------+ | my_row_id | col1 | +-----------+------+ | 1 | a | +-----------+------+
SELECT *
ではmy_
が出力されませんが、明示的にカラム名を指定することで出力できます。
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_
オプションの変更はレプリケーションされないので、ソースでONにしてもレプリカではOFFのままです。ただし、GIPKモードで作成されたテーブル構造やデータは正常にレプリケーションされますので問題ありません。
また、sql_
オプションがソースでOFF、およびレプリカでONの場合に、プライマリキーなしのテーブルを作成してもレプリカでは不可視プライマリキーが設定されません。そのため、オプション値がソースとレプリカでズレていたとしても、問題ありません。
続いて、show_
オプションをOFFに変更すると、GIPKモードで作成されたプライマリキーの情報を、information_
のテーブルやSHOW CREATE TABLE文から表示させないことも可能です。
show_
オプションを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.