导入CSV、txt文件内容到MySQL -- Load Dat

原文：http://www.wangqi94.com/streamline/103

背景

对于大量数据导入DB，一般情况下我们都会拿到一个csv或这txt，导入的方式有很多：

1. 编码实现：Java/C/Spark程序……读取csv写入到MySQL
2. 使用数据库客户端导入csv：如navcat、Sqlyong、Sequel pro等直接导入

第一种最为麻烦；第二种是最简单的，但是可以支持csv导入的客户端不是很多，支持的或需要收费，还得安装。。个人觉得最方便的应该还是下面这种：

使用 Load Data Local Infile……指令

语法

LOAD DATA LOCAL INFILE 'PATH'   ## PATH为文件所在位置
INTO TABLE `TABLE`    ## 数据库表名
character set utf8    ##  字符编码
FIELDS TERMINATED BY ','   ## 以逗号拆分文件的每一行
ENCLOSED BY '\\'   ## 处理文件拆分后每个列中的特殊字符，即原封不动写入DB
LINES TERMINATED BY '\n'  ## 每一行以\n换行：Mac\Linux下，win下应为\r\n
IGNORE N ROWS   ## 跳过第N行

https://www.relaxheart.cn/streamline/103

常见的场景

测试数据表

CREATE TABLE `contact_big_dataset` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `contact_name` char(20) DEFAULT NULL COMMENT '联系人姓名',
  `contact_mobile` char(20) DEFAULT NULL COMMENT '联系人手机',
  `contact_qq` char(20) DEFAULT NULL COMMENT '联系人QQ',
  `contact_web` char(20) DEFAULT NULL COMMENT '电话',
  `contact_email` char(50) DEFAULT NULL COMMENT '邮箱',
  `contact_address` varchar(100) DEFAULT NULL COMMENT '地址',
  `gmt_create` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `gmt_modify` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`),
  KEY `idx_name_mobile` (`contact_name`,`contact_mobile`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='联系人数据';

字段一对一

一对一是指假设我的文件是这样子的：

image.png

示例：

LOAD DATA LOCAL INFILE '/Users/qi.wang/Desktop/bigdata.csv' 
INTO TABLE `contact_big_dataset`
character set utf8  
FIELDS TERMINATED BY ','
ENCLOSED BY '\\'
LINES TERMINATED BY '\n'

字段不全

一般情况数据表会有自增主键ID，但是csv可能没有这一列：

image.png

示例：

LOAD DATA LOCAL INFILE '/Users/qi.wang/Desktop/bigdata.csv' 
INTO TABLE `contact_big_dataset`
character set utf8  
FIELDS TERMINATED BY ','
ENCLOSED BY '\\'
LINES TERMINATED BY '\n'  (`contact_name`,`contact_mobile`,`contact_qq`,`contact_web`,`contact_email`,`contact_address`)    ## 在最后指定需要对应的列即可

跳过表头第一行

上面我们导入到库里的结果是这样子的：

image.png

示例

LOAD DATA LOCAL INFILE '/Users/qi.wang/Desktop/export.csv' 
INTO TABLE `contact_big_dataset`
character set utf8  
FIELDS TERMINATED BY ','
ENCLOSED BY '\\'  
LINES TERMINATED BY '\n'  
IGNORE 1 ROWS   ## 跳过第1行,可修改
(`contact_name`,`contact_mobile`,`contact_qq`,`contact_web`,`contact_email`,`contact_address`)

原文：http://www.wangqi94.com/streamline/103