in mysql,how to avoid loading the similar data file into table if already exist
i am using the following command
LOAD DATA INFILE '/sample.txt' INTO TABLE TEST FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';
whenever executed this query ,table "TEST" gets added the values from sample.txt file.
How to avoid this mul开发者_运维知识库tiple entries in table.i want to load this file if table doesn't load this file already.
diEcho's comment is nearly right - identify unique column combinations in the data and declare them as a unique index on the table - but importantly you also need to modify your LOAD DATA statement - as its stands, it will fall over at the first occurrence of a duplicate record. You need to add an IGNORE:
LOAD DATA INFILE '/sample.txt' IGNORE INTO TABLE `test`
FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';
Although for preference you should consider loading the full dataset into a staging table then handle the duplicates a bit more gracefully.
精彩评论