MySQL text file Import Export data SQL Demo

MySQL writes data usually with INSERT statements, such as

INSERT into Person VALUES (John, 20), (Dick, 21), (Harry,) ...;

Sometimes, however, you need to import data from text or export data to text in order to insert large amounts of data or exchange data more quickly.
First, set up the test table, prepare the data

First, create a table for testing that represents student information, with the ID, name, age, city, and salary in the field. ID and name are not
can be empty.

The code is as follows	Copy Code
CREATE TABLE Person ( ID INT not NULL auto_increment, Name VARCHAR not NULL, City VARCHAR (20), Salary INT, PRIMARY KEY (ID) ) Engine=innodb charset=gb2312;

Create a table screenshot as follows:

Next, write a text file for import: C:data.txt.

Zhang 331 Beijing 3000
Lee 425 Hangzhou 4000
Wang 545 N 4500
Xiao Ming 29 Tianjin N

Each item is delimited by the TAB key, and if the field is null, it is represented by N.

II. Import of data

Enter a command to import.

The code is as follows	Copy Code
LOAD DATA local INFILE "C:/data.txt" into the TABLE person (name,age,city,salary);

The screenshot of the import data is as follows:

Where local is represented locally. After execution, you can see that the null data is also imported correctly.

III. Export of data

Now 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;

The export data screenshot is as follows:

Where lines terminated by "RN" means that each row (that is, each record) is separated by RN, RN is the window System
Line break of the EC. The exported data_out.txt is exactly the same as the data.txt content.

Of course, we can also use the mysqldump command import export, this is the standard MySQL data Import Export command.

The first step is to export the old library

The code is as follows	Copy Code
mysqldump--default-character-set latin1-uroot-pxxx--database db >/tmp/old.sql

The second step is to convert the code

The code is as follows	Copy Code
Iconv-t Utf-8-F gbk-c/tmp/old.sql >/tmp/new.sql

Step three, import the new library

Modify New.sql, add an SQL statement: "SET NAMES UTF8;", save.

Four, the operating environment

Windows Vista Home Basic
MySQL 5.1.34-community

Five, attention

The separation between fields and the separating of records (rows) defaults to t (that is, tab) and N. But can change, for example:
FIELDS terminated by ', ' – field used, delimited
LINES terminated by '; ' – recorded; to separate
Also be aware that line breaks for other operating systems may not be the same as Windows.