How to import and export data from a MySQL text file

MySQL writes data typically with INSERT statements, such as

The code is as follows:

INSERT into person values (Zhang San, 20), (John Doe, 21), (Harry, 70) ...;

But sometimes in order to insert large quantities of data or exchange data more quickly, you need to import data from text or export data to text.
first, set up the test table, prepare the data
First set up a table for testing the student information, the fields are ID, name, age, city, salary. ID and name are not
can be empty.

The code is as follows:

CREATE TABLE Person (
ID int NOT NULL auto_increment,
Name varchar (+) is not NULL,
City varchar (20),
Salary int,
Primary KEY (ID)
) Engine=innodb charset=gb2312;

Create the table as follows:
650) this.width=650; "Width=" 484 "height=" "title=" 1 "style=" border-right:0px;border-top:0px;border-left:0px; border-bottom:0px; "alt=" 1 "src=" http://files.jb51.net/upload/2009-11/20091126184828329.png "border=" 0 "/>
Then write a text file for import: C:\data.txt.
Zhang 331 Beijing 3000
Lee 425 Hangzhou 4000
Wang 545 \ 4500
Xiao Ming 29 tianjin \ n
650) this.width=650; "Width=" 465 "height=" 203 "title=" 3 "style=" border-right:0px;border-top:0px;border-left:0px; border-bottom:0px; "alt=" 3 "src=" http://files.jb51.net/upload/2009-11/20091126184828654.png "border=" 0 "/>
The TAB key separates each item, and if the field is null, it is represented by \ n.
second, import data
Enter a command to import.
Load data local infile "C:/data.txt"
into table person (name,age,city,salary);
Import data as follows:
650) this.width=650; "Width=" 477 "height=" 312 "title=" 2 "style=" border-right:0px;border-top:0px;border-left:0px; border-bottom:0px; "alt=" 2 "src=" http://files.jb51.net/upload/2009-11/20091126184828918.png "border=" 0 "/>
Where local is represented locally. After execution, you can see that the null data is also imported correctly.
third, export data

Now export this table as a text file: C:\data_out.txt.

The code is as follows:

Select Name,age,city,salary
into outfile "C:/data_out.txt"
Lines terminated by "\ r \ n"
from person;

Export the data as follows:
650) this.width=650; "Width=" 453 "height=" 296 "title=" 4 "style=" border-right:0px;border-top:0px;border-left:0px; border-bottom:0px; "alt=" 4 "src=" http://files.jb51.net/upload/2009-11/20091126184828661.png "border=" 0 "/>
Where lines terminated by "\ r \ n" means that each line (i.e. each record) is separated by \ r \ r \ n is the window system
Line break for the system. The exported data_out.txt is exactly the same as the contents of Data.txt.
Four, the operating environment

Windows Vista Home Basic
MySQL 5.1.34-community
Five, Attention

The separation between fields and records (rows) is the default of \ t (that is, tab) and \ n. But can be changed, such as:
Fields TERMINATED by ', '--field, delimited
LINES TERMINATED by '; '--record employment; to separate
Also note that other operating systems may not have the same line break as Windows.

How to import and export data from a MySQL text file