当从一个文本文件装载一个表时,使用LOAD DATA INFILE。这通常比使用很多INSERT语句快20倍。
LOAD语法和ORACLE的SQLLOADER有些类似:
LOAD DATA [LOW_PRIORITY] [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE]
INTO TABLE tbl_name
[FIELDS
[TERMINATED BY '\t']
[OPTIONALLY] ENCLOSED BY '']
[ESCAPED BY '\\' ]]
[LINES TERMINATED BY '\n']
[IGNORE number LINES]
[(col_name,...)]
mysql> select * from t;
+-------+------+
| id | MC |
+-------+------+
| 2 | NULL |
| 3 | NULL |
| 4 | NULL |
| 5 | NULL |
| 6 | NULL |
| 7 | NULL |
| 8 | NULL |
| 9 | NULL |
| 1 | MC |
| 10000 | MC |
+-------+------+
10 rows in set (0.00 sec)
mysql> load data infile 'd:\\test.txt' into table t;
Query OK, 2 rows affected (0.05 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from t;
+-------+---------+
| id | MC |
+-------+---------+
| 2 | NULL |
| 3 | NULL |
| 4 | NULL |
| 5 | NULL |
| 6 | NULL |
| 7 | NULL |
| 8 | NULL |
| 9 | NULL |
| 100 | 111111
| 200 | 222222 |
| 1 | MC |
| 10000 | MC |
+-------+---------+
12 rows in set (0.00 sec)
1)创建表
create table t(id int)
2)创建文件test.txt
[root@SR3 ~]# more /tmp/test.txt
1
2
3
3)加载数据
mysql> load data infile '/tmp/test.txt' into table t;
Query OK, 3 rows affected (0.02 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
注,加载数据完成
另一个实例:
1)创建表
create table test ( id int,name VARCHAR(50))
2)编辑文件
[root@SR3 ~]# more /tmp/db.txt
1,wangmin
2,zhangsan
3)加载数据
mysql> use clas;
Database changed
mysql> Load Data InFile '/tmp/db.txt' Into Table test Fields Terminated By ',';
Query OK, 2 rows affected (0.00 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0
4)查看数据
mysql> select * from test;
+------+----------+
| id | name |
+------+----------+
| 1 | wangmin |
| 2 | zhangsan |
+------+----------+
2 rows in set (0.00 sec)
5)在客户端执行命令
mysql> select * from test;
+------+----------+
| id | name |
+------+----------+
| 1 | wangmin |
| 2 | zhangsan |
| 1 | wangmin |
| 2 | zhangsan |
+------+----------+
4 rows in set (0.00 sec)
注:执行成功