3

I have a table with 10 columns and I have to add many, many rows from a CSV file. Of course, I must not add two identical rows, so I need a SQL statement that ignore the command if the entire row does exists. The INSERT must be ignored only if all fields are identical. Two rows may have identical field1 or field2, but not all fields identical.

I tried INSERT IGNORE but it doesn't work. No column is set as UNIQUE, as the INSERT must be ignored onyl only if the entire row is identical.

What solution do you have for this? Thanks!

ali
  • 10,927
  • 20
  • 89
  • 138

1 Answers1

2

Create a combined index on all columns, then INSERT IGNORE or REPLACE INTO according to your needs.

From the docs:

If you use the IGNORE keyword, errors that occur while executing the INSERT statement are treated as warnings instead. For example, 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.

Eugen Rieck
  • 64,175
  • 10
  • 70
  • 92
  • I added a INDEX to all columns the used INSERT IGNORE, it doesn't work – ali Jun 13 '12 at 20:09
  • Yes, but two fields can have identical values. the INSERT is not permitted only if the entire row is already existing - each field of the new row is identical to each field of the existing one – ali Jun 13 '12 at 20:15
  • If I set UNIQUE INDEX, I cannot have identical values in two rows. – ali Jun 13 '12 at 20:15
  • 1
    Not a UNIQUE INDEX on **every single** column, but a combined UNIQUE INDEX on **all columns together**, this fits your requirements – Eugen Rieck Jun 13 '12 at 20:22
  • Well, I get this when trying to apply INDEX to all at once: #1071 - Specified key was too long; max key length is 1000 bytes – ali Jun 13 '12 at 20:44
  • If this is the case, you have run against a limit in keeping data unique in MySQL. You might want to check, if you really need these 1000 bytes - did you chose too long data types "just to make sure" ? – Eugen Rieck Jun 13 '12 at 20:47
  • O.K. I limited the length of the value to be checked. http://stackoverflow.com/questions/1814532/1071-specified-key-was-too-long-max-key-length-is-767-bytes/1814594#1814594 – ali Jun 13 '12 at 20:49
  • @EugenRieck how do you think this would result in a table with 26 million rows ? the table has just 3 columns and one of them is primary id. the other columns are two values that i hold for relation. one of them is a varchar(30) and the other one is INT.Would it have a deep impact on regular insert ? – kommradHomer Jan 17 '13 at 11:12