今日は、MySQL 8.0.30で追加されたGIPKを使ってみたの日。
目次
とある日
2022/07/26にMySQL8.0.30がリリースされました。
MySQL :: MySQL 8.0 Release Notes :: Changes in MySQL 8.0.30 (2022-07-26, General Availability)
その中で気になったGenerated Invisible Primary Keys (GIPKs)を試しに使ってみた話です。
Generated Invisible Primary Keys (GIPKs)
GIPKってなに？
MySQL 8.0.30 から、MySQL は GIPK モードでの実行時に生成された非表示の主キーをサポートします。このモードで実行している場合
InnoDB、明示的な主キーなしで作成されたテーブルの場合、MySQL サーバーは生成された非表示の主キー (GIPK) をテーブルに自動的に追加します。このセクションでは、GIPK モードを有効または無効にする方法と、CREATE TABLEこのモードが有効な場合の生成された主キーの動作について説明します。
要点
- PKのないテーブルを作ると自動的にPKを
INVISIBLE(非表示)で追加してくれる機能 - 設定でオン・オフできる（デフォルトオフ）
InnoDBストレージ エンジンを使用するテーブルにのみ適用
INVISIBLE(非表示)がなにそれって人は↓以下合わせてご覧ください。
MySQL :: MySQL 8.0 リファレンスマニュアル :: 8.3.12 不可視のインデックス
使ってみる
ドキュメントに沿ってやってみます。
MySQL :: MySQL 8.0 リファレンス マニュアル :: 13.1.20.11 生成された不可視の主キー
GIPK設定有効化
まず、設定を有効にする必要があります。
(root@localhost) [test] 8.0.30 > select @@sql_generate_invisible_primary_key; +--------------------------------------+ | @@sql_generate_invisible_primary_key | +--------------------------------------+ | 0 | +--------------------------------------+ 1 row in set (0.00 sec) (root@localhost) [test] 8.0.30 > set session sql_generate_invisible_primary_key=1; Query OK, 0 rows affected (0.00 sec) (root@localhost) [test] 8.0.30 > select @@sql_generate_invisible_primary_key; +--------------------------------------+ | @@sql_generate_invisible_primary_key | +--------------------------------------+ | 1 | +--------------------------------------+ 1 row in set (0.00 sec)
PKのないテーブル作成
(root@localhost) [test] 8.0.30 > create table not_primarykey ( -> a int not null , -> b char(8) not null , -> c int not null -> ); Query OK, 0 rows affected (0.06 sec)
列を確認します。
(root@localhost) [test] 8.0.30 > SHOW CREATE TABLE not_primarykey; +----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | not_primarykey | CREATE TABLE `not_primarykey` ( `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */, `a` int NOT NULL, `b` char(8) COLLATE utf8mb4_unicode_ci NOT NULL, `c` int NOT NULL, PRIMARY KEY (`my_row_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci | +----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
my_row_id bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,が追加されています。
INFORMATION_SCHEMA.COLUMNSで確認してみる
デフォルトオンなので特筆して操作は必要なし。
(root@localhost) [test] 8.0.30 > 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)
検索。
(root@localhost) [test] 8.0.30 > SELECT COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE, COLUMN_KEY -> FROM INFORMATION_SCHEMA.COLUMNS -> WHERE TABLE_NAME = "not_primarykey"; +-------------+------------------+-----------+------------+ | COLUMN_NAME | ORDINAL_POSITION | DATA_TYPE | COLUMN_KEY | +-------------+------------------+-----------+------------+ | a | 2 | int | | | b | 3 | char | | | c | 4 | int | | | my_row_id | 1 | bigint | PRI | +-------------+------------------+-----------+------------+ 4 rows in set (0.00 sec)
カラム位置は自動的に先頭になるっぽいです。
ORDINAL_POSITIONの値は制約内のカラムの位置であり、テーブル内のカラムの位置ではありません。カラムの位置には 1 から始まる番号が付けられています。MySQL :: MySQL 5.6 リファレンスマニュアル :: 21.10 INFORMATION_SCHEMA KEY_COLUMN_USAGE テーブル
どんなメリットがあるのか
わからないです。
見たところPKのないテーブルにINVISIBLEをつけたAUTO_INCREMENTのPKを追加しているだけに見えます。
一応Twitterで調べると↓の用途を想定しているらしいです。
https://twitter.com/mita2/status/1551904906734616576?s=20&t=9Jsu_cZy0rd9NpluZTiAGA
PKがほぼ必須のグループレプリケーションや必須のHeatWaveをご利用いただくケースを想定している模様です。他にも役立つケースはあるかとは思います。
— 🐬🍣🍻 (@RKajiyama) 2022年7月26日
なるほど。
気になった事やってみる
ドキュメントや検証してて個人的に気になったことをやってみます。
この列消せるの？
このGIPKを使わないから消せるのか検証。
結果はNo
(root@localhost) [test] 8.0.30 > ALTER TABLE not_primarykey DROP COLUMN my_row_id; ERROR 1235 (42000): This version of MySQL doesn't yet support 'existing primary key drop without adding a new primary key. In @@sql_generate_invisible_primary_key=ON mode table should have a primary key. Please add a new primary key to be able to drop existing primary key.'
このバージョンの MySQL では、新しいプライマリ キーを追加せずに既存のプライマリ キーを削除することはまだサポートされていません。sql_generate_invisible_primary_key=ON モードでは、テーブルには主キーがあるはずです。既存の主キーを削除できるようにするには、新しい主キーを追加してください。
カラム名同じやつあったらどうなるの？
my_row_idという固定のカラム名で追加されるので、明示的に同じカラム名で指定するとどうなるのか検証。
結果はERRORになる
(root@localhost) [test] 8.0.30 > create table not_primarykey2 ( -> a int not null , -> b char(8) not null , -> c int not null, -> my_row_id int not null -> ); ERROR 4108 (HY000): Failed to generate invisible primary key. Column 'my_row_id' already exists. (root@localhost) [test] 8.0.30 >
〆
新機能なので試してみましたが、個人的には使わなさそうだなという感想です。
