Mysql load data local

MySQL5 LOAD DATA 深入研究

 

数据库中，最常见的写入数据方式是通过SQL INSERT来写入，另外就是通过备份文件恢复数据库，这种备份文件在MySQL中是SQL脚本，实际上执行的还是在批量INSERT语句。

 

在实际中，常常会遇到两类问题：一类是数据导入，比如从word、excel表格或者txt文档导入数据（这些数据一般来自于非技术人员通过OFFICE工具录入的文档）；一类数据交换，比如从MySQL、Oracle、DB2数据库之间的数据交换。

 

这其中就面临一个问题：数据库SQL脚本有差异，SQL交换比较麻烦。但是几乎所有的数据库都支持文本数据导入(LOAD)导出(EXPORT)功能。利用这一点，就可以解决上面所提到的数据交换和导入问题。

 

MySQL的LOAD DATA INFILE语句用于高速地从一个文本文件中读取行，并装入一个表中。文件名称必须为一个文字字符串。下面以MySQL5为例说明，说明如何使用MySQL的LOAD DATA命令实现文本数据的导入。

 

注意：这里所说的文本是有一定格式的文本，比如说，文本分行，每行中用相同的符号隔开文本等等。等等，获取这样的文本方法也非常的多，比如可以把word、excel表格保存成文本。

 

 

一、测试环境：

 

Windows XP Professional 简体中文版

mysql-5.0.45-win32

 

注意：不要以为测试环境写操作系统是多余的，我想说一点：不同操作系统的回车换行不大一样，会得出不同的结果。

 

二、准备数据库、目标表、测试数据。

 

1、准备测试数据

数据的来源是：http://www.cstj.gov.cn/upload/newstxt/dmfl.htm，这个是“国民经济行业分类与代码表”，网页格式。我们现在需要的是文本数据，如何获取符合格式的文本数据呢？方法是，复制网页到excel，删除前面无用的三列，另存为文本文档C:\DM_HY.TXT;

 

2、创建目标数据库、目标表

 

-- 创建目标数据库

CREATE DATABASE IF NOT EXISTS TESTDB;

 

-- 设置当前数据库

USE TESTDB;

 

-- 预防性删除要创建的目标表

DROP TABLE IF EXISTS DM_HY;

 

-- 创建目标表

CREATE TABLE DM_HY (

  ID BIGINT(20) NOT NULL AUTO_INCREMENT,

  ML VARCHAR(1) DEFAULT NULL,

  DL VARCHAR(2) DEFAULT NULL,

  ZL VARCHAR(3) DEFAULT NULL,

  XL VARCHAR(4) DEFAULT NULL,

  MC VARCHAR(120) DEFAULT NULL,

  SM VARCHAR(1200) DEFAULT NULL,

  PRIMARY KEY  (ID)

) ENGINE=MYISAM DEFAULT CHARSET=GBK;

 

说明：

AUTO_INCREMENT 主键自增

DEFAULT CHARSET=GBK 设定表的默认字符集

 

三、LOAD数据

 

一步登录数据库，并且设置当前数据库：

mysql -uroot -pleizhimin

use testdb;

 

-- 从文本文件导入数据到目标表

LOAD DATA INFILE 'C:\DM_HY.TXT'

INTO TABLE DM_HY(ML,DL,ZL,XL,MC,SM);

 

这样即可完成数据的导入。

 

控制台截图如下：

 

四、EXPORT数据

 

如果我现在要将MySQL中的DM_HY这个表导入到DB2,我还需要导出一个文本文件，然后再利用DB2的LOAD导入到DB2数据库。还以DM_HY表为例，将上面导入的数据重新导出到C:\DM_HY_EXPORT.TXT.

 

实际上，在MySQL5中不存在真正的EXPORT命令，而是SELECT ... INTO OUTFILE 命令。下面是操作过程：

 

-- 导出数据为文本文件

SELECT ML,DL,ZL,XL,MC,SM

INTO OUTFILE 'C:\DM_HY_EXPORT.TXT'

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ''''

LINES TERMINATED BY '\n'

FROM DM_HY;

 

FIELDS TERMINATED BY ',' 数据字段间用逗号隔开

OPTIONALLY ENCLOSED BY '''' 每个字段的数据用单引号括住（注意单引号的表达方法）

LINES TERMINATED BY '\n' 每条数据结束用'\n'作为换行符。

 

执行过程的截图如下：

 

注意：如果导出数据的时候指定分隔符、换行符，那么在导入的时候也要指定。

 

五、总结

 

LOAD DATA是一个很有用的命令，而且命令的选项很多，但大多都用不到，如果真的需要，用的时候看看官方文档即可。下面我给出MySQL的官方的语法以供参考：

 

LOAD DATA INFILE语法

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'

    [REPLACE | IGNORE]

    INTO TABLE tbl_name

    [FIELDS

        [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,...)]

 

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, ...

    [INTO OUTFILE 'file_name' export_options

      | INTO DUMPFILE 'file_name']

    [FROM table_references

    [WHERE where_definition]

    [GROUP BY {col_name | expr | position}

      [ASC | DESC], ... [WITH ROLLUP]]

    [HAVING where_definition]

    [ORDER BY {col_name | expr | position}

      [ASC | DESC] , ...]

    [LIMIT {[offset,] row_count | row_count OFFSET offset}]

    [PROCEDURE procedure_name(argument_list)]

    [FOR UPDATE | LOCK IN SHARE MODE]]