DB設計 順番を持ったデータの順序を更新する際の処理方法について

Question

任意の順番をもったデータがあり、後からそのデータの順番を変えた場合にどのようにデータを更新すれば良いのか知恵をお貸しください。具体的には以下のような例です。

• 初期データが以下とする。

No	名前	年齢
1	John	20
2	Bob	19
3	James	21
4	Jack	20

• No.2のBobとNo.3のJamesの間にNickを追加し、Nickを新たなNo.3としJames以降はひとつずつNo.を増やす。

No	名前	年齢
1	John	20
2	Bob	19
3	Nick	22
4	James	21
5	Jack	20

この場合for loopなどで一つずつJames以降の値を更新する仕組みにしてしまうと、データが大量になった場合に処理量が増えてしまいます。この他にも、例えば並んだデータの順番を入れ替える上下ボタンのようなものを実装したいのですが、それも同じく時間がかかってしまう気がしています。

No	名前	年齢
1	John	20
2	Bob	19
3	Nick	22
4	James	21
5	Jack	20

• BobとNickの入れ替えボタンを押す。

No	名前	年齢
1	John	20
2	Nick	22
3	Bob	19
4	James	21
5	Jack	20

• NickとBobが入れ替わりNoが合わせて入れ替わる。

別途IDを用意して順番を管理するDBを作ることも考えたのですが結局同じことになる気がしていて悩んでおります。よろしくお願いいたします。

Answer 1

実際に実装したことのある方法ですが、ご参考になれば。

テーブル定義・サンプルデータ

CREATE TABLE `sample` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(16) DEFAULT NULL,
  `order_number` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `sample` (`id`, `name`, `order_number`)
VALUES
	(1,'社内文書',1),
	(2,'社外文書',2),
	(3,'社外秘文書',3),
	(4,'重要書類',4),
	(5,'法定文書',5),
	(6,'財務文書',6);

id	name	order_number
1	社内文書	1
2	社外文書	2
3	社外秘文書	3
4	重要書類	4
5	法定文書	5
6	財務文書	6

例1

「法定文書」を現在の表示順「5」から「2」に移動したい場合

UPDATE `sample` SET `order_number` = 2
WHERE `id` = 5;

この時、id=2〜4のレコードは、order_number を＋1すればいい。
移動したレコードの表示順が「5 → 2」に変化することから、

UPDATE `sample` SET `order_number` = `order_number` + 1 
WHERE `order_number` >= 2
AND `order_number` < 5
AND `id` != 5

例2

「社外文書」を現在の表示順「2」から「5」に移動したい場合

UPDATE `sample` SET `order_number` = 5
WHERE `id` = 2;

この時、id=3〜5のレコードは、order_number を-1すればいい。
移動したレコードの表示順が「2 → 5」に変化することから、

UPDATE `sample` SET `order_number` = `order_number` - 1 
WHERE `order_number` > 2
AND `order_number` <= 5
AND `id` != 2

どちらも2つのクエリを実行すれば良いということになります。

Comments

[退会済みユーザー]

すごい！

[TakahiRoyte]

解りやすい回答をありがとうございます。クエリ数が少なければデータが多くても問題なさそうですね！ご回答を応用して他にやりたいこともイメージできたので大きな助けになりました。ありがとうございました。

[TakahiRoyte]

一つ気づいたのは頂いた回答ですと番号がかぶって存在してしまうので、クエリ数は一つ増えてしまいますが、変更予定のデータのorder_numberを一度"0"に設定し、後で希望の番号へ変更する方法で対応できました。

[TakahiRoyte]

サンプルのSQLです。
http://sqlfiddle.com/#!9/ca952/1/0

[退会済みユーザー]

そうなんですよね。夜にでも訂正しようかと、思ってます。

[退会済みユーザー]

削除

[退会済みユーザー]

「order_numberを一度"0"に設定」しなくてもできますよ。

[TakahiRoyte]

重ね重ねありがとうございます。
ロジック考えるのに柔軟な思考は大事ですね。

Answer 2

順番って何が基準の順番なのでしょうか？登録順であれば登録日時で並び替えれば良いのでは？

Comments

[TakahiRoyte]

任意の順番です。後から入れ替えが出来る順番にしたいので、登録日時だと後からデータを追加したい時や順番の前後を入れ替えたい時に整合性が合わなくなってしまいます。かと言って単なる連番だとデータの更新が更新箇所以降に影響があるので悩んでおります。

[退会済みユーザー]

DBとどう関係があるのかよくわからないです。「データの順番を入れ替える上下ボタン」ってビュー側のUIの話ですよね？

[TakahiRoyte]

UIで表示している順番をDB内に保持しておきたいのです。ビュー側で表示している間ビュー内のデータに持たせておいているだけでは、再度そのデータを使ったページを開いた際に順番がもとに戻ってしまうと思っていて、それを回避するためには順番をDB内で保持して表示のタイミングでその順番データが必要になるかと思ったのですが誤ってますでしょうか。