尧图网页设计 
简介《使用Navicat将Excel数据导入mysql》是一份PDF教程面向使用Navicat将Excel电子表格数据批量导入MySQL的开发者与数据库管理员重点弥补了网络上该功能教程零散、细节含糊的问题。教程以作者实际环境Navicat特定版本、MySQL 5.7为背景先讲解Excel文件的准备工作包括按数据库字段设置表头、因自增主键而忽略id列、将文件名改为纯英文等随后逐步演示通过Navicat导入向导选择Excel文件连续下一步并选择追加或覆盖模式最终启动导入并查看结果。整个流程覆盖了从准备到排错的关键环节导入成功会有明确提示遇到error可根据log定位原因。资源为1个PDF文档压缩包约189KB短小精悍适合需要快速完成Excel数据迁移的入门与中级用户。资源已有6600余人学习阅读按步骤操作即可避开常见坑点显著提升批量导入数据的效率。1. 不用复制粘贴Navicat 导 Excel 到 MySQL 的正确姿势业务方把一张几万行的 Excel 扔过来说帮我把这些数据导入 MySQL。第一次做这件事的人十有八九会打开数据库表直接复制粘贴然后看着满屏报错发呆——日期糊成一串数字中文变成问号手机号成了科学计数法。这不是操作失误是复制粘贴绕过了类型、编码和约束这三道关卡。Navicat 的导入向导、LOAD DATA 和 mysqlimport 才是正规做法它们能把 Excel 里的文本、数字、日期按预定规则写进 MySQL并在出错时留下明确日志。这篇文章按实际落地顺序拆开讲源表怎么准备、目标表怎么建、三种导入方式怎么选、字段映射怎么处理、哪些坑必须绕开。适合所有被 Excel 入库需求找上门的开发、运维和数据岗位。2. 导入前的两个准备先看 Excel 长什么样再定 MySQL 表结构2.1 检查 Excel 源表表头、列类型、脏数据拿到 Excel 后别急着打开 Navicat先花十分钟把源表过一遍。这一步省下的时间是后头的几十倍。重点看四件事表头是不是只有一行、列名有没有特殊字符、每一列的数据类型是否统一、有没有合并单元格和空行。表头必须是一行。很多业务表上面顶着两行标题比如“2024 年销售明细”加一行字段名导进 MySQL 后第一行直接变成数据最后清洗时烦到怀疑人生。字段名也尽量改成英文字母加下划线中文表头不是不行但遇到空格、括号、百分号这种字符后续写 SQL 得时刻记着加反引号。类型统一是重灾区金额列里出现“¥1,200”这种文本导入后 DECIMAL 类型直接报错或者被截断日期列里混着“2024/5/1”和“2024 年 5 月 1 日”两种写法后面 STR_TO_DATE 转换时必炸。合并单元格在 Excel 里看起来很整洁导入后只有第一个单元格有值其余全是 NULL空行则会造成主键或唯一键冲突。我把日常检查的清单放在下面照着过一遍基本不会漏检查点要求原因处理方法表头行数只有一行多行标题会被当数据导入删除多余行保留一行字段名字段名英文字母、数字、下划线特殊字符在 SQL 里要加反引号容易埋雷提前改名或导入后再用 ALTER TABLE 改列类型同列格式统一混型会导致类型转换失败在 Excel 里统一格式合并单元格全部取消合并后只有首格有值取消合并并填充空行全部删除空行导入后是 NULL 或空串筛选空行删除日期格式统一成 yyyy-mm-dd中文格式转换麻烦在 Excel 里先设置单元格格式长编号设为文本手机号、身份证会被科学计数法吃掉选中列设置单元格格式为文本最后一个小习惯把文件另存为 .xlsx。老的 .xls 格式 Navicat 也能读但大文件解析慢偶尔还会把超过 65536 行的数据直接截断。另存为时如果 CSV 导出优先选 UTF-8 编码这个在第五章讲乱码时还会再提。2.2 设计 MySQL 目标表字段类型和字符集一次定对目标表的结构决定了导入是顺利还是反复试错。我的习惯是先看完 Excel 每一列的内容再建表绝不先建表再看数据。下面这张销售订单表是典型的例子CREATE TABLE sales_order ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 自增主键, order_no VARCHAR(32) NOT NULL COMMENT 订单号Excel 里的文本, order_date DATETIME NOT NULL COMMENT 下单时间, amount DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT 订单金额, customer_name VARCHAR(64) DEFAULT NULL COMMENT 客户名称, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 导入时间, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci COMMENT销售订单表;几个参数值得说清楚。字符集选 utf8mb4 而不是 utf8因为 MySQL 的 utf8 最多存三个字节遇到 emoji 或生僻字会报错或变问号utf8mb4 是完整的 UTF-8导业务数据时省心得多。金额用 DECIMAL(12,2) 而不用 FLOAT是因为浮点数存金额会有精度误差0.1 加 0.2 变成 0.30000000000000004对账时解释不清。order_no 用 VARCHAR(32) 而不是 INT因为订单号这种字段只是在 Excel 里长得像数字实际不会参与加减乘除而且还要保留前导零。唯一键 uk_order_no 不是必须的但只要后续要用更新模式做增量导入它就是前提条件。2.3 字段命名对齐让导入向导少干活Navicat 导入向导里有一个字段映射页面如果 Excel 表头和目标表字段名一致它会自动按名字匹配省掉手工拖拽。所以建表时我建议让字段名尽量跟 Excel 表头一致或者在 Excel 里先把表头改成目标表字段名。不一致也没关系映射页面里手工对应一下就行但几百列的表手工拖起来确实费手。另外一个常被忽略的点建表时把 NOT NULL 和 DEFAULT 设置好。Excel 里空单元格导入后变成 NULL如果目标列是 NOT NULL导入直接报错中断如果列允许 NULL那么空值就保留了。哪种对取决于业务。金额不想出现 NULL就设 NOT NULL DEFAULT 0.00客户名允许空就保留 NULL 并允许为空。这一步设计对了导入时不会因为空值反复折腾。3. 三种导入方式导入向导、LOAD DATA、mysqlimport 怎么选把 Navicat 导 Excel 这件事拆开看本质上是“读取文件→解析类型→写入表”三步。Navicat 提供了三种入口图形界面的导入向导适合几万行以内的日常任务LOAD DATA INFILE 适合几十万行以上的批量导入mysqlimport 则是 LOAD DATA 的命令行封装适合写脚本定时跑。先记住这个选型结论下面逐个讲操作。3.1 导入向导适合几万行以内的日常导入导入向导是 Navicat 最直观的入口适合一次性导入、偶尔手工操作。步骤是这样连接 MySQL在左侧列表里找到目标表右键选择“导入向导”第一个页面选文件类型Excel 文件选 Excel 文件然后选择要导入的 .xlsx 或 .xls 文件选中工作表接下来就是关键的两个页面——字段映射和导入模式。字段映射页左边是 Excel 列右边是目标表字段中间可以拖拽对应。Excel 表头和字段名一致时会自动匹配上这一步基本不用动。导入模式页里首次导入空表选“追加”把数据直接插入如果同一份文件要反复导而且目标表有唯一键选“更新”模式重复执行时会覆盖已有记录而不是插入重复行。不同版本的 Navicat 对模式的定义略有差异以界面上的说明为准。最后点“开始”底部会显示进度和日志。导入完成后日志里能看到成功多少行、失败多少行失败的原因也会列出来。这里有个很多人不知道的小细节日志里提示的行数是从文件第一行开始算的也就是说如果把表头也算进去了日志里的行数和 Excel 数据行数差了 1别慌数一下映射页里是否勾选了“第一行作为字段名”。3.2 LOAD DATA INFILE大数据量 CSV 的导入主力当 Excel 超过十万行向导会明显变慢。这个量级我一般把 Excel 另存为 CSV然后用 LOAD DATA INFILE。它把文件解析和写入都放在 MySQL 内部完成速度比向导快一个数量级。命令如下mysql --local-infile1 -h127.0.0.1 -uroot -p sales_db SQL LOAD DATA LOCAL INFILE /tmp/sales_20240501.csv INTO TABLE sales_order CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (order_no, order_date, amount, customer_name); SQL这里用 heredoc 方式传 SQL避免在命令行里跟引号搏斗。几个参数是真实踩坑点--local-infile1表示文件在客户端机器上不加这个参数MySQL 会去服务器目录找文件找不到就报Cant get stat of file之类的错。FIELDS TERMINATED BY ,声明 CSV 的列分隔符如果文件里是用 Tab 分隔就改成\t。OPTIONALLY ENCLOSED BY 处理字段值里有逗号的情况比如客户名写成Smith, John没有这个参数逗号会被当成列分隔符整行错位。IGNORE 1 LINES跳过第一行表头如果导出 CSV 时没带表头这一行就不要写。最后括号里列出要导入的列名顺序必须和 CSV 里的列顺序一致。目标表里没列出来的字段会用默认值比如 created_at 会自动取当前时间。LOAD DATA 导入失败时错误提示会精确到行号和列号照着 CSV 打开对应行查就行这也是我优先用它处理大文件的原因。3.3 mysqlimport一条命令完成 CSV 导入mysqlimport 是 MySQL 自带的命令行工具底层调用的就是 LOAD DATA INFILE。适合把导入逻辑写进脚本、每天定时执行。基本用法mysqlimport --local --fields-terminated-by, \ --fields-optionally-enclosed-by \ --lines-terminated-by\n \ --ignore-lines1 \ -h127.0.0.1 -uroot -p \ sales_db /data/sales_order.csv它有两条约定必须先知道。第一文件名去掉扩展名后必须和表名一致比如sales_order.csv导入的是sales_order表sales_20240501.csv会尝试导入sales_20240501表表不存在直接报错。第二--fields-terminated-by、--fields-optionally-enclosed-by这两个参数和 LOAD DATA 里的 FIELDS 从句是一一对应的含义完全一样。用 mysqlimport 的好处是参数可以提前写在脚本里不用每次打开图形界面。它没有额外输出导入成功与否看退出码和报错信息所以脚本里记得加 echo ok || echo fail这类判断。4. 字段映射与类型转换从 Excel 的“文本”到 MySQL 的“类型”4.1 Excel 列类型和 MySQL 字段类型的对应关系Excel 里只有文本、数字、日期、布尔几种概念MySQL 里的类型却细分到几十种。导入时如果不做映射Navicat 会按自己的猜测给类型猜错的概率不低。表是这么对应比较稳Excel 里的样子MySQL 推荐类型注意点短文本姓名、地址VARCHAR(长度)长度按最大可能值给别抠长文本描述、备注TEXT不要用 VARCHAR(9999)会产生临时表排序性能问题整数数量、次数INT / BIGINT超过 20 亿用 BIGINT小数金额、单价DECIMAL(精度,标度)不要用 FLOAT/DOUBLE日期2024-05-01DATE / DATETIMEExcel 日期本质是序列号见 5.1布尔是/否TINYINT(1)值是 0/1别存 true/false身份证号、订单号VARCHAR(32)只要不是参与计算的数一律按文本重点说最后一行。身份证号、订单号、手机号在 Excel 里默认是数字格式超过 11 位会被科学计数法显示成1.38001E11后几位变成 0。导入 MySQL 后再想找回原始值神仙也救不回来。所以在 Excel 里先选中这些列设置单元格格式为文本再重新输入或粘贴才能保住完整内容。如果源文件已经生成没有原始数据可改唯一的后悔药是让业务方重新导出。4.2 在向导里手动修正字段类型导入向导的字段映射页面每一列的目标类型都可以手动改。Excel 里明明是数字、实际却是编号的列在映射时把目标列直接指定为 VARCHARNavicat 就会按字符串写入不会经过数字转换前导零也能保住。这个操作要在点“开始”之前完成导入中途改不了。LOAD DATA 的场景则在建表时把类型定好LOAD DATA 会按目标表字段类型做转换类型对不上的会报错中断。这里有个常见的错误认识以为导入时只要字段类型选对了Excel 里的格式问题就能自动纠正。实际上类型转换发生在 MySQL 侧Excel 源文件里若已经是科学计数法MySQL 拿到的是转换后的坏值不是原始值。所以第 4.1 节里改 Excel 源文件那步不是洁癖是必要操作。4.3 先导临时表再清洗入库有时候 Excel 里的数据质量确实差有千分位逗号、混着中文的日期、首尾空格。直接往正式表导失败率高不说反复试错还会留下半截数据。我一般会用“临时表接住原始数据清洗后转正式表”的链路先把 CSV 导到一张所有字段都定义为 VARCHAR 的临时表避开类型转换报错再写 INSERT INTO...SELECT 做清洗。-- 临时表先不设严格类型按文本接收原始数据 CREATE TABLE tmp_import ( order_no VARCHAR(64), order_date VARCHAR(32), amount VARCHAR(32), customer_name VARCHAR(128) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci; -- 清洗后写入正式表 INSERT INTO sales_order (order_no, order_date, amount, customer_name) SELECT order_no, STR_TO_DATE(order_date, %Y-%m-%d %H:%i:%s), CAST(REPLACE(amount, ,, ) AS DECIMAL(12,2)), TRIM(customer_name) FROM tmp_import WHERE order_no IS NOT NULL AND order_no ;这段 SQL 里做了三件事REPLACE 把金额里的千分位逗号去掉再转 DECIMAL否则 CAST 会报Invalid valueSTR_TO_DATE 把字符串日期按指定格式转成 DATETIME格式里%Y是四位数年份、%m是两位数月份、%H:%i:%s是时分秒如果 Excel 里日期是2024/5/1这种格式就要对应改成%Y/%m/%dTRIM 去掉客户名两端的空格避免后面按名字分组时同一个客户被拆成两条。最后 WHERE 过滤掉空行。4.4 空单元格和默认值的处理Excel 里的空单元格导入向导默认写入 NULLLOAD DATA 写入的也是 NULL。目标列是 NOT NULL 的导入会中断允许 NULL 的空值就留了下来。按业务需求不同处理方式也不同。如果空值需要变成业务默认值可以改表结构ALTER TABLE sales_order MODIFY COLUMN customer_name VARCHAR(64) NOT NULL DEFAULT 未知 COMMENT 客户名称;这样导入时空值会被替换成“未知”。要注意这条 ALTER 会把已存在的 NULL 也一并改成“未知”如果只想影响导入时的新数据就别用 MODIFY而是在 INSERT 的 SELECT 里用 COALESCE(customer_name, 未知)范围更可控。空转到 NULL 还是空字符串也建议保持默认不乱改因为 NULL 在 SQL 聚合时会被忽略空字符串不会这两个语义差很多。5. Navicat 导入 Excel 的五个常见坑和排查方法5.1 Excel 里的日期导入后变成 44231 这样的数字现象导入后 date 字段里的值是一串整数比如 44231不是日期。原因Excel 内部把日期存成从 1900-01-01 起算的序列号单元格显示成“2021-01-31”只是格式化效果。如果源文件里日期列不是真正的日期格式而是文本或常规格式Navicat 读取到的就是序列号本身。解决在 Excel 里选中日期列设置单元格格式为“日期”并指定 yyyy-mm-dd 样式然后再另存导入。如果数据已经导进去了可以用DATE_ADD(1900-01-01, INTERVAL 44231 - 2 DAY)换算回来但与其写这种绕弯的 SQL不如回去改源文件重导。5.2 中文导入后全是问号现象导入成功但 VARCHAR 字段里的中文变成一串问号。原因CSV 文件本身是 ANSI 编码GBK而导入时连接用的字符集是 utf8mb4或者反之。导入向导里的“文件编码”选项如果选错中文必然乱。解决Excel 另存为 CSV 时选择 UTF-8 编码而不是默认的“CSV逗号分隔”。在 Navicat 导入向导的第一个页面里把文件编码显式选成 UTF-8LOAD DATA 里对应CHARACTER SET utf8mb4这一句。已经导进去的乱码数据没有修复价值直接清空重导。用 LOAD DATA 导入前可以用file命令先看一眼文件编码file -bi sales.csv输出里能看到 charsetutf-8 还是 us-ascii。5.3 导入到一半报 Duplicate entry for key现象导到几千行时报错提示Duplicate entry xxx for key uk_order_no导入中断。原因目标表有主键或唯一键Excel 里有重复记录或者之前导过一遍这次没选更新模式而是选了追加。解决先定位重复数据在哪。如果只在源文件里重复清洗源文件如果是重复导入在导入模式的选项里选“更新”让重复行走更新而不是插入。用 LOAD DATA 时没有“更新”这个选项需要在导入前先清理目标表或者改用 INSERT ... ON DUPLICATE KEY UPDATE 的方式这个在第六章会给出具体写法。5.4 编号 00123 变成了 123现象工号、编号这类带前导零的列导入后前导零全部消失。原因Excel 把这列当数字处理了00123 显示时被转成 123。根源在源文件不在 Navicat。解决Excel 里把该列设为文本格式后重新录入。如果已经是这种坏数据导入后可以用 LPAD 补零但前提是知道原始长度UPDATE sales_order SET order_no LPAD(order_no, 6, 0) WHERE LENGTH(order_no) 6;这个 SQL 把不足 6 位的编号在左侧补零。前提是编号原本是等长的如果有些编号本身就是 5 位有些是 6 位LPAD 会补错。所以最稳的做法还是从源头改 Excel。5.5 几十万行导入慢到像卡死现象导入向导跑了十几分钟没结束进度条不动数据库连接看着也正常。原因导入向导逐行执行 INSERT 并立即提交每条记录一次网络往返目标表上的索引越多写一条要同步维护的索引就越多。解决换成 LOAD DATA INFILE它把解析和写入都放到 MySQL 内部一次性完成。还可以在导入前临时删掉非必要的索引导完再重建。删除唯一索引会影响更新模式的去重所以一般只删辅助索引主键保留-- 导入前删掉非唯一索引 ALTER TABLE sales_order DROP INDEX idx_order_date; -- 导入完成后再加回来 ALTER TABLE sales_order ADD INDEX idx_order_date (order_date);这类慢问题十次有八次不是 MySQL 慢而是导入方式选错了。数据量超过五万行直接走 LOAD DATA别跟向导较劲。6. 验证与增量导入的进阶用法导入之后还要做的事6.1 三个必跑的验证查询导入成功不等于数据正确。我无论用哪种方式导入完成后都会先跑三个查询行数对比、金额汇总对比、日期范围抽查确认没问题才告诉业务方。-- 行数核对和 Excel 数据行数去掉表头对比 SELECT COUNT(*) FROM sales_order; -- 金额对比按月份汇总跟 Excel 里的透视表结果对一下 SELECT DATE_FORMAT(order_date, %Y-%m) AS month, SUM(amount) FROM sales_order GROUP BY month; -- 边界检查日期不能出现 0000-00-00 或超出业务范围 SELECT MIN(order_date), MAX(order_date) FROM sales_order;行数对不上的情况优先查导入日志里失败的行金额对不上排查是不是有空值被当成 0、或者浮点精度问题。这三条 SQL 跑完心里就有底了。6.2 全量重导和增量更新的两种模式日常运维中Excel 数据每天都在变分两种套路。全量重导适合数据量不大、每天直接用新文件覆盖的场景先 TRUNCATE 清空目标表再 LOAD DATA 导入。增量更新适合源文件里只有新增和修改、不能清空历史数据的场景利用唯一键做更新INSERT INTO sales_order (order_no, order_date, amount, customer_name) SELECT order_no, order_date, amount, customer_name FROM tmp_import AS src ON DUPLICATE KEY UPDATE amount src.amount, customer_name src.customer_name;注意这里写的是src.amount这种别名方式MySQL 8.0.20 之后已经弃用了老式的VALUES(amount)写法虽然老写法还能跑但会报警告。这段 SQL 的前提是 order_no 上有唯一键没有唯一键就没有“重复”这个依据。6.3 用 cron 定时跑导入脚本如果每天都有人往固定目录扔 CSV可以把导入脚本化。mysqlimport 的参数写进脚本配一条 cron#!/bin/bash # 每天 02:10 把当天导出的 CSV 追加进 MySQL CSV/data/sales_$(date %Y%m%d).csv if [ -f $CSV ]; then mysqlimport --local --fields-terminated-by, \ --fields-optionally-enclosed-by \ --lines-terminated-by\n \ --ignore-lines1 \ --defaults-extra-file/etc/mysql-client.cnf \ sales_db $CSV echo $(date) import ok /var/log/excel_import.log fi密码不要写在命令行里用--defaults-extra-file指定一个权限为 600 的配置文件避免ps命令直接看到密码。脚本里文件名用日期拼接所以 mysqlimport 对文件命名的要求必须满足文件名去掉扩展名后得和表名一致否则它找不到目标表脚本会一直静默失败。我自己的习惯是每次导入后先跑 COUNT(*) 和 SUM(amount)确认无误再离开。有一回图省事看到“导入成功”就关了窗口第二天报表对不上查了半天发现是漏了最后一页 Excel 数据没导全。希望这份流程能帮你少走这段弯路。本文还有配套的精品资源点击获取 Navicat导入Excel到MySQL：三种方式、字段映射与避坑指南
简介《使用Navicat将Excel数据导入mysql》是一份PDF教程面向使用Navicat将Excel电子表格数据批量导入MySQL的开发者与数据库管理员重点弥补了网络上该功能教程零散、细节含糊的问题。教程以作者实际环境Navicat特定版本、MySQL 5.7为背景先讲解Excel文件的准备工作包括按数据库字段设置表头、因自增主键而忽略id列、将文件名改为纯英文等随后逐步演示通过Navicat导入向导选择Excel文件连续下一步并选择追加或覆盖模式最终启动导入并查看结果。整个流程覆盖了从准备到排错的关键环节导入成功会有明确提示遇到error可根据log定位原因。资源为1个PDF文档压缩包约189KB短小精悍适合需要快速完成Excel数据迁移的入门与中级用户。资源已有6600余人学习阅读按步骤操作即可避开常见坑点显著提升批量导入数据的效率。1. 不用复制粘贴Navicat 导 Excel 到 MySQL 的正确姿势业务方把一张几万行的 Excel 扔过来说帮我把这些数据导入 MySQL。第一次做这件事的人十有八九会打开数据库表直接复制粘贴然后看着满屏报错发呆——日期糊成一串数字中文变成问号手机号成了科学计数法。这不是操作失误是复制粘贴绕过了类型、编码和约束这三道关卡。Navicat 的导入向导、LOAD DATA 和 mysqlimport 才是正规做法它们能把 Excel 里的文本、数字、日期按预定规则写进 MySQL并在出错时留下明确日志。这篇文章按实际落地顺序拆开讲源表怎么准备、目标表怎么建、三种导入方式怎么选、字段映射怎么处理、哪些坑必须绕开。适合所有被 Excel 入库需求找上门的开发、运维和数据岗位。2. 导入前的两个准备先看 Excel 长什么样再定 MySQL 表结构2.1 检查 Excel 源表表头、列类型、脏数据拿到 Excel 后别急着打开 Navicat先花十分钟把源表过一遍。这一步省下的时间是后头的几十倍。重点看四件事表头是不是只有一行、列名有没有特殊字符、每一列的数据类型是否统一、有没有合并单元格和空行。表头必须是一行。很多业务表上面顶着两行标题比如“2024 年销售明细”加一行字段名导进 MySQL 后第一行直接变成数据最后清洗时烦到怀疑人生。字段名也尽量改成英文字母加下划线中文表头不是不行但遇到空格、括号、百分号这种字符后续写 SQL 得时刻记着加反引号。类型统一是重灾区金额列里出现“¥1,200”这种文本导入后 DECIMAL 类型直接报错或者被截断日期列里混着“2024/5/1”和“2024 年 5 月 1 日”两种写法后面 STR_TO_DATE 转换时必炸。合并单元格在 Excel 里看起来很整洁导入后只有第一个单元格有值其余全是 NULL空行则会造成主键或唯一键冲突。我把日常检查的清单放在下面照着过一遍基本不会漏检查点要求原因处理方法表头行数只有一行多行标题会被当数据导入删除多余行保留一行字段名字段名英文字母、数字、下划线特殊字符在 SQL 里要加反引号容易埋雷提前改名或导入后再用 ALTER TABLE 改列类型同列格式统一混型会导致类型转换失败在 Excel 里统一格式合并单元格全部取消合并后只有首格有值取消合并并填充空行全部删除空行导入后是 NULL 或空串筛选空行删除日期格式统一成 yyyy-mm-dd中文格式转换麻烦在 Excel 里先设置单元格格式长编号设为文本手机号、身份证会被科学计数法吃掉选中列设置单元格格式为文本最后一个小习惯把文件另存为 .xlsx。老的 .xls 格式 Navicat 也能读但大文件解析慢偶尔还会把超过 65536 行的数据直接截断。另存为时如果 CSV 导出优先选 UTF-8 编码这个在第五章讲乱码时还会再提。2.2 设计 MySQL 目标表字段类型和字符集一次定对目标表的结构决定了导入是顺利还是反复试错。我的习惯是先看完 Excel 每一列的内容再建表绝不先建表再看数据。下面这张销售订单表是典型的例子CREATE TABLE sales_order ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 自增主键, order_no VARCHAR(32) NOT NULL COMMENT 订单号Excel 里的文本, order_date DATETIME NOT NULL COMMENT 下单时间, amount DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT 订单金额, customer_name VARCHAR(64) DEFAULT NULL COMMENT 客户名称, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 导入时间, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci COMMENT销售订单表;几个参数值得说清楚。字符集选 utf8mb4 而不是 utf8因为 MySQL 的 utf8 最多存三个字节遇到 emoji 或生僻字会报错或变问号utf8mb4 是完整的 UTF-8导业务数据时省心得多。金额用 DECIMAL(12,2) 而不用 FLOAT是因为浮点数存金额会有精度误差0.1 加 0.2 变成 0.30000000000000004对账时解释不清。order_no 用 VARCHAR(32) 而不是 INT因为订单号这种字段只是在 Excel 里长得像数字实际不会参与加减乘除而且还要保留前导零。唯一键 uk_order_no 不是必须的但只要后续要用更新模式做增量导入它就是前提条件。2.3 字段命名对齐让导入向导少干活Navicat 导入向导里有一个字段映射页面如果 Excel 表头和目标表字段名一致它会自动按名字匹配省掉手工拖拽。所以建表时我建议让字段名尽量跟 Excel 表头一致或者在 Excel 里先把表头改成目标表字段名。不一致也没关系映射页面里手工对应一下就行但几百列的表手工拖起来确实费手。另外一个常被忽略的点建表时把 NOT NULL 和 DEFAULT 设置好。Excel 里空单元格导入后变成 NULL如果目标列是 NOT NULL导入直接报错中断如果列允许 NULL那么空值就保留了。哪种对取决于业务。金额不想出现 NULL就设 NOT NULL DEFAULT 0.00客户名允许空就保留 NULL 并允许为空。这一步设计对了导入时不会因为空值反复折腾。3. 三种导入方式导入向导、LOAD DATA、mysqlimport 怎么选把 Navicat 导 Excel 这件事拆开看本质上是“读取文件→解析类型→写入表”三步。Navicat 提供了三种入口图形界面的导入向导适合几万行以内的日常任务LOAD DATA INFILE 适合几十万行以上的批量导入mysqlimport 则是 LOAD DATA 的命令行封装适合写脚本定时跑。先记住这个选型结论下面逐个讲操作。3.1 导入向导适合几万行以内的日常导入导入向导是 Navicat 最直观的入口适合一次性导入、偶尔手工操作。步骤是这样连接 MySQL在左侧列表里找到目标表右键选择“导入向导”第一个页面选文件类型Excel 文件选 Excel 文件然后选择要导入的 .xlsx 或 .xls 文件选中工作表接下来就是关键的两个页面——字段映射和导入模式。字段映射页左边是 Excel 列右边是目标表字段中间可以拖拽对应。Excel 表头和字段名一致时会自动匹配上这一步基本不用动。导入模式页里首次导入空表选“追加”把数据直接插入如果同一份文件要反复导而且目标表有唯一键选“更新”模式重复执行时会覆盖已有记录而不是插入重复行。不同版本的 Navicat 对模式的定义略有差异以界面上的说明为准。最后点“开始”底部会显示进度和日志。导入完成后日志里能看到成功多少行、失败多少行失败的原因也会列出来。这里有个很多人不知道的小细节日志里提示的行数是从文件第一行开始算的也就是说如果把表头也算进去了日志里的行数和 Excel 数据行数差了 1别慌数一下映射页里是否勾选了“第一行作为字段名”。3.2 LOAD DATA INFILE大数据量 CSV 的导入主力当 Excel 超过十万行向导会明显变慢。这个量级我一般把 Excel 另存为 CSV然后用 LOAD DATA INFILE。它把文件解析和写入都放在 MySQL 内部完成速度比向导快一个数量级。命令如下mysql --local-infile1 -h127.0.0.1 -uroot -p sales_db SQL LOAD DATA LOCAL INFILE /tmp/sales_20240501.csv INTO TABLE sales_order CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (order_no, order_date, amount, customer_name); SQL这里用 heredoc 方式传 SQL避免在命令行里跟引号搏斗。几个参数是真实踩坑点--local-infile1表示文件在客户端机器上不加这个参数MySQL 会去服务器目录找文件找不到就报Cant get stat of file之类的错。FIELDS TERMINATED BY ,声明 CSV 的列分隔符如果文件里是用 Tab 分隔就改成\t。OPTIONALLY ENCLOSED BY 处理字段值里有逗号的情况比如客户名写成Smith, John没有这个参数逗号会被当成列分隔符整行错位。IGNORE 1 LINES跳过第一行表头如果导出 CSV 时没带表头这一行就不要写。最后括号里列出要导入的列名顺序必须和 CSV 里的列顺序一致。目标表里没列出来的字段会用默认值比如 created_at 会自动取当前时间。LOAD DATA 导入失败时错误提示会精确到行号和列号照着 CSV 打开对应行查就行这也是我优先用它处理大文件的原因。3.3 mysqlimport一条命令完成 CSV 导入mysqlimport 是 MySQL 自带的命令行工具底层调用的就是 LOAD DATA INFILE。适合把导入逻辑写进脚本、每天定时执行。基本用法mysqlimport --local --fields-terminated-by, \ --fields-optionally-enclosed-by \ --lines-terminated-by\n \ --ignore-lines1 \ -h127.0.0.1 -uroot -p \ sales_db /data/sales_order.csv它有两条约定必须先知道。第一文件名去掉扩展名后必须和表名一致比如sales_order.csv导入的是sales_order表sales_20240501.csv会尝试导入sales_20240501表表不存在直接报错。第二--fields-terminated-by、--fields-optionally-enclosed-by这两个参数和 LOAD DATA 里的 FIELDS 从句是一一对应的含义完全一样。用 mysqlimport 的好处是参数可以提前写在脚本里不用每次打开图形界面。它没有额外输出导入成功与否看退出码和报错信息所以脚本里记得加 echo ok || echo fail这类判断。4. 字段映射与类型转换从 Excel 的“文本”到 MySQL 的“类型”4.1 Excel 列类型和 MySQL 字段类型的对应关系Excel 里只有文本、数字、日期、布尔几种概念MySQL 里的类型却细分到几十种。导入时如果不做映射Navicat 会按自己的猜测给类型猜错的概率不低。表是这么对应比较稳Excel 里的样子MySQL 推荐类型注意点短文本姓名、地址VARCHAR(长度)长度按最大可能值给别抠长文本描述、备注TEXT不要用 VARCHAR(9999)会产生临时表排序性能问题整数数量、次数INT / BIGINT超过 20 亿用 BIGINT小数金额、单价DECIMAL(精度,标度)不要用 FLOAT/DOUBLE日期2024-05-01DATE / DATETIMEExcel 日期本质是序列号见 5.1布尔是/否TINYINT(1)值是 0/1别存 true/false身份证号、订单号VARCHAR(32)只要不是参与计算的数一律按文本重点说最后一行。身份证号、订单号、手机号在 Excel 里默认是数字格式超过 11 位会被科学计数法显示成1.38001E11后几位变成 0。导入 MySQL 后再想找回原始值神仙也救不回来。所以在 Excel 里先选中这些列设置单元格格式为文本再重新输入或粘贴才能保住完整内容。如果源文件已经生成没有原始数据可改唯一的后悔药是让业务方重新导出。4.2 在向导里手动修正字段类型导入向导的字段映射页面每一列的目标类型都可以手动改。Excel 里明明是数字、实际却是编号的列在映射时把目标列直接指定为 VARCHARNavicat 就会按字符串写入不会经过数字转换前导零也能保住。这个操作要在点“开始”之前完成导入中途改不了。LOAD DATA 的场景则在建表时把类型定好LOAD DATA 会按目标表字段类型做转换类型对不上的会报错中断。这里有个常见的错误认识以为导入时只要字段类型选对了Excel 里的格式问题就能自动纠正。实际上类型转换发生在 MySQL 侧Excel 源文件里若已经是科学计数法MySQL 拿到的是转换后的坏值不是原始值。所以第 4.1 节里改 Excel 源文件那步不是洁癖是必要操作。4.3 先导临时表再清洗入库有时候 Excel 里的数据质量确实差有千分位逗号、混着中文的日期、首尾空格。直接往正式表导失败率高不说反复试错还会留下半截数据。我一般会用“临时表接住原始数据清洗后转正式表”的链路先把 CSV 导到一张所有字段都定义为 VARCHAR 的临时表避开类型转换报错再写 INSERT INTO...SELECT 做清洗。-- 临时表先不设严格类型按文本接收原始数据 CREATE TABLE tmp_import ( order_no VARCHAR(64), order_date VARCHAR(32), amount VARCHAR(32), customer_name VARCHAR(128) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci; -- 清洗后写入正式表 INSERT INTO sales_order (order_no, order_date, amount, customer_name) SELECT order_no, STR_TO_DATE(order_date, %Y-%m-%d %H:%i:%s), CAST(REPLACE(amount, ,, ) AS DECIMAL(12,2)), TRIM(customer_name) FROM tmp_import WHERE order_no IS NOT NULL AND order_no ;这段 SQL 里做了三件事REPLACE 把金额里的千分位逗号去掉再转 DECIMAL否则 CAST 会报Invalid valueSTR_TO_DATE 把字符串日期按指定格式转成 DATETIME格式里%Y是四位数年份、%m是两位数月份、%H:%i:%s是时分秒如果 Excel 里日期是2024/5/1这种格式就要对应改成%Y/%m/%dTRIM 去掉客户名两端的空格避免后面按名字分组时同一个客户被拆成两条。最后 WHERE 过滤掉空行。4.4 空单元格和默认值的处理Excel 里的空单元格导入向导默认写入 NULLLOAD DATA 写入的也是 NULL。目标列是 NOT NULL 的导入会中断允许 NULL 的空值就留了下来。按业务需求不同处理方式也不同。如果空值需要变成业务默认值可以改表结构ALTER TABLE sales_order MODIFY COLUMN customer_name VARCHAR(64) NOT NULL DEFAULT 未知 COMMENT 客户名称;这样导入时空值会被替换成“未知”。要注意这条 ALTER 会把已存在的 NULL 也一并改成“未知”如果只想影响导入时的新数据就别用 MODIFY而是在 INSERT 的 SELECT 里用 COALESCE(customer_name, 未知)范围更可控。空转到 NULL 还是空字符串也建议保持默认不乱改因为 NULL 在 SQL 聚合时会被忽略空字符串不会这两个语义差很多。5. Navicat 导入 Excel 的五个常见坑和排查方法5.1 Excel 里的日期导入后变成 44231 这样的数字现象导入后 date 字段里的值是一串整数比如 44231不是日期。原因Excel 内部把日期存成从 1900-01-01 起算的序列号单元格显示成“2021-01-31”只是格式化效果。如果源文件里日期列不是真正的日期格式而是文本或常规格式Navicat 读取到的就是序列号本身。解决在 Excel 里选中日期列设置单元格格式为“日期”并指定 yyyy-mm-dd 样式然后再另存导入。如果数据已经导进去了可以用DATE_ADD(1900-01-01, INTERVAL 44231 - 2 DAY)换算回来但与其写这种绕弯的 SQL不如回去改源文件重导。5.2 中文导入后全是问号现象导入成功但 VARCHAR 字段里的中文变成一串问号。原因CSV 文件本身是 ANSI 编码GBK而导入时连接用的字符集是 utf8mb4或者反之。导入向导里的“文件编码”选项如果选错中文必然乱。解决Excel 另存为 CSV 时选择 UTF-8 编码而不是默认的“CSV逗号分隔”。在 Navicat 导入向导的第一个页面里把文件编码显式选成 UTF-8LOAD DATA 里对应CHARACTER SET utf8mb4这一句。已经导进去的乱码数据没有修复价值直接清空重导。用 LOAD DATA 导入前可以用file命令先看一眼文件编码file -bi sales.csv输出里能看到 charsetutf-8 还是 us-ascii。5.3 导入到一半报 Duplicate entry for key现象导到几千行时报错提示Duplicate entry xxx for key uk_order_no导入中断。原因目标表有主键或唯一键Excel 里有重复记录或者之前导过一遍这次没选更新模式而是选了追加。解决先定位重复数据在哪。如果只在源文件里重复清洗源文件如果是重复导入在导入模式的选项里选“更新”让重复行走更新而不是插入。用 LOAD DATA 时没有“更新”这个选项需要在导入前先清理目标表或者改用 INSERT ... ON DUPLICATE KEY UPDATE 的方式这个在第六章会给出具体写法。5.4 编号 00123 变成了 123现象工号、编号这类带前导零的列导入后前导零全部消失。原因Excel 把这列当数字处理了00123 显示时被转成 123。根源在源文件不在 Navicat。解决Excel 里把该列设为文本格式后重新录入。如果已经是这种坏数据导入后可以用 LPAD 补零但前提是知道原始长度UPDATE sales_order SET order_no LPAD(order_no, 6, 0) WHERE LENGTH(order_no) 6;这个 SQL 把不足 6 位的编号在左侧补零。前提是编号原本是等长的如果有些编号本身就是 5 位有些是 6 位LPAD 会补错。所以最稳的做法还是从源头改 Excel。5.5 几十万行导入慢到像卡死现象导入向导跑了十几分钟没结束进度条不动数据库连接看着也正常。原因导入向导逐行执行 INSERT 并立即提交每条记录一次网络往返目标表上的索引越多写一条要同步维护的索引就越多。解决换成 LOAD DATA INFILE它把解析和写入都放到 MySQL 内部一次性完成。还可以在导入前临时删掉非必要的索引导完再重建。删除唯一索引会影响更新模式的去重所以一般只删辅助索引主键保留-- 导入前删掉非唯一索引 ALTER TABLE sales_order DROP INDEX idx_order_date; -- 导入完成后再加回来 ALTER TABLE sales_order ADD INDEX idx_order_date (order_date);这类慢问题十次有八次不是 MySQL 慢而是导入方式选错了。数据量超过五万行直接走 LOAD DATA别跟向导较劲。6. 验证与增量导入的进阶用法导入之后还要做的事6.1 三个必跑的验证查询导入成功不等于数据正确。我无论用哪种方式导入完成后都会先跑三个查询行数对比、金额汇总对比、日期范围抽查确认没问题才告诉业务方。-- 行数核对和 Excel 数据行数去掉表头对比 SELECT COUNT(*) FROM sales_order; -- 金额对比按月份汇总跟 Excel 里的透视表结果对一下 SELECT DATE_FORMAT(order_date, %Y-%m) AS month, SUM(amount) FROM sales_order GROUP BY month; -- 边界检查日期不能出现 0000-00-00 或超出业务范围 SELECT MIN(order_date), MAX(order_date) FROM sales_order;行数对不上的情况优先查导入日志里失败的行金额对不上排查是不是有空值被当成 0、或者浮点精度问题。这三条 SQL 跑完心里就有底了。6.2 全量重导和增量更新的两种模式日常运维中Excel 数据每天都在变分两种套路。全量重导适合数据量不大、每天直接用新文件覆盖的场景先 TRUNCATE 清空目标表再 LOAD DATA 导入。增量更新适合源文件里只有新增和修改、不能清空历史数据的场景利用唯一键做更新INSERT INTO sales_order (order_no, order_date, amount, customer_name) SELECT order_no, order_date, amount, customer_name FROM tmp_import AS src ON DUPLICATE KEY UPDATE amount src.amount, customer_name src.customer_name;注意这里写的是src.amount这种别名方式MySQL 8.0.20 之后已经弃用了老式的VALUES(amount)写法虽然老写法还能跑但会报警告。这段 SQL 的前提是 order_no 上有唯一键没有唯一键就没有“重复”这个依据。6.3 用 cron 定时跑导入脚本如果每天都有人往固定目录扔 CSV可以把导入脚本化。mysqlimport 的参数写进脚本配一条 cron#!/bin/bash # 每天 02:10 把当天导出的 CSV 追加进 MySQL CSV/data/sales_$(date %Y%m%d).csv if [ -f $CSV ]; then mysqlimport --local --fields-terminated-by, \ --fields-optionally-enclosed-by \ --lines-terminated-by\n \ --ignore-lines1 \ --defaults-extra-file/etc/mysql-client.cnf \ sales_db $CSV echo $(date) import ok /var/log/excel_import.log fi密码不要写在命令行里用--defaults-extra-file指定一个权限为 600 的配置文件避免ps命令直接看到密码。脚本里文件名用日期拼接所以 mysqlimport 对文件命名的要求必须满足文件名去掉扩展名后得和表名一致否则它找不到目标表脚本会一直静默失败。我自己的习惯是每次导入后先跑 COUNT(*) 和 SUM(amount)确认无误再离开。有一回图省事看到“导入成功”就关了窗口第二天报表对不上查了半天发现是漏了最后一页 Excel 数据没导全。希望这份流程能帮你少走这段弯路。本文还有配套的精品资源点击获取