お久しぶりです。
JGです。
今回はMySQLの主キーについて話をしたいと思います。

概要

皆さんが「MySQLでテーブルを作成する際に主キーは必須?」と質問されたら、なんと返答しますか?

恥ずかしながら以前は必須だと思っておりました・・・

私は前職もITエンジニアとして働いており、その際に担当した案件でたまたま主キーがないテーブルを見つけました。

当時はMySQLのバージョンが5の時代でしたが、その際に公式ドキュメントを調べた限りは主キーは必須と書かれておらず、設定で主キーを必須にする機能は見当たりませんでした・・・

主キーはあって当たり前と思い込みでMySQLを扱ってきた自分にとって、主キーが必須ではないことを知ったのは結構衝撃的でした・・・

ちなみにですがそのテーブルは昔は使われていましたが、現在は使われていないテーブルでしたので特に問題になることもありませんでした。

主キーがないテーブルの挙動

あれから時が経ちMySQLもバージョン8が誕生し、改めて公式ドキュメントで主キーが必須かどうか確認しましたが、やはり主キーは必須ではありませんでした。

MySQL8で主キーがないときの挙動を軽く説明しますが、不可視プライマリキー(GIPK)についてはふれません。

下記のような主キーのないテーブルを作成し、1万件データを流し込みをします。

CREATE TABLE `noexist_primary` (
  `number` int NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_general_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO noexist_primary (number, name) VALUES (1, "test1");
INSERT INTO noexist_primary (number, name) VALUES (2, "test2");
・・・
INSERT INTO noexist_primary (number, name) VALUES (9999, "test9999");
INSERT INTO noexist_primary (number, name) VALUES (10000, "test10000");

テーブルが出来上がりましたので、ここからは幾つかSQL文を実行し挙動を見ていきます。

下記のようなSQLを実行しても、numberが主キーでもなくかつインデックスを貼っていないためフルスキャンになります(インデックスを貼れば改善されます)。

SELECT * FROM noexist_primary WHERE number = 89;

下記のようなSQLを複数回実行しても、主キーがないため重複データが登録できてしまいます。

INSERT INTO noexist_primary (number, name) VALUES (10000, "test10000");

重複データができた後に、下記のUPDATE文を実行すると重複データが全て更新されます。

UPDATE noexist_primary SET name = 'test10000a' WHERE number = 10000;

重複データができた後に、下記のDELETE文を実行すると重複データが全て削除されます。

DELETE FROM noexist_primary WHERE number = 10000;

主キーがないテーブルの挙動については、皆さんが想像した通りの挙動であったと思います。

主キーがないテーブルはパフォーマンスも悪くなりがちで、重複データができる等の問題があるため極力避けるべきです。

主キーがないテーブルへの対策

主キーがないテーブルを作成した時はエラーで制御したいというのが、エンジニア皆さんの気持ちだと思います。

私が以前調べた時のMySQL5ではそのような機能は見つかりませんでしたが、MySQL8.0.13から導入されました。

sql_require_primary_keyで、詳細は下記URLに書かれています。

https://dev.mysql.com/doc/refman/8.0/ja/server-system-variables.html#sysvar_sql_require_primary_key

簡単に説明しますとONにすると主キーのないテーブルの作成、または既存テーブルから主キーを削除しようとするとエラーになります。

デフォルトはOFFのため、この機能を使いたい場合はONにする必要があります。

sql_require_primary_keyをONにしたときの挙動を確認しましたが、主キーのないテーブルを作成しようとするとエラーになり、既存のテーブルから主キーを削除するとエラーになりました。

既に主キーがないテーブルの挙動は上記で説明した挙動と変わりませんでした。

重複データはできてしまいますし、重複データができた後にUPDATEやDELETEを実行すると重複データ全てが更新・削除されました。

最後に

MySQLは主キーがないテーブルができてしまう可能性があることがわかったと思います。

これを防ぐためにはMySQL8.0.13から導入されたsql_require_primary_keyをONにすることで、主キーがないテーブルができないように制御できるようになります。

皆さんの中にも「これはこういうものだ」と思い込んでいるものを調べてみると、意外と自分の認識と違って技術的な知見を得ることもあるので、時間がある時に調べてみるのも良いと思います。

今回はこれで失礼します。