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

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

ただいまの
回答率

90.61%

  • MySQL

    5701questions

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

MySQLで同じテキストが存在する場合にはINSERTしないより良いSQL文を教えてください

解決済

回答 4

投稿 編集

  • 評価
  • クリップ 1
  • VIEW 902

hojo

score 178

下記のようなテキストを保存するためのテーブルがあった時

CREATE TABLE text (
  text_id SERIAL PRIMARY KEY COMMENT 'テキストID',
  text VARCHAR(65535) NOT NULL COMMENT 'テキスト'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='テキストデータテーブル';

下記のようにすることで
同じテキストが存在すればinsertしないが
まだ保存されていないテキストの場合はinsertすることができました。

   INSERT into text (text)
   SELECT 'ここに挿入したいテキスト'
   WHERE (
     SELECT COUNT(text) = 0
     FROM text
     WHERE text = 'ここに挿入したいテキスト'
   )

しかしこれだとSQL文に同じテキストを貼り付けることになってしまうので、少し気持ち悪いなあと思っています。

ユーザ定義変数を利用せずに
重複している'ここに挿入したいテキスト'を1つだけにしてなんとか同じようにするにはどうすれば良いですか?

よろしくお願いいたします。

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

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

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

    クリップを取り消します

  • 良い質問の評価を上げる

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

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

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

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

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

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

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

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 4

checkベストアンサー

+2

MySQL独自構文ですが、INSERT IGNOREで重複を除外する事が出来ます。下記の場合、既にtextに'a'があるとINSERTされないです。
(例)
INSERT IGNORE INTO text(text_id ,text) VALUES(1,'a');

但し、上記で言うと、textカラムにUNIQUEキー制約が必要ですね。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/12/28 11:10

    これです!
    実運用でこの手法使ってますが速くて気に入っています。
    欠点は変なデータが入り込んできてもエラーも出ずに捨てられることですかね。

    キャンセル

+1

 ※これは回答ではありません。

すでに解決済みではありますが、いくつか注意点がありますので、以下に記載させていただきます。


'text'カラムのサイズによっては、INSERT IGNORE による方法では実現できません。

なぜなら、MySQL には作成できるインデックスの長さに制限があるからです。
https://dev.mysql.com/doc/refman/5.6/ja/innodb-restrictions.html

InnoDB の内部的な最大キー長は 3500 バイトですが、MySQL 自体では 3072 バイトに制限されています。

UTF-8 の場合、1文字のサイズは2〜3バイト(だったと思います)なので、textカラムに格納したい文字数が約1000文字を超える場合、
カラム値全体をカバーするインデックスが作成できないことになります。

mysql> CREATE TABLE test (text VARCHAR(2048), UNIQUE INDEX (text));
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

また、MySQLの文字列比較には

  • デフォルトの照合順序では、大文字/小文字を区別しない
  • 末尾の空白を無視する

という制約があります。

mysql> SHOW COLLATION WHERE `Charset` = 'utf8';
+--------------------------+---------+-----+---------+----------+---------+
| Collation                | Charset | Id  | Default | Compiled | Sortlen |
+--------------------------+---------+-----+---------+----------+---------+
| utf8_general_ci          | utf8    |  33 | Yes     | Yes      |       1 |
| utf8_bin                 | utf8    |  83 |         | Yes      |       1 |
| utf8_unicode_ci          | utf8    | 192 |         | Yes      |       8 |
(以下略)


※ '_ci'は Case Insensitive の略

https://dev.mysql.com/doc/refman/5.6/ja/char.html

MySQL のすべての照合順序は、PADSPACE 型のものです。これは、MySQL 内のすべての CHAR、VARCHAR、および TEXT 値が、末尾のスペースに関係なく比較されることを意味します。

そのため、

  • 大文字/小文字を区別したい場合、textカラムの照合順序を utf8_bin に
  • 末尾の空白も無視させたくない場合、照合順序を binary に

変更する必要があります。
(もっとも、照合順序を変更すると、他の部分に影響が出るかもしれませんが、、)

 デフォルトの比較順序 (utf8_general_ci) の場合
mysql> CREATE TABLE test (text VARCHAR(1024), UNIQUE INDEX (text));
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT IGNORE INTO test VALUES ('hoge'), ('HOGE'), ('hoge     ');
Query OK, 1 row affected, 2 warnings (0.01 sec)
Records: 3  Duplicates: 2  Warnings: 2

mysql> SELECT * FROM test;
+------+
| text |
+------+
| hoge |
+------+
1 row in set (0.00 sec)
 utf8_bin の場合
mysql> CREATE TABLE test (text VARCHAR(1024) COLLATE 'utf8_bin', UNIQUE INDEX (text));
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT IGNORE INTO test VALUES ('hoge'), ('HOGE'), ('hoge     ');
Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 3  Duplicates: 1  Warnings: 1

mysql> SELECT * FROM test;
+------+
| text |
+------+
| HOGE |
| hoge |
+------+
2 rows in set (0.00 sec)
 binary の場合
mysql> CREATE TABLE test (text VARCHAR(1024) COLLATE 'binary', UNIQUE INDEX (text));
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT IGNORE INTO test VALUES ('hoge'), ('HOGE'), ('hoge     ');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test;
+-----------+
| text      |
+-----------+
| HOGE      |
| hoge      |
| hoge      |
+-----------+
3 rows in set (0.00 sec)


※ SQL の実行結果は、全て MySQL5.7 によるもの

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/12/29 01:15 編集

    丁寧なご解説ありがとうございます。
    A.Ichiさんも指摘してくださっていましたが、KiyoshiMotokiさんからも指摘を受け
    やはりMD5のハッシュを使うことは必須になるのだろうと思いました。

    最大キー長は3500バイトであるため長いvarcharはキーの利用に不向きであること
    _ciがCase Insensitiveの略であること、そしてMySQLの文字列比較は大文字小文字を区別しないことなど、自分が知らなかったことをご指摘いただきましたのでとても勉強になりました!

    ありがとうございます。

    キャンセル

0

変数を使ってよろしければ

   SET @foo:="ここに挿入したいテキスト";

   INSERT into text (text)
   SELECT @foo
   WHERE (
     SELECT COUNT(text) = 0
     FROM text
     WHERE text = @foo
   )

当環境では上記のSQLがうまく動かないので????
INSERT into text (text)
SELECT * FROM (SELECT @foo) X
WHERE (
SELECT COUNT(text) = 0
FROM text
WHERE text = @foo
)

余計な事ですが、text VARCHAR(65535)が大きいのでmd5を設けて条件文にも使われた方が、今後大量になる場合に良いと考えます。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/12/28 10:48

    回答ありがとうございます!
    正直その箇所について不安があったのですが解決方法が思いつかなかったので
    MD5で検索して調べて見てなるほど!と思いました。

    textテーブルにハッシュカラムを作成し
    同じテキストであるかどうかはハッシュを比較することで計算するように
    仕様を変えてみようと思います。

    キャンセル

0

SQLServerならMERGE構文という、同じ内容レコードがあればUPDATE(アップデートしなくてもいい)、無ければINSERTを行える構文があります。

MySQLでもINSERT … ON DUPLICATE KEY UPDATEがそれに該当するようです。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/12/28 10:49

    本文中に記載しなかったのですが
    UPDATEは使えないよう制限がかかっているので
    この方法ではうまくできないかもしれないと考えています。

    キャンセル

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

  • ただいまの回答率 90.61%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

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

  • MySQL

    5701questions

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