前提
こちらの3つのテーブルがあります。
my_fruits
: フルーツテーブル
my_tags
: タグテーブル
my_tag_holders
: リレーションテーブル
そして35行目にあるように、my_tag_holders
の主キーによって「フルーツとタグの同じ組み合わせはない」 とユニーク制限をかけています。
しかし問題が5行目で、my_fruits.fruits_name
はvarchar(1000)
なので ユニーク制限ができない様子 です。
3つのテーブル
SQL
-- フルーツテーブル CREATE TABLE my_fruits ( `ID` int AUTO_INCREMENT, `count_likes` int(100) DEFAULT 0, `fruits_name` varchar(1000) not null, # 1000文字なのでユニーク制限ができない PRIMARY KEY(`ID`)); INSERT INTO my_fruits (`count_likes`, `fruits_name`) VALUES (10, 'フルーツ1'), (56, 'フルーツ2'), (36, 'フルーツ3'); -- タグテーブル CREATE TABLE my_tags ( `ID` int AUTO_INCREMENT, `tag_kind_id` int default 1, `tag_name` varchar(100), PRIMARY KEY(`ID`), UNIQUE u_my_tags_01 (`tag_kind_id`, `tag_name`)); INSERT INTO my_tags (`tag_kind_id`, `tag_name`) VALUES (1, '赤'), (1, '青'), (1, '緑'), (1, '黄'), (1, '白'); -- リレーションテーブル CREATE TABLE my_tag_holders ( `fruits_ID` int, `tags_ID` int, PRIMARY KEY (`fruits_ID`, `tags_ID`), #「フルーツとタグの同じ組み合わせはない」という制限 INDEX idx_my_tag_holders_01 (`tags_ID`, `fruits_ID`), CONSTRAINT fk_my_tag_holders_01 FOREIGN KEY (`fruits_ID`) REFERENCES my_fruits(`ID`), CONSTRAINT fk_my_tag_holders_02 FOREIGN KEY (`tags_ID`) REFERENCES my_tags(`ID`) ); INSERT INTO my_tag_holders (`fruits_ID`, `tags_ID`) VALUES (1, 1),(1, 2), (2, 2),(2, 4),(2, 5), (3, 3);
実現したいこと
ユニーク制限ができないmy_fruits.fruits_name
に対し、「なければINSERT」を行いたいです。
発生している問題
ユニーク制限ができないために、ON DUPLICATE KEY UPDATE
が使えず、「なければINSERT」ができない。という点がネックになっています。
該当のソースコード
上記3つのテーブルに対し、次の流れで新たな INSERT を進めようと思っています。
最後の☆my_tag_holders を「なければINSERT」する
はできるのですが、
問題は★my_fruits を「なければINSERT」する
はできないということです。
(インジェクションリスクがある点はスルーしてください。)
php
// 送信値 $fruits_name = $_POST['fruits_name']; $tag_name_arr = $_POST['tag_name_arr'] ?? []; // タグがなければ fruits_name だけで既存確認を行う if ( empty($tag_name_arr) ) { // ★my_fruits を「なければINSERT」する $sth = $dbh->query(" INSERT INTO my_fruits (`fruits_name`) VALUES ('$fruits_name') ON DUPLICATE KEY UPDATE fruits_name = '$fruits_name' ";); // INSERT|UPDATE の結果を rowCount で得る /* ON DUPLICATE KEY UPDATE ができれば INSERT なら 1、UPDATE なら 2 を rowCount で得れるが、 fruits_name をユニークにできないためそれらが得れず よって「なければINSERT」を 達成できない */ $count = $sth->rowCount(); // $fruits_id を得る if ( $count === 1 ) { $fruits_id = $dbh->lastInsertId(); // 挿入されたIDを得る } else { $sth = $dbh->query(" SELECT ID FROM my_fruit WHERE fruits_name = '$fruits_name' ";); $fruits_id = $sth[0]['ID']; // 既存のIDを得る } } // タグがあればフルーツとタグの組み合わせで既存確認を行う else { // my_fruits を INSERT して、$fruits_id を得る $dbh->query(" INSERT INTO my_fruits (`fruits_name`) VALUES ('$fruits_name') ";); $fruits_id = $dbh->lastInsertId(); // my_tags を INSERT して、$tag_id を得る $tag_name = $tag_name_arr[0]; // コードが煩雑になるのでとりあえず1つだけ $dbh->query(" INSERT INTO my_tags (`tag_name`) VALUES {'$tag_name'} ";); $tag_id = $dbh->lastInsertId(); // ☆my_tag_holders を「なければINSERT」する /* このときは「フルーツとタグの同じ組み合わせはない」という制限によって 「なければINSERT」を達成できる (あればエラーになってINSERTされない) */ $dbh->query(" INSERT INTO my_tag_holders (`fruits_ID`,`tags_ID`) VALUES ($fruits_id, $tag_id) ";); } return $fruits_id;
試したこと
「LOCK TABLES → SELECT → INSERT → LOCK TABLES 解除」 でなら「なければINSERT」できますが、テーブル全体のロックは避けたいです。
また INSERT IGNORE はあらゆるエラーを回避していまうのでこれも避けたいです。
ツールのバージョン
それぞれ次のバージョンです。
PHP 8.0
MySQL 5.7.2
宜しくお願い致します。
まだ回答がついていません
会員登録して回答してみよう