Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

9cfde65b598c5e2fe8578307bfbad682?s=47 ham
May 18, 2021

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

9cfde65b598c5e2fe8578307bfbad682?s=128

ham

May 18, 2021
Tweet

More Decks by ham

Other Decks in Technology

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