MySQL Imports and Exports text data

MySQL writes data through common insert statements, such:

Insert into person values (Zhang San, 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.

1. Create a test table and prepare data

First, create a table for testing student information. The fields include id, name, age, city, and salary. The 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;

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 table person (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 Into outfile "c:/data_out.txt" Lines terminated by "\ r \ n" From person;

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. Runtime Environment

Windows vista home basic

MySQL 5.1.34-community

5. Note

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

Fields terminated by ',' -- use, to separate FIELDS Lines terminated by ';' -- records are separated;

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