質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
87.20%
MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

PHP

PHPは、Webサイト構築に特化して開発されたプログラミング言語です。大きな特徴のひとつは、HTMLに直接プログラムを埋め込むことができるという点です。PHPを用いることで、HTMLを動的コンテンツとして出力できます。HTMLがそのままブラウザに表示されるのに対し、PHPプログラムはサーバ側で実行された結果がブラウザに表示されるため、PHPスクリプトは「サーバサイドスクリプト」と呼ばれています。

解決済

INSERT IGNORE を使うタイミングと正しい記述

nikuatsu
nikuatsu

総合スコア168

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

PHP

PHPは、Webサイト構築に特化して開発されたプログラミング言語です。大きな特徴のひとつは、HTMLに直接プログラムを埋め込むことができるという点です。PHPを用いることで、HTMLを動的コンテンツとして出力できます。HTMLがそのままブラウザに表示されるのに対し、PHPプログラムはサーバ側で実行された結果がブラウザに表示されるため、PHPスクリプトは「サーバサイドスクリプト」と呼ばれています。

1回答

0評価

0クリップ

316閲覧

投稿2022/07/31 09:57

編集2022/08/06 22:13

前提

該当のソースコードのように3つのテーブルがあります。

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

そして my_tag_holders の主キー(34行目)によって 「フルーツとタグの同じ組み合わせはない」 という制限をしています。

発生している問題

「フルーツとタグの同じ組み合わせはない」 という制限のために INSERT IGNORE を使うつもりですが、そのタイミングと正しい記述がわかりません。

該当のソースコード

SQL

-- フルーツテーブル CREATE TABLE my_fruits ( `ID` int AUTO_INCREMENT, `count_likes` int(100) DEFAULT 0, `content` varchar(100) not null, PRIMARY KEY(`ID`)); INSERT INTO my_fruits (`count_likes`, `content`) VALUES (10, 'フルーツ1'), (56, 'フルーツ2'), (36, 'フルーツ3'); -- タグテーブル CREATE TABLE my_tags ( `ID` int AUTO_INCREMENT, `tag_kind_id` int, `tag_name` varchar(100), PRIMARY KEY(`ID`)); 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 (`tags_ID`, `fruits_ID`), #「フルーツとタグの同じ組み合わせはない」という制限 CONSTRAINT fk_01 FOREIGN KEY (`fruits_ID`) REFERENCES my_fruits(`ID`), CONSTRAINT fk_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); -- タグテーブルにユニーク制約を追加( sazi様からのご指摘 2022/07/31 20:54 を受けての追加 ) ALTER TABLE my_tags ADD CONSTRAINT UNIQUE u_01 (`tag_kind_id`, `tag_name`);

具体例とご質問

例えばあるユーザーが次の値をPOSTするという例で考えますと、下の方は 先述の制限 によってエラーとなります。(既にフルーツ3の緑はINSERT済み)

php

