使用load导出mysql表数据

　　当我们需要备份数据库时，需要导出数据，我们一般是使用mysqldump命令来进行数据的导出，下面我们先来试验一下，

　　首先创建一张表

CREATE TABLE `t1` (  `id` int(11) DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8

创建t1表，只有一个字段，往数据表中插入几行数据

insert into t1 values(1),(2),(3);

然后开始备份

可以看到已经导出的数据

下面这样我们就备份完毕了，我们可以查看一下test.sql

--
-- Table structure for table `t1`
--


DROP TABLE IF EXISTS `t1`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;


--
-- Dumping data for table `t1`
--


LOCK TABLES `t1` WRITE;
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
INSERT INTO `t1` VALUES (1),(2),(3);
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

这只是一部分，我们可以看到里面有一大堆表结构的语句和表创建的语句，当我们的数据量非常大的时候，导出的速度就可想而知，于是我们就需要在导出的时候避开这些语句，这里就要用到load的infile和outfile来导入导出数据

我们先来看下outfile的语法

Syntax:
SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
      | INTO DUMPFILE 'file_name'
      | INTO var_name [, var_name]]
    [FOR UPDATE | LOCK IN SHARE MODE]]

下面举个例子

select * from t1 into outfile "/tmp/test.sql"

这样就可以把t1表的所有列都导出来，当然，也可以只导出某一列的数据

这是导出，下面看下导入

导入要用到我们的load data来实现，看下语法

Syntax:
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number LINES]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...]

同样的一个例子来说明

load data infile "/tmp/test.sql" into table t1

或者导入到某一列或几列中

load data infile "/tmp/test.sql" into table t1(id)

下面进行一下实际操作，在t1表中插入1000万行数据

mysql> select count(*) from t1;        
+----------+
| count(*) |
+----------+
| 10485760 |
+----------+
1 row in set (0.00 sec)

下面来导出数据

mysql> select * from t1 into outfile "/tmp/outfile.sql";
Query OK, 10485760 rows affected (2.43 sec)

发现导出的速度非常快，1000万行数据只需要2秒多

下面看一下outfile.sql中的数据是什么样子的

我们发现，文件中只有数据，而不存在表的结构和其他不相干的语句，这样就大大减少了导出数据的量

下面来导入数据，这里需要注意的是，有outfile导出的数据不包含表结构，所以，我们不能够删除表或者数据库来进行操作

下面我们清空一下t1表，进行一下操作

mysql> truncate t1;
Query OK, 0 rows affected (0.12 sec)


mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

下面使用load data infile来导入

mysql> load data infile "/tmp/outfile.sql" into table t1;    
Query OK, 10485760 rows affected (12.46 sec)
Records: 10485760  Deleted: 0  Skipped: 0  Warnings: 0


mysql> select count(*) from t1;                      
+----------+
| count(*) |
+----------+
| 10485760 |
+----------+
1 row in set (0.00 sec)

发现导入的速度也是不慢的

下面为了对比一下mysqldump的导出，来用mysqldump进行一下操作

对比一下outfile，mysqldump导出花费了大约12秒，大概是outfile的5倍左右

下面看一下这两个文件的大小

可以看到mysqldump导出的数据是outfile的2倍，这也就说明了outfile的优势所在。

因此，当有海量数据时，使用outfile和load data infile的速度会更快，消耗的资源更少。