前阵子公司财务发了份四十多MB的Excel报表给我让我把里面的订单明细导入MySQL库里。第一个念头是用Navicat直接导入可表格里有合并单元格、多层表头、时间字段长成了2024/07/01 12:30:45这种格式整完老半天才导进去中途还踩了一串乱码和类型转换的坑。后来我把这些流程整理成了几条稳妥的路线也顺手写成这篇给正在跟Excel导入MySQL较劲的朋友。内容会覆盖最常用的图形化方案、适合命令行的方案、适合自动化场景的方案以及各种报错的处理思路。1. 动手导入之前先把Excel这头的问题处理干净不管最后选择哪种导入方式Excel原始文件的质量直接决定成败。很多人一上来就开Navicat向导结果跑到一半报错回头才发现是Excel文件本身有问题。磨刀不误砍柴工导入前建议先花十来分钟处理下面四件事。1.1 文件编码Excel导出CSV时最容易踩的乱码源头如果用Navicat直接连Excel文件中文一般还好。但如果你打算用LOAD DATA INFILE或Python的pandas读取CSV编码问题就是第一大坑。Windows下Excel另存为CSV时默认是用ANSI编码保存的在中文系统里实际是GBK而MySQL客户端和表结构普遍是utf8/utf8mb4。结果就是用命令行导入后中文全部变成问号或者乱码。解决办法有几个用WPS或Excel打开文件后通过另存为选择CSV格式时如果是新版Office会有UTF-8编码选项直接选它。老版本Excel没有这个选项可以先把Excel另存为CSV然后用记事本打开CSV再另存为UTF-8编码。如果你用的是Python导入读取时直接指定encodinggbk或encodingutf-8前提是搞清楚原始文件到底是什么编码。我自己的习惯是只要能选一律输出UTF-8无BOM格式。MySQL对UTF-8 with BOM也能处理但个别版本和程序在读取BOM头时会把它当成一个不可见字符混进第一列很恶心。所以无BOM优先。1.2 表结构设计字段类型选错了后面都是泪Excel里看起来差不多的内容到了MySQL里可能要对应完全不同的类型。举个经典例子订单号、身份证号、手机号这类数值型但不需要参与计算的字段如果图省事把整列设成INT或BIGINT超过一定长度以后精度会丢失导入后数据直接错了。这种列要用VARCHAR而且长度要给够。日期字段是另一个高发区。Excel里的日期本质上是序列号显示成2024-07-01只是格式在起作用。导入时如果识别不出来会出现两种情况要么变成一串44000多的小数要么导入到MySQL后变成0000-00-00。为了避免这种问题建议先在Excel里把日期列统一改成文本格式或明确的日期格式比如YYYY-MM-DD HH:MM:SS让导入工具不需要猜。建表时还有一个索引策略要说如果导入的是几万行以上的数据不要在导入前就建立太多索引。索引会影响写入速度正确的做法是先建没有任何索引的裸表导入导入完成后再用ALTER TABLE加索引时间能省下不少。1.3 数据清洗合并单元格、换行符、科学计数法Excel表格看着工整细节里全是坑合并单元格Navicat导入时合并单元格通常只有左上角那个值被保留其余为NULL。真实导入前建议先在Excel里把合并单元格全部取消并且把值向下填充完整。单元格内有换行符说明Excel文本框里有AltEnter换行。这种换行符在部分导入工具里会被当成一行结束导致列错位。导入前需要用查找替换把换行符清理掉或者确保导入时用ENCLOSED BY 包起来。科学计数法列的宽度不够时长数字会自动显示成1.23457E17。这不是数据变了只是显示问题但导出成CSV时会真实写入科学计数法导致精度丢失。处理方式是把该列格式改成文本再重新导出。还有一个常见情况是多行表头。Excel表格第一行是标题第二行是单位第三行才是真正的表头。导入时一定要让工具跳过错误的前几行否则第一列就会变成表头文字。1.4 空值处理NULL和空字符串不是一回事这是几乎所有导入工具都容易忽略的细节。Excel里空白单元格在导入时可能变成NULL也可能变成空字符串取决于工具和字段设置。两者在SQL里的行为截然不同NULL参与COUNT(column)时不计入参与IS NULL判断时能查到。不是NULLCOUNT会计数IS NULL查不到。如果你的业务表有非空约束空字符串通常没问题但NULL会触发约束导致导入中断。如果要保留业务上的为空语义建议明确约定整列全空或者某个占位符如N/A转换为NULLExcel空白转换为NULL或者空字符串要明确选择。Navicat导入向导里有NULL值设置项见过很多人没注意导入后统计数字对不上才回头查。2. Navicat图形化导入日常最快路径也是大多数人第一次成功导入的选择很多同事问我用什么方式答案很简单如果你只是偶尔导一次数据不想记命令不想写脚本用Navicat肯定是最快的。它支持直接读取Excel文件不需要你先转成CSV图形界面下能看到预览和错误日志对新手相当友好。2.1 两种打开导入向导的方式Navicat导入Excel一般有两种入口第一种在数据库连接中找到目标表所在的库点击表在右侧空白区域右键选择导入向导。第二种直接右键目标表选择导入向导此时向导会预先选中该表一会儿映射字段时更省事。进入向导后第一步就是选择文件类型。一定要选Excel文件.xlsx、.xls不是文本文件。选Excel格式后Navicat不需要你安装什么额外的驱动它自己能解析。但如果Excel文件是2003的老格式.xls部分新版本Navicat也能处理万一处理不了先用Excel打开另存为.xlsx再导。向导里有一个容易忽视的地方工作表选择。一个Excel文件里可能有多张Sheet比如1月、2月、汇总默认选的是当前可见的第一张表得手动确认你要导入的是哪张Sheet不要相信默认值。2.2 字段映射和类型识别的细节到了源字段和目标字段映射这一步建议不要闭眼点下一步。Navicat会自动按列名匹配同名表字段但匹配不上时就得手动拖动映射。偶尔遇到目标字段是自增主键导入时想保留Excel里的原始ID就必须把Excel中对应列映射到该主键字段并且确认表字段不是AUTO_INCREMENT或者导入途中关闭自增。否则MySQL会重新生成ID原始对应关系就乱了。类型识别上Navicat有自己的一套判断规则。比如Excel里看起来是文本的订单号到了Navicat预览阶段可能会被识别成DOUBLE因为内容全是数字。这种需要在字段映射界面手动把目标类型改过来或者干脆建临时表来导导入后再用INSERT INTO ... SELECT ...转换进正式表。还有一个资深用户才会注意的点预览区域显示的数据将被截断警告。这个警告出现在某些列超出VARCHAR长度时一般是因为表字段长度定短了。遇到这类提示不要直接忽略回到建表语句改大字段长度更稳妥。2.3 分批提交和出错日志不用推倒重来Navicat的导入向导里有一个高级选项里面包含批量插入大小和失败时停止两个关键配置。批量插入大小默认是100条一批。这个值不是越大越好因为MySQL单条INSERT多值插入有max_allowed_packet限制一批数据太大反而报错。通常100到500之间比较稳。如果Excel有几万行建议直接把批量大小设成200速度适中日志也好定位问题。失败时停止这个选项默认是开启的。如果在导入过程中有一条数据不符合约束整个导入会停在那里。对于已经跑了十几分钟的大文件这非常浪费时间。我的做法是预期Excel数据基本干净时开启失败时停止。预期有部分脏数据时关闭失败时停止让导入继续跑最后打开日志文件看失败行数用日志里给出的行号回到Excel检查。2.4 为什么我不建议直接双击Excel表导入Navicat有一个导入Excel为一张新表的快捷功能看上去很快但在表结构上非常难控制。它会根据Excel内容自动推断字段类型推断结果往往不符合规范比如把订单号推断成INT把日期推断成TEXT把金额推断成DOUBLE。除非是临时调查用的一次性数据否则我不建议用这个方式。正确做法是手动建表明确字段类型和约束再走导入向导。建表花十分钟但能避免导入完成后才发现类型不对、重新返工的大坑。3. LOAD DATA INFILE命令行导入的正确打开方式如果遇到的是几百万行的大文件或者需要在服务器端自动完成导入图形化工具就显得力不从心了。LOAD DATA INFILE是MySQL内置的高效导入命令速度远快于逐条INSERT也是我处理大文件时的首选。前提是需要一点命令行操作能力。3.1 权限和文件路径MySQL不能访问你随便放的Excel命令的核心限制是MySQL服务进程只能读取指定目录下的文件这个目录由secure_file_priv参数控制。在服务器上执行下面这条SQL查看SHOW VARIABLES LIKE secure_file_priv;如果结果是NULL表示MySQL禁止了所有LOAD DATA INFILE操作你需要修改my.cnf再重启服务。如果结果是某个路径比如/var/lib/mysql-files/那就必须先把文件放到这个目录下。如果结果为空字符串说明不限制路径可以读取任意位置的文件。顺带说明LOAD DATA INFILE读取的可以是CSV文本但直接读Excel的.xlsx格式不行它是压缩的XML结构。所以走命令行方案的通用做法是Excel另存为CSV再执行导入。这反而便于控制编码。3.2 一条语句搞定的完整参数以一个简单的用户表导入为例表结构为CREATE TABLE user_import ( id INT, username VARCHAR(50), created_at DATETIME );CSV文件内容如下第一行是表头id,username,created_at 1001,张三,2024-07-01 10:30:00 1002,李四,2024-07-02 11:00:00导入语句LOAD DATA INFILE /var/lib/mysql-files/user_import.csv INTO TABLE user_import CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 ROWS (id, username, created_at) SET created_at STR_TO_DATE(created_at, %Y-%m-%d %H:%i:%s);几个参数逐个说CHARACTER SET utf8mb4明确指定文件字符集防止乱码。FIELDS TERMINATED BY ,列分隔符如果是管道符号就改成|。OPTIONALLY ENCLOSED BY 在某些内容中包含逗号或换行符时用双引号包住整个字段这样就不会误拆列。LINES TERMINATED BY \nWindows生成的CSV通常是\r\n这里要改成\r\n。如果漏了,你会在最后一列看到一堆\r残留在数据里。IGNORE 1 ROWS第一行是表头跳过。字段列表后面可以用变量接收原始值再通过SET转换。上面的created_at如果直接映射到DATETIME字段字符串也能自动转但遇到不规则格式就会报错。用STR_TO_DATE显式转换更可控。另存CSV时如果用了UTF-8 with BOM第一列首个字符可能带上BOM字节这会导致第一个字段读入时多出不可见字符。建议用无BOM的UTF-8或者用IGNORE 1 ROWS把表头跳过后在第一个字段上用TRIM(BOTH char(0xEF))之类的处理。3.3 大数据量导入的提速技巧实践下来百万行级别的CSVLOAD DATA INFILE会比逐条INSERT快十倍以上。如果想更进一步优化可以用下面几个方法导入前删除目标表上的非聚簇索引导入完成后重建。索引在数据写入时会拖慢速度且索引碎片也更多。临时把autocommit关闭导入结束后再开启。但LOAD DATA INFILE本身是自带事务包装的不是每行一个提交所以通常情况下不需要额外处理。使用临时表先导入再用INSERT INTO ... SELECT ...做数据转换和过滤。这种方式虽然多一步但可以让原始Excel数据先进一个宽松表再转进正式表避免脏数据直接污染正式表。确保表引擎是InnoDB时合理设置innodb_buffer_pool_size。大文件导入的瓶颈往往是刷盘内存给足能明显降低耗时。4. Python pandas让重复导入变成一键脚本如果你每个月初都要导入同样结构的Excel还附带一堆清洗逻辑比如去掉空行、统一日期格式、过滤掉金额为零的数据那条LOAD DATA语句就不够看了。我用Python场景的典型做法是pandas负责读Excel和清洗SQLAlchemy或者pymysql负责写入MySQL把这套流程固化成脚本以后直接跑一遍。4.1 为什么我最终把频率高的导入写成了Python用Python的相对优势在于清洗步骤可以写在代码里跟导入一次成型不需要人工去改Excel。还能做校验比如导入前检查数据总量、检查重复ID、检查必填字段为空的行数有异常就直接中止避免把坏数据灌进库。比如之前处理的是一个带合并单元格的Excelpandas读取时合并单元格会变成只有一行有值其余为NaN代码里一句ffill()就能把上面的值向下填充比手工在Excel里改快得多。这种灵活程度是Navicat和LOAD DATA没法比的。4.2 最小的可用代码先安装依赖pip install pandas openpyxl pymysql sqlalchemy然后写一段最小可用的导入脚本import pandas as pd from sqlalchemy import create_engine # 1. 读取Excel指定Sheet名 df pd.read_excel(订单数据.xlsx, sheet_nameSheet1, header0) # 2. 简单清洗去掉全空行把日期列转成字符串 df df.dropna(howall) df[订单日期] pd.to_datetime(df[订单日期]).dt.strftime(%Y-%m-%d %H:%M:%S) # 3. 连接MySQL engine create_engine(mysqlpymysql://用户名:密码127.0.0.1:3306/数据库名?charsetutf8mb4) # 4. 写入数据库 df.to_sql( order_detail, conengine, if_existsappend, indexFalse, chunksize500 )这里有几个细节header0表示从Excel第一行开始当表头。如果表头在第三行就要改成header2。pd.to_datetime统一日期格式避免导入后在MySQL里出现千奇百怪的日期字符串。to_sql的chunksize控制单批写入行数。实测500到1000是性能与稳定性的平衡区间。if_existsappend是追加如果改成replace会直接DROP表重建使用前务必确认。读取复杂Excel时还可以配合usecols指定列避免导入多余列。4.3 增量更新与幂等处理重复导入最怕的是跑两遍数据翻倍。解决思路是让导入操作具备幂等性也就是无论跑几次最终结果都一致。最简单的方法是在导入前删除当日或该批次的旧数据。engine.execute(DELETE FROM order_detail WHERE 批次号 %s, (batch_no,)) df.to_sql(order_detail, conengine, if_existsappend, indexFalse, chunksize500)如果表本身有唯一键比如订单号唯一可以用INSERT ... ON DUPLICATE KEY UPDATE。但pandas的to_sql不支持这种语法这时可以选择两步走先用临时表写入再执行一句INSERT INTO 正式表 SELECT * FROM 临时表 ON DUPLICATE KEY UPDATE ...。还有一个我踩过的坑to_sql默认会依据DataFrame的dtype推断MySQL字段类型比如字符串列可能是TEXT如果原始Excel很长就没问题但如果列本来应该是VARCHAR(32)就要在dtype参数里显式指定from sqlalchemy.types import VARCHAR, DATETIME df.to_sql( order_detail, conengine, if_existsappend, indexFalse, chunksize500, dtype{ 订单号: VARCHAR(50), 订单日期: DATETIME } )5. 实测一个月后总结出的五个高频坑工具和命令都聊完了最后分享这一个月里我实际遇到过、也在团队里帮人排查过的五个常见问题。这些坑在网上被问得很多但往往是零散信息我按排查链路整理了一下。5.1 报错1366 Incorrect string value如果导入时看到类似ERROR 1366 (HY000): Incorrect string value: \xE5\xBC\xA0... for column的错误基本可以确定字符集不一致。MySQL的字段字符集是utf8mb4但导入连接可能用的latin1。检查连接字符集的语句是SHOW VARIABLES LIKE character_set%;重点看character_set_client和character_set_connection。如果显示的是latin1执行SET NAMES utf8mb4;然后再执行导入。Navicat连接时可以在连接属性里设置编码SQLAlchemy连接串中也要带上charsetutf8mb4。另外如果表字段本身建成了latin1那就得先改表字符集。5.2 日期字段变成0000-00-00Excel里的空日期单元格导入到MySQL后经常变成0000-00-00这个值在严格SQL模式下会直接报错。你可以这样处理排查MySQL的sql_modeSELECT sql_mode;如果包含NO_ZERO_DATE那么写入0000-00-00会被拒绝。解决办法有两个在Excel里把空日期单元格先填充成业务约定的默认日期比如1970-01-01或者1970-01-01是通常可接受的默认值。修改sql_mode去掉NO_ZERO_DATE重启后生效。但我不建议为了一次导入去动全局配置影响范围太大。更推荐的做法导入过程中把日期列的空值统一置为NULL同时确保目标字段允许NULL。在SQL层面就是LOAD DATA INFILE ... SET created_at NULLIF(created_at, );或者在pandas里写df[日期] df[日期].fillna(pd.NaT)导出后就是NULL。5.3 Excel长数字精度丢失这个坑几乎每个人都遇到过。Excel里输入超过15位的数字比如订单号、身份证号超过15位后的部分会被抹成0。一旦变成了0再想从Excel里找回原始号码是不可能的。所以处理长数字要趁早Excel里先选中该列设置单元格格式为文本再粘贴数据。如果数据已经在列里且显示成科学计数法但看起来有15位以上其实原始数值已经受损。此时不要再用它做任何拼接直接回上游系统导一份纯文本备份。导入MySQL时长数字字段一律用VARCHAR。如果用数字类型不仅可能精度丢失还可能因为超过BIGINT范围而导入失败。5.4 导入中断后重复数据堆积导入跑到一半报错停了因为之前已经提交了一部分批次此时库里已经有数据。如果直接重跑整个导入表里就会出现一部分重复数据。解决办法其实在导入前就该做好规划如果表有唯一键利用INSERT IGNORE或ON DUPLICATE KEY UPDATE实现可重入。如果没有唯一键可以给表中增加一个导入批次号字段。每次导入前用同一个批次号先DELETE一行再执行导入这样中断重跑不会叠数据。在pandas脚本里批次号可以用时间戳生成batch_no datetime.now().strftime(%Y%m%d%H%M%S)写入时给每行都带上这个批次号。重跑时如果希望完全替换就删除该批次号对应的数据再导。5.5 批量写入引发的锁等待超时大规模写入时如果库里还有其他业务在读写同一张表容易触发Lock wait timeout exceeded; try restarting transaction。常见原因是InnoDB行锁等待超时默认值是50秒。遇到这个问题不要直接调innodb_lock_wait_timeout那会让后续业务卡得更久。正确做法往往是把导入动作放在业务低峰期或者分批提交每次导入例如5万行就COMMIT一次避免长事务持有锁太久。如果允许把导入文件按时间或ID分区分成多个小任务串行执行。检查是否有其他事务长期未提交。SELECT * FROM information_schema.innodb_trx;能查到正在执行的事务发现持锁过久的事务再判断是否应该处理。这类问题的排查思路就一句话先看事务再看超时时间不要一上来就改全局变量。这段试用下来我个人最深的体会是Excel导入MySQL本身不复杂难的是导入前对Excel数据的理解。编码、字段类型、空值、长数字这四关过好了后面无论用什么工具都顺。如果只是单次导入Navicat最省心如果是服务器端大文件LOAD DATA INFILE最利索如果要经常重复导花半个小时写个pandas脚本之后就是双击一下的事。