皆さんは、普段テーブルにレコードを追加する時にどういう方法を使っているでしょうか。プログラムを書いてORMから操作したり、SQLで直接DBに流し込んだり、さまざまな方法をとっていると思います。単一のテーブルの操作であればSQLでも良いけど、関係を持っているテーブルへの挿入があると困ってしまうのが、外部キーの扱いです。そういった場合はプログラムを用意して、外部キーを保存して、という方法を取る場合も多いと思いますが、今回はSQLでそれを何とかする方法を紹介してみたいと思います。
検証環境
今回はdockerで建てたMySQLを使用します。以下のコマンドでdockerを建ててローカルからアクセスをします。
% docker run --platform linux/x86_64 -p 3307:3306 -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:latest % % mysql -uroot -pmy-secret-pw
執筆時点では、以下の通りMySQL 8.
mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.28 | +-----------+ 1 row in set (0.01 sec)
今回のテスト用に、testデータベースと2つのテーブルを、以下のクエリで作っておきます。
mysql> create database test; mysql> use test mysql> create table author(id SERIAL PRIMARY KEY, name text ); mysql> create table article(id SERIAL PRIMARY KEY, author_id BIGINT UNSIGNED, title TEXT, FOREIGN KEY(author_id) REFERENCES author(id));
著者
mysql> INSERT INTO author(name) VALUES('sato'),('suzuki'),('takahashi') ;
mysql> INSERT INTO article(author_id, title) VALUES(1, 'by sato'),(2, 'by suzuki'),(3, 'by takahashi') ;
SELECTをして確認してみます。今回はせっかくなのでINNER JOINをして確認してみます。
mysql> select * FROM author INNER JOIN article ON author.id = article.author_id ; +----+-----------+----+-----------+--------------+ | id | name | id | author_id | title | +----+-----------+----+-----------+--------------+ | 1 | sato | 1 | 1 | by sato | | 2 | suzuki | 2 | 2 | by suzuki | | 3 | takahashi | 3 | 3 | by takahashi | +----+-----------+----+-----------+--------------+ 3 rows in set (0.01 sec)
SQLだけで同一トランザクション内で複数のテーブルにinsertを行う方法を考えてみる
さて、これらのテーブルのauthorにkimura、articleにby kimuraを同時に足したい場合を考えてみます。ここで問題になるのは、articleのidの値をauthorにinsertする前に知る方法がないというところです。そのために、この場合取れる方法は、以下の2つのどちらかになると思います。
- auto_
incrementの値と増加量を調べてidを考えて用意する - LAST_
INSERT_ ID()の値を変数にSETして挿入する
auto_
利用者を制限できる場合
メンテナンスモード等に入ることができて作業者以外が存在しない状態であれば、auto_
auto_
mysql> SELECT AUTO_INCREMENT FROM information_schema.tables WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'author'; +----------------+ | AUTO_INCREMENT | +----------------+ | 3 | +----------------+ 1 row in set (0.08 sec)
今回は結果が3だったので、挿入に使用されるSQLは以下のようなクエリになると思います。
BEGIN;
INSERT INTO author(name) VALUES('kimura');
INSERT INTO article(author_id, title) VALUES(4, 'by kimura');
COMMIT;
では、結果を確認してみましょう。
mysql> select * FROM author INNER JOIN article ON author.id = article.author_id ; +----+-----------+----+-----------+--------------+ | id | name | id | author_id | title | +----+-----------+----+-----------+--------------+ | 1 | sato | 1 | 1 | by sato | | 2 | suzuki | 2 | 2 | by suzuki | | 3 | takahashi | 3 | 3 | by takahashi | | 4 | kimura | 4 | 4 | by kimura | +----+-----------+----+-----------+--------------+ 4 rows in set (0.01 sec)
操作する人が利用者に絞れていれば、特に難しいことはないと思います。
利用者を制限できない場合
こちらは先ほど述べたとおり、2種類の方法があります。auto_
auto_incrementを進める方法
こちらは話が単純で、利用率などを見て、使用されるはずがないところまで、auto_
たとえば、上記のテーブルにauthorのnameが
BEGIN;
INSERT INTO author(id, name) VALUES(10, 'kimura2');
INSERT INTO article(author_id, title) VALUES(10, 'by kimura2');
COMMIT;
行の増加量を加味して増やしてあげれば、挿入できることがわかると思います。さて、皆さんが気になるのは、auto_
これは正直要件によりますが、MySQLに関しては問題ありません。飛ばしてしまった番号に関しては使用されませんが、飛ばした場所から続けて使用されます。
BEGIN;
INSERT INTO author(name) VALUES('kimura3');
INSERT INTO author(name) VALUES('kimura4');
COMMIT;
mysql> select * FROM author; +----+-----------+ | id | name | +----+-----------+ | 1 | sato | | 2 | suzuki | | 3 | takahashi | | 4 | kimura | | 10 | kimura2 | | 11 | kimura3 | | 12 | kimura4 | +----+-----------+ 7 rows in set (0.01 sec)
このように使用されていることがわかります。
さて、では要件によるというのはどういう場合なのかなと疑問に思った方がいるかも知れません。たとえば、このidが本当にただの外部キーとして使用されている場合は問題ないでしよう。しかし、たとえばこのidの中からrandomを取って、ということになると、このように偏りがあると困ってしまう場合があるかもしれません。
auto_
LAST_INSERT_ID()を使用する
続いて、LAST_
これを使うと、そのセッションの中で最後に挿入した値を取得できるので、SQLに組み込んで使うことができます。たとえば
BEGIN;
INSERT INTO author(name) VALUES('kimura5');
SET @insert_id = LAST_INSERT_ID();
INSERT INTO article(author_id, title) VALUES(@insert_id, 'by kimura5');
COMMIT;
上記のように書いて実行すると、以下のような結果が得られます。
mysql> select * FROM author INNER JOIN article ON author.id = article.author_id ; +----+-----------+----+-----------+--------------+ | id | name | id | author_id | title | +----+-----------+----+-----------+--------------+ | 1 | sato | 1 | 1 | by sato | | 2 | suzuki | 2 | 2 | by suzuki | | 3 | takahashi | 3 | 3 | by takahashi | | 4 | kimura | 4 | 4 | by kimura | | 10 | kimura2 | 5 | 10 | by kimura2 | | 13 | kimura5 | 6 | 13 | by kimura5 | +----+-----------+----+-----------+--------------+ 6 rows in set (0.00 sec)
このように、LAST_
まとめ
こういった、SQLだけで複数の関連するテーブルにinsertする状況はあまりないとは思いますが、イレギュラーな処理で一時的にSQLでどうにかしたい場合などに知っておくと便利なことがあります。
どうしてもSQLで対応しないといけなくなった場合に、こういう事ができると知っておくと、たいへん便利にできると思いますので、頭の片隅に置いておくと良いでしょう。