[ // これは投稿できる [ 'content'=>'フルーツ3', 'tag_name'=>'赤' ], // これは「フルーツとタグの同じ組み合わせはない」という制限によりエラー [ 'content'=>'フルーツ3', 'tag_name'=>'緑' ] ]

しかし当然エラーによって同じ組み合わせを防ぐのではなく「既存確認」の処理を挟むべきだろうと考えているのですが、では「既存確認」はどのように書いたらよろしいでしょうか?

INSERT IGNORE を使うのかと想像はできるのですが、テーブルが3つにまたがっているせいで書き方がわかりません。

もしテーブルが1つならば次のようにすればいいと思いますが…

SQL

INSERT IGNORE INTO my_fruits (`content`, `tag_name` ) VALUES ('フルーツ3', '赤'), ('フルーツ3', '緑');

…3つの場合はどうすればいいのでしょうか?

自分で考えられる方法1

自分で考えられるのは2つの方法です。

まず3つのテーブルの INSERT より前に、SELECT で「既存確認」を入れるという方法です。

php

// SELECT で「既存確認」を入れる $sth = $dbh->(" SELECT * FROM my_fruits F LEFT JOIN my_tag_holders TH ON TH.fruits_ID = F.ID LEFT JOIN my_tags T ON T.ID = TH.tags_ID WHERE F.content = $content AND T.tag_name = $tag_name ;"); $count = $sth->rowCount(); // フルーツとタグの同じ組み合わせがあればdieで中断(質問のための簡易表現) if ( $count > 0 ) die( 'フルーツとタグの同じ組み合わせが既にあります' ); // my_fruits を INSERT して、$fruits_id を得る $dbh->query(" INSERT INTO my_fruits (`content`) VALUES ('$content') ";); $fruits_id = $dbh->lastInsertId(); // my_tags を INSERT して、$tag_id を得る $dbh->query(" INSERT INTO my_tags (`tag_name`) VALUES ('$tag_name') ";); $tag_id = $dbh->lastInsertId(); // my_tag_holders を INSERT する $dbh->query(" INSERT INTO my_tag_holders (`tags_ID`, `fruits_ID`) VALUES ( $tag_id, $fruits_id ) ";);

しかしこの方法は、整合性を保つために「既存確認」の前にLOCK TABLES が必要となり負荷が懸念されます。

自分で考えられる方法2

続いて考えれる方法としては、上記のような SELECT での「既存確認」はせず、最後の my_tag_holders への INSERT の際に INSERT IGNORE を使って「既存確認」をするというものです。 最後に ROW_COUNT() して0ならロールバックし整合性を保つつもりです。

php

// my_fruits を INSERT して、$fruits_id を得る $dbh->query(" INSERT INTO my_fruits (`content`) VALUES ('$content') ";); $fruits_id = $dbh->lastInsertId(); // my_tags を INSERT して、$tag_id を得る $dbh->query(" INSERT INTO my_tags (`tag_name`) VALUES ('$tag_name') ";); $tag_id = $dbh->lastInsertId(); // my_tag_holders を INSERT IGNORE を使って「既存確認」してから INSERT する $dbh->query(" INSERT IGNORE INTO my_tag_holders (`tags_ID`, `fruits_ID`) VALUES ( $tag_id, $fruits_id ) ";);

しかしこの方法は、失敗を前提としているような印象で違和感を覚えます。私がテーブルが1つの場合の INSERT IGNORE しか知らないためにこの方法に至ったのですが、もし3つのテーブルについて INSERT IGNORE ができれば、my_fruits を INSERT するより以前に「既存確認」ができるので、この違和感もなくなるのではと思いました。

改めまして、INSERT IGNORE のタイミングと正しい記述についてご教示頂けませんでしょうか。

補足情報(FW/ツールのバージョンなど)

それぞれ次のバージョンです。

PHP 8.0
MySQL 5.7.2

宜しくお願い致します。

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

良い質問の評価を上げる

以下のような質問は評価を上げましょう

  • 質問内容が明確
  • 自分も答えを知りたい
  • 質問者以外のユーザにも役立つ

評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

気になる質問をクリップする

クリップした質問は、後からいつでもマイページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

  • プログラミングに関係のない質問
  • やってほしいことだけを記載した丸投げの質問
  • 問題・課題が含まれていない質問
  • 意図的に内容が抹消された質問
  • 過去に投稿した質問と同じ内容の質問
  • 広告と受け取られるような投稿

評価を下げると、トップページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

sazi

2022/07/31 11:21 編集

my_fruits もmy_tagsPRIMARY KEYはAUTO_INCREMENTですけど更新はあるのでしょうか? あるとしたら更新とする際の条件は何ですか?
nikuatsu

2022/07/31 11:39 編集

コメントありがとうございます。PRIMARY KEY 以外のカラムは更新の可能性があります。 ただ「条件」というのがイメ―ジできないのですが… 「状況」でよろしければ、例えば「いいねがクリックされたとき」に my_fruits.count_likes が更新されたりします。他にも例えば「やっぱり "赤" の tag_kind_id を 2 に変更しようというとき」に my_tags.tag_kind_id が更新されたりもします。 仰る「条件」がわからず「状況」のご説明になってしまいましたが、これで返信になっているでしょうか…
sazi

2022/07/31 11:54

IDを指定して更新するんですね。 では、my_tag_holdersはIDの中身に関係なくIDの値の組合せという事で良いんでしょうか? ※例えば色を変更して内容的には重複している状況が生まれるわけですけど。
nikuatsu

2022/07/31 12:18

ありがとうございます。まったく仰る通りでした。 その点は次のユニーク制約を付与することで解決できるでしょうか。 ALTER TABLE my_tags ADD CONSTRAINT UNIQUE u_01 ( tag_kind_id, tag_name ); この制約によって、tags.ID=1 の "赤" を "青" に更新しようとしても、すでに存在するので更新できない。これにより「> 色を変更して内容的には重複している状況」を生じなくさせることができる。という目論見です。
sazi

2022/07/31 13:41 編集

UNIQUE制約で、IDとその内容に変更が無い事の完全な保証にはならないですが、そこまで求めるという感じでは無いのでそれ良いかと思います。

まだ回答がついていません

会員登録して回答してみよう

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
87.20%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問

同じタグがついた質問を見る

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

PHP

PHPは、Webサイト構築に特化して開発されたプログラミング言語です。大きな特徴のひとつは、HTMLに直接プログラムを埋め込むことができるという点です。PHPを用いることで、HTMLを動的コンテンツとして出力できます。HTMLがそのままブラウザに表示されるのに対し、PHPプログラムはサーバ側で実行された結果がブラウザに表示されるため、PHPスクリプトは「サーバサイドスクリプト」と呼ばれています。