如果你日常和数据打交道大概率经历过这个场景Excel 里的表格排得整整齐齐领导一句“把这张表导入 MySQL我们需要跑数”结果打开客户端对着空表发呆。数据量小的时候复制粘贴还能勉强糊弄一旦上千行、几千行手写 SQL 不现实直接粘贴又容易错位串列。实际上Excel 数据导入 MySQL 的快捷方式非常多从图形化导入向导到命令行 LOAD DATA再到 Python 脚本批量处理每条路线都有各自的适用场景和坑。这篇文章不聊花活就把我实际用过的导入方案挨个拆一遍包括具体操作步骤、参数含义和踩过的坑。1. 导入之前先把Excel整理成MySQL认识的样子很多导入失败根本不是工具的问题而是源表的问题。Excel 给人看很友好给数据库看就浑身是刺。我见过最多的失败案例都是源表里有合并单元格、标题行、小计行或者日期格式千奇百怪。所以无论你用哪条导入路线第一步都是把 Excel 源文件整理成“数据库能直接消化”的样子。1.1 源表结构的三个硬性要求第一第一行必须是字段名。字段名尽量用简洁的英文或者不带空格的中文不要用“数量件”“日期(格式1)”这种带括号、空格和特殊字符的表头不然导入工具的字段映射界面会非常难用后面写 SQL 也麻烦。第二不能有合并单元格。合并单元格在导入时只会保留左上角那个值其余全是空值。如果表格里有跨行合并的标题比如第一行写着“2024年销售数据汇总”第二行才是真正的字段名那么请在导入前删掉第一行或者复制为值之后取消合并把字段名放到第一行。第三去掉所有小计、合计、总计行。Excel 里为了展示方便经常在底部加一行“合计12345”这行数据在导入工具眼里就是一条正常记录会被原样插进表里。要么删掉要么把数据区域单独复制到一个新工作表再导出。再说一个容易被忽略的点单元格里的公式。如果某列是VLOOKUP(...)或者B2*C2这种公式导入工具读取时可能取到缓存值也可能因为公式引用范围问题读取失败。最稳妥的做法是全选数据区域复制然后右键粘贴为值。1.2 字段类型映射大多数导入报错的根源Excel 里看到的东西和 MySQL 的数据类型不是一一对应的。经常出现的情况是Excel 里看是数字导入后前导零丢了Excel 里看是日期导入后变成一串错误值。我列一个常用的对照导入前对着检查一遍Excel 显示形态实际含义MySQL 推荐类型注意点12345普通整数INT超过 21 亿要用 BIGINT1,234.56带千分位的数字DECIMAL(10,2)先取消千分位否则带逗号导入数值列会报错00123文本型编号VARCHAR(50)必须在 Excel 里设为文本格式否则变成 1232024-01-05日期DATE保证全表格式统一2024-01-05 10:20:30日期时间DATETIME同理格式统一身份证号、长单号超长文本VARCHAR(50)文本格式避免科学计数法丢精度多行备注长文本VARCHAR(500) 或 TEXT注意 Excel 单元格内换行符的处理最常见的翻车现场是“文本型数字”。比如商品编码是00123Excel 默认把它当数字显示出来可能已经是123了。导入数据库后如果目标列又是 INT那这个编码就彻底废了。所以源表里凡是编码、单号、身份证号这类字段必须提前设置为文本格式确保单元格左上角出现绿色小三角。日期字段也是重灾区。Excel 里有人写2024/1/5有人写2024年1月5日有人写2024-01-05一旦混在一个文件里导入向导就识别不统一。建议在 Excel 里统一选中日期列设置单元格格式为yyyy-mm-dd或者直接用一个临时列用TEXT()函数转成标准格式。1.3 编码中文乱码的分水岭中文乱码是新手最容易遇到的问题而且排查起来很玄学。问题通常出在 CSV 文件的编码和 MySQL 连接字符集不一致上。在 Windows 上Excel 老版本保存 CSV 时默认用的是 ANSI 编码也就是 GBK。如果你直接把这份 CSV 用 MySQL Workbench 或者 LOAD DATA 按 utf8mb4 读取中文全部变成问号或乱码。解决办法有两个第一在 Excel 里另存为时选择“CSV UTF-8逗号分隔”第二如果已经是 ANSI 编码的文件在导入时指定CHARACTER SET GBK读取。还有一个肉眼看不到的坑Excel 保存的 UTF-8 CSV 会带 BOM文件开头的EF BB BF几个字节。MySQL 读取时 BOM 会被当成第一个字段名的一部分最常见的表现是导入后第一列字段名变成了\ufeffid。所以用编辑器打开 CSV 时如果看到文件开头有看不见的字符保存为“无 BOM 的 UTF-8”就可以。关于 MySQL 字符集建表时建议统一使用utf8mb4连接串里也要带charsetutf8mb4这一点在后面的 Python 方案里还会再提到。2. 图形化导入Navicat这类客户端怎么用最省事如果你只是偶尔导一次数据不想折腾命令行图形化客户端是最快的路线。这类工具的导入向导已经把 Excel 解析、字段映射、类型推断都封装好了你只需要点几下鼠标。2.1 什么时候优先选图形化工具我自己的判断标准是四条一次性导入、数据量不大几千行以内、表结构还没定、或者使用者不熟悉 SQL。满足任意两条就直接上图形化工具。图形化工具最大的价值是“看得见”——源字段和目标字段的映射关系一目了然哪一列对错了当场就能调不需要瞎猜。但它也有明显的短板大数据量下速度慢而且不方便自动化。同样是 5 万行数据图形化工具可能要跑一两分钟LOAD DATA 几秒就完事。另外图形化工具每次导入都要手动操作一遍如果你每周都要导同一份格式的新数据还坚持用向导那纯粹是给自己找罪受。2.2 Navicat导入向导的完整步骤Navicat 是我最常用的图形化客户端它的导入向导对 Excel 的支持很完整。完整的操作步骤如下打开数据库连接进入目标库。在目标表上右键选择“导入向导”Import Wizard。第一步选文件类型选 Excel 文件注意区分.xlsx和.xls。选择要导入的工作表。如果一个文件里有多个 sheet每个 sheet 都要单独做一次导入。选择目标表。如果表已经存在选“导入到现有表”或者选择“复制到新建表”如果希望追加数据选“追加记录”。进入字段映射界面。左边是 Excel 里的列右边是目标表的字段拖动或下拉选择对应的关系。不想要的列可以设为忽略。设置选项勾选“首行是字段名”日期格式选%Y-%m-%d或根据源数据实际情况选。点击开始等日志跑完检查有无错误行。整个流程里最容易出问题的是第 6 步的字段映射。特别是源表在末尾多了一列空列时映射界面会出现一个源列对应不上目标字段直接报错。所以源表在导出前最好把无用的空列删掉。2.3 先建表再导入还是让工具自动建表Navicat 支持导入时自动创建新表但我强烈建议你先用 SQL 把表建好再走导入向导。原因很简单工具自动建表得到的字段类型非常不可控它经常把所有的字符列都建成 TEXT或者把日期列建成 VARCHAR后面还得再改表结构得不偿失。先建表的做法是CREATE TABLE sales_data ( id BIGINT PRIMARY KEY AUTO_INCREMENT, order_no VARCHAR(50) NOT NULL, customer_name VARCHAR(100), order_date DATE, amount DECIMAL(12,2), create_time DATETIME DEFAULT CURRENT_TIMESTAMP ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;然后再在导入向导里选择“导入到现有表”手工映射字段。这样字段类型、长度、默认值都在你的掌控里导入后基本不需要二次修复。2.4 其他图形工具的几个不同处理方式不是所有人都有 Navicat我也经常被问到 MySQL Workbench、DBeaver、phpMyAdmin 怎么导。这几个工具的导入能力不太一样提前知道能少走弯路MySQL Workbench自带 Table Data Import Wizard但它只支持 CSV 和 JSON 格式不能直接读.xlsx。所以用 Workbench 的话Excel 必须先另存为 CSV再走导入向导。DBeaver支持更灵活的数据导入但连接 Excel 文件需要额外安装对应的驱动配置起来比 Navicat 麻烦一点。phpMyAdmin网页端有“导入”页签可以直接上传 CSV但受服务器上传大小限制大文件基本传不上去更适合小数据临时导入。图形化工具的选择其实没有绝对优劣核心是看你对哪个工具更熟。工具只是入口真正决定成败的是源表质量和字段映射这两点抓住了用哪个工具都能导进去。3. 命令行批量导入LOAD DATA INFILE才是真正的高效通道如果你要导几万行以上的数据或者想把导入做成一条可复用的 SQL 流程那必须认识LOAD DATA INFILE。这是 MySQL 原生的数据导入命令速度比图形化工具高一个量级。3.1 为什么Excel要走CSV这道桥LOAD DATA INFILE只认文本文件不认识.xlsx。所以走这条路线得先让 Excel 输出成 CSV 格式。这不是坏事反而帮你强制清理了一遍源数据公式会转成计算后的值合并单元格只会保留左上角的值小计行如果没删导入后你也会立刻发现问题。另存为 CSV 时注意两个细节。一是编码Windows 下优先选“CSV UTF-8逗号分隔”如果只能存成 ANSI那么后面 SQL 里要指定CHARACTER SET GBK。二是字段内含逗号的情况Excel 在另存为 CSV 时会自动用双引号把这类字段包起来比如北京,朝阳这正好对应 SQL 里的OPTIONALLY ENCLOSED BY 。3.2 LOAD DATA INFILE的基本用法假设现在有个销售数据文件sales.csv内容是order_no,customer_name,order_date,amount A1001,张三,2024-01-05,1200.50 A1002,李四,2024-01-06,800.00对应的导入 SQL 是LOAD DATA LOCAL INFILE /home/user/sales.csv INTO TABLE sales_data CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (order_no, customer_name, order_date, amount);逐段解释一下不然你改成自己的数据时容易懵LOCAL INFILE表示文件在客户端你执行命令的机器上。不加LOCAL要求文件必须放在 MySQL 服务器本机且受secure_file_priv目录限制。CHARACTER SET utf8mb4指定文件编码中文乱码的解决方案就藏在这一行。FIELDS TERMINATED BY ,列分隔符CSV 默认逗号。OPTIONALLY ENCLOSED BY 允许字段值被双引号包裹Excel 导出的 CSV 经常会这样。LINES TERMINATED BY \n行分隔符。这里有个坑Windows 下 Excel 另存的 CSV 行尾可能是\r\n如果导入后发现最后一列数据末尾多了\r把这里改成\r\n。IGNORE 1 LINES跳过第一行表头。最后的(order_no, ...)按顺序声明 CSV 中每列对应的目标字段。如果 CSV 的列顺序和表字段顺序不一致靠这个声明解决。3.3 权限与安全设置LOAD DATA LOCAL INFILE在默认配置下可能不会直接放行常见报错有两个ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides解决方案是在 MySQL 会话里开启SET GLOBAL local_infile 1;然后在客户端连接时加参数mysql --local-infile1 -u root -p如果不加LOCAL改用服务器本机路径则会遇到另一个限制——secure_file_priv。这个变量限制了服务器可以读取的文件目录你可以先查一下SHOW VARIABLES LIKE secure_file_priv;如果返回空值说明没有限制如果返回/var/lib/mysql-files/那文件必须放在这个目录里否则会报ERROR 1290。生产环境里我建议优先用服务器本机路径加secure_file_priv目录比开放local_infile更安全。3.4 用变量和函数处理日期、空值CSV 里的日期格式如果和 MySQL 期望的不一致直接导入会得到全零日期或者报错。比如 CSV 里是2024/1/5MySQL 的 DATE 类型根本不认识。这时候用自定义变量加STR_TO_DATE转换LOAD DATA LOCAL INFILE sales_temp.csv INTO TABLE sales_data CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (order_no, customer_name, order_date, amount) SET order_date STR_TO_DATE(order_date, %Y/%m/%d);这里的逻辑是先用order_date这个用户变量把 CSV 的第四列原样接住然后在SET里调用STR_TO_DATE()转成标准日期再写入目标列。如果转换失败MySQL 会把值置为NULL导入后你很容易发现哪些行有问题。空值处理也常用到SET。CSV 里的空字段导入后默认是空字符串不是 SQL 的NULL。如果你希望入库是NULL可以这样SET customer_name NULLIF(customer_name, );CSV 里如果本身写的是\NMySQL 会直接识别成NULL这也是一个原生技巧。有些列你根本不需要导但 CSV 里又占了位可以用dummy接收后丢弃(id, dummy, customer_name, order_date, amount)3.5 十万行级别的实测表现我导过一份 8 万行、30 列左右的业务数据CSV 文件约 20MBLOAD DATA LOCAL INFILE大概 5 秒内完成。同样一份文件图形化工具要跑一两分钟手写 INSERT 循环就不提了。这个差距在大数据量下会越来越明显。几个能让大文件导入更稳的操作导入前临时关闭外键检查导完再打开避免外键约束导致中途失败前提是你确定数据关系没问题SET FOREIGN_KEY_CHECKS 0; -- 执行 LOAD DATA SET FOREIGN_KEY_CHECKS 1;如果目标表是重新导入先 TRUNCATE 而不是 DELETETRUNCATE 不会产生大量 undo 日志速度更快。另外单个字段特别长时有可能会触发max_allowed_packet报错临时调大SET GLOBAL max_allowed_packet 67108864;这行语句不建议作为常规操作长期开着导入完最好恢复默认值。4. 脚本化路线用Python把导入变成可复用的流水线前面两条路线都手动每次导入都要人盯着。如果你的场景是“每周导一次新文件”或者“导入前要做点清洗逻辑”那就应该上 Python。4.1 什么时候值得上PythonPython 不是用来替代前面方案的它解决的是前面方案做不到或者做起来很痛苦的事。典型场景有四个周期性导入每周一都要处理一份新 Excel结构和路径基本固定。需要先清洗去重、去空、类型转换、合并列。多个源文件一个数据报告拆了 12 个 sheet每个 sheet 都要入库。需要增量更新不是无脑全量清空重导而是要和库里已有数据做比对只更新新增或变更的记录。如果你只是偶尔导一次Python 反而麻烦上图形化工具就够了。工具要用在刀刃上。4.2 pandas SQLAlchemy三板斧常用的组合是pandas负责读 Excel、清洗SQLAlchemy负责建连接和写库驱动用pymysql。一个最基础的完整脚本长这样import pandas as pd from sqlalchemy import create_engine, types engine create_engine( mysqlpymysql://root:passwordlocalhost:3306/testdb?charsetutf8mb4 ) df pd.read_excel(sales.xlsx, sheet_nameSheet1, dtypestr) # 把 pandas 的 NaN 替换成 None否则会写入 nan 字符串 df df.where(df.notna(), None) df.to_sql( sales_data, engine, if_existsappend, indexFalse, chunksize5000, dtype{ order_no: types.VARCHAR(50), customer_name: types.VARCHAR(100), order_date: types.DATE, amount: types.DECIMAL(12, 2), }, )几个关键点拆开说dtypestr读 Excel 时把所有列都当作字符串读入避免 pandas 自作主张把日期变成 Timestamp、把00123变成123。读进来之后你再按需转换主动权在自己手里。df.where(df.notna(), None)pandas 的缺失值默认是NaN直接写库会变成字符串nan。这一步把它转成 Python 的None写库时才能正确变成 SQL 的NULL。to_sql的dtype参数强制指定每列写入 MySQL 时的类型。不指定的话所有字符串列可能全部变成TEXT浪费存储也影响性能。chunksize5000控制分成多少行一批写入防止大批量写入时超过 MySQL 的 packet 限制或者触发超时。跑完脚本后再验证一下行数SELECT COUNT(*) FROM sales_data;和 Excel 右下角的状态栏计数比一下数字对得上基本就成功了。4.3 pandas自动类型推断的坑pandas 读取 Excel 时有一套自动类型推断机制这套机制在导入数据库时会变成麻烦。我踩过的坑有三个你大概率也会遇到第一个是日期列。Excel 里明明是2024-01-05pandas 读进来变成Timestamp(2024-01-05 00:00:00)写入DATE列时要么报错要么把时间部分也带进去。处理办法是读的时候指定解析或者读完后统一转df[order_date] pd.to_datetime(df[order_date], errorscoerce).dt.dateerrorscoerce表示解析失败的值变成NaT不会中断脚本之后你可以在后续步骤里筛选出来人工处理。第二个是文本型数字。Excel 里的编码列00123pandas 默认读成整数123前导零直接丢。解决办法是读文件时用dtype参数强制指定dtype_map {order_no: str, id_card: str} df pd.read_excel(data.xlsx, dtypedtype_map)第三个是长数字变科学计数法。身份证号这类 18 位数字如果 Excel 单元格不是文本格式pandas 读进来可能是1.23457e17精度已经丢了。这种问题源头在 Excel必须在源文件里就把列设为文本脚本层面只能发现并报警。4.4 从全量导入到增量同步如果每次导入都是清空重来那to_sql里用if_existsreplace就行。但实际业务更多是增量同步库里已经有一批历史数据新 Excel 只新增和更新一部分。增量同步我常用“临时表 合并 SQL”的组合。先把 Excel 写进临时表再执行一条INSERT ... ON DUPLICATE KEY UPDATE做合并INSERT INTO sales_data (order_no, customer_name, order_date, amount) SELECT order_no, customer_name, order_date, amount FROM temp_sales ON DUPLICATE KEY UPDATE customer_name VALUES(customer_name), amount VALUES(amount);前提是目标表的order_no有唯一索引。这样新数据写入已有数据按主键命中后更新字段不重复插入。定时自动化这块Linux 上用 cronWindows 上用任务计划程序定期执行脚本即可。我一般会在脚本里加一个日志输出记录每次导入的行数、失败行数和耗时出问题时有据可查。5. 导入后的验证清单与高频坑位数据导入成功界面显示“导入完成”这并不代表数据是对的。我在实际工作里养成了一个习惯导入后必做三步验证全部通过才敢说“导完了”。5.1 导入完成不等于数据正确第一步是行数对比。SELECT COUNT(*)和 Excel 里数据区域的行数右下角状态栏会显示计数逐一对齐。差一行都要查清楚多出来的往往是汇总行少了的大概率是某些行因为类型不合法被工具跳过。第二步是抽样对比。别只看总数随机取几条业务记录把 Excel 原文和数据库记录并排看。重点看日期、金额、文本型编码这三类最容易被悄悄改写的字段。第三步是字段体检用几条聚合 SQL 快速扫一遍空值异常SELECT COUNT(*) AS total, COUNT(order_no) AS has_order_no, COUNT(customer_name) AS has_name, COUNT(DISTINCT order_no) AS distinct_order_no, MIN(LENGTH(customer_name)) AS min_name_len FROM sales_data;如果has_name明显比total少而 Excel 里名字列明明没有空那基本可以肯定空值处理环节出了问题要么是空字符串要么是源表里有隐藏空格。5.2 高频坑乱码、日期、长数字、行尾符把这些年遇到的导入问题归类高频坑位基本集中在五个方向中文乱码排查三个点CSV 文件本身的编码、MySQL 连接字符集、表的默认字符集。最稳的组合是CSV 存成 UTF-8、连接串带charsetutf8mb4、表建为utf8mb4。日期变成0000-00-00目标列是 DATE源数据格式不合法。解决办法就是前面说的STR_TO_DATE或者源头把 Excel 日期统一成yyyy-mm-dd。长数字变成科学计数源头问题Excel 单元格必须设为文本格式转 CSV 时保持原样。行尾符\rWindows CSV 的行尾是\r\n按\n切分时每一行最后一个字段会带着一个\r。要么LINES TERMINATED BY \r\n要么导入后清一下执行UPDATE table SET col TRIM(col)。隐藏的空格Excel 数据里经常有“张三”和“张三 ”这种带尾随空格的值看起来一样分组统计时却是两行。建议导入前在 Excel 里用TRIM清洗或者导入后整体清理一次。还有一个不太常见但遇到就容易懵的Excel 单元格内容里本身就带逗号或双引号时另存为 CSV 会有复杂的转义规则导致 CSV 分列错乱。最直观的表现是 CSV 里出现多出来的双引号导入后某个字段多了一截内容。这种文件建议先在 Excel 里把逗号替换成其他分隔符再走导入流程。5.3 三条路线怎么选一张表说清楚到这一步读者里可能有三种人只想赶紧把数据导完的、追求速度的大批量场景、以及要长期维护数据流程的。选型逻辑我总结成一张表场景推荐路线理由一次性、几千行以内图形化导入快不写 SQL字段映射直观一次性、几万行以上Excel 转 CSV LOAD DATA原生接口性能强失败定位方便周期性更新、多文件、需清洗Python 脚本可复用、可定时、可加逻辑服务器本机已有 CSV 文件LOAD DATA服务器路径性能最好注意 secure-file-priv 限制我自己在项目里最常用的组合是日常一次性导入直接用客户端的导入向导凡是需要重复跑的表一律准备一份固定模板的 Excel——第一行英文表头、日期统一、编码列设成文本格式、不合并单元格、不写合计行——然后配一条写好的LOAD DATASQL 模板每次只改文件路径和参数。这套流程稳定跑了大半年基本没再为导入的事加过班。最后再分享一个容易忽略的操作习惯无论走哪条路线导入前先备份目标表。做法很简单CREATE TABLE sales_data_bak AS SELECT * FROM sales_data;万一导入的数据有问题至少有一条后悔路。导入这件事多花十秒备份能省后面十个小时的返工。 
长河君 · 2026/10/14 17:40:01 · 阅读 1.2k Excel数据导入MySQL的3种实操方案：从图形化到命令行再到Python
如果你日常和数据打交道大概率经历过这个场景Excel 里的表格排得整整齐齐领导一句“把这张表导入 MySQL我们需要跑数”结果打开客户端对着空表发呆。数据量小的时候复制粘贴还能勉强糊弄一旦上千行、几千行手写 SQL 不现实直接粘贴又容易错位串列。实际上Excel 数据导入 MySQL 的快捷方式非常多从图形化导入向导到命令行 LOAD DATA再到 Python 脚本批量处理每条路线都有各自的适用场景和坑。这篇文章不聊花活就把我实际用过的导入方案挨个拆一遍包括具体操作步骤、参数含义和踩过的坑。1. 导入之前先把Excel整理成MySQL认识的样子很多导入失败根本不是工具的问题而是源表的问题。Excel 给人看很友好给数据库看就浑身是刺。我见过最多的失败案例都是源表里有合并单元格、标题行、小计行或者日期格式千奇百怪。所以无论你用哪条导入路线第一步都是把 Excel 源文件整理成“数据库能直接消化”的样子。1.1 源表结构的三个硬性要求第一第一行必须是字段名。字段名尽量用简洁的英文或者不带空格的中文不要用“数量件”“日期(格式1)”这种带括号、空格和特殊字符的表头不然导入工具的字段映射界面会非常难用后面写 SQL 也麻烦。第二不能有合并单元格。合并单元格在导入时只会保留左上角那个值其余全是空值。如果表格里有跨行合并的标题比如第一行写着“2024年销售数据汇总”第二行才是真正的字段名那么请在导入前删掉第一行或者复制为值之后取消合并把字段名放到第一行。第三去掉所有小计、合计、总计行。Excel 里为了展示方便经常在底部加一行“合计12345”这行数据在导入工具眼里就是一条正常记录会被原样插进表里。要么删掉要么把数据区域单独复制到一个新工作表再导出。再说一个容易被忽略的点单元格里的公式。如果某列是VLOOKUP(...)或者B2*C2这种公式导入工具读取时可能取到缓存值也可能因为公式引用范围问题读取失败。最稳妥的做法是全选数据区域复制然后右键粘贴为值。1.2 字段类型映射大多数导入报错的根源Excel 里看到的东西和 MySQL 的数据类型不是一一对应的。经常出现的情况是Excel 里看是数字导入后前导零丢了Excel 里看是日期导入后变成一串错误值。我列一个常用的对照导入前对着检查一遍Excel 显示形态实际含义MySQL 推荐类型注意点12345普通整数INT超过 21 亿要用 BIGINT1,234.56带千分位的数字DECIMAL(10,2)先取消千分位否则带逗号导入数值列会报错00123文本型编号VARCHAR(50)必须在 Excel 里设为文本格式否则变成 1232024-01-05日期DATE保证全表格式统一2024-01-05 10:20:30日期时间DATETIME同理格式统一身份证号、长单号超长文本VARCHAR(50)文本格式避免科学计数法丢精度多行备注长文本VARCHAR(500) 或 TEXT注意 Excel 单元格内换行符的处理最常见的翻车现场是“文本型数字”。比如商品编码是00123Excel 默认把它当数字显示出来可能已经是123了。导入数据库后如果目标列又是 INT那这个编码就彻底废了。所以源表里凡是编码、单号、身份证号这类字段必须提前设置为文本格式确保单元格左上角出现绿色小三角。日期字段也是重灾区。Excel 里有人写2024/1/5有人写2024年1月5日有人写2024-01-05一旦混在一个文件里导入向导就识别不统一。建议在 Excel 里统一选中日期列设置单元格格式为yyyy-mm-dd或者直接用一个临时列用TEXT()函数转成标准格式。1.3 编码中文乱码的分水岭中文乱码是新手最容易遇到的问题而且排查起来很玄学。问题通常出在 CSV 文件的编码和 MySQL 连接字符集不一致上。在 Windows 上Excel 老版本保存 CSV 时默认用的是 ANSI 编码也就是 GBK。如果你直接把这份 CSV 用 MySQL Workbench 或者 LOAD DATA 按 utf8mb4 读取中文全部变成问号或乱码。解决办法有两个第一在 Excel 里另存为时选择“CSV UTF-8逗号分隔”第二如果已经是 ANSI 编码的文件在导入时指定CHARACTER SET GBK读取。还有一个肉眼看不到的坑Excel 保存的 UTF-8 CSV 会带 BOM文件开头的EF BB BF几个字节。MySQL 读取时 BOM 会被当成第一个字段名的一部分最常见的表现是导入后第一列字段名变成了\ufeffid。所以用编辑器打开 CSV 时如果看到文件开头有看不见的字符保存为“无 BOM 的 UTF-8”就可以。关于 MySQL 字符集建表时建议统一使用utf8mb4连接串里也要带charsetutf8mb4这一点在后面的 Python 方案里还会再提到。2. 图形化导入Navicat这类客户端怎么用最省事如果你只是偶尔导一次数据不想折腾命令行图形化客户端是最快的路线。这类工具的导入向导已经把 Excel 解析、字段映射、类型推断都封装好了你只需要点几下鼠标。2.1 什么时候优先选图形化工具我自己的判断标准是四条一次性导入、数据量不大几千行以内、表结构还没定、或者使用者不熟悉 SQL。满足任意两条就直接上图形化工具。图形化工具最大的价值是“看得见”——源字段和目标字段的映射关系一目了然哪一列对错了当场就能调不需要瞎猜。但它也有明显的短板大数据量下速度慢而且不方便自动化。同样是 5 万行数据图形化工具可能要跑一两分钟LOAD DATA 几秒就完事。另外图形化工具每次导入都要手动操作一遍如果你每周都要导同一份格式的新数据还坚持用向导那纯粹是给自己找罪受。2.2 Navicat导入向导的完整步骤Navicat 是我最常用的图形化客户端它的导入向导对 Excel 的支持很完整。完整的操作步骤如下打开数据库连接进入目标库。在目标表上右键选择“导入向导”Import Wizard。第一步选文件类型选 Excel 文件注意区分.xlsx和.xls。选择要导入的工作表。如果一个文件里有多个 sheet每个 sheet 都要单独做一次导入。选择目标表。如果表已经存在选“导入到现有表”或者选择“复制到新建表”如果希望追加数据选“追加记录”。进入字段映射界面。左边是 Excel 里的列右边是目标表的字段拖动或下拉选择对应的关系。不想要的列可以设为忽略。设置选项勾选“首行是字段名”日期格式选%Y-%m-%d或根据源数据实际情况选。点击开始等日志跑完检查有无错误行。整个流程里最容易出问题的是第 6 步的字段映射。特别是源表在末尾多了一列空列时映射界面会出现一个源列对应不上目标字段直接报错。所以源表在导出前最好把无用的空列删掉。2.3 先建表再导入还是让工具自动建表Navicat 支持导入时自动创建新表但我强烈建议你先用 SQL 把表建好再走导入向导。原因很简单工具自动建表得到的字段类型非常不可控它经常把所有的字符列都建成 TEXT或者把日期列建成 VARCHAR后面还得再改表结构得不偿失。先建表的做法是CREATE TABLE sales_data ( id BIGINT PRIMARY KEY AUTO_INCREMENT, order_no VARCHAR(50) NOT NULL, customer_name VARCHAR(100), order_date DATE, amount DECIMAL(12,2), create_time DATETIME DEFAULT CURRENT_TIMESTAMP ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;然后再在导入向导里选择“导入到现有表”手工映射字段。这样字段类型、长度、默认值都在你的掌控里导入后基本不需要二次修复。2.4 其他图形工具的几个不同处理方式不是所有人都有 Navicat我也经常被问到 MySQL Workbench、DBeaver、phpMyAdmin 怎么导。这几个工具的导入能力不太一样提前知道能少走弯路MySQL Workbench自带 Table Data Import Wizard但它只支持 CSV 和 JSON 格式不能直接读.xlsx。所以用 Workbench 的话Excel 必须先另存为 CSV再走导入向导。DBeaver支持更灵活的数据导入但连接 Excel 文件需要额外安装对应的驱动配置起来比 Navicat 麻烦一点。phpMyAdmin网页端有“导入”页签可以直接上传 CSV但受服务器上传大小限制大文件基本传不上去更适合小数据临时导入。图形化工具的选择其实没有绝对优劣核心是看你对哪个工具更熟。工具只是入口真正决定成败的是源表质量和字段映射这两点抓住了用哪个工具都能导进去。3. 命令行批量导入LOAD DATA INFILE才是真正的高效通道如果你要导几万行以上的数据或者想把导入做成一条可复用的 SQL 流程那必须认识LOAD DATA INFILE。这是 MySQL 原生的数据导入命令速度比图形化工具高一个量级。3.1 为什么Excel要走CSV这道桥LOAD DATA INFILE只认文本文件不认识.xlsx。所以走这条路线得先让 Excel 输出成 CSV 格式。这不是坏事反而帮你强制清理了一遍源数据公式会转成计算后的值合并单元格只会保留左上角的值小计行如果没删导入后你也会立刻发现问题。另存为 CSV 时注意两个细节。一是编码Windows 下优先选“CSV UTF-8逗号分隔”如果只能存成 ANSI那么后面 SQL 里要指定CHARACTER SET GBK。二是字段内含逗号的情况Excel 在另存为 CSV 时会自动用双引号把这类字段包起来比如北京,朝阳这正好对应 SQL 里的OPTIONALLY ENCLOSED BY 。3.2 LOAD DATA INFILE的基本用法假设现在有个销售数据文件sales.csv内容是order_no,customer_name,order_date,amount A1001,张三,2024-01-05,1200.50 A1002,李四,2024-01-06,800.00对应的导入 SQL 是LOAD DATA LOCAL INFILE /home/user/sales.csv INTO TABLE sales_data CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (order_no, customer_name, order_date, amount);逐段解释一下不然你改成自己的数据时容易懵LOCAL INFILE表示文件在客户端你执行命令的机器上。不加LOCAL要求文件必须放在 MySQL 服务器本机且受secure_file_priv目录限制。CHARACTER SET utf8mb4指定文件编码中文乱码的解决方案就藏在这一行。FIELDS TERMINATED BY ,列分隔符CSV 默认逗号。OPTIONALLY ENCLOSED BY 允许字段值被双引号包裹Excel 导出的 CSV 经常会这样。LINES TERMINATED BY \n行分隔符。这里有个坑Windows 下 Excel 另存的 CSV 行尾可能是\r\n如果导入后发现最后一列数据末尾多了\r把这里改成\r\n。IGNORE 1 LINES跳过第一行表头。最后的(order_no, ...)按顺序声明 CSV 中每列对应的目标字段。如果 CSV 的列顺序和表字段顺序不一致靠这个声明解决。3.3 权限与安全设置LOAD DATA LOCAL INFILE在默认配置下可能不会直接放行常见报错有两个ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides解决方案是在 MySQL 会话里开启SET GLOBAL local_infile 1;然后在客户端连接时加参数mysql --local-infile1 -u root -p如果不加LOCAL改用服务器本机路径则会遇到另一个限制——secure_file_priv。这个变量限制了服务器可以读取的文件目录你可以先查一下SHOW VARIABLES LIKE secure_file_priv;如果返回空值说明没有限制如果返回/var/lib/mysql-files/那文件必须放在这个目录里否则会报ERROR 1290。生产环境里我建议优先用服务器本机路径加secure_file_priv目录比开放local_infile更安全。3.4 用变量和函数处理日期、空值CSV 里的日期格式如果和 MySQL 期望的不一致直接导入会得到全零日期或者报错。比如 CSV 里是2024/1/5MySQL 的 DATE 类型根本不认识。这时候用自定义变量加STR_TO_DATE转换LOAD DATA LOCAL INFILE sales_temp.csv INTO TABLE sales_data CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (order_no, customer_name, order_date, amount) SET order_date STR_TO_DATE(order_date, %Y/%m/%d);这里的逻辑是先用order_date这个用户变量把 CSV 的第四列原样接住然后在SET里调用STR_TO_DATE()转成标准日期再写入目标列。如果转换失败MySQL 会把值置为NULL导入后你很容易发现哪些行有问题。空值处理也常用到SET。CSV 里的空字段导入后默认是空字符串不是 SQL 的NULL。如果你希望入库是NULL可以这样SET customer_name NULLIF(customer_name, );CSV 里如果本身写的是\NMySQL 会直接识别成NULL这也是一个原生技巧。有些列你根本不需要导但 CSV 里又占了位可以用dummy接收后丢弃(id, dummy, customer_name, order_date, amount)3.5 十万行级别的实测表现我导过一份 8 万行、30 列左右的业务数据CSV 文件约 20MBLOAD DATA LOCAL INFILE大概 5 秒内完成。同样一份文件图形化工具要跑一两分钟手写 INSERT 循环就不提了。这个差距在大数据量下会越来越明显。几个能让大文件导入更稳的操作导入前临时关闭外键检查导完再打开避免外键约束导致中途失败前提是你确定数据关系没问题SET FOREIGN_KEY_CHECKS 0; -- 执行 LOAD DATA SET FOREIGN_KEY_CHECKS 1;如果目标表是重新导入先 TRUNCATE 而不是 DELETETRUNCATE 不会产生大量 undo 日志速度更快。另外单个字段特别长时有可能会触发max_allowed_packet报错临时调大SET GLOBAL max_allowed_packet 67108864;这行语句不建议作为常规操作长期开着导入完最好恢复默认值。4. 脚本化路线用Python把导入变成可复用的流水线前面两条路线都手动每次导入都要人盯着。如果你的场景是“每周导一次新文件”或者“导入前要做点清洗逻辑”那就应该上 Python。4.1 什么时候值得上PythonPython 不是用来替代前面方案的它解决的是前面方案做不到或者做起来很痛苦的事。典型场景有四个周期性导入每周一都要处理一份新 Excel结构和路径基本固定。需要先清洗去重、去空、类型转换、合并列。多个源文件一个数据报告拆了 12 个 sheet每个 sheet 都要入库。需要增量更新不是无脑全量清空重导而是要和库里已有数据做比对只更新新增或变更的记录。如果你只是偶尔导一次Python 反而麻烦上图形化工具就够了。工具要用在刀刃上。4.2 pandas SQLAlchemy三板斧常用的组合是pandas负责读 Excel、清洗SQLAlchemy负责建连接和写库驱动用pymysql。一个最基础的完整脚本长这样import pandas as pd from sqlalchemy import create_engine, types engine create_engine( mysqlpymysql://root:passwordlocalhost:3306/testdb?charsetutf8mb4 ) df pd.read_excel(sales.xlsx, sheet_nameSheet1, dtypestr) # 把 pandas 的 NaN 替换成 None否则会写入 nan 字符串 df df.where(df.notna(), None) df.to_sql( sales_data, engine, if_existsappend, indexFalse, chunksize5000, dtype{ order_no: types.VARCHAR(50), customer_name: types.VARCHAR(100), order_date: types.DATE, amount: types.DECIMAL(12, 2), }, )几个关键点拆开说dtypestr读 Excel 时把所有列都当作字符串读入避免 pandas 自作主张把日期变成 Timestamp、把00123变成123。读进来之后你再按需转换主动权在自己手里。df.where(df.notna(), None)pandas 的缺失值默认是NaN直接写库会变成字符串nan。这一步把它转成 Python 的None写库时才能正确变成 SQL 的NULL。to_sql的dtype参数强制指定每列写入 MySQL 时的类型。不指定的话所有字符串列可能全部变成TEXT浪费存储也影响性能。chunksize5000控制分成多少行一批写入防止大批量写入时超过 MySQL 的 packet 限制或者触发超时。跑完脚本后再验证一下行数SELECT COUNT(*) FROM sales_data;和 Excel 右下角的状态栏计数比一下数字对得上基本就成功了。4.3 pandas自动类型推断的坑pandas 读取 Excel 时有一套自动类型推断机制这套机制在导入数据库时会变成麻烦。我踩过的坑有三个你大概率也会遇到第一个是日期列。Excel 里明明是2024-01-05pandas 读进来变成Timestamp(2024-01-05 00:00:00)写入DATE列时要么报错要么把时间部分也带进去。处理办法是读的时候指定解析或者读完后统一转df[order_date] pd.to_datetime(df[order_date], errorscoerce).dt.dateerrorscoerce表示解析失败的值变成NaT不会中断脚本之后你可以在后续步骤里筛选出来人工处理。第二个是文本型数字。Excel 里的编码列00123pandas 默认读成整数123前导零直接丢。解决办法是读文件时用dtype参数强制指定dtype_map {order_no: str, id_card: str} df pd.read_excel(data.xlsx, dtypedtype_map)第三个是长数字变科学计数法。身份证号这类 18 位数字如果 Excel 单元格不是文本格式pandas 读进来可能是1.23457e17精度已经丢了。这种问题源头在 Excel必须在源文件里就把列设为文本脚本层面只能发现并报警。4.4 从全量导入到增量同步如果每次导入都是清空重来那to_sql里用if_existsreplace就行。但实际业务更多是增量同步库里已经有一批历史数据新 Excel 只新增和更新一部分。增量同步我常用“临时表 合并 SQL”的组合。先把 Excel 写进临时表再执行一条INSERT ... ON DUPLICATE KEY UPDATE做合并INSERT INTO sales_data (order_no, customer_name, order_date, amount) SELECT order_no, customer_name, order_date, amount FROM temp_sales ON DUPLICATE KEY UPDATE customer_name VALUES(customer_name), amount VALUES(amount);前提是目标表的order_no有唯一索引。这样新数据写入已有数据按主键命中后更新字段不重复插入。定时自动化这块Linux 上用 cronWindows 上用任务计划程序定期执行脚本即可。我一般会在脚本里加一个日志输出记录每次导入的行数、失败行数和耗时出问题时有据可查。5. 导入后的验证清单与高频坑位数据导入成功界面显示“导入完成”这并不代表数据是对的。我在实际工作里养成了一个习惯导入后必做三步验证全部通过才敢说“导完了”。5.1 导入完成不等于数据正确第一步是行数对比。SELECT COUNT(*)和 Excel 里数据区域的行数右下角状态栏会显示计数逐一对齐。差一行都要查清楚多出来的往往是汇总行少了的大概率是某些行因为类型不合法被工具跳过。第二步是抽样对比。别只看总数随机取几条业务记录把 Excel 原文和数据库记录并排看。重点看日期、金额、文本型编码这三类最容易被悄悄改写的字段。第三步是字段体检用几条聚合 SQL 快速扫一遍空值异常SELECT COUNT(*) AS total, COUNT(order_no) AS has_order_no, COUNT(customer_name) AS has_name, COUNT(DISTINCT order_no) AS distinct_order_no, MIN(LENGTH(customer_name)) AS min_name_len FROM sales_data;如果has_name明显比total少而 Excel 里名字列明明没有空那基本可以肯定空值处理环节出了问题要么是空字符串要么是源表里有隐藏空格。5.2 高频坑乱码、日期、长数字、行尾符把这些年遇到的导入问题归类高频坑位基本集中在五个方向中文乱码排查三个点CSV 文件本身的编码、MySQL 连接字符集、表的默认字符集。最稳的组合是CSV 存成 UTF-8、连接串带charsetutf8mb4、表建为utf8mb4。日期变成0000-00-00目标列是 DATE源数据格式不合法。解决办法就是前面说的STR_TO_DATE或者源头把 Excel 日期统一成yyyy-mm-dd。长数字变成科学计数源头问题Excel 单元格必须设为文本格式转 CSV 时保持原样。行尾符\rWindows CSV 的行尾是\r\n按\n切分时每一行最后一个字段会带着一个\r。要么LINES TERMINATED BY \r\n要么导入后清一下执行UPDATE table SET col TRIM(col)。隐藏的空格Excel 数据里经常有“张三”和“张三 ”这种带尾随空格的值看起来一样分组统计时却是两行。建议导入前在 Excel 里用TRIM清洗或者导入后整体清理一次。还有一个不太常见但遇到就容易懵的Excel 单元格内容里本身就带逗号或双引号时另存为 CSV 会有复杂的转义规则导致 CSV 分列错乱。最直观的表现是 CSV 里出现多出来的双引号导入后某个字段多了一截内容。这种文件建议先在 Excel 里把逗号替换成其他分隔符再走导入流程。5.3 三条路线怎么选一张表说清楚到这一步读者里可能有三种人只想赶紧把数据导完的、追求速度的大批量场景、以及要长期维护数据流程的。选型逻辑我总结成一张表场景推荐路线理由一次性、几千行以内图形化导入快不写 SQL字段映射直观一次性、几万行以上Excel 转 CSV LOAD DATA原生接口性能强失败定位方便周期性更新、多文件、需清洗Python 脚本可复用、可定时、可加逻辑服务器本机已有 CSV 文件LOAD DATA服务器路径性能最好注意 secure-file-priv 限制我自己在项目里最常用的组合是日常一次性导入直接用客户端的导入向导凡是需要重复跑的表一律准备一份固定模板的 Excel——第一行英文表头、日期统一、编码列设成文本格式、不合并单元格、不写合计行——然后配一条写好的LOAD DATASQL 模板每次只改文件路径和参数。这套流程稳定跑了大半年基本没再为导入的事加过班。最后再分享一个容易忽略的操作习惯无论走哪条路线导入前先备份目标表。做法很简单CREATE TABLE sales_data_bak AS SELECT * FROM sales_data;万一导入的数据有问题至少有一条后悔路。导入这件事多花十秒备份能省后面十个小时的返工。