投稿2023/11/17 01:43
編集2023/12/12 08:59複数テーブルにまたがってユニーク制約をかける方法を知りたいです。
より具体的には、以下の検索条件（クエリストリング）を重複せずに保存したいです。
ブラウザからitem/searchを呼び、これにアイテムの検索条件として次のようなクエリストリングが続き、クエリストリングを「この検索条件を保存する」で保存できるという仕組みを考えています。
?tags=tagUuid1-6,tagUuid2+4
&authors=userUuid1,userUuid2
&low=100
&high=1200
この保存において、重複を回避するためのユニーク制約をどうかけたらいいか重複を回避するためにはどうしたらいいかというのが悩みです。
以下のようにCREATEしているのですが、MySQL上では重複した検索条件が保存できてしまいます。
こちらがCREATE文です。
SQL
1-- -------------------------------------------------------- 2-- 検索条件に関係するテーブルのCREATE文 3-- -------------------------------------------------------- 4 5-- 会員のテーブル 6CREATE TABLE users ( 7 id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 8 uuid VARCHAR(36) NOT NULL, 9 nickname VARCHAR(100) NOT NULL, 10 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 11 updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 12 UNIQUE u_users_01 (uuid), 13 UNIQUE u_users_02 (nickname) 14); 15 16-- タグのテーブル 17CREATE TABLE tags ( 18 id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 19 uuid VARCHAR(36) NOT NULL, 20 tag_name VARCHAR(50) NOT NULL, 21 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 22 updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 23 UNIQUE u_tags_01 (uuid), 24 UNIQUE u_tags_02 (tag_name) 25); 26 27-- 検索条件のテーブル 28-- 検索条件 29CREATE TABLE search_conditions ( 30 id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 31 low INT UNSIGNED NOT NULL, 32 high INT UNSIGNED NOT NULL, 33 item_kind_ids VARCHAR(100), -- '1,2' のようにアイテム種別IDが入り、NULL なら全アイテムを取得 34 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 35 updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 36); 37 38-- 検索条件のテーブル 39-- タグセット 40CREATE TABLE tag_sets ( 41 id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 42 tag_id INT UNSIGNED NOT NULL, 43 strength INT NOT NULL DEFAULT 1, 44 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 45 updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 46 UNIQUE u_tag_sets_01 (tag_id, strength), 47 CONSTRAINT fk_tag_sets_01 FOREIGN KEY (tag_id) REFERENCES tags(id) 48); 49 50-- 検索条件のテーブル 51-- 検索条件とタグセットのリレーション 52CREATE TABLE search_condition_tag_sets ( 53 id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 54 search_condition_id INT UNSIGNED NOT NULL, 55 tag_set_id INT UNSIGNED NOT NULL, 56 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 57 updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 58 UNIQUE u_search_condition_tag_sets_01 (search_condition_id, tag_set_id), 59 CONSTRAINT fk_search_condition_tag_sets_01 FOREIGN KEY (search_condition_id) REFERENCES search_conditions(id), 60 CONSTRAINT fk_search_condition_tag_sets_02 FOREIGN KEY (tag_set_id) REFERENCES tag_sets(id) 61); 62 63-- 検索条件のテーブル 64-- 検索条件と編者のリレーション 65CREATE TABLE search_condition_authors ( 66 id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 67 search_condition_id INT UNSIGNED NOT NULL, 68 user_id INT UNSIGNED NOT NULL, 69 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 70 updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 71 UNIQUE u_search_condition_authors_01 (search_condition_id, user_id), 72 CONSTRAINT fk_search_condition_authors_01 FOREIGN KEY (user_id) REFERENCES users(id) 73);
テストデータのINSERT文です。冒頭の検索条件（クエリストリング）はここに入っており、ユーザーに「保存中の検索条件」として表示するつもりです。
SQL
1-- -------------------------------------------------------- 2-- テストデータのINSERT文 3-- -------------------------------------------------------- 4 5-- users テーブルへのデータ挿入 6INSERT INTO users (uuid, nickname) VALUES 7('userUuid1', 'User1'), 8('userUuid2', 'User2'); 9 10-- tags テーブルへのデータ挿入 11INSERT INTO tags (uuid, tag_name) VALUES 12('tagUuid1', 'Tag1'), 13('tagUuid2', 'Tag2'); 14 15-- search_conditions テーブルへのデータ挿入 16INSERT INTO search_conditions (low, high, item_kind_ids) VALUES 17(100, 1200, '1,2'), 18(40, 8000, NULL); 19 20-- tag_sets テーブルへのデータ挿入 21INSERT INTO tag_sets (tag_id, strength) VALUES 22(1, 2), 23(2, 4), 24(2, 6), 25(1, -6); 26 27-- search_condition_tag_sets テーブルへのデータ挿入 28INSERT INTO search_condition_tag_sets (search_condition_id, tag_set_id) VALUES 29(1, 2), 30(1, 4); 31 32-- search_condition_authors テーブルへのデータ挿入 33INSERT INTO search_condition_authors (search_condition_id, user_id) VALUES 34(1, 1), 35(1, 2), 36(2, 1);
しかし、このように新たに同じ条件でINSERTできてしまい、重複してしまうのです。
SQL
1-- -------------------------------------------------------- 2-- 追加のINSERT文 3-- -------------------------------------------------------- 4 5-- search_condition=1 と同じ条件で新たに search_condition=3 をINSERT 6INSERT INTO search_conditions (low, high, item_kind_ids) VALUES 7(100, 1200, '1,2'); 8 9INSERT INTO search_condition_authors (search_condition_id, user_id) VALUES 10(3, 1), 11(3, 2); 12 13INSERT INTO search_condition_tag_sets (search_condition_id, tag_set_id) VALUES 14(3, 2), 15(3, 4);
重複してしまうことはこちらのSELECT文で確認できるかと思います。
SQL
1-- -------------------------------------------------------- 2-- 検索条件のSELECT文 3-- -------------------------------------------------------- 4SELECT 5 sc.id, 6 sc.low, 7 sc.high, 8 GROUP_CONCAT(u.uuid) AS authors, 9 (SELECT JSON_ARRAYAGG(JSON_OBJECT( 10 'id', t.id, 11 'uuid', t.uuid, 12 'tag_name', t.tag_name, 13 'strength', ts.strength 14 )) 15 FROM search_condition_tag_sets scts 16 LEFT JOIN tag_sets ts ON ts.id = scts.tag_set_id 17 LEFT JOIN tags t ON t.id = ts.tag_id 18 WHERE scts.search_condition_id = sc.id 19 ) AS tags 20FROM search_conditions sc 21-- 編者情報の連結 22LEFT JOIN search_condition_authors sca ON sca.search_condition_id = sc.id 23LEFT JOIN users u ON u.id = sca.user_id 24WHERE 25 -- ランク条件 26 (sc.low = 100 AND sc.high = 1200) 27 -- アイテム種別条件 28 AND (sc.item_kind_ids = '1,2') 29 -- ユーザー条件 30 AND u.uuid IN ('userUuid1', 'userUuid2') 31GROUP BY sc.id;
以上、こちらで実行して頂けます。
https://www.db-fiddle.com/f/2q8oVpCSBRvnF8QCq9ezuj/0
対策は３つしか思い浮かばず、どれもパっとしません…。
① search_conditions テーブルにクエリストリングをまるごと保存するカラムを設け、そこにユニーク制約をかける対策
→ 各テーブルを見ればわかる情報なのに、ユニーク性担保のために重複した情報を保存することになります…。
➁ リレーション ( search_condition_tag_setsとsearch_condition_authors ) をやめ、すべて search_condditions テーブルのカラムに収める対策
→ タグ数も編者数も不定数なので NULL カラムが発生し、ユニーク制約をあきらめることになります…。または NULL でなく 0 を入れてユニーク制約をかける代わりに、外部キー制約をあきらめることになります…。
③ MySQLで論理的に重複しうることは許容し、PHPで「GET LOCK>SELECT>INSERT」とすることで現実的に重複を回避する対策
→ データ整合性の確実性が低下します…。
PHP は 8.2 です。
MySQL はやや古めで 5.7 になります。（SQLServer なら➁でNULLを入れてもユニーク制約ができるようですが、MySQL は外せません。）
良い方法がございましたらご教示頂けませんでしょうか…。
よろしくお願い致します。
対策➁は下記のように-- カラム追加し、search_conditionsテーブル に検索条件（クエリストリング）の全てを横に並べることができれば、そこにユニーク制約をかけることができるという案です。( search_condition_tag_sets と search_condition_authors テーブルをやめ、すべてsearch_conditions テーブルのカラムに収めています。)
SQL
1CREATE TABLE search_conditions ( 2 id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 3 low INT UNSIGNED NOT NULL, 4 high INT UNSIGNED NOT NULL, 5 item_kind_ids VARCHAR(100), -- '1,2' のようにアイテム種別IDが入り、NULL なら全アイテムを取得 6 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 7 updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 8 9 -- カラム追加 10 author_id1 INT UNSIGNED NOT NULL, 11 author_id2 INT UNSIGNED NOT NULL, 12 author_id3 INT UNSIGNED NOT NULL, 13 tag_set_id1 INT UNSIGNED NOT NULL, 14 tag_set_id2 INT UNSIGNED NOT NULL, 15 tag_set_id3 INT UNSIGNED NOT NULL, 16);
しかし author_id と tag_set_id が３つずつ入るように備えている反面、もし2つしか指定がない場合に余ったカラムにNULLが入ってしまうことになり、やはりユニーク制約が不十分です。そのため
タグ数も編者数も不定数なので
NULLカラムが発生し、ユニーク制約をあきらめることになります…。またはNULLでなく0を入れてユニーク制約をかける代わりに、外部キー制約をあきらめることになります…。
という考えに至った次第です。
回答3件
0
冗長な質問で要領を得ないのですが、同じデータがあるならユニークではないのは必然では？
などが有効だと思います
投稿2023/11/17 01:58
総合スコア117618
0
ベストアンサー
正直主旨が良く解らないけれど...
「検索条件の重複」とは、以下のような「クエリストリングの全部の重複」を意味
に書き換えて、順序や表現の揺らぎを排除した「正規化済みクエリストリング」を生成し、それを検索条件として保存すればユニーク制約が効くのでは？
投稿2023/12/11 03:25
総合スコア434
0
→ 各テーブルを見ればわかる情報なのに、ユニーク性担保のために重複した情報を保存することになります…。
むしろ、重複させたくないクエリストリングをバラバラにして保存する意図がいまいちピンとこない感じもあります。
投稿2023/11/17 04:39
総合スコア146536
あなたの回答
tips
プレビュー
Q&A
