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

Question

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

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文に同じテキストを貼り付けることになってしまうので、少し気持ち悪いなあと思っています。

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

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

Answer 1

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

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

Comments

[miyabi-sun]

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

Answer 2

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

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

---

'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 によるもの

Comments

[hojo]

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

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

ありがとうございます。

Answer 3

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

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

Comments

[hojo]

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

Answer 4

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

   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を設けて条件文にも使われた方が、今後大量になる場合に良いと考えます。

Comments

[hojo]

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

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