現在、サービスを作っており、それぞれのレコードの順番が大切なのですが途中に新しいレコードが入る事が想定され、データベース設計が思いつきません。
id | text | created |
---|---|---|
1 | こんにちは | 2016/12/11 |
2 | 私の名前は太郎です | 2016/12/12 |
3 | どうぞよろしくお願いします。 | 2016/12/13 |
このようなテーブルがあった時に、
id | text | created |
---|---|---|
1 | こんにちは | 2016/12/11 |
と、
id | text | created |
---|---|---|
2 | 私の名前は太郎です | 2016/12/12 |
の間に新たにレコードを挿入したい時、どのように設計、ソートすれば良いでしょうか・・・
id | text | created | sort |
---|---|---|---|
1 | こんにちは | 2016/12/11 | 1 |
4 | はじめまして | 2016/12/13 | 2 |
2 | 私の名前は太郎です | 2016/12/12 | 3 |
3 | どうぞよろしくお願いします。 | 2016/12/13 | 4 |
こんな事も考えましたが1つレコードの更新があるたびに結局は全レコードのsortを更新しなければなりません。
id | text | created | sort |
---|---|---|---|
1 | こんにちは | 2016/12/11 | 1 |
4 | はじめまして | 2016/12/13 | 1.5 |
6 | それにしても、 | 2016/12/14 | 1.625 |
5 | 今日は寒いですね | 2016/12/14 | 1.75 |
2 | 私の名前は太郎です | 2016/12/12 | 2 |
3 | どうぞよろしくお願いします。 | 2016/12/13 | 3 |
※日本語が崩壊しているのは気にしないでください笑
前後のレコードのsort平均値をセットしてソート、現実的そうですが何かとても気持ち悪いです。
何か良いアイデアがあればよろしくお願いします。
気になる質問をクリップする
クリップした質問は、後からいつでもMYページで確認できます。
またクリップした質問に回答があった際、通知やメールを受け取ることができます。
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。

