質問編集履歴
2
誤字を訂正しました
test
CHANGED
|
File without changes
|
test
CHANGED
|
@@ -8,7 +8,7 @@
|
|
|
8
8
|
`?tags=tagUuid1-6,tagUuid2+4`
|
|
9
9
|
`&authors=userUuid1,userUuid2`
|
|
10
10
|
`&low=100`
|
|
11
|
-
`&h
|
|
11
|
+
`&high=1200`
|
|
12
12
|
|
|
13
13
|
この保存において、~~重複を回避するためのユニーク制約をどうかけたらいいか~~重複を回避するためにはどうしたらいいかというのが悩みです。
|
|
14
14
|
|
|
@@ -49,7 +49,7 @@
|
|
|
49
49
|
CREATE TABLE search_conditions (
|
|
50
50
|
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
51
51
|
low INT UNSIGNED NOT NULL,
|
|
52
|
-
h
|
|
52
|
+
high INT UNSIGNED NOT NULL,
|
|
53
53
|
item_kind_ids VARCHAR(100), -- '1,2' のようにアイテム種別IDが入り、NULL なら全アイテムを取得
|
|
54
54
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
55
55
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
@@ -110,7 +110,7 @@
|
|
|
110
110
|
('tagUuid2', 'Tag2');
|
|
111
111
|
|
|
112
112
|
-- search_conditions テーブルへのデータ挿入
|
|
113
|
-
INSERT INTO search_conditions (low, h
|
|
113
|
+
INSERT INTO search_conditions (low, high, item_kind_ids) VALUES
|
|
114
114
|
(100, 1200, '1,2'),
|
|
115
115
|
(40, 8000, NULL);
|
|
116
116
|
|
|
@@ -140,7 +140,7 @@
|
|
|
140
140
|
-- --------------------------------------------------------
|
|
141
141
|
|
|
142
142
|
-- search_condition=1 と同じ条件で新たに search_condition=3 をINSERT
|
|
143
|
-
INSERT INTO search_conditions (low, h
|
|
143
|
+
INSERT INTO search_conditions (low, high, item_kind_ids) VALUES
|
|
144
144
|
(100, 1200, '1,2');
|
|
145
145
|
|
|
146
146
|
INSERT INTO search_condition_authors (search_condition_id, user_id) VALUES
|
|
@@ -160,7 +160,7 @@
|
|
|
160
160
|
SELECT
|
|
161
161
|
sc.id,
|
|
162
162
|
sc.low,
|
|
163
|
-
sc.h
|
|
163
|
+
sc.high,
|
|
164
164
|
GROUP_CONCAT(u.uuid) AS authors,
|
|
165
165
|
(SELECT JSON_ARRAYAGG(JSON_OBJECT(
|
|
166
166
|
'id', t.id,
|
|
@@ -179,7 +179,7 @@
|
|
|
179
179
|
LEFT JOIN users u ON u.id = sca.user_id
|
|
180
180
|
WHERE
|
|
181
181
|
-- ランク条件
|
|
182
|
-
(sc.low = 100 AND sc.h
|
|
182
|
+
(sc.low = 100 AND sc.high = 1200)
|
|
183
183
|
-- アイテム種別条件
|
|
184
184
|
AND (sc.item_kind_ids = '1,2')
|
|
185
185
|
-- ユーザー条件
|
|
@@ -217,7 +217,7 @@
|
|
|
217
217
|
CREATE TABLE search_conditions (
|
|
218
218
|
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
219
219
|
low INT UNSIGNED NOT NULL,
|
|
220
|
-
h
|
|
220
|
+
high INT UNSIGNED NOT NULL,
|
|
221
221
|
item_kind_ids VARCHAR(100), -- '1,2' のようにアイテム種別IDが入り、NULL なら全アイテムを取得
|
|
222
222
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
223
223
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
1
対策の➁について補足をし、質問内容を明確に修正いたしました
test
CHANGED
|
File without changes
|
test
CHANGED
|
@@ -1,5 +1,6 @@
|
|
|
1
1
|
### 実現したいこと
|
|
2
|
-
複数テーブルにまたがってユニーク制約をかける方法を知りたいです。
|
|
2
|
+
~~複数テーブルにまたがってユニーク制約をかける方法を知りたいです。~~
|
|
3
|
+
より具体的には、以下の検索条件（クエリストリング）を重複せずに保存したいです。
|
|
3
4
|
|
|
4
5
|
### 前提
|
|
5
6
|
ブラウザから`item/search`を呼び、これにアイテムの検索条件として次のようなクエリストリングが続き、クエリストリングを「この検索条件を保存する」で保存できるという仕組みを考えています。
|
|
@@ -9,10 +10,10 @@
|
|
|
9
10
|
`&low=100`
|
|
10
11
|
`&height=1200`
|
|
11
12
|
|
|
12
|
-
この保存において、重複を回避するためのユニーク制約をどうかけたらいいかというのが悩みです。
|
|
13
|
+
この保存において、~~重複を回避するためのユニーク制約をどうかけたらいいか~~重複を回避するためにはどうしたらいいかというのが悩みです。
|
|
13
14
|
|
|
14
15
|
### 発生している問題
|
|
15
|
-
以下のように`CREATE`しているのですが、
|
|
16
|
+
以下のように`CREATE`しているのですが、MySQL上では重複した検索条件が保存できてしまいます。
|
|
16
17
|
|
|
17
18
|
### 該当のソースコード
|
|
18
19
|
こちらが`CREATE`文です。
|
|
@@ -92,7 +93,7 @@
|
|
|
92
93
|
);
|
|
93
94
|
```
|
|
94
95
|
|
|
95
|
-
テストデータの`INSERT`文です。冒頭のクエリストリングはここに入っており、
|
|
96
|
+
テストデータの`INSERT`文です。冒頭の検索条件（クエリストリング）はここに入っており、ユーザーに「保存中の検索条件」として表示するつもりです。
|
|
96
97
|
```SQL
|
|
97
98
|
-- --------------------------------------------------------
|
|
98
99
|
-- テストデータのINSERT文
|
|
@@ -190,7 +191,7 @@
|
|
|
190
191
|
https://www.db-fiddle.com/f/2q8oVpCSBRvnF8QCq9ezuj/0
|
|
191
192
|
|
|
192
193
|
### 自分で考えられる対策
|
|
193
|
-
対策は
|
|
194
|
+
対策は３つしか思い浮かばず、どれもパっとしません…。
|
|
194
195
|
|
|
195
196
|
① `search_conditions` テーブルにクエリストリングをまるごと保存するカラムを設け、そこにユニーク制約をかける対策
|
|
196
197
|
→ 各テーブルを見ればわかる情報なのに、ユニーク性担保のために重複した情報を保存することになります…。
|
|
@@ -207,3 +208,32 @@
|
|
|
207
208
|
|
|
208
209
|
良い方法がございましたらご教示頂けませんでしょうか…。
|
|
209
210
|
よろしくお願い致します。
|
|
211
|
+
|
|
212
|
+
### 対策の➁について補足
|
|
213
|
+
|
|
214
|
+
対策➁は下記のように`-- カラム追加`し、`search_conditions`テーブル に検索条件（クエリストリング）の全てを横に並べることができれば、そこにユニーク制約をかけることができるという案です。( `search_condition_tag_sets` と `search_condition_authors` テーブルをやめ、すべて`search_conditions ` テーブルのカラムに収めています。)
|
|
215
|
+
|
|
216
|
+
```SQL
|
|
217
|
+
CREATE TABLE search_conditions (
|
|
218
|
+
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
219
|
+
low INT UNSIGNED NOT NULL,
|
|
220
|
+
height INT UNSIGNED NOT NULL,
|
|
221
|
+
item_kind_ids VARCHAR(100), -- '1,2' のようにアイテム種別IDが入り、NULL なら全アイテムを取得
|
|
222
|
+
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
223
|
+
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
224
|
+
|
|
225
|
+
-- カラム追加
|
|
226
|
+
author_id1 INT UNSIGNED NOT NULL,
|
|
227
|
+
author_id2 INT UNSIGNED NOT NULL,
|
|
228
|
+
author_id3 INT UNSIGNED NOT NULL,
|
|
229
|
+
tag_set_id1 INT UNSIGNED NOT NULL,
|
|
230
|
+
tag_set_id2 INT UNSIGNED NOT NULL,
|
|
231
|
+
tag_set_id3 INT UNSIGNED NOT NULL,
|
|
232
|
+
);
|
|
233
|
+
```
|
|
234
|
+
|
|
235
|
+
しかし `author_id` と `tag_set_id` が３つずつ入るように備えている反面、もし2つしか指定がない場合に余ったカラムに`NULL`が入ってしまうことになり、やはりユニーク制約が不十分です。そのため
|
|
236
|
+
|
|
237
|
+
> タグ数も編者数も不定数なので `NULL` カラムが発生し、ユニーク制約をあきらめることになります…。または `NULL` でなく `0` を入れてユニーク制約をかける代わりに、外部キー制約をあきらめることになります…。
|
|
238
|
+
|
|
239
|
+
という考えに至った次第です。
|