黑马程序员技术交流社区

标题: mysql load data应用 [打印本页]

作者: LiuKang 时间: 2013-11-16 20:57
标题: mysql load data应用
原文来自http://www.linuxidc.com/Linux/2011-06/36947.htm
mysql> desc t;
+-----------------+---------------------+------+-----+---------+-------+
| Field          | Type             | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(512)       | YES  |    | NULL |    |
| TABLE_SCHEMA | varchar(64)       | NO |    | NULL |    |
| TABLE_NAME    | varchar(64)       | NO |    | NULL |    |
| TABLE_TYPE    | varchar(64)       | NO |    | NULL |    |
| ENGINE       | varchar(64)       | YES  |    | NULL |    |
| VERSION       | bigint(21) unsigned | YES  |    | NULL |    |
| ROW_FORMAT    | varchar(10)       | YES  |    | NULL |    |
| TABLE_ROWS    | bigint(21) unsigned | YES  |    | NULL |    |
| AVG_ROW_LENGTH  | bigint(21) unsigned | YES  |    | NULL |    |
| DATA_LENGTH    | bigint(21) unsigned | YES  |    | NULL |    |
| MAX_DATA_LENGTH | bigint(21) unsigned | YES  |    | NULL |    |
| INDEX_LENGTH | bigint(21) unsigned | YES  |    | NULL |    |
| DATA_FREE    | bigint(21) unsigned | YES  |    | NULL |    |
| AUTO_INCREMENT  | bigint(21) unsigned | YES  |    | NULL |    |
| CREATE_TIME    | datetime          | YES  |    | NULL |    |
| UPDATE_TIME    | datetime          | YES  |    | NULL |    |
| CHECK_TIME    | datetime          | YES  |    | NULL |    |
| TABLE_COLLATION | varchar(32)       | YES  |    | NULL |    |
| CHECKSUM       | bigint(21) unsigned | YES  |    | NULL |    |
| CREATE_OPTIONS  | varchar(255)       | YES  |    | NULL |    |
| TABLE_COMMENT | varchar(80)       | NO |    | NULL |    |
+-----------------+---------------------+------+-----+---------+-------+
21 rows in set (0.04 sec)
mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
| 502564 |
+----------+
1 row in set (0.00 sec)
mysql> select * from t into outfile '/tmp/t.txt' fields terminated by ',' enclosed by '"';
Query OK, 502564 rows affected (5.09 sec)
mysql> create table t1 select * from t where 1<>1;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> select * from t1;
Empty set (0.01 sec)
mysql> load data infile '/tmp/t.txt' into table t1 fields terminated by ',' enclosed by '"';
Query OK, 502564 rows affected (8.73 sec)
Records: 502564  Deleted: 0  Skipped: 0  Warnings: 0
mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
| 502564 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 502564 |
+----------+
1 row in set (0.00 sec)
mysql>
图1 用 information_schema.tables来生成测试表t.

[root@rhel5 ~ 05:04:56]#ll -h /tmp
total 88M
-rw-rw-rw- 1 mysql  mysql    80 Sep 21 16:16 a.txt
drwx------ 2 root root    4.0K Feb 18  2015 keyring-mViyDu
srwxr-xr-x 1 Oracle oinstall 0 Aug  3  2010 mapping-oracle
srwxr-xr-x 1 root root       0 Feb 18  2015 mapping-root
-rw-rw---- 1 mysql  mysql 2.8K Sep 21 16:56 mysqlslow.log
srwxrwxrwx 1 mysql  mysql    0 Sep 21 15:17 mysql.sock
srw------- 1 oracle oinstall 0 Aug  3  2010 scim-panel-socket:0-oracle
srw------- 1 root root       0 Feb 18  2015 scim-panel-socket:0-root
drwx------ 2 root root    4.0K Sep 21 17:02 ssh-kahSRu6355
drwx------ 2 root root    4.0K Sep 21 16:16 ssh-XCGOuG6217
-rw-rw-rw- 1 mysql  mysql    88M Sep 21 17:01 t.txt
图2 测试表t导出数据文件为t.txt

测试过程如下:

用 insert into t select * from t; 不断插入数据直到其记录数为50万条。

然后把t的数据导入到文本文件中。

用loaddata命令把文本文件中的数据导入到表t2中。

各操作时间已经显示在图中，速度比我预想得要快得多！

作者: 狼王 时间: 2013-11-16 21:09
现在还看不懂，哈哈

作者: LiuKang 时间: 2013-11-17 21:25

狼王发表于 2013-11-16 21:09
现在还看不懂，哈哈

没关系，大家来黑马就是为了学习的，如果都会了就不用来黑马了，你说呢

作者: 狼王 时间: 2013-11-17 21:51

LiuKang 发表于 2013-11-17 21:25
没关系，大家来黑马就是为了学习的，如果都会了就不用来黑马了，你说呢 ...

恩，我还要好好努力，你也好好努力哈

作者: LiuKang 时间: 2013-11-17 22:04

狼王发表于 2013-11-17 21:51
恩，我还要好好努力，你也好好努力哈

好的，兄弟们一起加油哈

欢迎光临黑马程序员技术交流社区 (http://bbs.itheima.com/)

黑马程序员IT技术论坛 X3.2