質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.01%

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

解決済

回答 3

投稿 編集

  • 評価
  • クリップ 1
  • VIEW 1,174

fmstyle28

score 9

前提・実現したいこと

データ一覧の画面上に入力欄を作り、更新ボタン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

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 3

checkベストアンサー

+4

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

更新の流れの例

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

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


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

一旦負の値にしてからの例

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/05/17 18:56

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

    キャンセル

  • 2017/05/17 18:58

    それ、楽そうですね。

    キャンセル

  • 2017/05/17 19:01 編集

    ですね。

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

    キャンセル

  • 2017/05/22 10:02

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

    キャンセル

+2

もし、priorityNOT 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;

でいけそうですけど。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

+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;

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/05/22 10:01

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

    キャンセル

  • 2017/05/22 10:35

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

    キャンセル

  • 2017/05/22 10:55

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

    キャンセル

  • 2017/05/22 10:58

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.01%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる