I want to load txt-files into a MySQL table and duplicates should be ignored based on several key-fields. Plus, I want one field to auto-increment so the load sequence can be tracked.
But since an auto-increment field is part of teh key, every loaded record is treated as unique, so duplicates are loaded as well! Why is the auto-increment field combined in the match of the duplicates, although I don't specify this field in the load cmd?
Here's an example of the txt-file (post, key1, key2)
20031105120000|k1|k2
20031105120011|k1|k2
20031105120000|k1|k3
Here's the LOAD DATA INFILE cmd
load data infile 'inbound.txt'
ignore
into table broker_inbound
fields terminated by '|'
(post,key1,key2)
And this is the table
CREATE TABLE broker_inbound (
rid int(11) NOT NULL auto_increment,
post timestamp(14) NOT NULL,
cycle timestamp(14) NOT NULL,
key1 char(32) NOT NULL default '',
key2 char(32) NOT NULL default '',
PRIMARY KEY (rid,post,key1,key2)
) TYPE=MyISAM;
But since an auto-increment field is part of teh key, every loaded record is treated as unique, so duplicates are loaded as well! Why is the auto-increment field combined in the match of the duplicates, although I don't specify this field in the load cmd?
Here's an example of the txt-file (post, key1, key2)
20031105120000|k1|k2
20031105120011|k1|k2
20031105120000|k1|k3
Here's the LOAD DATA INFILE cmd
load data infile 'inbound.txt'
ignore
into table broker_inbound
fields terminated by '|'
(post,key1,key2)
And this is the table
CREATE TABLE broker_inbound (
rid int(11) NOT NULL auto_increment,
post timestamp(14) NOT NULL,
cycle timestamp(14) NOT NULL,
key1 char(32) NOT NULL default '',
key2 char(32) NOT NULL default '',
PRIMARY KEY (rid,post,key1,key2)
) TYPE=MyISAM;