回答3件
0
1つレコードの更新があるたびに結局は全レコードのsortを更新しなければなりません。
RDBMSというものの本質を考えると、そういう手段しかないと思います。RDBMSは、関係代数や関係論理に基づいた集合を扱うシステムですので、データそのものに「順番付け」はありません。別に管理するしかない、ということになります。
RDBMS以外の体系だと、順番を守って追加削除したい場合、「連結リスト」として保管するというような方法も考えられますが、RDBMSのシステムとは全く相容れません。
投稿2016/12/15 01:44
総合スコア146681
0
ベストアンサー
途中に新しいレコードが入る
頻度やレコード数にもよりますが、以下のようにすれば、比較的シンプルに実装できると思います。
sql
1mysql> CREATE TABLE test ( 2 -> id int PRIMARY KEY AUTO_INCREMENT, 3 -> text text, 4 -> created DATE, 5 -> sort int NOT NULL UNIQUE KEY 6 -> ); 7Query OK, 0 rows affected (0.05 sec) 8 9mysql> INSERT INTO test (text, created, sort) VALUES 10 -> ('こんにちは', '2016-12-11', 1), 11 -> ('私の名前は太郎です', '2016-12-12', 2), 12 -> ('どうぞよろしくお願いします。', '2016-12-13', 3); 13Query OK, 3 rows affected (0.04 sec) 14Records: 3 Duplicates: 0 Warnings: 0 15 16mysql> SELECT * FROM test ORDER BY sort; 17+----+--------------------------------------------+------------+------+ 18| id | text | created | sort | 19+----+--------------------------------------------+------------+------+ 20| 1 | こんにちは | 2016-12-11 | 1 | 21| 2 | 私の名前は太郎です | 2016-12-12 | 2 | 22| 3 | どうぞよろしくお願いします。 | 2016-12-13 | 3 | 23+----+--------------------------------------------+------------+------+ 243 rows in set (0.00 sec) 25 26 27# ココからが「新たにレコードを挿入」する処理 28mysql> BEGIN; 29Query OK, 0 rows affected (0.00 sec) 30 31## 挿入したい箇所の"sort"値を取得。 32## データの不整合を防ぐため、SELECT ... FOR UPDATE 構文を使用する。 33## https://dev.mysql.com/doc/refman/5.6/ja/innodb-locking-reads.html 34mysql> SELECT sort FROM test WHERE text = '私の名前は太郎です' FOR UPDATE; 35+------+ 36| sort | 37+------+ 38| 2 | 39+------+ 401 row in set (0.01 sec) 41 42mysql> UPDATE test SET sort = sort + 1 WHERE sort >= 2 ORDER BY sort DESC; 43Query OK, 2 rows affected (0.02 sec) 44Rows matched: 2 Changed: 2 Warnings: 0 45 46mysql> INSERT INTO test (text, created, sort) VALUES 47 -> ('はじめまして', '2016-12-13', 2); 48Query OK, 1 row affected (0.00 sec) 49 50mysql> COMMIT; 51Query OK, 0 rows affected (0.00 sec) 52 53mysql> SELECT * FROM test ORDER BY sort; 54+----+--------------------------------------------+------------+------+ 55| id | text | created | sort | 56+----+--------------------------------------------+------------+------+ 57| 1 | こんにちは | 2016-12-11 | 1 | 58| 4 | はじめまして | 2016-12-13 | 2 | 59| 2 | 私の名前は太郎です | 2016-12-12 | 3 | 60| 3 | どうぞよろしくお願いします。 | 2016-12-13 | 4 | 61+----+--------------------------------------------+------------+------+ 624 rows in set (0.00 sec)
sort
カラムに(ユニーク)インデックスを張っておけば、ソートによって処理が重くなるようなことも無いはずです。
https://dev.mysql.com/doc/refman/5.6/ja/order-by-optimization.html
また、MySQL をお使いであれば、maisumakun様の回答にある通り
「連結リスト」として保管する
方法は難易度が高いと思われます。
連結リストは「再帰クエリ」(recursive query)と呼ぶ機能があると簡単に実現できますが、
MySQL は再帰クエリを実装しておらず、ストアドプロシージャを使用する必要があるらしいからです。
https://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL
Though MySQL does not support common table expressions there exists a demonstration of WITH RECURSIVE emulation[17] using stored procedures.
以下追記
「obj」カラムと「sort」カラム、この2つを合わせてユニークキーになる
であれば、そのようにユニークインデックスを作成してやればよいです。
で、SELECT, UPDATE, INSERT する際にも obj カラムの値を指定してやれば、単一テーブルで実現できます。
sql
1mysql> CREATE TABLE test ( 2 -> id int PRIMARY KEY AUTO_INCREMENT, 3 -> text text, 4 -> created DATE, 5 -> obj int NOT NULL, 6 -> sort int NOT NULL, 7 -> UNIQUE KEY (obj, sort) 8 -> ); 9Query OK, 0 rows affected (0.04 sec) 10 11mysql> INSERT INTO test (text, created, obj, sort) VALUES 12 -> ('こんにちは', '2016-12-11', 1, 1), 13 -> ('私の名前は太郎です', '2016-12-12', 1, 2), 14 -> ('どうぞよろしくお願いします。', '2016-12-13', 1, 3), 15 -> ('こんばんは', '2016-12-11', 2, 1), 16 -> ('私の名前は花子です', '2016-12-12', 2, 2), 17 -> ('どうぞよろしくお願いします。', '2016-12-13', 2, 3); 18Query OK, 6 rows affected (0.01 sec) 19Records: 6 Duplicates: 0 Warnings: 0 20 21mysql> SELECT * FROM test ORDER BY obj, sort; 22+----+--------------------------------------------+------------+-----+------+ 23| id | text | created | obj | sort | 24+----+--------------------------------------------+------------+-----+------+ 25| 1 | こんにちは | 2016-12-11 | 1 | 1 | 26| 2 | 私の名前は太郎です | 2016-12-12 | 1 | 2 | 27| 3 | どうぞよろしくお願いします。 | 2016-12-13 | 1 | 3 | 28| 4 | こんばんは | 2016-12-11 | 2 | 1 | 29| 5 | 私の名前は花子です | 2016-12-12 | 2 | 2 | 30| 6 | どうぞよろしくお願いします。 | 2016-12-13 | 2 | 3 | 31+----+--------------------------------------------+------------+-----+------+ 326 rows in set (0.00 sec) 33 34mysql> BEGIN; 35Query OK, 0 rows affected (0.00 sec) 36 37mysql> SELECT sort FROM test WHERE obj = 1 AND text = '私の名前は太郎です' FOR UPDATE; 38+------+ 39| sort | 40+------+ 41| 2 | 42+------+ 431 row in set (0.01 sec) 44 45mysql> UPDATE test SET sort = sort + 1 WHERE obj = 1 AND sort >= 2 ORDER BY sort DESC; 46Query OK, 2 rows affected (0.02 sec) 47Rows matched: 2 Changed: 2 Warnings: 0 48 49mysql> INSERT INTO test (text, created, obj, sort) VALUES 50 -> ('はじめまして', '2016-12-13', 1, 2); 51Query OK, 1 row affected (0.00 sec) 52 53mysql> COMMIT; 54Query OK, 0 rows affected (0.01 sec) 55 56mysql> SELECT * FROM test ORDER BY obj, sort; 57+----+--------------------------------------------+------------+-----+------+ 58| id | text | created | obj | sort | 59+----+--------------------------------------------+------------+-----+------+ 60| 1 | こんにちは | 2016-12-11 | 1 | 1 | 61| 7 | はじめまして | 2016-12-13 | 1 | 2 | 62| 2 | 私の名前は太郎です | 2016-12-12 | 1 | 3 | 63| 3 | どうぞよろしくお願いします。 | 2016-12-13 | 1 | 4 | 64| 4 | こんばんは | 2016-12-11 | 2 | 1 | 65| 5 | 私の名前は花子です | 2016-12-12 | 2 | 2 | 66| 6 | どうぞよろしくお願いします。 | 2016-12-13 | 2 | 3 | 67+----+--------------------------------------------+------------+-----+------+ 687 rows in set (0.00 sec)
投稿2016/12/15 05:57
編集2016/12/15 09:16総合スコア4791
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。

あなたの回答
tips
太字
斜体
打ち消し線
見出し
引用テキストの挿入
コードの挿入
リンクの挿入
リストの挿入
番号リストの挿入
表の挿入
水平線の挿入
プレビュー
質問の解決につながる回答をしましょう。 サンプルコードなど、より具体的な説明があると質問者の理解の助けになります。 また、読む側のことを考えた、分かりやすい文章を心がけましょう。
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2016/12/15 01:59