Environment
- MySQL 5.7
LOAD DATA INFILE
If you want to import CSV data into MySQL table, it’d be good to use MySQL LOAD DATA INFILE.
ref. MySQL :: MySQL 5.7 Reference Manual :: 13.2.6 LOAD DATA INFILE Syntax
Let’s use it on the command line.
$ mysql -u root -e "LOAD DATA INFILE './data.csv' INTO TABLE db_development.users(email, password);"
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
But I’ve got an error, which says The MySQL server is running with the –secure-file-priv option.
LOAD DATA LOCAL INFILE
A stackoverflow answer says it’s good to use LOAD DATA LOCAL.
$ mysql -u root -e "LOAD DATA LOCAL INFILE './data.csv' INTO TABLE db_development.users(email, password);"
ERROR 1148 (42000) at line 1: The used command is not allowed with this MySQL version
Hmm, we’ve got another error, which says The used command is not allowed with this MySQL version.
local-infile option
Another answer says it should be used with --local-infile option.
$ mysql -u root --local-infile -e "LOAD DATA LOCAL INFILE './data.csv' INTO TABLE db_development.users(email, password);"
It worked, but imported data was a bit wrong because I didn’t specify fields separator. The CSV data was separated by comma(,), so let’s specify comma.
$ mysql -u root --local-infile -e "LOAD DATA LOCAL INFILE './data.csv' INTO TABLE db_development.users FIELDS TERMINATED BY ',' (email, password);"
It properly worked!