2

From my understanding INSERT IGNORE inserts a new entry if it doesn't already exists, if it does, ignore it. So I've been trying to do that for a while and it doesn't seem to be working. Here's my attempt:

insert insert ignore into rss_feeds (md5sum) values ("1234");
Query OK, 1 row affected (0.00 sec)

mysql> insert ignore into rss_feeds (md5sum) values ("1234");
Query OK, 1 row affected (0.00 sec)

mysql> insert ignore into rss_feeds (md5sum) values ("1234");
Query OK, 1 row affected (0.00 sec)

mysql> insert ignore into rss_feeds (md5sum) values ("1234");
Query OK, 1 row affected (0.00 sec)

mysql> select * from rss_feeds where md5sum="1234";
+------+--------+----------+---------+----------+--------+--------+---------+
| link | source | headline | updated | inserted | md5sum | itemid | emailed |
+------+--------+----------+---------+----------+--------+--------+---------+
| NULL | NULL   | NULL     | NULL    | NULL     | 1234   |   NULL |    NULL |
| NULL | NULL   | NULL     | NULL    | NULL     | 1234   |   NULL |    NULL |
| NULL | NULL   | NULL     | NULL    | NULL     | 1234   |   NULL |    NULL |
| NULL | NULL   | NULL     | NULL    | NULL     | 1234   |   NULL |    NULL | 
+------+--------+----------+---------+----------+--------+--------+---------+
4 rows in set (0.00 sec)
Stupid.Fat.Cat
  • 8,623
  • 15
  • 62
  • 118

2 Answers2

8

As documented under INSERT Syntax:

without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row still is not inserted, but no error is issued.

You need to define a UNIQUE index on md5sum:

ALTER TABLE rss_feeds ADD UNIQUE INDEX (md5sum);
eggyal
  • 113,121
  • 18
  • 188
  • 221
  • Thank you for your prompt response. Could you explain to me a bit more of what that's doing? – Stupid.Fat.Cat Sep 14 '12 at 17:13
  • @Shelby.S: As documented under [`CREATE INDEX` Syntax](http://dev.mysql.com/doc/en/create-index.html): "*A `UNIQUE` index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, a `UNIQUE` index permits multiple `NULL` values for columns that can contain `NULL`. If you specify a prefix value for a column in a `UNIQUE` index, the column values must be unique within the prefix.*" – eggyal Sep 14 '12 at 17:15
  • thank you so much. But I'm encountering another issue (please bear with me as I'm completely new to mysql), I get ERROR 1170, BLOB/TEXT column md5sum used in key specification without a key length.. not sure what's goingo n. I tried exactly what you suggested. – Stupid.Fat.Cat Sep 14 '12 at 17:19
  • @Shelby.S: What is the data type of your `md5sum` column? Since all MD5 digests are 16-bytes in length, a suitable data type would be `BINARY(16)` for the raw value or `CHAR(32)` for the hexadecimal representation. – eggyal Sep 14 '12 at 17:21
  • Oh I see, so it's because I have it as text. Thanks! – Stupid.Fat.Cat Sep 14 '12 at 17:22
  • @Shelby.S: Right. You *can* create indexes (including `UNIQUE` ones) over `TEXT` columns, but not over the entire 64KB that each record might contain -- instead you can only index some smaller prefix, i.e. the leftmost part of each field (up to 1000 bytes in MyISAM or 767 bytes in InnoDB). – eggyal Sep 14 '12 at 17:26
0

IGNORE will ignore errors produced by any unique key constraints. Basically it will just ignore any errors that occur doing the insert.

Matthew Scragg
  • 3,886
  • 3
  • 17
  • 26
  • 1
    It doesn't ignore unique key constraints. It respects them. It just won't complain about conflict and instead *ignore* the duplicate records. – tadman Sep 14 '12 at 17:12
  • To be clear, though, that's the only type of error it ignores. All others are reported as usual. – tadman Sep 14 '12 at 18:53
  • It ignores other stuff as well dealing with partitions and data conversion issues. – Matthew Scragg Sep 14 '12 at 19:42