MySQLのUPDATE文にORDERが指定できると知った件

Transcript

1. MySQLのUPDATE文に ORDERが指定できると知った件 2021/5/18 Dev MTG ham
2. 何をやろうとしていたのか
3. シーケンシャルなデータを更新しようとした 下記のテーブルのsequential_idを一括で更新しようとしていた。 ※同じparent_idで重複したsequential_idを持てないように、 [parent_id, sequential_id]でユニーク制約あり Field Type memo id bigint PRIMARY KEY; auto_increment parent_id bigint sequential_id int unsigned parent_id内で連番
4. シーケンシャルなデータを更新しようとした 下記のデータがある場合、途中にレコードを追加したい。 id parent_id sequential_id 1 100 1 2 100 2 3 100 3 1と2の間に新しいレコードを入れたい
5. シーケンシャルなデータを更新しようとした 最終的にはこうなればOK id parent_id sequential_id 1 100 1 4 100 2 2 100 3 3 100 4 ・レコードが追加される ・id: 2, 3のsequential_idが1増える
6. シーケンシャルなデータを更新しようとした 下記のデータがある場合、途中にレコードを追加したい。 UPDATE文1発で実行したいので下記のクエリーで実行 UPDATE hoges SET sequential_id = sequential_id + 1 WHERE parent_id = 100 ABD sequential_id >= 2; id parent_id sequential_id 1 100 1 2 100 2 3 100 3 sequential_id=2はid: 2に使われている ので、先にid: 2, 3のsequential_idをず らす。
7. シーケンシャルなデータを更新しようとした 下記のデータがある場合、途中にレコードを追加したい。 UPDATE文1発で実行したいので下記のクエリーで実行 UPDATE hoges SET sequential_id = sequential_id + 1 WHERE parent_id = 100 ABD sequential_id >= 2; id parent_id sequential_id 1 100 1 2 100 2 3 100 3 sequential_id=2はid: 2に使われている ので、先にid: 2, 3のsequential_idをず らす。 ユニーク制約違反のエラーが発生・・・！！
8. シーケンシャルなデータを更新しようとした 下記のデータがある場合、途中にレコードを追加したい。 UPDATE文1発で実行したいので下記のクエリーで実行 UPDATE hoges SET sequential_id = sequential_id + 1 WHERE parent_id = 100 AND sequential_id >= 2; id parent_id sequential_id 1 100 1 2 100 2 3 100 3 Update文が1つでも内部的には1レコードずつ更 新される。 (✗全てのレコードが一発で同時更新されるわけ ではない) →MySQLでは順序が未指定の場合は大体Primary Key(今回はid)順に動く。 →今回もidの小さい2のレコードから更新しよう としてid:3のレコード(100-3のユニーク制約)と衝 突したと考えられる。
9. じゃあどうするか？ 今回やりたいことはsequential_idのインクリメント。 ということはsequential_idの大きいものから順番に更新していけばユニーク 制約に引っかからないはず。 でもUpdateって内部の更新順制御できるの？？？ そこで公式ドキュメント https://dev.mysql.com/doc/refman/5.6/ja/update.html
10. じゃあどうするか？ 今回やりたいことはsequential_idのインクリメント。 ということはsequential_idの大きいものから順番に更新していけばユニーク 制約に引っかからないはず。 でもUpdateって内部の更新順制御できるの？？？ そこで公式ドキュメント https://dev.mysql.com/doc/refman/5.6/ja/update.html ORDER BYあるやん！！！！ UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ... [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]
11. シーケンシャルなデータを更新しようとした ORDER BY sequential_id DESCを付けることで無事Update成功 UPDATE hoges SET sequential_id = sequential_id + 1 WHERE parent_id = 100 ABD sequential_id >= 2 ORDER BY sequential_id DESC; id parent_id sequential_id 1 100 1 2 100 3 3 100 4
12. シーケンシャルなデータを更新しようとした sequential_id: 2があいたのであとはINSERTすればOK INSERT INTO hoges (parent_id, sequential_id) VALUES (100, 2); 無事、id:4のレコードの追加完了！！ id parent_id sequential_id 1 100 1 4 100 2 2 100 3 3 100 4