7月にリリースされた MySQL 8.0.30 では「Generated Invisible Primary Key(GIPK)」という機能が追加されました。各テーブルにPKがあることを必須とし、ない場合はテーブル作成時に自動的に不可視列としてAUTO_INCREMENTなPK列を追加してくれる、という機能です。
Invisible Column の仕組み自体は MySQL 8.0.23 （2021年1月）で追加されたものです。当時の日記を読み返すと、使い道がよくわからないと言いながらも、8.0.30で追加された機能と似たような使い方を想像はしていたようです。悪くない。
ということで、どんなクセを持ったものなのか触ってみたいと思います。
きっかけは tmtms さん
自動でカラムを追加してくれるということは、そのカラムと同じ名前のものを明示していた場合、どうなるんだろうという、tmtmsさんのツイートをみて、試してみたいと思ったのがきっかけでした。
my_row_id カラムを別に指定してたらどうなるんだろ #LINE_DM
— とみたまさひろ🍣🍺 (@tmtms) 2022年8月2日
モードの確認と設定
GIPKの機能によりPKを自動生成させるには「GIPKモード」にしておく必要があります。@@sql_generate_invisible_primary_key 変数にて設定します。デフォルトはオフ(0)です。
mysql> SELECT @@sql_generate_invisible_primary_key; +--------------------------------------+ | @@sql_generate_invisible_primary_key | +--------------------------------------+ | 0 | +--------------------------------------+
オンにするには、1 または on または true にします。
mysql> SET @@sql_generate_invisible_primary_key=on; Query OK, 0 rows affected (0.00 sec)
確認するとオン（1）になっていることがわかります。
mysql> SELECT @@sql_generate_invisible_primary_key; +--------------------------------------+ | @@sql_generate_invisible_primary_key | +--------------------------------------+ | 1 | +--------------------------------------+
なお、今回はテスト用に一時的に自分のセッションだけ GIPKモードにしたものなので、再接続など別セッションとなった場合はまたオフになることにご注意ください。
global と session の各設定状況を見てもその通りになっていますね。
mysql> show global variables like '%invisible%'; +------------------------------------+-------+ | Variable_name | Value | +------------------------------------+-------+ | sql_generate_invisible_primary_key | OFF | +------------------------------------+-------+
mysql> show session variables like '%invisible%'; +------------------------------------+-------+ | Variable_name | Value | +------------------------------------+-------+ | sql_generate_invisible_primary_key | ON | +------------------------------------+-------+
変数名をSELECTしたときには 0/1 で表示されるのに、variables を見た場合は ON/OFF になっているのが、ちょっと面白いですね。
GIPKモードがオンとオフの動作の違い
一旦、GIPKモードをオフにして、PKなしのテーブル（id, name の2つの列を持つ）t1 を作成して、作成されたテーブル定義を確認してみます。
mysql> SET @@sql_generate_invisible_primary_key=off; mysql> CREATE TABLE t1 (id INTEGER, name VARCHAR(10)); Query OK, 0 rows affected (0.02 sec) mysql> SHOW CREATE TABLE t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int DEFAULT NULL, `name` varchar(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=cp932
当然、CREATE TABLE 文で指定した2つの列を持つテーブルが作成されました。
次に、GIPKモードをオンにして、同様の列を持つテーブル t2 を作成してみます。
mysql> SET @@sql_generate_invisible_primary_key=on; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE t2 (id INTEGER, name VARCHAR(10)); Query OK, 0 rows affected (0.02 sec) mysql> SHOW CREATE TABLE t2\G *************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE `t2` ( `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */, `id` int DEFAULT NULL, `name` varchar(10) DEFAULT NULL, PRIMARY KEY (`my_row_id`) ) ENGINE=InnoDB DEFAULT CHARSET=cp932
CREATE TABLE 文で指定していない my_row_id という列が追加されました。 8.0.23以降の（8.0.30ではありません）場合に "INVISIBLE"が有効となるように記述されていますね。
データを入れてみる
ユーザが作成を指示したテーブルは、id, name の2つの列を持つテーブルですから、勝手に付けたカラムが通常の操作を阻害するものとなってはいけません。簡単なINSERT文で試してみます。
mysql> INSERT INTO t2 VALUES (3, "test"); Query OK, 1 row affected (0.01 sec) mysql> select * FROM t2; +------+------+ | id | name | +------+------+ | 3 | test | +------+------+
よかった。 INSERT 文の VALUES の前に列を明示していませんが、（3カラムではなく）2カラムのINSERT文が無事実行されました。
列を明示してSELECTすると、my_row_id にも値が入っていることが確認できます。
mysql> select my_row_id, id, name from t2; +-----------+------+------+ | my_row_id | id | name | +-----------+------+------+ | 1 | 3 | test | +-----------+------+------+
同名の列（my_row_id）がすでにある場合は？
冒頭の tmtms さんのツイートにある疑問を、ここで試してみましょう。
PKではない my_row_id 列を持つ CREATE TABLE 文を実行します。
mysql> CREATE TABLE t3 (my_row_id INTEGER, name VARCHAR(10)); ERROR 4108 (HY000): Failed to generate invisible primary key. Column 'my_row_id' already exists.
エラーになりました。
ん？でもちょっと待ってください。 Column 'my_row_id' already exists ですって？ 単純に同じ名前のカラムを2つ以上指定して CREATE TABLE を実行した場合は、別のエラー（Duplicate column name）となるので、それとは別のロジックによりエラーが出されていることになります。このへん、昨日 LINE Developers Meetup のMySQLの回でお話聞かせてもらった tom__boさんならソースコード追い始めるのでしょうが、私はここで断念。 機会あったらこれを題材にして追い方講座など、お話聞いてみたいものです。
mysql> CREATE TABLE t3 (id INTEGER, id INTEGER, name VARCHAR(10)); ERROR 1060 (42S21): Duplicate column name 'id'
GIPKは消せるの？
単純には消せません。GIPKモードというのは「テーブルにPKを必須とする」モードなので、PKがなくなることが許されないのです。・・・と分かったような書きっぷりをしてしまいましたが、エラーメッセージの「今のバージョンではまだ」という記述がちょっと気になりますね。将来はDROPできるようになる（＝PK必須モードというわけではない）のかもしれません。
＞＞This version of MySQL doesn't yet support 'existing primary key drop without adding a new primary key.
mysql> ALTER TABLE t3 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.'
なお、エラーメッセージで指示されたとおり、GIPK列をDROPすると同時に新たなPK列を追加すれば、エラーなく実行できます。
mysql> ALTER TABLE t3 DROP COLUMN my_row_id, ADD COLUMN (id INTEGER PRIMARY KEY); Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SHOW CREATE TABLE t3\G *************************** 1. row *************************** Table: t3 Create Table: CREATE TABLE `t3` ( `name` varchar(10) DEFAULT NULL, `id` int NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=cp932
GIPK の機能を使わないで明示的に指定することももちろんできる
GIPKは「CREATE TABLE に PK の指定がない場合に、自動で隠しPK列を追加しておく」という機能なので、PKを明示すれば発動しませんし、また、そのPK列を明示的にINVISIBLEにする（変な日本語）ことも可能となります。
mysql> CREATE TABLE t4 (id INTEGER AUTO_INCREMENT PRIMARY KEY INVISIBLE, name VARCHAR(10)); Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO t4 VALUES ("test"); Query OK, 1 row affected (0.01 sec)
テーブルにひとつはVISIBLEな列は必要
8.0.30 の Generated Invisible な話に限らず、8.0.23からの話ではありますが、Invisible列だけのテーブルを作ったり、そういうテーブルになるようなテーブル定義操作はできません。
mysql> ALTER TABLE t4 DROP COLUMN name; ERROR 4028 (HY000): A table must have at least one visible column.
おわりに
ということで、Generate Invisible Primary Key ならびに、そもそもの Invisible カラムについて、遊んで見ました。検証中に少し気になることを検索していたら、既に Updraft さんが 8月1日に似たようなことを試されていました。さすがです。 遅れを取った。。。
追追記(2022/09/10)：INVISIBLEでも LAST_INSERT_ID()は取れるの？
表題は @hmatsu さんの疑問ツイート。
アプリケーションからLAST_INSERT_ID()が取れないと困るケースも結構ありそうなんですが、不可視で取れましたっけ？（取れるとしたら「不可視」の意味…）
— hmatsu47(まつ) (@hmatsu47) 2022年9月10日
（ORMとの相性が悪そうな予感）
早速試してくれた @yoku0825 さん。結論は「できます」。
元のツイートから 14分後の速攻のお試し＆回答でした。早すぎる！
（試した例は GIPK ではなく自前で作ったINVISIBLEカラムですが、同じ動作のはずです）
Invisible column、たかだか「SELECT * で展開されない」「カラムリスト指定なしのINSERTで無視できる」くらいだろうと思っているのでできるだろうと思ったらできました。https://t.co/Bp5c4daNP9
— yoku0825 (@yoku0825) 2022年9月10日
