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

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

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

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

PHP

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

解決済

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

nikuatsu
nikuatsu

総合スコア160

MySQL

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

PHP

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

2回答

0評価

0クリップ

306閲覧

投稿2022/08/03 06:44

編集2022/08/09 17:13

前提

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

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

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

しかし問題が5行目で、my_fruits.fruits_namevarchar(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

宜しくお願い致します。

良い質問の評価を上げる

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

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

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

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

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

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

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

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

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

m.ts10806

2022/08/03 07:02

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

2022/08/03 07:07

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

2022/08/03 07:14 編集

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

2022/08/03 07:25

ユニーク性を確保しなければならないとは思っていません。 > ユニーク制限ができないmy_fruits.fruits_nameに対し、「なければINSERT」を行いたいです。 を実現したいのであって、 ユニーク性を確保した上で、「なければINSERT」を行いたい。 という強い希望はございません。 私のような初心者は「なければINSERT」のためにまず ON DUPLICATE KEY UPDATE を用いようとし、しかしユニーク性の確保が要求されているために頓挫した。というのが質問の経緯です。
maisumakun

2022/08/03 07:32

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

2022/08/03 07:38 編集

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

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

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

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

ただいまの回答率
87.20%

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

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

質問する

関連した質問

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

MySQL

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

PHP

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