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

一、需求：将百万行表格数据快速导入mysql中

二、解决方法：

1. 使用navicat导入xlsx格式文件。
2. 写python脚本，导入mysql。
3. 使用load data命令行，快速导入mysql。

三、三种方式的实现：

• 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分钟，不太卡，可以进行其他工作。

五、总结

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