[MySQL] UNIQUE制約の値を更新する方法

表題にUNIQUE制約の値を「更新」する方法とありますが、
正確にはUNIQUE制約が設定された値を「入れ替える」方法について質問します。

あるテーブルに order という並び順（数値）を格納するカラムがあるとします。※このテーブルのプライマリーキーは id とします。

並び順ですので一意な値です。よって、order にUNIQUE制約を設定しています。
例えば 1～5 個のデータ（フィールド）があったとして、並び順も 1～5 あるわけですが、この並び順を入れ替える場合、並び順を格納しているカラム（ order ）にUNIQUE制約があるため、それぞれ UPDATE で上書きすることができません。
つまり、{ id: 1, order: 1 } というデータと { id: 2, order: 2 } というデータの並び順（ order ）を { id: 1, order: 2 } と { id: 2, order: 1 } というように入れ替えようとする場合、並び順（ order ）は一意であるため、UPDATE で上書きができないのです。

できればトランザクション内で処理したいのですが、これらの処理をスマートに更新する方法はないでしょうか。それとも、並び順（ order ）にUNIQUE制約を設定せず、アプリケーション側で並び順（ order ）を一意に保つよう実装するしかないでしょうか。

No.3 ベストアンサー 回答者： mpro-gram 回答日時： 2013/11/24 01:46

並び順が替わるというのは、別のtableなどからのデータで、変更するのですよね？、１行２行の入れ替えという物ではなさそうなので、構成を練り直した方が良いようにも見受けるけど、毎回集計しないで、一回集計した順で通常のアクセス時の表示を速くしようとしてるのかな。

であれば、order順定義用カラムは、別table に分けた方がいいのでは？
id と orderだけのtableにしてorder をauto_incrementとしておけば、truncate したあとに、一気に、並べ替えたい順でidだけ流し込めば、orderは、自動的に1から振り直される。
この方がorderとid 以外のデータにはさわらないので、大事なデータが失われる可能性もなくなり、作業時間も短縮するのでは？
通常表示の部分は、元table と id でjoin して表示順をコントロールすることにはなるけど。

この回答へのお礼

別テーブルという方法も検討しましたが、auto_incrementを利用して並び順を振りなおすという発想はありませんでした。
たしかにこの方法であれば、並び順以外のデータを失う危険性もなく高速に処理でき、作業時間も短縮できますね。
joinもO/Rマッパー使えば一発ですし。

とはいえもう実装してしまいました^^;
結局、トランザクション内で並び替え対象のレコードを削除して挿入しなおすことにしました。
ただ、扱うデータが大きい場合は処理に時間がかかりそうなのであまりよい方法ではないかも知れません。

総合的にみるとmpro-gramさんの方法が一番いいですね。
ありがとうございました。

お礼日時：2013/11/25 04:18

No.2 回答者： Siegrune 回答日時： 2013/11/24 00:05

案１

夜間バッチ処理で全レコード処理するのなら、
テーブルのバックアップを取って
いったんUNIQUE制約をはずして、
Updateして
UNIQUE制約を付ける
（UNIQUE制約を付けるのに失敗したらバックアップを戻す
　・・・Updateの処理でUniqueになっている保証ができるなら、
　バックアップも戻す処理も不要）

が一番手っ取り早いと思いますが。

案２
並び順が少数部を持っている（または持つように変更できる）なら
1⇒2.1
2⇒1.1
と.1を付けた値にUpdateして
再度、
update文１個で、.1をとる（ update table1 set 並び順= int(並び順)）
という方法もあります。（全件一度に処理します）
※処理前の状態で、小数部が入っていないことが条件となりますが。

案３
並び順をどういう方法で入れ替えようとしているのかにもよりますが、
max(並び順)を求めてその次の番号が最小となるようにして連番を振れば問題ないと思いますが。
1⇒3
2⇒4
※1から順番にしたい理由があれば、
再度Updateで、
update table1 set 並び順= 並び順 - 処理前のmax(並び順)の値
とすれば、1からに戻ります。（全件一度に処理します）
3⇒3 - 2 = 1
4⇒4 - 2 = 2
となりますし重複しないですから。
## 単に並び順というだけなら、別に1から始まらなくてもいい気がしますが、
## 1からに戻してもいいということで。

この回答へのお礼

貴重なご意見ありがとうございます。

案1のいったんUNIQUE制約を外す方法も検討しましたが、やはりその場合もアプリケーション側で整合性を保たなければいけませんね。

案2のいったん別の値に書き換えるという方法はいいですね。
その場合、いったん並び順の最後の位置にして入れ替えれば型も整数のままですみますし、操作も4つですみます。

案3も案2と同じように帰結しますね。

お礼日時：2013/11/25 03:01

No.1 回答者： bin-chan 回答日時： 2013/11/23 21:33

ユニークキー列を除く列を入れ替えてはダメですか？

私ならたぶん、
テンポラリテーブルへ吐き出しておいて、
元データをDeleteし
テンポラリテーブルからInsertする

この回答へのお礼

テンポラリーテーブルですか。
興味深いご意見ありがとうございます。
こちらであればオーバーヘッドも少なく済みそうですね。
検討してみます、ありがとうございます。

お礼日時：2013/11/23 22:56