MySQL道普請便り

第179回SQLだけで複数のテーブルにinsertを行う方法

皆さんは、普段テーブルにレコードを追加する時にどういう方法を使っているでしょうか。プログラムを書いて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.0.28を使用しております。

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));

著者(author)と記事(article)という関連したテーブルを作成しました。初期データとして以下のものを用意します。

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_incrementの値を調べてidを考えて用意する場合は、利用者を制限できる場合とできない場合で、取れる方法が若干異なります。

利用者を制限できる場合

メンテナンスモード等に入ることができて作業者以外が存在しない状態であれば、auto_incrementの値を調べてその1つ後の値を入れてあげれば良いことは、直感的にわかると思います。

auto_incrementとして次に使用される値を調べる方法としては、以下のようにinformation_schemaを使って調べる方法があります。TABLE_SCHEMAにはデータベース名を指定して、TABLE_NAMEには調べたいテーブル名を指定します。

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_incrementを進める方法とLAST_INSERT_ID()を使用する方法です。

auto_incrementを進める方法

こちらは話が単純で、利用率などを見て、使用されるはずがないところまで、auto_incrementで指定されているIDを進めてしまう方法になります。

たとえば、上記のテーブルにauthorのnameが「kimura2」で、それに紐づくarticleのtitleが「by kimura2」の行を挿入したい場合を考えます。この場合、authorのauto_incrementの値は5なので、5より大きい数を入れてあげれば良いことになります。なので、飛ばして10の所に入れてみましょう。

BEGIN;
INSERT INTO author(id, name) VALUES(10, 'kimura2');
INSERT INTO article(author_id, title) VALUES(10, 'by kimura2');
COMMIT;

行の増加量を加味して増やしてあげれば、挿入できることがわかると思います。さて、皆さんが気になるのは、auto_incrementの値を飛ばしてしまって問題ないのか?というところだと思います。

これは正直要件によりますが、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_incrementを飛ばす場合は、要件としてやって大丈夫か?というところを意識して進めましょう。

LAST_INSERT_ID()を使用する

続いて、LAST_INSERT_ID()を使用する方法を紹介します。このLAST_INSERT_ID()は、最後に挿入した値を取得できる関数です。公式のドキュメントはこちらになります。

これを使うと、そのセッションの中で最後に挿入した値を取得できるので、SQLに組み込んで使うことができます。たとえば「kimura5」というauthorで、⁠by kimura5」というタイトルのarticleを入れる例を考えてみましょう。

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_INSERT_IDを使うと変数を用いて、auto_incrementのような動的に決まる結果を使用して、挿入することができました。

まとめ

こういった、SQLだけで複数の関連するテーブルにinsertする状況はあまりないとは思いますが、イレギュラーな処理で一時的にSQLでどうにかしたい場合などに知っておくと便利なことがあります。

どうしてもSQLで対応しないといけなくなった場合に、こういう事ができると知っておくと、たいへん便利にできると思いますので、頭の片隅に置いておくと良いでしょう。

おすすめ記事

記事・ニュース一覧