MySQLで文字列を分割して個別の値としてテーブルに挿入する方法

MySQLでは、プリペアドステートメント（PREPARE文）を活用することで、カンマ区切りの文字列を分割し、個別の値としてテーブルに挿入することができます。本記事では、その具体的な手順をサンプルコードとともに解説します。

1. テーブルを作成する

まず、データを挿入するためのテーブルを作成します。以下のクエリで「University」テーブルを作成しましょう。

mysql> create table University
-> (
-> UserId int,
-> UniversityId int
-> );
Query OK, 0 rows affected (0.64 sec)

2. 変数に値を設定する

次に、挿入する値を変数に設定します。ここでは、UserId列にカンマ区切りの文字列「8,9,10」を設定し、この文字列を分割してテーブルに挿入していきます。

mysql> SET @userId = '8,9,10';
Query OK, 0 rows affected (0.00 sec)
mysql> SET @UniversityId = 100;
Query OK, 0 rows affected (0.00 sec)

3. 文字列を整形してINSERT文を組み立てる

REPLACE関数とCONCAT関数を使って、カンマ区切りの文字列を複数行のVALUES句に変換します。

mysql> SET @myValues = REPLACE(@userId, ',', CONCAT(', ', @UniversityId, '),('));
Query OK, 0 rows affected (0.00 sec)
mysql> SET @myValues = CONCAT('(', @myValues, ', ', @UniversityId, ')');
Query OK, 0 rows affected (0.00 sec)
mysql> SET @insertQuery1 = CONCAT('INSERT INTO University VALUES', @myValues);
Query OK, 0 rows affected (0.00 sec)

この時点で、@insertQuery1には以下のようなSQL文が組み立てられています。

INSERT INTO University VALUES(8, 100),(9, 100),(10, 100)

4. プリペアドステートメントを実行する

すべての準備が整ったら、組み立てたクエリをPREPARE文で準備し、EXECUTE文で実行します。

mysql> PREPARE st FROM @insertQuery1;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql> EXECUTE st;
Query OK, 3 rows affected (0.18 sec)
Records: 3 Duplicates: 0 Warnings: 0

実行結果を見ると、3件のレコードが正常に挿入されたことがわかります。

5. 挿入結果を確認する

SELECT文でテーブル内の全レコードを表示して確認してみましょう。

mysql> select *from University;

以下の出力から、文字列「8,9,10」が正しく分割され、それぞれ個別の値として挿入されたことが確認できます。

+--------+--------------+
| UserId | UniversityId |
+--------+--------------+
|      8 |          100 |
|      9 |          100 |
|     10 |          100 |
+--------+--------------+
3 rows in set (0.00 sec)

6. プリペアドステートメントを解放する

最後に、使用済みのプリペアドステートメントをDEALLOCATE文で解放します。これにより、サーバーリソースを無駄に占有することを防げます。

mysql> DEALLOCATE PREPARE st;
Query OK, 0 rows affected (0.00 sec)

まとめ

このように、SET文・REPLACE関数・CONCAT関数・プリペアドステートメントを組み合わせることで、カンマ区切りの文字列を動的に分割し、複数行のデータとしてMySQLテーブルへ一括挿入できます。アプリケーション側で文字列を分解する処理を書く必要がないため、シンプルなバッチ処理などで役立つテクニックです。