SQL demonstration of MySQL text file import and export data

Most of us will use the mysqldump command to import and export data in mysql. Today I want to introduce other commands to import and export text file commands, hoping to help you.

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. No Id or name
Can be empty.
 

The Code is as follows:	Copy code
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 the field is NULL, N is used.

Ii. Import Data

Enter the command to import the file.

The Code is as follows:	Copy code
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.

The Code is as follows:	Copy code
SELECT name, age, city, salary Into outfile "c:/data_out.txt" Lines terminated by "rn" FROM person;

Export data as follows:

Lines terminated by "rn" indicates that each row (that is, each record) is separated by rn, And rn is the window system.
Line Break. The imported data_out.txtis exactly the same as that of data.txt.

Of course, you can also use the mysqldump command to import and export data. This is the standard mysql Data Import and Export command.

Step 1: export the old database

The Code is as follows:	Copy code
Mysqldump -- default-character-set latin1-uroot-pXXX -- database db>/tmp/old. SQL

Step 2: Convert the Encoding

The Code is as follows:	Copy code
Iconv-t UTF-8-f gbk-c/tmp/old. SQL>/tmp/new. SQL

Step 3: import the new database

Modify new. SQL and add an SQL statement: "SET NAMES utf8;" to save the statement.

Iv. Runtime Environment

Windows vista home basic
MySQL 5.1.34-community

5. Note

Fields and records (rows) are separated by t (Tab) and n by default. But it can be changed, such:
Fields terminated by ','-FIELDS are separated
Lines terminated by ';'-records are separated;
Note that the line breaks of other operating systems may be different from those of windows.