php

百万级xlsx表格导入数据库的实现方案

2736
13
2020-04-19

需求是这样的,供应商给到一份 xlsx 表格(144MB),里面刚好有 100w 行数据(11列)。我需要把它们稍作处理后写入到 mysql 数据库

项目基于 Laravel,既然需要稍作处理,那我首先想到的就是找一个能读 xlsx 的 composer 包
我尝试过最知名的PhpSpreadsheet、对 Laravel 友好的 Laravel-Excel。然而不管是顺序读还是分块读,尝试各种方案最终都会因内存耗尽而中断,哪怕我给 PHP 分配了 8g 内存
这里点名表扬一下 spout,唯有它能在低内存占用的前提下正常顺序读取表格。但整体速度实在太慢,每秒仅13行 ,粗略一算要20多个小时。虽然能用但还是不太行

正苦恼之时,旁边的大佬给我指了条明路——

load data infile

我才学疏浅,这个关键字只在刚接触 mysql 时见过一眼,还从没用过。被这一点才醒悟过来,对呀,我何不把表格导进数据库再读取处理呢,那效率肯定比读 xlsx 高到不知哪里去了
不过 mysql 原生不支持 xlsx 文件的导入,需要先用 Excel 打开然后将它另存为 csv 格式。这一步视电脑配置而异,我这边耗时约1分钟
中文 Windows 下的 Excel 用的是 GBK 编码,而数据表一般都是 UTF-8。因此还需要转码

使用 VSCode 打开这个 csv 文件,里面的中文部分乱码,因为默认用的是 UTF-8 编码打开的。点击右下角的 UTF-8,选择 通过编码重新打开,输入 GBK 然后回车即可正常显示中文
再点击右下角的 GBK,选择 通过编码保存,输入 UTF-8,回车后即可完成转码,耗时约2分钟

接着连接数据库,将表格的列作为字段来建表。我这里建的是 metadata
执行如下 sql

LOAD DATA LOCAL INFILE 'F:/metadata.csv'
INTO TABLE metadata
FIELDS TERMINATED BY ','
IGNORE 1 LINES

如果你的表格不含表头,则不需要 IGNORE 1 LINES

接下来就是见证奇迹的时刻,百万行数据,仅 12秒 就全部成功入库!爽啦
最后写脚本对表里的数据进行处理,边读边写,速度也比直接去读 xlsx 要快几个量级

如果你在用一些 GUI 工具的话会更方便些。这里以我常用的两款举例
首先用 Excel 将 xlsx 另存为 csv (无需转码)

Navicat

选中数据库,选择,点击新建表按钮旁的导入向导,选择 CSV 类型,编码选择 GBK,然后无脑下一步,最后点击开始,稍等几分钟即可完成

之所以不直接选 xlsx 类型是因为 mysql 并不直接支持这个格式的导入,应该是由 navicat 做了其他处理。亲测速度还不如手动 Excel 打开、另存为 csv 后再导入

DataGrip

右键点击数据库,选择Import Data from File,选择 csv 文件,然后选择编码,点击 OK 即可。不得不夸一句 Jetbrains 爸爸,一分钟不到就搞定了

昵称
邮箱
网址
两双筷子的头像 2021-12-11 22:42

如果需要经常导入这个量级的数据,php可以用迭代器分批读取csv批量入库 (如果导入的数据需要逻辑处理的话)

数字君的头像 2021-08-01 17:18

这个会不不会程序卡死,没试过这么多数据一下导入的,收藏下,以后试试看。

吴飞飞的头像 2021-02-10 22:53
吴飞飞

一亿行数据怎么导入呢

Kenvix的头像 2020-05-10 21:28

甚至可以不导入,直接开一个CSV引擎的数据表然后把csv文件放进去(

mokeyjay的头像 2020-05-10 23:45
mokeyjay 博主

?还有这种操作?

西瓜的头像 2020-04-20 17:11
西瓜

navicat for mysql可以导入xlsx呀

mokeyjay的头像 2020-04-20 20:07
mokeyjay 博主

我知道呀,请把文章看完

xiaobeii的头像 2020-04-20 11:27
xiaobeii

如果只是导入的话进数据库的话可以用用kettle,可以从excel导入到数据库里,也可以从别的数据库导出到指定数据库,嫌慢可以多线程
我现在都是用这个软件来导入数据,用到以下场景
1.从excel导入到mysql
2.从oracle导入到mysql
3.定时任务导入

mokeyjay的头像 2020-04-20 15:56
mokeyjay 博主

简单看了下好像是依赖 Java ……?Java 搞不来(捂脸哭

xiaobeii的头像 2020-04-20 15:58
xiaobeii

不用,全是图形化操作界面,当然也可以自己编写。

mokeyjay的头像 2020-04-20 15:59
mokeyjay 博主

是吗?我再看看,谢谢嗷

Ryoma的头像 2020-04-20 08:37

哈哈,我也遇到一个类似的场景,不过是导入 MongoDB,而且数量级比较小(也就几万条),不过用的方法和你一样,先用 Navicat 导入到数据库,然后在写脚本进一步处理。

mokeyjay的头像 2020-04-20 15:56
mokeyjay 博主

几万条的话如果列数不是很多,直接用脚本读也能 hold 住