原文来自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中。
各操作时间已经显示在图中，速度比我预想得要快得多！ |
收藏
淘帖
踩
