MySQL 5.5How to import data using LOAD DATA INFILE statements
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!
