Making a script to check, update and add a set of records to MySQL can be quite daunting. And even when done, the script will be slow. Luckily, there is a way to do this with a simple and fast SQL command.
1 2 3 4 5 6 7 8 9 10 | LOAD DATA LOCAL INFILE '/location/on/drive' -- the location of the CSV REPLACE INTO TABLE mytable -- table to 'replace' in to CHARACTER SET charset_name -- the character set the CSV is in FIELDS TERMINATED BY ';' -- character between fields OPTIONALLY ENCLOSED BY '"' -- character to enclose fields ESCAPED BY '"' -- excape character LINES TERMINATED BY '\n' -- end of line character IGNORE 1 LINES; -- ignore n lines, useful if first line is a header (id, name, location, @phonenumber) -- fields, use @ to name a field that does not corespond to a existing column SET contact=CONCAT('Phone number: ',@phonenumber) -- maybe use the new name to set a column value |
The keyword LOCAL is used to specify, that the given file is relative to the client, from which the script was run, otherwise, the path relative to the server would be used.
REPLACE replaces the lines, that have a matching unique field. IGNORE ignores them.
Resources: