Q&A
なければ挿入、あれば無視を実践する限り、uniqueは担保されるはずですが
なぜuniqueがだめなのかわかりません
普通のSQL使いならご提示のような無駄な処理はあまりしません
表題の通りで「なければ挿入」「あれば無視（更新でもOK）」というSQLを一回のクエリで
かつすべての条件で汎用的に行いたいです
よく使われるような文なのに簡潔に書けるものがないのが不思議です
今回はmysql 5.6.40 を使いました。
テーブルは以下のような形です
このテーブルに、「str1がa」かつ「str2がa」のものが「なければ挿入」「あれば無視（更新でもOK）」をしたいです
CREATE TABLE IF NOT EXISTS `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `str1` text NOT NULL, `str2` text NOT NULL, PRIMARY KEY (`id`) )
まず、この内容でググるとすぐに出てくるのが
「ON DUPLICATE KEY UPDATE」
これに関しては、主キー、ユニークキーが検索対象の条件でない場合は動かないので
除外してください、今回はインデックスなしの「str1」「str2」を対象とします
「IGNORE」も同じ理由で除外だと思われます。
また、「str1とstr2」を合体させたユニークカラムを作る、は期待する情報ではありませんテーブルの構造はこの形のままでSQLが知りたいです。
次に考えられるのが副問い合わせというもの
下記で動くのであれば、非常に直感的で分かりやすいのですが
これでは文法エラーになります。。。SQLの設計者はなぜこれで動くようにしなかったのか理解に苦しみます
INSERT INTO `test`(str1,str2) VALUES('a','a') WHERE NOT EXISTS (SELECT * FROM `test` WHERE `str1` = 'a' AND `str2` = 'a');
そこで次のようにすると一部条件で動くようになるのですが
今回の場合は動きません、「a」が重複しているので「#1060 - Duplicate column name 'a'」となります
INSERT INTO `test`(str1,str2) SELECT * FROM (SELECT 'a','a') AS TMP WHERE NOT EXISTS (SELECT * FROM `test` WHERE `str1` = 'a' AND `str2` = 'a');
aとbにすればエラーは起きず期待する結果になります
INSERT INTO `test`(str1,str2) SELECT * FROM (SELECT 'a','b') AS TMP WHERE NOT EXISTS (SELECT * FROM `test` WHERE `str1` = 'a' AND `str2` = 'b');
「なければ挿入」「あれば無視（更新でもOK）」というSQLを一回のクエリで、かつすべての条件で汎用的に行うのは不可能なのでしょうか
ベテランの方ご教授いただけますと幸いです
maisumakun様からの回答で解決しました
同じ内容で悩んでいる方のために、SQLの具体例を書いておきます
お役立てください
INSERT INTO `test`(str1,str2) SELECT * FROM (SELECT 'a'as col1,'a'as col2) AS TMP WHERE NOT EXISTS (SELECT * FROM `test` WHERE `str1` = 'a' AND `str2` = 'a');
「汎用的」とはどの程度の汎用さを求めているのでしょう？
「str1がa」かつ「str2がa」
今回のように2カラムにまたがって初めてユニークであってほしい場合もそうなのでしょうか？
単純に「str1」「str2」をユニークキーにしてしまうと「a」「b」、「b」「a」が登録できなくなってしまいます
「str1とstr2」を合体させたユニークカラムを作る、は期待する情報ではありません
テーブルの構造はこの形のままでSQLが知りたいです
> 単純に「str1」「str2」をユニークキーにしてしまうと「a」「b」、「b」「a」が登録できなくなってしまいます
マルチカラムで一意制約可能な事をご存じないのですか？
（誤読のため削除）
今回の条件では分かりやすいように、単純にstr1とstr2としましたが
str1 ～ strN のカラムがあって、それを「str1 AND str5」「str1 AND str2 AND str15」「str1 AND ( str10 OR str18)」などの条件で「なければ挿入」「あれば無視（更新でもOK）」をしたいからです
全パターンのユニークキーを作るのは現実的ではないので、ユニークキーに依存しないSQLの書き方をお教えいただきたいのです
> 全パターンのユニークキーを作るのは現実的ではない
単に、ユニークとなる項目を列挙するだけですが、それがなぜ「全パターン」という表現になるのでしょう？
複数のカラムからピックアップしたカラムの組み合わせでユニークになるというなら、正規化が行われていないという事ですが。
また、上記と絡んで「汎用的」とはどの程度の汎用さを求めているのでしょう？
たとえば
str1,str2がOKならばstr3,str4がユニークであるという条件を無視して
イイのでしょうか？
おそらく質問者さんの頭で考えているものは最初から要件がおかしい
内容だと思います
複数条件を想定するならきちんと提示して、弾く場合と進める場合の
場合分けを明示スべきです
>もとのテーブル構造の正規化が不十分、ということも考えられます。
>ユニーク属性つけてください
>正規化が行われていない
皆様 聞いている身分で大変恐縮なのですが DB構造のベストプラクティスを質問しているのではなく、SQLの質問をしております
テーブルの正規化が不十分な本テーブルに対して
「なければ挿入」「あれば無視（更新でもOK）」というSQLを一回のクエリで行ことは「できない」という回答が事実になりますでしょうか
「何を実現するために」1回のクエリで済ませたいのでしょうか？
目的意識によっては他の手段のほうが合理的なため、その「やりたいこと」を確認しなければ的はずれな回答となってしまう危険が大きいです。
> 「なければ挿入」「あれば無視（更新でもOK）」というSQLを一回のクエリで行いたい
それについて質問しているのですがスルーされ続けています。
「汎用的」とはどの程度の汎用さを求めているのでしょう？
> SQLを一回のクエリで行ことは「できない」という回答が事実になりますでしょうか？
「汎用的」の内容次第です。
maisumakun 様
一回のクエリしか書けず、テーブルの構造も変えられないシステムを更新している、とイメージしていただければと思います、ストアドの設定もできないです。何も変えずSQLでそれを実現できる方法を探しています、そのSQLがパフォーマンスが悪くてもアンチパターンでも一回のクエリで期待する結果になることが重要です。
sazi様
重要な点、返信できておらず申し訳ありません
汎用的は
「str1がa」かつ「str2がa」のものが「なければ挿入」「あれば無視（更新でもOK）」
「str1がa」かつ「str2がb」のものが「なければ挿入」「あれば無視（更新でもOK）」
またstrを増やして
「str1がa」かつ「str2がa」かつ「str3がa」のものが「なければ挿入」「あれば無視（更新でもOK）」
「str1がa」かつ「str2がb」かつ「str3がb」のものが「なければ挿入」「あれば無視（更新でもOK）」
ができれば期待する汎用的なものであると思います
> 期待する汎用的なものであると思います
動的にテーブル構造が変わるのですか？
> テーブルの構造も変えられないシステム
ということなのに「strを増やして」とは？
別テーブルという事ですか？
>動的にテーブル構造が変わるのですか？
いいえ、「汎用的」の定義に関して私からの回答です
私はそのようなパターンのテーブルがあった場合も期待する結果になるSQL文の書き方をご教授願っております
>ということなのに「strを増やして」とは？別テーブルという事ですか？
分かりやすいように具体的なテーブルの情報を書いたのが混乱のもとだったのかもしれません
strはnです3個かもしれないし20個あるかもしれません
私が質問しているのはSQL文です
例えば値が重複していなければ以下のSQLで期待する結果になります
strが20個であってもただstrを増やしていくだけです
INSERT INTO `test`(str1,str2,str3) SELECT * FROM (SELECT 'a','b','c') AS TMP WHERE NOT EXISTS (SELECT * FROM `test` WHERE `str1` = 'a' AND `str2` = 'b' AND `str3` = 'c');
しかし値が重複すれば使えません。値が重複しても使える上のようなSQLの例が分かる方がいらっしゃったら教えていただきたい
ただそれだけです、テーブルの構造は重要ではありません、SQLの情報をご教授願っています。
出来そうだけど、回答が無くても良いというような口ぶりですね。
それに回答の内容次第では満足しなそうな気もするし。
maisumakunさまからの助言で解決いたしました
名前が重複してるとエラーが出てるなら別名で退避すればいい が期待するものでした
>出来そうだけど、回答が無くても良いというような口ぶりですね
いえ、私一人の力では絶対に解決できなかったです
ここに書き込んでくれた皆様も本当にありがとうございました
エラーについては気付いていましたが、目的が図れなかったのでやたらと長くなりましたね。
汎用的という事で、回答を作成していましたが無駄でした。
根本的にはやろうとされていたこととと大差ないので回答はしませんが。
回答2件
0
ベストアンサー
今回のように2カラムにまたがって初めてユニークであってほしい場合もそうなのでしょうか？
はい、そのための複合ユニークキーという機能があります。
投稿2020/12/07 04:06
総合スコア146018
まさに、「str1がa」かつ「str2がa」という条件を満たすレコードが1つになるようにするための機能です。
> 全パターンのユニークキーを作るのは現実的ではないので、ユニークキーに依存しないSQLの書き方をお教えいただきたいのです
トランザクションの中で、チェックと挿入を分けて書くのが妥当かと思います。どう考えても「よく使われるような」処理ではありません。
> str1 ～ strN のカラムがあって
もとのテーブル構造の正規化が不十分、ということも考えられます。
> 全パターンのユニークキーを作るのは現実的ではない
逆にその条件はすべてユニークである必要があるわけですから
すべてをパターンにユニーク属性つけてください
>トランザクションの中で、チェックと挿入を分けて書くのが妥当かと思います。どう考えても「よく使われるような」処理ではありません。
ありがとうございます、つまり「なければ挿入」「あれば無視（更新でもOK）」というSQLを一回のクエリで行ことは不可能ということでしょうか？
なぜ1回のクエリにこだわっているのでしょうか？
（ストアドを仕掛けて、外から呼び出すのはストアドを1回呼ぶだけ、のようなトンチ的な解決策でよければ可能ではあります）
説明が難しいのですが、
一回のクエリしか書けず、テーブルの構造も変えられないシステムを更新している、とイメージしていただければと思います
ストアドも設定できないです
> 今回の場合は動きません、「a」が重複しているので「#1060 - Duplicate column name 'a'」となります
単に「SELECT 'a' as col1, 'a' as col2」のような適当な列名を与える方法で処理はできませんか？
うおおおおおお！！！！！！
これです！！！私が求めていた回答はこれです！！！
そっか！名前が重複してるとエラーが出てるなら別名で退避すればいいんですね
あきらめていたのでめちゃくちゃ嬉しいです！！
本当にありがとうございます！！！
0
トランザクションで投入して、条件があわなければrollbackすればよい
と思いますが・・・
投稿2020/12/07 04:29
総合スコア116724
ありがとうございます！期待する回答はSQLです
一回のクエリしか書けず、テーブルの構造も変えられないシステムを更新している、とイメージしていただければと思います、ストアドの設定もできないです、トランザクション＆ロールバックも出来ないです。何も変えずSQLでそれを実現できる方法を探しています、そのSQLがパフォーマンスが悪くてもアンチパターンでも一回のクエリで期待する結果になることが重要です。
トランザクションもプロシージャもなしに
特殊な検索は厳しいと思いますが・・
テンポラリテーブルも１文にならないからNGですよね？
はい、テンポラリテーブルも作ることは出来ません
やはりできないのですね、、、
値が重ならなければ以下のような形で期待する結果にはなりそうなのですが
INSERT INTO `test`(str1,str2) SELECT * FROM (SELECT 'a','b') AS TMP WHERE NOT EXISTS (SELECT * FROM `test` WHERE `str1` = 'a' AND `str2` = 'b');
もうちょっと回答を待ちたいと思います
あなたの回答
tips
プレビュー
