MySQLストアドプロシージャで文字列を分割し、各値をループ処理する方法

MySQLのストアドプロシージャ内で文字列を分割し、すべての値に対してループ処理を行いたい場合、REPLACE()関数を使用する必要はありません。SUBSTRING_INDEX()関数とLOOP文を組み合わせることで実現できます。手順としては、まずストアドプロシージャを作成し、次にデータを格納するテーブルを作成してから、値を渡してプロシージャを呼び出します。すると、分割された各値が順番にテーブルへ挿入されます。

ストアドプロシージャの作成

文字列を分割するストアドプロシージャを作成するクエリは以下の通りです。カンマ区切りの文字列を受け取り、先頭の要素を取り出してはテーブルに挿入し、処理済みの部分を削りながらループを回す仕組みになっています。

mysql> DELIMITER //
mysql> CREATE PROCEDURE SP_SplitString(Value longtext)
    -> BEGIN
    -> DECLARE front TEXT DEFAULT NULL;
    -> DECLARE frontlen INT DEFAULT NULL;
    -> DECLARE TempValue TEXT DEFAULT NULL;
    -> iterator:
    -> LOOP 
    -> IF LENGTH(TRIM(Value)) = 0 OR Value IS NULL THEN
    -> LEAVE iterator;
    -> END IF;
    -> SET front = SUBSTRING_INDEX(Value,',',1);
    -> SET frontlen = LENGTH(front);
    -> SET TempValue = TRIM(front);
    -> INSERT INTO store (allValues) VALUES (TempValue);
    -> SET Value = INSERT(Value,1,frontlen + 1,'');
    -> END LOOP;
    -> END //
Query OK, 0 rows affected (0.22 sec)
mysql> DELIMITER ;

このプロシージャのポイントは次の通りです。

• SUBSTRING_INDEX(Value, ',', 1): カンマ区切りの文字列から最初の要素を取得します。
• TRIM(): 要素の前後にある余分な空白を除去します。
• INSERT(Value, 1, frontlen + 1, ''): 処理済みの要素と区切り文字を元の文字列から削除し、残りの部分だけを次のループに渡します。
• LEAVE iterator: 文字列が空になった場合やNULLの場合にループを抜けます。

データ格納用テーブルの作成

次に、ストアドプロシージャで分割された値を保存するテーブルを作成します。テーブル作成のクエリは以下の通りです。

mysql> CREATE TABLE store(
   -> Id int NOT NULL AUTO_INCREMENT,
   -> allValues varchar(30),
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (0.63 sec)

テーブルの初期状態を確認

テーブル内のすべてのレコードを表示して、初期状態を確認しておきましょう。

mysql> select *from store;
Empty set (0.00 sec)

この時点では、テーブルにはまだ何のレコードも存在していません。

ストアドプロシージャの呼び出し

CALLコマンドを使ってストアドプロシージャを呼び出します。引数としてカンマ区切りの文字列を渡します。

mysql> call SP_SplitString('Hi,Hello,Good Morning,Bye');
Query OK, 1 row affected (1.02 sec)

実行結果の確認

ストアドプロシージャを呼び出した後、改めてテーブルのレコードを表示してみましょう。

mysql> select *from store;

以下が出力結果です。

+----+--------------+
| Id | allValues    |
+----+--------------+
|  1 | Hi           |
|  2 | Hello        |
|  3 | Good Morning |
|  4 | Bye          |
+----+--------------+
4 rows in set (0.00 sec)

このように、「Hi」「Hello」「Good Morning」「Bye」の4つの値がカンマで正しく分割され、それぞれ個別のレコードとしてテーブルに挿入されていることが確認できます。「Good Morning」のようにスペースを含む要素も、TRIM()によって余分な空白だけが除去され、正しく保持されます。