LOAD DATA

当从一个文本文件装载一个表时，使用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)

 

注：执行成功