CODEWITHSUNDEEP
mysql
Jake Wilson
Jake Wilson

Reputation: 91233

INSERT IGNORE when key is auto_incremented?

I have a very basic table, consisting of an auto_incrementing id column (primary key), and a TEXT column containing some various text.

I need to insert data into this table, but I don't want to insert duplicate rows. I thought using INSERT IGNORE INTO but apparently the IGNORE uses the table's key to determine if the row is a duplicate or not. Since the key field in my table is auto incrementing, that means a duplicate will never appear to show up.

Is there a better approach to my table design? Does the TEXT column need to be a key also?

Upvotes: 0

Views: 114

Answers (3)

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44373

I would recommend using a before insert trigger and have a hash (MD5) of the first 255 characters

Run this sample

drop database if exists jakobud;
create database jakobud;
use jakobud
create table mytext
(
    id int not null auto_increment,
    txt text not null,
    txtmd5 char(32) not null default '',
    primary key (id),
    unique key (txtmd5)
);
DELIMITER $$    
CREATE TRIGGER mytext_bi
    BEFORE INSERT ON mytext
    FOR EACH ROW    
BEGIN
    DECLARE found_count INT;

    SELECT COUNT(1) INTO found_count
    FROM mytext WHERE txtmd5 = MD5(LEFT(new.txt,10));
    IF found_count = 1 THEN
        SELECT COUNT(1) INTO found_count FROM table_that_does_not_exist;
    END IF;
    SET new.txtmd5 = MD5(LEFT(new.txt,10));
END; $$    
DELIMITER ;

then load a bunch of data into the mytext

This will ignore duplicates of the MD5 of the first 255 characters.

I wrote it this way so you do not need to create an index on the txt field itself

What's the benefit? The auto_increment will not skip because I cause the trigger to break on purpose if a text is entered whose MD5 of the first 255 characters are not unique

Give it a Try !!!

Upvotes: 0

John Sykor
John Sykor

Reputation: 727

EDIT: Simply follow this tutorial and you should be set http://www.tutorialspoint.com/mysql/mysql-handling-duplicates.htm

The main points you already had, simply set TEXT as the Primary key and you won't have to worry as long as you do an Insert Ignore like you stated previously.

Upvotes: 1

Denys Séguret
Denys Séguret

Reputation: 382474

Make an UNIQUE index for your TEXT column :

errors will be ignored.

See http://dev.mysql.com/doc/refman/5.1/en/insert.html

Upvotes: 2

Related Questions