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

目录

一、需求

二、解决方法

三、三种方式的实现

navicat直接导入

python脚本

load data命令

四、三种方式效率比较

五、总结

---

• navicat直接导入

我是同时使用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-CSDN博客）。

五、总结

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