表題に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ベストアンサー
- 回答日時:
並び順が替わるというのは、別のtableなどからのデータで、変更するのですよね?、1行2行の入れ替えという物ではなさそうなので、構成を練り直した方が良いようにも見受けるけど、毎回集計しないで、一回集計した順で通常のアクセス時の表示を速くしようとしてるのかな。
であれば、order順定義用カラムは、別table に分けた方がいいのでは?
id と orderだけのtableにしてorder をauto_incrementとしておけば、truncate したあとに、一気に、並べ替えたい順でidだけ流し込めば、orderは、自動的に1から振り直される。
この方がorderとid 以外のデータにはさわらないので、大事なデータが失われる可能性もなくなり、作業時間も短縮するのでは?
通常表示の部分は、元table と id でjoin して表示順をコントロールすることにはなるけど。
別テーブルという方法も検討しましたが、auto_incrementを利用して並び順を振りなおすという発想はありませんでした。
たしかにこの方法であれば、並び順以外のデータを失う危険性もなく高速に処理でき、作業時間も短縮できますね。
joinもO/Rマッパー使えば一発ですし。
とはいえもう実装してしまいました^^;
結局、トランザクション内で並び替え対象のレコードを削除して挿入しなおすことにしました。
ただ、扱うデータが大きい場合は処理に時間がかかりそうなのであまりよい方法ではないかも知れません。
総合的にみるとmpro-gramさんの方法が一番いいですね。
ありがとうございました。
No.2
- 回答日時:
案1
夜間バッチ処理で全レコード処理するのなら、
テーブルのバックアップを取って
いったんUNIQUE制約をはずして、
Updateして
UNIQUE制約を付ける
(UNIQUE制約を付けるのに失敗したらバックアップを戻す
・・・Updateの処理でUniqueになっている保証ができるなら、
バックアップも戻す処理も不要)
が一番手っ取り早いと思いますが。
案2
並び順が少数部を持っている(または持つように変更できる)なら
1⇒2.1
2⇒1.1
と.1を付けた値にUpdateして
再度、
update文1個で、.1をとる( update table1 set 並び順= int(並び順))
という方法もあります。(全件一度に処理します)
※処理前の状態で、小数部が入っていないことが条件となりますが。
案3
並び順をどういう方法で入れ替えようとしているのかにもよりますが、
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と同じように帰結しますね。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・ゆるやかでぃべーと タイムマシンを破壊すべきか。
- ・「I love you」 をかっこよく翻訳してみてください
- ・歩いた自慢大会
- ・許せない心理テスト
- ・字面がカッコいい英単語
- ・昔のあなたへのアドバイス
- ・かっこよく答えてください!!
- ・あなたが好きな本屋さんを教えてください
- ・スタッフと宿泊客が全員斜め上を行くホテルのレビュー
- ・【大喜利】【投稿~8/27】 こんなガソリンスタンド二度と来るか!なぜそう思った?
- ・これ何て呼びますか Part2
- ・人生で一番思い出に残ってる靴
- ・【お題】動物のキャッチフレーズ
- ・【お題】甲子園での思い出の残し方
- ・ゆるやかでぃべーと すべての高校生はアルバイトをするべきだ。
- ・「それ、メッセージ花火でわざわざ伝えること?」
- ・自分用のお土産
- ・人生で一番お金がなかったとき
- ・一番好きなみそ汁の具材は?
- ・泣きながら食べたご飯の思い出
- ・ちょっと先の未来クイズ第1問
- ・ゴリラ向け動画サイト「ウホウホ動画」にありがちなこと
- ・初めて自分の家と他人の家が違う、と意識した時
- ・単二電池
- ・チョコミントアイス
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
VIEWの元のテーブルのindexって...
-
エクセルで最後の文字だけ置き...
-
エクセルの関数について教えて...
-
select文のwhere句に配列を入れ...
-
エラー 1068 (42000): 複数の主...
-
SQLにて特定の文字を除いた検索...
-
SQLサーバから、項目の属性(型...
-
最小値をUPDATE
-
副問合せの書き方について
-
WHERE id = ? について
-
単純なクエリーなのにSELECTし...
-
期間の重複を調べるSQL文につい...
-
WordpressのContact form 7でzi...
-
updateを1行ずつ実行したい。
-
Updateの複数テーブル条件時のL...
-
ある条件の最大値+1を初番する...
-
Unionした最後にGROUP BYを追加...
-
selectした大量データをinsert...
-
accessのチェックボックスをSQL...
-
SQL Left Join で重複を排除す...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルで最後の文字だけ置き...
-
エクセルの関数について教えて...
-
SQLサーバから、項目の属性(型...
-
副問合せの書き方について
-
select文のwhere句に配列を入れ...
-
SQL Left Join で重複を排除す...
-
VIEWの元のテーブルのindexって...
-
SQLにて特定の文字を除いた検索...
-
マイクラPC版のコマンドで効率...
-
sqlで、600行あるテーブルを100...
-
クエリ表示と、ADOで抽出したレ...
-
PL/SQLの変数について
-
Unionした最後にGROUP BYを追加...
-
エラー 1068 (42000): 複数の主...
-
バインド変数について
-
[MySQL] 3つのテーブルの結合で...
-
Access パラメータクエリをcsv...
-
LEFT JOIN と GROUP BY
-
php+mysqlで複数選択削除について
-
insertを高速化させたい
おすすめ情報