MySQL5 LOAD DATA 深入研究

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介:
数据库中,最常见的写入数据方式是通过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、准备测试数据
数据的来源是: [url]http://www.cstj.gov.cn/upload/newstxt/dmfl.htm[/url],这个是“国民经济行业分类与代码表”,网页格式。我们现在需要的是文本数据,如何获取符合格式的文本数据呢?方法是,复制网页到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]]


本文转自 leizhimin 51CTO博客,原文链接:http://blog.51cto.com/lavasoft/40168,如需转载请自行联系原作者
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
8月前
|
关系型数据库 MySQL 索引
带你读《2022技术人的百宝黑皮书》——mysql锁机制的再研究(3)
带你读《2022技术人的百宝黑皮书》——mysql锁机制的再研究(3)
|
8月前
|
关系型数据库 MySQL 索引
带你读《2022技术人的百宝黑皮书》——mysql锁机制的再研究(4)
带你读《2022技术人的百宝黑皮书》——mysql锁机制的再研究(4)
|
8月前
|
关系型数据库 MySQL
带你读《2022技术人的百宝黑皮书》——mysql锁机制的再研究(5)
带你读《2022技术人的百宝黑皮书》——mysql锁机制的再研究(5)
|
8月前
|
分布式计算 算法 关系型数据库
带你读《2022技术人的百宝黑皮书》——mysql锁机制的再研究(6)
带你读《2022技术人的百宝黑皮书》——mysql锁机制的再研究(6)
|
8月前
|
搜索推荐 Java 数据建模
基于 SpringBoot+Vue+MySql 的家乡特色菜系统研究与实现(一)
基于 SpringBoot+Vue+MySql 的家乡特色菜系统研究与实现
|
10天前
|
存储 关系型数据库 MySQL
使用阿里云的数据传输服务DTS(Data Transmission Service)进行MySQL 5.6到MySQL 8.0的迁移
使用阿里云的数据传输服务DTS(Data Transmission Service)进行MySQL 5.6到MySQL 8.0的迁移
52 1
|
10天前
|
关系型数据库 MySQL
MySQL 报错 [ERROR] [FATAL] InnoDB: Table flags are 0 in the data dictionary but the flags in file
MySQL 报错 [ERROR] [FATAL] InnoDB: Table flags are 0 in the data dictionary but the flags in file
465 0
|
8月前
|
SQL 关系型数据库 MySQL
解决:MySQL找不到data文件的位置
解决:MySQL找不到data文件的位置
127 0
|
10天前
|
SQL 关系型数据库 MySQL
MySQL技能完整学习列表10、数据导入和导出——1、数据导入(LOAD DATA, mysqldump)——2、数据导出(SELECT ... INTO OUTFILE, mysqldump)
MySQL技能完整学习列表10、数据导入和导出——1、数据导入(LOAD DATA, mysqldump)——2、数据导出(SELECT ... INTO OUTFILE, mysqldump)
57 0
|
10天前
|
Java 数据库
SpringBoot出现com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Incorrect datetime va
SpringBoot出现com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Incorrect datetime va
18 0