如何将大量同结构Excel文件合并导入MySQL及相关问题求助

单个.xlsx文件包含10000行、170-180列数据，其中170列为公共列。因磁盘空间限制，尝试用Python将Excel数据导入MySQL生成ibd文件，遇到以下两个问题：

核心问题 #

• 分批导入后生成3个ibd文件，需复制到另一台电脑，无法导入新电脑的MySQL
• 生成的ibd文件体积约为原Excel的5倍，需要更高效的Excel导入MySQL方法

当前使用的Python代码 #

header = "序号,标题,摘要…"
header_list = header.split(',')
header2 = "var001,var002,var003,var004,var005,var006,var007,var008,var009,var010,var011,var012,var013,var014,var015,var016,var017,var018,var019,var020,var021,var022,var023,var024,var025,var026,var027,var028,var029,var030,var031,var032,var033,var034,var035,var036,var037,var038,var039,var040,var041,var042,var043,var044,var045,var046,var047,var048,var049,var050,var051,var052,var053,var054,var055,var056,var057,var058,var059,var060,var061,var062,var063,var064,var065,var066,var067,var068,var069,var070,var071,var072,var073,var074,var075,var076,var077,var078,var079,var080,var081,var082,var083,var084,var085,var086,var087,var088,var089,var090,var091,var092,var093,var094,var095,var096,var097,var098,var099,var100,var101,var102,var103,var104,var105,var106,var107,var108,var109,var110,var111,var112,var113,var114,var115,var116,var117,var118,var119,var120,var121,var122,var123,var124,var125,var126,var127,var128,var129,var130,var131,var132,var133,var134,var135,var136,var137,var138,var139,var140,var141,var142,var143,var144,var145,var146,var147,var148,var149,var150,var151,var152,var153,var154,var155,var156,var157,var158,var159,var160,var161,var162,var163,var164,var165,var166,var167,var168,var169,var170,var171,var172,var173"
header2_list = header2.split(',')

import os
import glob
import pandas as pd
import pymysql
import datetime
from sqlalchemy import create_engine
engine = create_engine('mysql://root:123456@localhost/ldz')

start = datetime.datetime.now()
print('本次运行开始时间：'+str(start))

# 连接MySQL数据库
conn = pymysql.connect(host='localhost', user='root', password='123456', database='ldz')
cursor = conn.cursor()

# 文件夹路径
# folder_path = input('請輸入文件夾：')  # 目标目录
folder_path = r"E:\BaiduNetdiskDownload\浩大工程\ldz000"  # 目标目录

# 使用glob模块获取所有xlsx文件的路径
xlsx_files = glob.glob(os.path.join(folder_path, '**/*.xlsx'), recursive=True)

# 存储每个文件的路径和名称到一个列表中
file_paths = []
for file_path in xlsx_files:
    file_paths.append(file_path)

# print(file_paths)
print(str(len(file_paths))+"\n")

for item in file_paths:
    data = pd.read_excel(item)
    print(str(datetime.datetime.now()) + "  正在處理：" + str(item)+" 長度爲："+ str(len(data))+" 列數爲："+str(len(data.columns)))
    data = data.reindex(columns=header_list)
#     print("    chulihou" + str(item)+" changdu,"+ str(len(data))+" lieshu,"+str(len(data.columns)))
    data.columns = header2_list
    data.fillna('', inplace=True)

    # 将数据逐行存入数据库
    data.to_sql('ldz0', engine, if_exists='append', index=False)

# 关闭数据库连接
print(str("  关闭数据库连接：" + str(datetime.datetime.now())) )
cursor.close()
conn.close()

解决方案 #

1. 导入ibd文件到新电脑MySQL的步骤 #

ibd是InnoDB独立表空间文件，直接复制导入要求严格，步骤如下：

• 确保新电脑MySQL版本、配置（需开启innodb_file_per_table=1）与原电脑完全一致
• 在新电脑MySQL中创建结构完全相同的空表（表名、字段类型、索引、引擎等必须和原表一致）
• 停止新电脑的MySQL服务
• 将复制的ibd文件替换新表对应的ibd文件，修改文件权限为mysql用户可读可写
• 启动MySQL服务，执行以下命令：

  ALTER TABLE ldz0 DISCARD TABLESPACE;
  ALTER TABLE ldz0 IMPORT TABLESPACE;
  

• 验证表数据是否正常。若导入失败，建议改用mysqldump导出SQL文件或CSV文件中转导入，兼容性更强

2. 减小ibd体积&高效导入方法 #

（1）优化表结构

• 匹配字段类型：用VARCHAR(n)替代TEXT（n设为实际最大长度），用INT替代BIGINT（数据范围足够时），日期类型用DATE/DATETIME而非字符串
• 精简索引：仅保留必要的索引，多余索引会大幅增加ibd体积

（2）导入过程优化

• 关闭事务自动提交：导入前执行SET autocommit=0;，导入完成后COMMIT;，减少事务日志开销
• 禁用约束检查：导入前执行SET FOREIGN_KEY_CHECKS=0;和SET UNIQUE_CHECKS=0;，导入后恢复为1
• 分批插入：修改data.to_sql时添加chunksize=1000参数，降低内存占用并提高效率
• 用CSV中转导入：先将Excel转成CSV文件，再用MySQL官方的LOAD DATA INFILE命令导入，效率远高于pandas逐行插入，示例命令：

  LOAD DATA INFILE '/path/to/your/file.csv'
  INTO TABLE ldz0
  CHARACTER SET utf8mb4
  FIELDS TERMINATED BY ','
  OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  IGNORE 1 ROWS;
  

（3）导入后优化

• 整理表空间：执行OPTIMIZE TABLE ldz0;（注意该操作会锁表，适合离线场景），消除碎片减小体积
• 启用表压缩：创建表时指定ROW_FORMAT=COMPRESSED，可大幅降低存储体积（需MySQL支持）

内容的提问来源于stack exchange，提问作者Manfred L