PHPでUNIQUEなカラムを含むMySQLのデータを一括更新したい

Question

###前提・実現したいこと
データ一覧の画面上に入力欄を作り、更新ボタン1つで全部のMySQL (MariaDB) 上のデータが更新されるようなページを作りたいと思っていますが、UNIQUEなカラムを含む場合の処理で悩んでいます。

MySQLのテーブル vegetable を以下のように設定しています。priorityはUNIQUEにし、重複がないようにしています。

|Column name| |
|:--|:--:|--:|
|id|Primary Key|
|name||
|priority|Unique|

ユーザーに、以下のようなUIで、野菜の優先順位を変えてもらえるようにしたいです。

＜当初のデータ＞

＜入力後・更新ボタン押す前のデータ＞

「更新」ボタンを押すと、PHPがPOSTデータを読み取って、以下のSQL文を実行し、野菜の優先順位が更新されるようにしたいです。

UPDATE `vegetable` SET `priority` = 1 WHERE `id` = 1
UPDATE `vegetable` SET `priority` = 3 WHERE `id` = 2
UPDATE `vegetable` SET `priority` = 2 WHERE `id` = 3

###発生している問題
しかし、当然ですが、上のSQL文の2行目を実行する時点で、priority=3がきゃべつとたまねぎとで重複するため、2行目の実行はエラーとなってしまいます。

このように、UNIQUEカラムのデータを一括で更新したい場合はどのように
実装するのがよいでしょうか？

最近PHPやMySQLの勉強を始めたため、基本的な部分で理解が足らないかもしれません。ご教授ください。

###環境
PHP: 5.6.16。
mysqli を使用して接続
DB: MariaDB 5.5.52

Answer 1

priorityをunique制約をつけてしまったがためにハマるネタですね。
更新処理を一括で行って途切れなくやるためには、
事前に勝手にpriorityを仮の値で振り直せば良いのではないでしょうか。

仮の値の考え方として、例えば
priorityの最大値をselect文で取得し、
フォーム入力されたpriorityパラメータの最大値とどちらが大きいか、
大きい方の値を元に個々のpriorityの値に足しこんでupdate文しておきます。
そのあと、フォーム入力されたpriority値を個々に適切にupdate文する流れです。

もちろん、一連の流れはトランザクション処理の中で行います。

---

コメントいただいた方法だと、
事前の値の振り直し基準値を求める手間が省けるし、
仮の値を振るUPDATE文が1回で済んで速いというのもあります。

Comments

[KiyoshiMotoki]

priority の取り得る値が 0より大きく、かつ カラムの型が SIGNED の数値型であれば、
　UPDATE `vegetable` SET `priority` = - `priority`
で、いったん全てのレコードの priority を負値に更新する、という手でも実現できそうですね。

[退会済みユーザー]

それ、楽そうですね。

[KiyoshiMotoki]

ですね。

実は私のアイデアではありませんがw
http://stackoverflow.com/a/4798420/6299234

[fmstyle28]

一度負の値にするというのは分かりやすいので取り入れてみたいと思います。ありがとうございました。

Answer 2

ちょっと問題があるかもしれませんがフラグを合わせて処理するとか

create table tbl(id int unique,data int,flg tinyint not null,unique(data,flg));
insert into tbl(id,data) values(1,1),(2,2),(3,3);

※dataは単体でuniqueなのではなくflgとの組み合わせでユニークとする
flgは基本値=0

insert into tbl(id,data) values(1,3),(2,2),(3,1) on duplicate key update data=values(data),flg=1;

データ更新後flgを初期化しておきます

update tbl set flg=0;

以下のように競合するデータを投入する場合、transactionが有効ならrollbackされる

insert into tbl(id,data) values(1,3),(2,2),(3,3) on duplicate key update data=values(data),flg=1;

Comments

[fmstyle28]

ありがとうございます。単体ではなく組み合わせをユニークにする方法もあるんですね。勉強になります。

[退会済みユーザー]

個人的には、これが1番きれいな気がします。
yambejp さん、どのへんに「ちょっと問題がある」のでしょうか？

[yambejp]

te2jiさん、確認ありがとうございます
わたしのテストした範囲ではトランザクションをきちんとしないと
想定外の動きをする場合ありました
よほど頻繁に同時処理が発生しなければ大丈夫だと思うんですけど

[退会済みユーザー]

理解しました。
ありがとうございました！

Answer 3

もし、priorityがNOT NULLでなければ、

UPDATE `vegetable` SET `priority` = NULL WHERE `id` = 1;
UPDATE `vegetable` SET `priority` = NULL WHERE `id` = 2;
UPDATE `vegetable` SET `priority` = NULL WHERE `id` = 3;
UPDATE `vegetable` SET `priority` = 1 WHERE `id` = 1;
UPDATE `vegetable` SET `priority` = 3 WHERE `id` = 2;
UPDATE `vegetable` SET `priority` = 2 WHERE `id` = 3;
```でいけそうですけど。