How to import txt file in mysql?

Sometimes when we use mysql database, we want to import a txt text document. How to do it? The following article will introduce the method to you, I hope it will be helpful to you.

1. Prepare data and create a test table

1. Prepare the txt text document that needs to be imported. The path is: c :/data.txt.

Use the Tab key to separate each item. If the field is NULL, use /N to indicate it.

2. Create a new database and a table. The fields designed in the table need to correspond to the number of columns in the imported text document.

Example: Create a table representing student information for testing. The fields include id, name, age, city, and salary. Id and name cannot be empty.

create table person(
id int not null auto_increment,
name varchar(40) not null,
city varchar(20),
salary int,
primary key(id)
)engine=innodb charset=gb2312;

The screenshot of the created table is as follows:

2. Import data

Enter the command to import:

load data local infile “c:/data.txt”  
into table person(name,age,city,salary);

The screenshot of the imported data is as follows:

where local means local. After execution, you can see that NULL data is also imported correctly.

3. Export data

Now export this table to a text file: c:/data_out.txt.

select name,age,city,salary  
into outfile “c:/data_out.txt”  
lines terminated by “/r/n”  
from person;

The screenshot of the exported data is as follows:

where lines terminated by “/r/n” means that each line (i.e. each record) uses /r/ n separates, /r/n is the newline character of the window system. The contents of the exported data_out.txt are exactly the same as data.txt.

The above is the detailed content of How to import txt file in mysql?. For more information, please follow other related articles on the PHP Chinese website!