MySQL writes data through common insert statements, such
CopyCode The Code is as follows: 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.Copy codeThe Code is as follows: 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.Copy codeThe Code is as follows: 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
separated fields and records (rows) are \ t (Tab) and \ n by default. But it can be changed, such as:
fields terminated by ',' -- use, to separate the fields
lines terminated by ';' -- used for record;
note that the line breaks of other operating systems may be different from those of windows.