load语句导入数据到mysql_使用load data导入数据到mysql

除了mysqlimport方式导入数据到mysql，用得更多是load data语句，其功能类似与Oracle的sqlload。详细的用法可以help load data查看。

1、准备导入数据

[root@D2-LZY245 ~]# cat /tmp/emp.txt

100,Steven,King,24000.00

101,Neena,Kochhar,17000.00

102,Lex,De Haan,17000.00

103,Alexander,Hunold,9000.00

104,Bruce,Ernst,6000.00

105,David,Austin,4800.00

106,Valli,Pataballa,4800.00

107,Diana,Lorentz,4200.00

108,Nancy,Greenberg,12008.00

109,Daniel,Faviet,9000.00

110,John,Chen,8200.00

2、创建InnoDB引擎表

mysql> create table emp1(

-> employee_id int(10),

-> first_name varchar(50),

-> last_name varchar(50),

-> salary decimal(10,2)) engine=innodb charset=utf8;

Query OK, 0 rows affected (0.00 sec)

3、导入数据

mysql> load data infile '/tmp/emp.txt' into table emp1 charset utf8 fields terminated by ',';

Query OK, 11 rows affected (0.00 sec)

Records: 11  Deleted: 0  Skipped: 0  Warnings: 0

4、查看结果

mysql> select * from emp1;

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

| employee_id | first_name | last_name | salary   |

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

|         100 | Steven     | King      | 24000.00 |

|         101 | Neena      | Kochhar   | 17000.00 |

|         102 | Lex        | De Haan   | 17000.00 |

|         103 | Alexander  | Hunold    |  9000.00 |

|         104 | Bruce      | Ernst     |  6000.00 |

|         105 | David      | Austin    |  4800.00 |

|         106 | Valli      | Pataballa |  4800.00 |

|         107 | Diana      | Lorentz   |  4200.00 |

|         108 | Nancy      | Greenberg | 12008.00 |

|         109 | Daniel     | Faviet    |  9000.00 |

|         110 | John       | Chen      |  8200.00 |

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

11 rows in set (0.00 sec)