非ユニークカラムに、「 なければ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文字もあるもの（文章）」に対してユニーク性を確保しなければならない」設計の見直しの方が先決です。実装でなんとかしようとすると無理が出ます。
例ならば例で済む内容、そうでないなら本質が分かる程度に情報を提示されないことには
解決には向かわないと思います。

Answer 1

テンポラリに一旦保持してから投入してみては？
//元データ

CREATE TABLE my_fruits (
  ID int primary key auto_increment,
  count_likes int(100) DEFAULT 0, 
  fruits_name varchar(1000) not null
);

INSERT INTO my_fruits (`count_likes`, `fruits_name`)
VALUES
(10,  'フルーツ１'),
(56,  'フルーツ２'),
(36,  'フルーツ３');

//追加処理

create temporary table tmp (
  `count_likes` int(100) DEFAULT 0, 
  `fruits_name` varchar(1000) not null
);
insert into tmp values
(90,  'フルーツ２'),
(91,  'フルーツ３'),
(92,  'フルーツ４'),
(93,  'フルーツ５');

insert my_fruits(count_likes,fruits_name)
select * from tmp as t1 where not exists (select 1 from my_fruits where fruits_name=t1.fruits_name );

Comments

[nikuatsu]

どうもありがとうございます。できました。これは凄いです。temporary table というのはどこかで耳にした覚えがあるのですが、初めて身近な例を目の当たりにできました。

[sazi]

解決済みですけど、insert select が競合する場合の重複データは回避できていないように思えますが。

[nikuatsu]

sazi様、コメントありがとうございます。

「> insert select が競合する場合の重複データ」とは何か？が理解できませんでした。

実際に次のコードで試したのですが重複は起こらなかったので、この事実でもって「> できました」とコメントしたのですが…
https://onlinephp.io/c/75e85

具体的に「> insert select が競合する場合の重複データ」というのはどのデータで、どういうときに生じうるのでしょうか？

[sazi]

> 実際に次のコードで試したのですが重複は起こらなかったので
PHPは良く分かりませんが、ロックも制約もない状態ですから、全く同時に複数の処理が実行された場合、各々がinsertする事になるように思うのですが。

[nikuatsu]

今試しましたがやはり重複は起こりませんでした。ロックはbeginTransactionが適宜行ってくれそうではありますが、私もどうして重複が起こらないのか分かっておりません。「なんかできた」という感じです…。

Answer 2

「LOCK TABLES → SELECT → INSERT → LOCK TABLES 解除」 でなら「なければINSERT」できますが、テーブル全体のロックは避けたいです。

SELECT FOR UPDATEで当該行だけロック、とすればいいのではないでしょうか。

Comments

[nikuatsu]

どうもありがとうございます。調べてみたらこれはとても便利そうです。

しかし質問の例での使い方が疑問です。つまり新たにINSERTされる行なのでロックのしようがないのでは？と思います。

もし「次のAUTO_INCLIMENTが自動的にロックされる」という機能があれば使えそうですが、調べた限りでは見つかりません。

どのように新たな行がロックされるのか教えて頂けませんでしょうか。

[maisumakun]

> もし「次のAUTO_INCLIMENTが自動的にロックされる」という機能があれば使えそうですが

ネクストキーロック、というのは実在します。

[nikuatsu]

仮に次のAUTO_INCLIMENTがロックされても関係なかったですね…

改めて「>SELECT FOR UPDATEで当該行だけロック」で、どのようにINSERTするのかという点はご存知ないでしょうか？

[nikuatsu]

SELECT FOR UPDATE で解決するというのはやはりお間違いだったみたいですね…。追及してしまい申し訳ございませんでした。

でも SELECT FOR UPDATE というのはいつか使えそうなので覚えておきたいと思います。

[ockeghem]

MySQLですと、「存在しない行」に関するロックは一応可能です。ギャップロックやネクストキーロックという用語で検索してみてください。やり方は SELECT ... FOR UPDATE であっています。ただ、ギャップロックやネクストキーロックは使い方が難しく、デッドロックなどになりやすいです。また、当該列にインデックスがないと、そもそも行ロックにならずテーブルロックになります。これは行が存在する・しないにかかわらずそうなります。

[nikuatsu]

ockeghem様
コメントありがとうございます。
ネクストキーロックはmaisumakun様に教えて頂き、調べて、実際に書いた上で、しかし重複されてしまいましたので「お間違いだったみたいです」とコメントしたのですが…
しかしこのレベルのお二方が仰るのなら、むしろ私の記述こそが間違っているのだと思います。
仰るように使い方が難しいということでしょうか。
もう少し色々試してみたいと思います。

[nikuatsu]

ockeghem様
あれから調査を進めてみますと、今回のご質問（INSERT 前の既存確認という処理）に際しての SELECT ... FOR UPDATE はデッドロックを回避しきれないようです。

こちらの質問（ https://teratail.com/questions/ri4p38r3mw86c0 ）には次の２点があります。

・SELECTが空振りするとギャップロックになる
・同時に複数のギャップロックが発生するとお互いをブロックしデッドロック状態となる

そして INSERT 前の既存確認という処理は、それ自体が「空振り」を孕みます。
つまりもし存在しない値を二者がほぼ同時に SELECT ... FOR UPDATE して空振りしたとき、必ずデッドロック状態となる。というわけです。

そのため、今回の質問について SELECT FOR UPDATE で解決するというのはやはりお間違いだったみたいです。

以上は素人の調査なので、もし「いやそんなことはない」とデッドロックを回避するようなお考えがございましたら、是非またコメント等よろしくお願いいたします。

[mpyw]

こちらもご参照ください
https://zenn.dev/mpyw/articles/rdb-advisory-locks