csv大数据导入mysql操作 --笔记备忘

一、背景：

arrow 有两个文件， Mouser 1个， Digikey 1个

1. 合并arrow的两个文件
2. 删除3个文件中，每个文件中price1大于20 或者pirce1 小于 0.2 行, 保留[0.2 20]
3. 删除3个文件中sellsValue 小于100 的行
4. 生成文件arrow-ok , Mouser-ok, digikey-ok
5. 找出3个文件中以productCodeManufacturer 共同的，部分，把各个 sellsValue 相加 生成一个 arrow-mourse-digikey-merger 文件 ， 列名以Digikey为依据，取前9列即可。

二、预处理：

• 1、数据库/数据表统一 utf8 编码，兼容中文（默认latin会出现中文乱码）

use sakila;
show variables like 'char%'; -- 查看系统字符集
alter database sakila character set utf8; --GB2312/GBK也可尝试
set character_set_database=utf8; -- 或者设置win默认的字符集为utf8
-- 展示导入限制，并修改 secure_file_priv 中的 my.ini 文件中的路径为:""
show variables like '%secure%';

• 2、可以先建表再导入，也可以导入新建表；创建三个表

create table `digikey`(
    `uuid` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id自增列',
    `productCodeProvider` varchar(64) DEFAULT NULL,
    `productGradePlateName` varchar(64) DEFAULT NULL,
    `productCodeManufacturer` varchar(64) DEFAULT NULL,
    `productMinEncapsulationNumber` varchar(64) DEFAULT NULL,
    `encapsulationModel` varchar(64) DEFAULT NULL,
    `strockDelta` varchar(64) DEFAULT NULL,
    `price1` varchar(64) DEFAULT NULL,
    `sellsValue` varchar(64) DEFAULT NULL,
    `catalog1` varchar(64) DEFAULT NULL,
    `strockNumber1` varchar(64) DEFAULT NULL,
    -- 后续仍有55个字段库存数
    PRIMARY KEY (`uuid`),
    KEY `IDX_PRODUCTCODEMANUFACTURER` (productCodeManufacturer) -- 索引关联加速查询速度
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

• 3、可用navicat IDE直接导入，选定编码和csv编码一致就行，可用文本编辑器查看：20936（Simplified Chiinese GB2312），而utf8兼容大部分中文。也可脚本导入：

格式如：

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[PARTITION (partition_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 | ROWS}]
[(col_name_or_user_var,...)]
[SET col_name = expr,...]

• 4、本次使用如下：

-- 建表导入
load data infile '' into table arrow
fields terminated by ','
enclosed by '"'
lines terminated by '\r\n'
IGNORE 1 rows;
-- 删除多余行记录
DELETE FROM arrow WHERE productCodeProvider='productCodeProvider';
-- 添加表自增列
-- BEFORE/AFTER [COLUMN]也可以
ALTER TABLE arrow add `uuid` INT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'id自增列' FIRST； 

--创建索引 唯一索引/组合索引，这里创建一个字段索引就满足需求，提升查询效率
CREATE INDEX IDX_PRODUCTCODEMANUFACTURER ON sakila.arrow(productCodeManufacturer);

三、查询合并集结果

select
t1.uuid
,t1.productCodeProvider
,t1.productGradePlateName
,t1.productCodeManufacturer
,t1.productMinEncapsulationNumber
,t1.encapsulationModel
,t1.strockDelta
,t1.price1
,t1.sellsValue + ifnull(t2.sellsValue,0) + ifnull(t3.sellsValue,0)  as total_sellsValue
,t1.catalog1
from
digikey t1
inner join
(
select productCodeManufacturer,sellsValue from arrow
where
price1 between 0.2 and 20
and
sellsValue>100
) t2 on t1.productCodeManufacturer=t2.productCodeManufacturer
inner join
(
select productCodeManufacturer,sellsValue from arrow
where
price1 between 0.2 and 20
and
sellsValue>100
) t3 on t1.productCodeManufacturer=t3.productCodeManufacturer
where
price1 between 0.2 and 20
and
sellsValue>100

至此任务完成。

处理结果如下：

四、注意项

1. 服务器/数据库/表/导入文件，编码兼容一致性，utf8格式可以解决大部分问题。
2. 建立索引/优化SQL，提升查询效率。