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.