MYSQLでカーソルを使ってjson_arrayの各要素を別テーブルの各カラムにループでINSERTする。

環境

MYSQL8.0.30とMYSQL Workbench8.0を使いコードを書きました。

作業内容

テーブル（table_1）にjson型のカラム（json_col)があり各レコードの各要素を別テーブル（table_2）のカラム（col_1~3）ごとに分けて挿入する為のプロシージャー（insert_json_porc）を作成して、実行する。

table_1

table_2（ﾃﾞｰﾀ挿入前）

完成形 table_2（ﾃﾞｰﾀ挿入後）

プロシージャー（insert_json_porc）作成コード

mysql_code

DROP PROCEDURE IF EXISTS insert_json_porc;
DELIMITER //
CREATE PROCEDURE insert_json_porc()
BEGIN
	-- 変数を宣言(カーソル定義前に行う必要あり) 
	-- カーソルから読み出した値を格納する変数を宣言
    DECLARE col_1_data VARCHAR(10);
    DECLARE col_2_data VARCHAR(10);
    DECLARE col_3_data VARCHAR(10);

	-- カーソルがデータセットの最後に達したか判定するための変数を宣言
	DECLARE done INT DEFAULT FALSE;
    
	-- カーソルを定義
	DECLARE myCursor CURSOR FOR
		select json_col ->> '$[0]', json_col ->> '$[1]', json_col ->> '$[2]'
		from table_1;
	
    -- カーソルがデータセットの最後に達したときの動作を制御
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

	-- カーソルをオープン
	OPEN myCursor;
    
	-- ループで1行ずつ処理
	read_loop: LOOP
		-- カーソルから1行読み出し
		FETCH myCursor INTO col_1_data, col_2_data, col_3_data;
        
        -- カーソルからの読み出しが最後に達していればループを抜ける
		IF done THEN
			LEAVE read_loop;
		END IF;
        
		-- 別テーブルにインサート
        INSERT INTO table_2(col_1, col_2, col_3) VALUES( col_1_data, col_2_data, col_3_data);
	END LOOP;
    
    -- カーソルを閉じる
	CLOSE myCursor;   
END //

DELIMITER ;

プロシージャー（insert_json_proc)を実行します。

mysql_code

call insert_json_porc();
select * from table_2;

完成形 tabel_2（挿入後）

以上で完成です。

免責事項

コンテンツや情報において、必ずしも正確性を保証するものではありません。また合法性や安全性なども保証しません。
掲載された内容によって生じた損害等の一切の責任を負いかねますので、ご了承ください。