使用load data方式将xlsx表格百万行文件快速导入mysql中(一)

本文对比了使用Navicat、Python脚本和LOAD DATA命令将百万行数据从xlsx文件导入MySQL数据库的效率。Navicat虽直观但易使电脑卡顿;Python脚本灵活但错误较多;LOAD DATA命令最快且资源占用少。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

目录

一、需求

二、解决方法

三、三种方式的实现

python脚本

load data命令

四、三种方式效率比较

五、总结


我是同时使用Navicat和mysql-front作为可视化界面的,各有优缺点。如front可以方便的新建表格、管理表格列等细节。navicat导入时可选更多格式文件,front不能导入xlsx格式文件。front一次也只能导入65535行,而navicat可以一次全部导入。在查看表格时navicat可分成1000条数据一页进行查看。因此,用navicat导入百万级xlsx格式文件比较合适,也比较简单,但是这种方式极容易使电脑卡死,完全不能做其他工作。

  • python脚本

源码如下:

#!/usr/bin.python
# -*- coding: UTF-8 -*-

import pymysql
import xlrd

# 打开数据库连接(请根据自己的用户名、密码及数据库名称进行修改)
db = pymysql.connect("localhost", "root", "root", "sherry")
# 使用cursor()方法获取操作游标
cursor = db.cursor()

# 使用execute方法执行SQL语句
cursor.execute("SELECT VERSION()")

# 创建数据表SQL语句
sql = """CREATE TABLE IF NOT EXISTS TEST (
         id int(11) NOT NULL AUTO_INCREMENT,
         交易订单号 CHAR(255),
         实收金额  double(20,2),
         商品小计  double(20,2),
         商品编码  CHAR(255),
         商品名称  CHAR(255),
         PRIMARY KEY (id)
          )"""

cursor.execute(sql)

xlsxFile = xlrd.open_workbook('./table1.xlsx')
dataRead = xlsxFile.sheet_by_index(0)
rowNum = dataRead.nrows

for i in range(2, rowNum):
    dataLine = dataRead.row_values(i)
    sql = "INSERT INTO TEST(交易订单号, 实收金额, 商品小计,商品编码, 商品名称) \
          VALUES (%s, %s, %s, %s, %s)"
    try:
       # 执行sql语句
       cursor.execute(sql,(dataLine[0],dataLine[1],dataLine[2],dataLine[3],dataLine[4]))
       # 提交到数据库执行
       db.commit()
    except:
       # Rollback in case there is any error
       print("insert error: ", dataLine)
       db.rollback()

# 执行sql语句
db.close()
  • load data命令

先将xlsx格式的表格另存为txt文件。在命令行下,

mysql -u root -p

然后:

use database

然后:

load data infile 'E:\\XSH\\MySQL\\FirstMonth.txt'
into table test2
fields terminated by '\t'
lines terminated by '\n'
ignore 1 lines
(交易订单号,实收金额,商品小计,商品编码,商品名称);

这时会出现一些错误,如SQL syntax等语法问题。

还有问题1:

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv

使用命令查看当前值:

show variables like '%secure%';

这个问题需要在安装的mysql目录下找到my.ini文件,在[mysqld]中加入:

secure-file-priv=''

这个表示任意目下都能导入文件到mysql中。也可以自己指定特定的目录。注意设置完成之后,需要在控制面板->管理工具->服务中找到mysql,重启mysql服务,在查看当前值是否修改。不行的话重启电脑试试。

问题2:

ERROR 1300 (HY000): Invalid utf8 character string: ''

这是txt文件格式不匹配导致。使用以下命令查看mysql中变量的格式:

show full columns from testtb;

如果显示utf-8,则需要将txt用记事本打开,另存为,选编码格式为utf-8.

问题3:

ERROR 1261 (01000): Row 228 doesn't contain data for all columns

查看当前连接的sql_mode:

mysql> show variables like 'sql_mode';

设置 MySQL sql_mode,使其不包含 “strict_trans_tables” mode:

set sql_mode='';

四、三种方式效率比较

  • navicat

大约2小时,电脑很卡,完全不能做其他操作。

  • python脚本

大约6小时,电脑不太卡,可以进行其他工作,同时报的error也比较多,大约丢失20%的数据。

  • load data

第一次大约10分钟,后面大约只需1分钟,不太卡,可以进行其他工作(再详细介绍load data步骤请移步:使用load data方式将xlsx表格百万行文件快速导入mysql中(二)_load data infile 导入xlsx-优快云博客)。

五、总结

使用load data方式最为高效,但是这个是需要转换成txt格式的文件,还可以试试直接使用xlsx格式导入。

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值