MySQL 5.5使用LOAD DATA INFILE语句导入数据

野性酷女 2024-04-18 02:17 70阅读 0赞

**相关参数：**  
  
 local 从客户端主机读取文本文件  
 ignore 如果你指定了这个参数，则重复的记录会被跳过  
 replace 如果你指定了这个参数，，则重复的记录会被覆盖掉  
 IGNORE number LINES 导入的时候跳过指定的前几行  
 例如：LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test IGNORE 1 LINES;  
 FIELDS TERMINATED BY 指定列的分隔符，默认是\\t  
 ENCLOSED BY 指定列的包括符，默认是空  
 例如：load data infile '/tmp/load\_starting.txt' into table load\_test fields terminated by ',' optionally enclosed by '"' lines starting by 'Neo';  
 ESCAPED BY 指定转义符，默认是\\  
 例如：load data infile '/tmp/ld\_t2.csv' into table ld\_t2 fields terminated by ',' enclosed by '"' escaped by '|';  
 LINES TERMINATED BY 指定换行符，默认是\\n  
 例如：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 指定每行开始的字符  
  
注意：  
每次执行LOAD DATA INFILE语句，都会将被导入表中的数据清空。  
  
\--导入示例  
 \--查看导入文件文件的内容  
\[root@localhost fire\]\# cat /tmp/dept.txt   
 10,ACCOUNTING,NEW YORK  
 20,RESEARCH,DALLAS  
 30,SALES,CHICAGO  
 40,OPERATIONS,BOSTON  
 50,MAINTENANCE,HULUDAO  
  
\--根据字段格式创建表  
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)  
  
\--使用root用户导入文本文件中的内容到对应的表中  
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  
  
\--查询导入表中的内容  
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)  
  
\--指定列名导入  
load data infile '/tmp/ld\_t7.csv' into table ld\_t7 fields terminated by ',' enclosed by '"' (id, uname, email, age);

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2084878/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2084878/