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

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

ham
May 18, 2021

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

ham

May 18, 2021
Tweet

More Decks by ham

Other Decks in Technology

Transcript

  1. MySQLのUPDATE文に
    ORDERが指定できると知った件
    2021/5/18 Dev MTG
    ham

    View full-size slide

  2. 何をやろうとしていたのか

    View full-size slide

  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内で連番

    View full-size slide

  4. シーケンシャルなデータを更新しようとした
    下記のデータがある場合、途中にレコードを追加したい。
    id parent_id sequential_id
    1 100 1
    2 100 2
    3 100 3
    1と2の間に新しいレコードを入れたい

    View full-size slide

  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増える

    View full-size slide

  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をず
    らす。

    View full-size slide

  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をず
    らす。
    ユニーク制約違反のエラーが発生・・・!!

    View full-size slide

  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のユニーク制約)と衝
    突したと考えられる。

    View full-size slide

  9. じゃあどうするか?
    今回やりたいことはsequential_idのインクリメント。
    ということはsequential_idの大きいものから順番に更新していけばユニーク
    制約に引っかからないはず。
    でもUpdateって内部の更新順制御できるの???
    そこで公式ドキュメント
    https://dev.mysql.com/doc/refman/5.6/ja/update.html

    View full-size slide

  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]

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide