非ユニークカラムに、「 なければINSERT 」する方法

Question

前提

こちらの３つのテーブルがあります。

my_fruits : フルーツテーブル
my_tags : タグテーブル
my_tag_holders : リレーションテーブル

そして35行目にあるように、my_tag_holders の主キーによって「フルーツとタグの同じ組み合わせはない」 とユニーク制限をかけています。

しかし問題が5行目で、my_fruits.fruits_name はvarchar(1000) なので ユニーク制限ができない様子 です。

３つのテーブル

-- フルーツテーブル
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,  'フルーツ１'),
(56,  'フルーツ２'),
(36,  'フルーツ３');

-- タグテーブル
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」ができない。という点がネックになっています。

該当のソースコード

上記３つのテーブルに対し、次の流れで新たな INSERT を進めようと思っています。

最後の☆my_tag_holders を「なければINSERT」するはできるのですが、
問題は★my_fruits を「なければINSERT」するはできないということです。

（インジェクションリスクがある点はスルーしてください。）

// 送信値
$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]; // コードが煩雑になるのでとりあえず１つだけ
	$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

宜しくお願い致します。

Comments

[m.ts10806]

そもそも1,000桁も必要なのはどういう想定の設計のなのでしょうか。
フルーツでそこまで必要に思えないのですが・・
不要な枠を確保してしまっていませんか？

[nikuatsu]

ご指摘ありがとうございます。カラム名はあくまで一例なので、「フルーツとタグ」でなく「文章とタグ」だと思ってください。変な例でごめんなさい🙇

[maisumakun]

> ユニーク制限ができないmy_fruits.fruits_nameに対し、

「1000文字もあるもの（文章）」に対してユニーク性を確保しなければならない、という設計そのものが妥当ではない印象を受けます（わずかな表記ゆれや句読点が違うようなものでもユニークとなってしまうので、有意な制約として運用できるのかが見えてきません）。

[nikuatsu]

ユニーク性を確保しなければならないとは思っていません。

> ユニーク制限ができないmy_fruits.fruits_nameに対し、「なければINSERT」を行いたいです。
を実現したいのであって、

ユニーク性を確保した上で、「なければINSERT」を行いたい。
という強い希望はございません。

私のような初心者は「なければINSERT」のためにまず ON DUPLICATE KEY UPDATE を用いようとし、しかしユニーク性の確保が要求されているために頓挫した。というのが質問の経緯です。

[maisumakun]

初心者ということでしたら、「テーブル全体のロックは避け」ずにやってみて、実際に問題が出てから考える、でも問題なかったような気もします。

[m.ts10806]

私もmaisumakunさんの指摘に賛成です。
例として出しているにも関わらずユニークが要求されているという変なフェイクが入っていて内容に整合性がなくなっていますし、
出来るやり方が分かっているなら、まずその通りやってからで良いのではと。
それにテーブルロックといっても一瞬の出来事です。
そんな秒間何万件もひっきりなしにアクセスされるようなアプリケーションなのですか？
それでしたら先に指摘のある「「1000文字もあるもの（文章）」に対してユニーク性を確保しなければならない」設計の見直しの方が先決です。実装でなんとかしようとすると無理が出ます。
例ならば例で済む内容、そうでないなら本質が分かる程度に情報を提示されないことには
解決には向かわないと思います。