[Solved] MySQL Import csv File[Error Code] 1290 – The MySQL server is running with the –secure-file-priv option

Error: [error code] 1290 – the MySQL server is running with the — secure file priv option

mysql>show variables like '%secure%';;

secure_file_prive=null --Restrict mysqld to disallow imports and exports

secure_file_priv=/tmp/ -- Restrict mysqld import and export to occur only in the /tmp/ directory

secure_file_priv=' ' -- do not restrict mysqld import/export

Solution:

Open my.ini file: add: Secure File priv = “D/JB” to the file

1. Import the test.csv file into mysql

load data infile 'D:/jb/a.csv' -- CSV file storage path
into table test -- the name of the table into which the data is to be imported
fields terminated by ',' optionally enclosed by '"' escaped by '"' -- fields separated by commas, strings enclosed by double quotes, strings themselves enclosed by two double quotes
lines terminated by '\r\n'; -- data lines are separated by \r\n

The successful results are as follows:

2. Import tes.csv file into MySQL (including Chinese)

a. Open the CSV file with a text editor, save it in utf8 format, and then import it

load data infile 'D:/jb/a.csv' -- CSV file storage path
into table test character set 'utf8' -- the name of the table to import the data into, set the encoding
fields terminated by ',' optionally enclosed by '"' escaped by '"' -- fields separated by commas, strings enclosed by double quotes, strings themselves enclosed by two double quotes
lines terminated by '\r\n'; -- data lines are separated by \r\n

3. Export the data in the library to a CSV file (including Chinese)

select * from test 
into outfile 'D:/jb/b.csv' character set 'gbk'
FIELDS TERMINATED BY ','   
OPTIONALLY ENCLOSED BY '"'   
LINES TERMINATED BY '\r\n';

The results are as follows:

Similar Posts: