MySQL Imports and Exports text data

MySQL writes data through common insert statements, such

Insert   Into Person Values (John, 20 ), (Li Si, 21 ), (Wang Wu, 70 )...;

However, sometimes to insert a large volume of data or exchange data more quickly, you need to import data from the text or export data to the text.

 

I,Create a test table and prepare data

 

First, create a table for testing student information. The fields include ID, name, age, city, and salary. No ID or name

Can be empty.

Create   Table Person (

IDInt Not NullAuto_increment,

NameVarchar(40)Not Null,

CityVarchar(20),

SalaryInt,

Primary Key(ID)

) Engine=InnoDB charset=Gb2312;

 

 

Create a table as follows:

 

Then write a text file for import: C: \ data.txt.

James 31 Beijing 3000

Li Si 25 Hangzhou 4000

Wang Wu 45 \ n 4500

James 29 Tianjin \ n

Each item is separated by a tab key. If this field is null, \ n is used.

 

II,Import Data

 

Enter the command to import the file.

Load Data local infile "C: / Data.txt"

Into TablePerson (name, age, city, salary );

 

The imported data is as follows:

Local indicates local. After execution, we can see that the NULL data is correctly imported.

 

III,Export data

 

Export the table as a text file: C: \ data_out.txt.

Select Name, age, city, salary

IntoOUTFILE "C:/Data_out.txt"

Lines terminatedBy"\ R \ n"

FromPerson;

 

Export data as follows:

Lines terminated by "\ r \ n" indicates that each row (that is, each record) is separated by \ r \ n, and \ r \ n is the window system.

Line Break. The imported data_out.txtis exactly the same as that of data.txt.

 

IV,Running Environment

 

Windows Vista Home Basic

MySQL 5.1.34-community

 

V,Note:

 

By default, the separation between fields and records (rows) is \ t (Tab) and \ n. But it can be changed, such:

Fields terminated By   ' , '   -- Fields are separated by commas (,).

Lines terminated By   ' ; '   -- Separated;

Note that the line breaks of other operating systems may be different from those of windows.