import csv file into mysql without workbench-CodePudding

I am try to import csv file into mysql in command prompt without workbench. But it shows error like ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement. what is the best way to import csv file.

what is best way to resolve the error.

CodePudding user response：

Try this:

LOAD DATA LOCAL
    INFILE 'yourPATH/table.csv'
    INTO TABLE `table`
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    LINES TERMINATED BY '\r\n'
    IGNORE 0 LINES;

Use this to find your path:

SHOW VARIABLES LIKE "secure_file_priv";

CodePudding user response：

Try this to turn off the option '--secure-file-priv':

In my.cnf file, add the following line under [mysqld]:

secure_file_priv=

Generally my.cnf file is at /etc/mysql/my.cnf, or you can search it by:

sudo find /* -name my.cnf