no-image

MySQLで複数テーブルにデータを入力(INSERT)するためのTRANSACTIONやLOCK TABLES使う時のテ ンプレート

MySQLでデータをINSERTするときに、複数の関連テーブルに別テーブルのデータを使って入れなきゃいけないことって多いと思います。

そのときにどうやって書けばいいのかを毎回調べたり、手続き的なことをきちんと網羅するのが意外と大変だったのでメモ。

TRANSACTIONとautocommitの挙動はこのぺーじがわかりやすかった。
http://kannokanno.hatenablog.com/entry/20120704/1341419338

START TRANSACTION 公式リファレンス
https://dev.mysql.com/doc/refman/5.6/ja/commit.html

SET autocommit = 0; #自動的に実行されるautocommitをfalse(0:false, 1:true)にする START TRANSACTION; #TRANSACTION(処理) の開始宣言
LOCK TABLES table1 write, table2 write;
#table1とtable2の書き込みをできないようにする(LOCKする) あとで必ずUNLOCK
TABLESを実行する。やり忘れるとLOCKしたテーブルがずっと書き込みできなくなる。
SELECT @table1_id_max := id FROM table1 ORDER BY id DESC LIMIT 1; #一番新しいid番号を取得して@table1_id_maxで呼び出せるようにする。
SET @now = CONVERT_TZ(NOW(), ‘+00:00′,’+09:00’);
#今の時間を格納しておいて呼び出せるようにする。CONVERT_TZ()はUTCを補正するための処理。
INSERT INTO table1 (… ,created_at,updated_at) VALUES #table1でINSERTを実行 #SQL
(… ,@now,@now),

(… ,@now,@now);

INSERT INTO table2 (table1_id, … ,created_at,updated_at) SELECT table1.id, … ,@now,@now FROM table1 WHERE table1.id > @table1_id_max; #table1のデータをtable2にINSERTする
UNLOCK TABLES; #テーブルのLOCKを解除
COMMIT; # TRANSACTION以降の内容を実行
SET autocommit = 1; # autocommitの設定をもとに戻しておく。ERRORが起きた時など、注意。 # SELECT @@autocommit; #autocommitが今どんな設定になっているかチェックするコード。コメント外して使用 # SELECT * FROM table1 ORDER BY id DESC LIMIT 100;
#一応目視でtable1がちゃんと更新できているか確認するとき用

SELECT * FROM table2 ORDER BY id DESC LIMIT 100; #table2用

pocketlinehatebuimagegalleryaudiovideocategorytagchatquotegoogleplusfacebookinstagramtwitterrsssearchenvelopeheartstaruserclosesearch-plushomeclockupdateeditshare-squarechevron-leftchevron-rightleafexclamation-trianglecalendarcommentthumb-tacklinknaviconasideangle-double-upangle-double-downangle-upangle-downstar-halfstatus