Solution to 1290 error when importing file data in mysql

Error scenario

Use the mysql command in cmd to add data to the student information table. Use the load data method to simply import data in batches.

Prepare text data: xueshengxinxi.txt file. Use the tab key to separate the data.

An error pops up when executing "load data infile text data path into table tab_load_data".

#load data (load data) syntax, to load data is as follows:
 1 Zhang Sannan Jiangxi 1
 2 Li Sinan Sichuan 2
 3 Wang Wunan Shanghai 1
 4 Zhao Liunu Hubei 3
 5 grandson seven daughter Hubei 3
 6 Zhou Ba Nan Hunan 1 

#Test data table create table tab_load_data (
  id int auto_increment primary key,
  name varchar(10),
  sex enum('male','female'),
  native varchar(10),
  f5 int
 ); 

Using load data to import file data always pops up an error (Error 1290.....) as follows:

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

Solution ideas (process)

1) Since the error message indicates that the secure-file-priv option is running and the load service cannot be executed, find the configuration item in the MYSQL configuration file "my.ini".

2) Before configuration, shut down the MySQL service

3) Open my.ini, search for the keyword "secure-file-priv" and find this option, then comment out this option, save it, and start the MYSQL service

4) The result is still the same error. Description The comment has no effect. What's going on?

I executed the command "show variables like '%secure%'" and found that after I commented it out, the value of secure-file-priv was NULL, indicating that import and export were prohibited.

This means that commenting it out does not work. If you don't want to delete the configuration file entry, you have to modify the directory.

5) Modify the path, execute the command "show variables like '%secure%'" to view, and restart MYSQL.

6) Execute load data again, the execution is successful

For more complex load usage, see the MySQL manual. At this point, the problem is solved.

3. Summary

Learn from this mistake:

1) load data: You can import data from an external Notepad file into a table.

The data in this notepad is usually required to be "relatively neat", with one line of text corresponding to one line of data, and a certain character (such as tab) is used to separate the values ​​of each field in a line.

2) The secure-file-priv parameter is used to limit the effects of data import and export operations.

For example: execute the LOAD, SELECT...INTO OUTFILE statement and the LOAD_FILE() function. These operations require the file permission.

3) If the secure-file-priv parameter is set to a directory name, the MYSQL service only allows file import and export operations to be performed in this directory.

This directory must exist, the MYSQL service will not create it.

4) If the secure-file-priv parameter is NULL, the MYSQL service will prohibit import and export operations.