Importing txt files to mysql

MySQL usually uses insert statements to write data, such as [c-sharp] viewplaincopyinsertintopersonvalues (John 3, 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 the data

MySQL usually uses insert statements to write data, such as [c-sharp] view plaincopy insertintopersonvalues (John 3, 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 the data

MySQL writes data through common insert statements, such

[C-sharp]View plaincopy

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

[C-sharp]View plaincopy

1. Create table person (
2. Id int not null auto_increment,
3. Name varchar (40) not null,
4. City varchar (20 ),
5. Salary int,
6. Primary key (id)
7. ) 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.

[C-sharp]View plaincopy

1. Load data local infile "c:/data.txt"
2. 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.

[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 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

The separation between fields and records (rows) is/t (Tab) And/n by default. But it can be changed, such:
Fields terminated by ',' -- use, to separate FIELDS
Lines terminated by ';' -- records are separated;
Note that the line breaks of other operating systems may be different from those of windows.