How to import and export data from a MySQL text file

Source: Internet
Author: User
Tags mysql text

MySQL writes data typically with INSERT statements, such as

The code is as follows:


INSERT into person values (Zhang San, 20), (John Doe, 21), (Harry, 70) ...;


But sometimes in order to insert large quantities of data or exchange data more quickly, you need to import data from text or export data to text.
first, set up the test table, prepare the data
First set up a table for testing the student information, the fields are ID, name, age, city, salary. ID and name are not
can be empty.

The code is as follows:


CREATE TABLE Person (
ID int NOT NULL auto_increment,
Name varchar (+) is not NULL,
City varchar (20),
Salary int,
Primary KEY (ID)
) Engine=innodb charset=gb2312;


Create the table as follows:
650) this.width=650; "Width=" 484 "height=" "title=" 1 "style=" border-right:0px;border-top:0px;border-left:0px; border-bottom:0px; "alt=" 1 "src=" http://files.jb51.net/upload/2009-11/20091126184828329.png "border=" 0 "/>
Then write a text file for import: C:\data.txt.
Zhang 331 Beijing 3000
Lee 425 Hangzhou 4000
Wang 545 \ 4500
Xiao Ming 29 tianjin \ n
650) this.width=650; "Width=" 465 "height=" 203 "title=" 3 "style=" border-right:0px;border-top:0px;border-left:0px; border-bottom:0px; "alt=" 3 "src=" http://files.jb51.net/upload/2009-11/20091126184828654.png "border=" 0 "/>
The TAB key separates each item, and if the field is null, it is represented by \ n.
second, import data
Enter a command to import.
Load data local infile "C:/data.txt"
into table person (name,age,city,salary);
Import data as follows:
650) this.width=650; "Width=" 477 "height=" 312 "title=" 2 "style=" border-right:0px;border-top:0px;border-left:0px; border-bottom:0px; "alt=" 2 "src=" http://files.jb51.net/upload/2009-11/20091126184828918.png "border=" 0 "/>
Where local is represented locally. After execution, you can see that the null data is also imported correctly.
third, export data

Now export this table as a text file: C:\data_out.txt.

The code is as follows:


Select Name,age,city,salary
into outfile "C:/data_out.txt"
Lines terminated by "\ r \ n"
from person;


Export the data as follows:
650) this.width=650; "Width=" 453 "height=" 296 "title=" 4 "style=" border-right:0px;border-top:0px;border-left:0px; border-bottom:0px; "alt=" 4 "src=" http://files.jb51.net/upload/2009-11/20091126184828661.png "border=" 0 "/>
Where lines terminated by "\ r \ n" means that each line (i.e. each record) is separated by \ r \ r \ n is the window system
Line break for the system. The exported data_out.txt is exactly the same as the contents of Data.txt.
Four, the operating environment

Windows Vista Home Basic
MySQL 5.1.34-community
Five, Attention

The separation between fields and records (rows) is the default of \ t (that is, tab) and \ n. But can be changed, such as:
Fields TERMINATED by ', '--field, delimited
LINES TERMINATED by '; '--record employment; to separate
Also note that other operating systems may not have the same line break as Windows.


How to import and export data from a MySQL text file

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.