Shulou(Shulou.com)05/31 Report--

This article mainly introduces MySQL 5.5 how to use LOAD DATA INFILE statement to import data, has a certain reference value, interested friends can refer to, I hope you read this article after a lot of gains, the following let Xiaobian with everyone to understand.

Related parameters:

localRead text files from client hosts

ignore If you specify this parameter, duplicate records will be skipped

replace If you specify this parameter, duplicate records will be overwritten

IGNORE number LINES Skip the first few lines specified when importing

Example: LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test IGNORE 1 LINES;

FIELDS TERMINATED BY Specifies column delimiter, default is\t

ENCLOSED BY The inclusion character of the specified column, the default is empty

Example: load data infile '/tmp/load_starting.txt' into table load_test fields terminated by ',' optionally enclosed by '"' lines starting by 'Neo';

ESCAPED BY Specifies escape character, default is\

Example: load data infile '/tmp/ld_t2.csv' into table ld_t2 fields terminated by ',' enclosed by '' escaped by '|';

LINES TERMINATED BY Specifies newline character, default is\n

Example: load data infile '/tmp/ld_t5.csv' into table ld_t5 fields terminated by ',' enclosed by '' escaped by '|' lines terminated by '\r\n';

LINES STARTING BY Specifies the character at the beginning of each line

Note:

Each time the LOAD DATA INFILE statement is executed, the data in the imported table is cleared.

--Import Example

--View the contents of the imported file

[root@localhost fire]# cat /tmp/dept.txt

10,ACCOUNTING,NEW YORK

20,RESEARCH,DALLAS

30,SALES,CHICAGO

40,OPERATIONS,BOSTON

50,MAINTENANCE,HULUDAO

--Create tables based on field formats

mysql> CREATE TABLE `test` (

-> `deptno` int(11) NOT NULL DEFAULT '0',

-> `dname` varchar(15) NOT NULL,

-> `loc` varchar(15) NOT NULL

-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.20 sec)

--Use root to import the contents of a text file into the corresponding table

mysql> use fire

Database changed

mysql> load data infile '/tmp/dept.txt' into table test charset gbk fields terminated by ',';

Query OK, 5 rows affected (0.07 sec)

Records: 5 Deleted: 0 Skipped: 0 Warnings: 0

--Query the contents of the imported table

mysql> select * from test;

+--------+-------------+-----------+

| deptno | dname | loc |

+--------+-------------+-----------+

| 10 | ACCOUNTING | NEW YORK

| 20 | RESEARCH | DALLAS

| 30 | SALES | CHICAGO

| 40 | OPERATIONS | BOSTON

| 50 | MAINTENANCE | HULUDAO

+--------+-------------+-----------+

5 rows in set (0.00 sec)

--Specify column name import

load data infile '/tmp/ld_t7.csv' into table ld_t7 fields terminated by ',' enclosed by '"' (id, uname, email, age);

Thank you for reading this article carefully. I hope that the article "MySQL 5.5 How to import data using LOAD DATA INFILE statement" shared by Xiaobian will be helpful to everyone. At the same time, I hope that everyone will support it a lot and pay attention to the industry information channel. More relevant knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.