MySQL Import txt file

Article Source: http://blog.csdn.net/piaolankeke/article/details/6272214

MySQL writes data typically with INSERT statements, such as

[C-sharp]View Plaincopy

1. 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.

[C-sharp]View Plaincopy

1. CREATE TABLE Person (
2. ID int not null auto_increment,
3. Name varchar (+) is not NULL,
4. City varchar (20),
5. Salary int,
6. Primary KEY (ID)
7. ) Engine=innodb charset=gb2312;

Create the table as follows:

Then write a text file for import: C:/data.txt.
Zhang 331 Beijing 3000
Lee 425 Hangzhou 4000
Harry 45/n 4500
Xiao Ming 29 Tianjin/N

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.

[C-sharp]View Plaincopy

1. Load data local infile "C:/data.txt"
2. into table person (name,age,city,salary);

Import data as follows:

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.

[C-sharp]View Plaincopy

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

Export the data as follows:

Where lines terminated by "/r/n" means each row (i.e. each record) is separated by/r/n,/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 between/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.

About MySQL LOAD DATA LOCAL infile support Issue 1: Error 1148 (42000): The used command is not allowed with this MySQL version workaround: First, go to MySQL and check the version number: 5.1 or more are supported. Mysql> Select Version () 1. This command is not allowed by default because MySQL compiles the installation.  1) Add the--enable-local-infile parameter to the installation and execute it./configure--prefix=/usr/local/mysql--enable-local-infilemake www.2cto.com make install 2. Can be resolved with the execution hit plus the--local-infile=1 parameter. Example:/usr/local/mysql/bin/mysql-uroot-h192.168.0.2-proot databaseName--local-infile=1-e "LOAD DATA local infile ' DATA.T XT ' into table test (name,sex)

MySQL Import txt file (GO)