10. load数据到数据库,这点跟oracle的sqlldr相似。
在load数据到表上时,myisam引擎会自动disable掉这个表上的非唯一索引,待导入结束后会enable这个索引。
做个试验,先将表t的数据导出到一个文本中:
mysql> drop table t ;
Query OK, 0 rows affected (0.00 sec)
mysql> create table t (id int,name varchar(20)) engine=myisam;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t values (1,'a'),(2,'b'),(3,'c');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from t;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | b |
| 3 | c |
+------+------+
3 rows in set (0.00 sec)
mysql> select * from t into outfile '/tmp/t.txt' fields terminated by ',' enclosed by '"';
Query OK, 3 rows affected (0.00 sec)
表的数据已导出到一个文本文件中了。
mysql> system cat /tmp/t.txt
"1","a"
"2","b"
"3","c"
现在试验一下load文本文件到表中:
mysql> truncate table t;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t;
Empty set (0.00 sec)
mysql> load data infile '/tmp/t.txt' into table t fields terminated by ',' enclosed by '"';
Query OK, 3 rows affected (0.00 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from t;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | b |
| 3 | c |
+------+------+
3 rows in set (0.00 sec)
11.当磁盘的空间耗尽时,不会象Oracle那样会报错,而是等待增加磁盘空间后会继续完成进程。