1. 批量导入大CSV这件事坑比想象中多手里攒了七八个CSV文件每个少则几百兆多则好几个G要一股脑塞进MySQL里做分析或者做数据迁移。这事听起来简单真动手就知道没那么轻松。我第一次接这种活的时候想当然地用客户端工具一个个点导入结果第一个文件跑到一半就超时断开第二个文件直接卡死界面第三个文件因为编码问题中文全变问号。折腾了一下午一个文件都没完整进去。后来换了思路用LOAD DATA INFILE配合批量脚本才把这件事做顺。这篇文章就把我踩过的坑和总结出来的方案完整讲一遍包括为什么客户端工具不适合干这个活、LOAD DATA INFILE到底怎么配置才能跑满速度、多个文件怎么批量处理、遇到脏数据怎么兜底。不管你是刚接触MySQL导入的新手还是已经用过LOAD DATA但总觉得速度不够快的老手应该都能从里面找到能直接用的东西。核心关键词就一个MySQL快速导入多个大CSV文件。围绕这个目标我会把方案选型、参数调优、批量脚本、异常处理这几个环节拆开讲每个环节都给出具体的操作和背后的逻辑。2. 方案选型为什么客户端工具不适合干这个活2.1 几种常见导入方式的真实对比很多人第一反应是用Navicat、DBeaver这类图形化工具或者MySQL Workbench自带的导入向导。这些工具不是不能用而是有明确的适用边界。我把常见的几种方式拉出来对比一下你就知道该在什么场景下选什么。导入方式适合的数据量速度多文件支持主要问题图形化工具导入向导几万行以内慢差超时断开、内存占用高、编码处理弱INSERT语句批量拼十万行以内中等需自己写脚本SQL包体过大、解析开销高LOAD DATA INFILE千万行级别快需自己写脚本需要服务端配置配合mysqlimport命令行千万行级别快原生支持多文件参数灵活性略低编程语言驱动批量插入百万行以内中等偏慢灵活网络往返开销大结论很清楚数据量上了百万行、文件上了几百兆就该用LOAD DATA INFILE或者mysqlimport。这两个本质上是同一套底层机制mysqlimport是LOAD DATA INFILE的命令行封装。图形化工具在数据量小的时候图个方便没问题但面对多个大文件它那个逐行解析再拼SQL的方式性能瓶颈非常明显。2.2 LOAD DATA INFILE为什么快要理解它为什么快得先知道普通INSERT慢在哪。你写一条INSERT INTO t VALUES (...)MySQL要做的事情包括解析SQL语句、检查权限、打开表、逐行写入、更新索引、写binlog、提交事务。如果是一万条INSERT这一整套流程要走一万遍。就算你用批量INSERT拼成一条插一千行SQL解析和网络传输的开销还是省不掉。LOAD DATA INFILE走的是完全不同的路径。它绕过了SQL解析层直接由存储引擎按格式读取文件内容写入表。官方文档里说它比INSERT快20倍这个数字在实测中基本靠谱数据量越大差距越明显。它快的原因有几个绕过SQL解析不需要把每一行拼成SQL语句再解析直接按分隔符切分字段。批量提交可以一次性提交大量行减少事务开销。顺序写入如果表是空表且没有二级索引写入基本是顺序追加磁盘IO效率高。可控的约束检查可以通过参数关闭部分检查进一步提速。注意LOAD DATA INFILE默认要求文件在MySQL服务端所在机器上用的是LOAD DATA INFILE。如果文件在客户端机器上要用LOAD DATA LOCAL INFILE但后者需要服务端和客户端都开启local_infile而且速度会受网络影响。生产环境里通常是把文件先传到数据库服务器本地再用LOAD DATA INFILE。2.3 多文件处理的两种思路多个CSV文件处理思路无非两种串行和并行。串行就是写个循环一个文件一个文件地导。优点是简单、可控、对数据库压力小。缺点是总耗时是各个文件耗时之和如果每个文件都要跑十分钟八个文件就是一个多小时。并行就是同时开多个导入任务。优点是总耗时取决于最慢的那个文件。缺点是会争抢数据库的IO和CPU资源如果表上有索引或者触发器并发写入还可能造成锁竞争。我的建议是先串行跑通确认单个文件的导入逻辑没问题再根据服务器配置决定要不要并行。如果服务器IO够强比如SSD阵列并行度开到2到4是比较稳妥的。再高的话收益递减不说还容易把数据库拖垮影响其他业务。3. 核心细节LOAD DATA INFILE的参数怎么配3.1 文件格式与字段分隔的对应关系CSV文件看着简单实际上格式差异很大。有的用逗号分隔有的用制表符有的字段带引号有的不带有的换行是\n有的是\r\n。这些细节不搞清楚导入进去的数据就是一团乱。先看一个标准的LOAD DATA INFILE语句长什么样LOAD DATA INFILE /var/lib/mysql-files/orders_202401.csv INTO TABLE orders FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (order_id, user_id, amount, created_at);逐段解释一下FIELDS TERMINATED BY ,字段之间用逗号分隔。如果是制表符写成\t。OPTIONALLY ENCLOSED BY 字段可能被双引号包起来。OPTIONALLY的意思是可有可无有些字段带引号有些没带也能处理。LINES TERMINATED BY \n行结束符。Windows导出的CSV通常是\r\n如果这里写错导入后每行末尾会多一个\r导致最后一个字段值不对。IGNORE 1 LINES跳过表头行。如果CSV没有表头这行去掉。最后的字段列表指定CSV各列对应表中的哪些字段。顺序要和CSV里的列顺序一致。实操心得判断CSV的行结束符可以用file命令看或者用head -1 file.csv | xxd | tail -2看末尾字节。0d 0a就是\r\n0a就是\n。这个细节不注意导入后最后一个字段会莫名其妙多出乱码。3.2 编码问题中文乱码的根源CSV文件的编码和数据库的字符集不一致是中文乱码的头号原因。常见的情况是CSV是GBK编码数据库是utf8mb4直接导入中文全变问号。解决办法有两个方案一导入前转码。用iconv把文件转成utf8iconv -f GBK -t UTF-8 source.csv source_utf8.csv这个方案的好处是文件层面就统一了后续导入不用操心编码。坏处是多一步转换大文件转换也要花时间。方案二在LOAD DATA时指定字符集LOAD DATA INFILE /path/to/file.csv INTO TABLE orders CHARACTER SET gbk FIELDS TERMINATED BY , ...这个方案不用改文件但要求你准确知道源文件的编码。如果搞错了乱码更严重。注意CHARACTER SET指定的是源文件的字符集不是目标表的字符集。MySQL会自动把它转成表的字符集。这个参数很容易理解反。3.3 关键性能参数让导入跑满速度默认参数下LOAD DATA INFILE已经比INSERT快很多了但还没到极限。有几个参数调一下速度还能再上一个台阶。innodb_flush_log_at_trx_commit这个参数控制事务日志的刷盘策略。默认值是1意思是每次事务提交都要把日志刷到磁盘保证不丢数据。导入大量数据时这个刷盘动作会成为瓶颈。临时改成2或者0可以大幅提升写入速度。SET GLOBAL innodb_flush_log_at_trx_commit 2;2每次提交写日志到操作系统缓存每秒刷一次盘。性能和数据安全折中。0每秒写日志并刷盘提交时不触发。性能最好但崩溃可能丢一秒数据。导入完成后记得改回1。unique_checks和foreign_key_checks如果表上有唯一索引和外键约束导入时每插一行都要检查开销不小。导入前临时关闭SET unique_checks 0; SET foreign_key_checks 0; -- 执行导入 SET unique_checks 1; SET foreign_key_checks 1;注意关闭唯一性检查的前提是你确信导入的数据本身没有重复。如果数据有重复关闭检查会导致重复数据进去后续再想清理就麻烦了。这个操作适合数据来源可靠、已经清洗过的场景。批量提交大小LOAD DATA INFILE本身没有直接的批量提交参数但可以通过SET autocommit和手动事务来控制。不过实测下来LOAD DATA内部已经做了优化手动包事务的收益不明显反而可能因为事务过大导致回滚段膨胀。一般不建议额外包事务。3.4 索引和约束的处理策略这是影响导入速度最大的因素之一也是最容易被忽略的。如果目标表在导入前是空的先建表不建索引导入完再建索引比带着索引导入快得多。原因很简单带着索引导入每插一行都要更新B树随机IO多导入完再建索引可以走排序后批量构建顺序IO为主。具体操作-- 1. 建表时只建主键不建二级索引 CREATE TABLE orders_staging ( order_id BIGINT PRIMARY KEY, user_id BIGINT, amount DECIMAL(10,2), created_at DATETIME ); -- 2. 导入数据 LOAD DATA INFILE ... INTO TABLE orders_staging ...; -- 3. 导入完成后建索引 ALTER TABLE orders_staging ADD INDEX idx_user (user_id); ALTER TABLE orders_staging ADD INDEX idx_created (created_at);如果目标表已经有数据不能删索引那就只能接受索引维护的开销。这种情况下可以考虑先导入到临时表再用INSERT INTO ... SELECT的方式合并但合并本身也有开销要权衡。4. 批量导入的完整实操流程4.1 环境准备与文件归置第一步是把文件放到MySQL服务端能访问的位置。LOAD DATA INFILE默认只能读secure_file_priv指定的目录。先查一下这个目录在哪SHOW VARIABLES LIKE secure_file_priv;假设返回的是/var/lib/mysql-files/那就把所有CSV文件复制到这个目录下。如果返回为空说明没有限制任意路径都能读。如果返回NULL说明禁止了文件导入需要在配置文件里设置。# 查看文件列表和大小 ls -lh /var/lib/mysql-files/*.csv # 确认文件编码 file -i /var/lib/mysql-files/*.csv把文件归置好之后先拿一个文件做小批量测试确认字段对应、编码、行结束符都没问题再批量跑。4.2 单文件导入的验证流程不要一上来就导全量。先取前1000行做个测试head -1000 orders_202401.csv test_sample.csv然后用LOAD DATA导入这个样本检查几件事行数对不对SELECT COUNT(*) FROM orders_staging;应该是1000如果跳过了表头就是999。中文字段有没有乱码SELECT * FROM orders_staging LIMIT 10;肉眼看一下。数值字段有没有精度丢失特别是金额、时间字段。最后一个字段有没有多余字符这是行结束符配错的典型症状。样本验证通过后再跑全量。4.3 多文件批量导入脚本单个文件跑通后写个脚本批量处理。用bash最简单直接#!/bin/bash DB_NAMEyour_database TABLE_NAMEorders_staging FILE_DIR/var/lib/mysql-files LOG_FILE/var/log/import_batch.log # 导入前优化参数 mysql -e SET GLOBAL innodb_flush_log_at_trx_commit 2; for csv_file in ${FILE_DIR}/*.csv; do filename$(basename $csv_file) echo [$(date %Y-%m-%d %H:%M:%S)] 开始导入: $filename $LOG_FILE start_time$(date %s) mysql $DB_NAME -e LOAD DATA INFILE ${csv_file} INTO TABLE ${TABLE_NAME} CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY \ LINES TERMINATED BY \n IGNORE 1 LINES (order_id, user_id, amount, created_at); 2 $LOG_FILE if [ $? -eq 0 ]; then end_time$(date %s) duration$((end_time - start_time)) echo [$(date %Y-%m-%d %H:%M:%S)] 完成: $filename, 耗时 ${duration}秒 $LOG_FILE else echo [$(date %Y-%m-%d %H:%M:%S)] 失败: $filename $LOG_FILE fi done # 恢复参数 mysql -e SET GLOBAL innodb_flush_log_at_trx_commit 1; echo 全部导入完成 $LOG_FILE这个脚本做了几件事循环遍历目录下所有CSV、记录每个文件的开始和结束时间、失败时记录到日志但不中断后续文件、导入前后自动调整性能参数。实操心得脚本里用2 $LOG_FILE把错误输出也重定向到日志这样如果某个文件导入失败错误信息会留在日志里方便排查。不要图省事把错误输出丢掉出了问题连原因都找不到。4.4 并行导入的取舍与实现如果串行跑下来总时间太长可以考虑并行。但并行不是简单地把循环改成后台执行要考虑几个问题目标表是否相同如果多个文件导入同一张表并行写入会有锁竞争InnoDB的行锁在批量插入时可能升级反而更慢。这种情况下可以每个文件导入一张独立的临时表最后再合并。服务器IO能力机械硬盘并行意义不大SSD可以适当并行。并行度控制一般2到4比较合适再多收益递减。一个简单的并行实现#!/bin/bash MAX_PARALLEL3 count0 for csv_file in /var/lib/mysql-files/*.csv; do filename$(basename $csv_file) table_nameorders_${filename%.csv} mysql your_database -e CREATE TABLE IF NOT EXISTS ${table_name} LIKE orders_staging; LOAD DATA INFILE ${csv_file} INTO TABLE ${table_name} FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY \ LINES TERMINATED BY \n IGNORE 1 LINES; count$((count 1)) if [ $((count % MAX_PARALLEL)) -eq 0 ]; then wait fi done wait这个脚本每个文件导入一张独立表避免锁竞争每3个文件等一次。最后再用INSERT INTO ... SELECT把各表数据合并到主表。5. 常见问题与排查技巧实录5.1 导入报错速查表报错信息原因解决办法The MySQL server is running with the --secure-file-priv option文件不在允许目录把文件移到secure_file_priv目录或修改配置Cant get stat of file文件路径错误或权限不足检查路径确保mysql用户有读权限Incorrect integer value字段类型不匹配或空值检查CSV对应列用SET子句处理空值Data too long for column字段长度不够扩大列长度或截断数据Duplicate entry for key主键或唯一键冲突导入前清空表或用IGNORE关键字跳过重复Row X doesnt contain data for all columns列数不匹配检查字段列表和CSV列数是否一致中文乱码编码不一致指定CHARACTER SET或提前转码5.2 导入速度慢的排查思路如果导入速度远低于预期按这个顺序排查第一看是不是带着索引导入的。用SHOW INDEX FROM table_name;确认。如果有多个二级索引考虑先删索引再导入。第二看innodb_flush_log_at_trx_commit是不是1。默认值1在导入场景下是性能杀手临时改成2。第三看磁盘IO是不是瓶颈。用iostat -x 1观察如果%util接近100%说明磁盘跑满了这时候再怎么调参数也没用得从硬件或者并行策略上想办法。第四看是不是在跑LOAD DATA LOCAL INFILE。本地文件导入走网络传输速度受网络带宽限制。把文件传到服务端本地再导。第五看表上有没有触发器。触发器会在每行插入时执行开销巨大。导入前禁用触发器。5.3 数据质量兜底脏数据怎么处理CSV文件来自不同系统脏数据几乎不可避免。常见的脏数据包括字段缺失、类型不对、超长、特殊字符。处理脏数据有两个思路思路一用SET子句做转换。LOAD DATA支持在字段列表里用变量和表达式LOAD DATA INFILE ... INTO TABLE orders_staging FIELDS TERMINATED BY , LINES TERMINATED BY \n IGNORE 1 LINES (order_id, user_id, amount, created_at) SET user_id IF(user_id , NULL, user_id), amount IF(amount , 0, amount);这里用user_id和amount接收原始值然后在SET里做判断和转换。空字符串转成NULL或0避免导入报错。思路二先导入全字符串的临时表。建一张所有列都是VARCHAR的临时表先把数据原样导进去再用SQL做清洗和转换最后插入正式表。这个方案适合脏数据多、清洗逻辑复杂的场景。-- 临时表全用VARCHAR CREATE TABLE orders_raw ( order_id VARCHAR(50), user_id VARCHAR(50), amount VARCHAR(50), created_at VARCHAR(50) ); -- 原样导入 LOAD DATA INFILE ... INTO TABLE orders_raw ...; -- 清洗后插入正式表 INSERT INTO orders_staging (order_id, user_id, amount, created_at) SELECT CAST(order_id AS UNSIGNED), CAST(NULLIF(user_id, ) AS UNSIGNED), CAST(NULLIF(amount, ) AS DECIMAL(10,2)), STR_TO_DATE(NULLIF(created_at, ), %Y-%m-%d %H:%i:%s) FROM orders_raw WHERE order_id REGEXP ^[0-9]$;实操心得临时表方案虽然多了一步但排查问题方便得多。数据有问题时原始值还在临时表里可以反复清洗重试不用重新导入文件。对于来源不可靠的CSV我一般都用这个方案。5.4 导入后的数据校验导入完成不代表万事大吉必须做校验。校验分三个层次行数校验CSV文件的行数减去表头应该等于导入后的表行数。# 统计CSV行数 wc -l orders_202401.csv-- 统计表行数 SELECT COUNT(*) FROM orders_staging;抽样校验随机取几行和CSV里的原始数据对比。SELECT * FROM orders_staging ORDER BY RAND() LIMIT 5;聚合校验对金额、数量这类数值字段做SUM或AVG和CSV里用命令行算出来的结果对比。# CSV里金额列求和假设是第3列 awk -F, NR1 {sum$3} END {print sum} orders_202401.csvSELECT SUM(amount) FROM orders_staging;两个结果一致说明数据完整性和准确性都没问题。6. 几个容易被忽略的细节6.1 文件权限与目录归属LOAD DATA INFILE是以MySQL服务进程的用户身份读文件的。如果文件是root创建的权限是600mysql用户读不了就会报Cant get stat of file。解决办法是给文件加上读权限或者把文件属主改成mysql。chmod 644 /var/lib/mysql-files/*.csv # 或者 chown mysql:mysql /var/lib/mysql-files/*.csv6.2 大文件的内存占用LOAD DATA INFILE本身是流式读取的不会把整个文件加载到内存。但如果表上有大的TEXT或BLOB字段或者用了复杂的SET表达式内存占用会上升。导入期间用top观察mysqld进程的内存如果持续增长不释放可能是innodb_buffer_pool_size设置过大或者有内存泄漏需要关注。6.3 binlog的影响如果数据库开了binlogLOAD DATA的每一行都会写binlog这会显著增加IO开销。如果导入的数据不需要主从同步可以临时关闭binlogSET sql_log_bin 0; -- 执行导入 SET sql_log_bin 1;注意关闭binlog只对当前会话有效且需要SUPER权限。生产环境操作前要确认不会影响主从复制。6.4 导入中断后的续传如果导入到一半中断了不要直接重跑整个文件会重复导入。正确的做法是先确认已经导入了多少行SELECT COUNT(*) FROM orders_staging;用tail -n N跳过已导入的行生成剩余部分的文件。导入剩余部分。# 假设已导入50000行加上表头从50002行开始 tail -n 50002 orders_202401.csv orders_202401_remain.csv这个操作要求CSV的行顺序和导入顺序一致且没有并行导入。如果用了并行或者顺序不确定就只能清空表重来。7. 我的实操体会这套方案我在几个不同的项目里用过导入过累计几十G的CSV数据。最大的体会是导入速度的瓶颈往往不在LOAD DATA本身而在前期的准备和参数配置。把索引处理、日志刷盘、编码这几个点搞定速度能差出好几倍。另一个体会是不要迷信并行。我试过把并行度开到8结果数据库IO跑满其他查询全部超时导入本身也没快多少。后来降到3整体反而更稳。并行度这个东西得根据服务器实际配置来调没有万能值。最后日志和校验不能省。批量导入最怕的就是某个文件悄悄失败了你没发现后面分析数据时才发现少了一块。脚本里记录日志、导入后做行数和聚合校验这两个习惯帮我省了很多事后排查的时间。 
长河君 · 2026/10/13 16:17:05 · 阅读 1.2k MySQL快速导入多个大CSV文件：LOAD DATA INFILE参数调优与批量脚本实战
1. 批量导入大CSV这件事坑比想象中多手里攒了七八个CSV文件每个少则几百兆多则好几个G要一股脑塞进MySQL里做分析或者做数据迁移。这事听起来简单真动手就知道没那么轻松。我第一次接这种活的时候想当然地用客户端工具一个个点导入结果第一个文件跑到一半就超时断开第二个文件直接卡死界面第三个文件因为编码问题中文全变问号。折腾了一下午一个文件都没完整进去。后来换了思路用LOAD DATA INFILE配合批量脚本才把这件事做顺。这篇文章就把我踩过的坑和总结出来的方案完整讲一遍包括为什么客户端工具不适合干这个活、LOAD DATA INFILE到底怎么配置才能跑满速度、多个文件怎么批量处理、遇到脏数据怎么兜底。不管你是刚接触MySQL导入的新手还是已经用过LOAD DATA但总觉得速度不够快的老手应该都能从里面找到能直接用的东西。核心关键词就一个MySQL快速导入多个大CSV文件。围绕这个目标我会把方案选型、参数调优、批量脚本、异常处理这几个环节拆开讲每个环节都给出具体的操作和背后的逻辑。2. 方案选型为什么客户端工具不适合干这个活2.1 几种常见导入方式的真实对比很多人第一反应是用Navicat、DBeaver这类图形化工具或者MySQL Workbench自带的导入向导。这些工具不是不能用而是有明确的适用边界。我把常见的几种方式拉出来对比一下你就知道该在什么场景下选什么。导入方式适合的数据量速度多文件支持主要问题图形化工具导入向导几万行以内慢差超时断开、内存占用高、编码处理弱INSERT语句批量拼十万行以内中等需自己写脚本SQL包体过大、解析开销高LOAD DATA INFILE千万行级别快需自己写脚本需要服务端配置配合mysqlimport命令行千万行级别快原生支持多文件参数灵活性略低编程语言驱动批量插入百万行以内中等偏慢灵活网络往返开销大结论很清楚数据量上了百万行、文件上了几百兆就该用LOAD DATA INFILE或者mysqlimport。这两个本质上是同一套底层机制mysqlimport是LOAD DATA INFILE的命令行封装。图形化工具在数据量小的时候图个方便没问题但面对多个大文件它那个逐行解析再拼SQL的方式性能瓶颈非常明显。2.2 LOAD DATA INFILE为什么快要理解它为什么快得先知道普通INSERT慢在哪。你写一条INSERT INTO t VALUES (...)MySQL要做的事情包括解析SQL语句、检查权限、打开表、逐行写入、更新索引、写binlog、提交事务。如果是一万条INSERT这一整套流程要走一万遍。就算你用批量INSERT拼成一条插一千行SQL解析和网络传输的开销还是省不掉。LOAD DATA INFILE走的是完全不同的路径。它绕过了SQL解析层直接由存储引擎按格式读取文件内容写入表。官方文档里说它比INSERT快20倍这个数字在实测中基本靠谱数据量越大差距越明显。它快的原因有几个绕过SQL解析不需要把每一行拼成SQL语句再解析直接按分隔符切分字段。批量提交可以一次性提交大量行减少事务开销。顺序写入如果表是空表且没有二级索引写入基本是顺序追加磁盘IO效率高。可控的约束检查可以通过参数关闭部分检查进一步提速。注意LOAD DATA INFILE默认要求文件在MySQL服务端所在机器上用的是LOAD DATA INFILE。如果文件在客户端机器上要用LOAD DATA LOCAL INFILE但后者需要服务端和客户端都开启local_infile而且速度会受网络影响。生产环境里通常是把文件先传到数据库服务器本地再用LOAD DATA INFILE。2.3 多文件处理的两种思路多个CSV文件处理思路无非两种串行和并行。串行就是写个循环一个文件一个文件地导。优点是简单、可控、对数据库压力小。缺点是总耗时是各个文件耗时之和如果每个文件都要跑十分钟八个文件就是一个多小时。并行就是同时开多个导入任务。优点是总耗时取决于最慢的那个文件。缺点是会争抢数据库的IO和CPU资源如果表上有索引或者触发器并发写入还可能造成锁竞争。我的建议是先串行跑通确认单个文件的导入逻辑没问题再根据服务器配置决定要不要并行。如果服务器IO够强比如SSD阵列并行度开到2到4是比较稳妥的。再高的话收益递减不说还容易把数据库拖垮影响其他业务。3. 核心细节LOAD DATA INFILE的参数怎么配3.1 文件格式与字段分隔的对应关系CSV文件看着简单实际上格式差异很大。有的用逗号分隔有的用制表符有的字段带引号有的不带有的换行是\n有的是\r\n。这些细节不搞清楚导入进去的数据就是一团乱。先看一个标准的LOAD DATA INFILE语句长什么样LOAD DATA INFILE /var/lib/mysql-files/orders_202401.csv INTO TABLE orders FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (order_id, user_id, amount, created_at);逐段解释一下FIELDS TERMINATED BY ,字段之间用逗号分隔。如果是制表符写成\t。OPTIONALLY ENCLOSED BY 字段可能被双引号包起来。OPTIONALLY的意思是可有可无有些字段带引号有些没带也能处理。LINES TERMINATED BY \n行结束符。Windows导出的CSV通常是\r\n如果这里写错导入后每行末尾会多一个\r导致最后一个字段值不对。IGNORE 1 LINES跳过表头行。如果CSV没有表头这行去掉。最后的字段列表指定CSV各列对应表中的哪些字段。顺序要和CSV里的列顺序一致。实操心得判断CSV的行结束符可以用file命令看或者用head -1 file.csv | xxd | tail -2看末尾字节。0d 0a就是\r\n0a就是\n。这个细节不注意导入后最后一个字段会莫名其妙多出乱码。3.2 编码问题中文乱码的根源CSV文件的编码和数据库的字符集不一致是中文乱码的头号原因。常见的情况是CSV是GBK编码数据库是utf8mb4直接导入中文全变问号。解决办法有两个方案一导入前转码。用iconv把文件转成utf8iconv -f GBK -t UTF-8 source.csv source_utf8.csv这个方案的好处是文件层面就统一了后续导入不用操心编码。坏处是多一步转换大文件转换也要花时间。方案二在LOAD DATA时指定字符集LOAD DATA INFILE /path/to/file.csv INTO TABLE orders CHARACTER SET gbk FIELDS TERMINATED BY , ...这个方案不用改文件但要求你准确知道源文件的编码。如果搞错了乱码更严重。注意CHARACTER SET指定的是源文件的字符集不是目标表的字符集。MySQL会自动把它转成表的字符集。这个参数很容易理解反。3.3 关键性能参数让导入跑满速度默认参数下LOAD DATA INFILE已经比INSERT快很多了但还没到极限。有几个参数调一下速度还能再上一个台阶。innodb_flush_log_at_trx_commit这个参数控制事务日志的刷盘策略。默认值是1意思是每次事务提交都要把日志刷到磁盘保证不丢数据。导入大量数据时这个刷盘动作会成为瓶颈。临时改成2或者0可以大幅提升写入速度。SET GLOBAL innodb_flush_log_at_trx_commit 2;2每次提交写日志到操作系统缓存每秒刷一次盘。性能和数据安全折中。0每秒写日志并刷盘提交时不触发。性能最好但崩溃可能丢一秒数据。导入完成后记得改回1。unique_checks和foreign_key_checks如果表上有唯一索引和外键约束导入时每插一行都要检查开销不小。导入前临时关闭SET unique_checks 0; SET foreign_key_checks 0; -- 执行导入 SET unique_checks 1; SET foreign_key_checks 1;注意关闭唯一性检查的前提是你确信导入的数据本身没有重复。如果数据有重复关闭检查会导致重复数据进去后续再想清理就麻烦了。这个操作适合数据来源可靠、已经清洗过的场景。批量提交大小LOAD DATA INFILE本身没有直接的批量提交参数但可以通过SET autocommit和手动事务来控制。不过实测下来LOAD DATA内部已经做了优化手动包事务的收益不明显反而可能因为事务过大导致回滚段膨胀。一般不建议额外包事务。3.4 索引和约束的处理策略这是影响导入速度最大的因素之一也是最容易被忽略的。如果目标表在导入前是空的先建表不建索引导入完再建索引比带着索引导入快得多。原因很简单带着索引导入每插一行都要更新B树随机IO多导入完再建索引可以走排序后批量构建顺序IO为主。具体操作-- 1. 建表时只建主键不建二级索引 CREATE TABLE orders_staging ( order_id BIGINT PRIMARY KEY, user_id BIGINT, amount DECIMAL(10,2), created_at DATETIME ); -- 2. 导入数据 LOAD DATA INFILE ... INTO TABLE orders_staging ...; -- 3. 导入完成后建索引 ALTER TABLE orders_staging ADD INDEX idx_user (user_id); ALTER TABLE orders_staging ADD INDEX idx_created (created_at);如果目标表已经有数据不能删索引那就只能接受索引维护的开销。这种情况下可以考虑先导入到临时表再用INSERT INTO ... SELECT的方式合并但合并本身也有开销要权衡。4. 批量导入的完整实操流程4.1 环境准备与文件归置第一步是把文件放到MySQL服务端能访问的位置。LOAD DATA INFILE默认只能读secure_file_priv指定的目录。先查一下这个目录在哪SHOW VARIABLES LIKE secure_file_priv;假设返回的是/var/lib/mysql-files/那就把所有CSV文件复制到这个目录下。如果返回为空说明没有限制任意路径都能读。如果返回NULL说明禁止了文件导入需要在配置文件里设置。# 查看文件列表和大小 ls -lh /var/lib/mysql-files/*.csv # 确认文件编码 file -i /var/lib/mysql-files/*.csv把文件归置好之后先拿一个文件做小批量测试确认字段对应、编码、行结束符都没问题再批量跑。4.2 单文件导入的验证流程不要一上来就导全量。先取前1000行做个测试head -1000 orders_202401.csv test_sample.csv然后用LOAD DATA导入这个样本检查几件事行数对不对SELECT COUNT(*) FROM orders_staging;应该是1000如果跳过了表头就是999。中文字段有没有乱码SELECT * FROM orders_staging LIMIT 10;肉眼看一下。数值字段有没有精度丢失特别是金额、时间字段。最后一个字段有没有多余字符这是行结束符配错的典型症状。样本验证通过后再跑全量。4.3 多文件批量导入脚本单个文件跑通后写个脚本批量处理。用bash最简单直接#!/bin/bash DB_NAMEyour_database TABLE_NAMEorders_staging FILE_DIR/var/lib/mysql-files LOG_FILE/var/log/import_batch.log # 导入前优化参数 mysql -e SET GLOBAL innodb_flush_log_at_trx_commit 2; for csv_file in ${FILE_DIR}/*.csv; do filename$(basename $csv_file) echo [$(date %Y-%m-%d %H:%M:%S)] 开始导入: $filename $LOG_FILE start_time$(date %s) mysql $DB_NAME -e LOAD DATA INFILE ${csv_file} INTO TABLE ${TABLE_NAME} CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY \ LINES TERMINATED BY \n IGNORE 1 LINES (order_id, user_id, amount, created_at); 2 $LOG_FILE if [ $? -eq 0 ]; then end_time$(date %s) duration$((end_time - start_time)) echo [$(date %Y-%m-%d %H:%M:%S)] 完成: $filename, 耗时 ${duration}秒 $LOG_FILE else echo [$(date %Y-%m-%d %H:%M:%S)] 失败: $filename $LOG_FILE fi done # 恢复参数 mysql -e SET GLOBAL innodb_flush_log_at_trx_commit 1; echo 全部导入完成 $LOG_FILE这个脚本做了几件事循环遍历目录下所有CSV、记录每个文件的开始和结束时间、失败时记录到日志但不中断后续文件、导入前后自动调整性能参数。实操心得脚本里用2 $LOG_FILE把错误输出也重定向到日志这样如果某个文件导入失败错误信息会留在日志里方便排查。不要图省事把错误输出丢掉出了问题连原因都找不到。4.4 并行导入的取舍与实现如果串行跑下来总时间太长可以考虑并行。但并行不是简单地把循环改成后台执行要考虑几个问题目标表是否相同如果多个文件导入同一张表并行写入会有锁竞争InnoDB的行锁在批量插入时可能升级反而更慢。这种情况下可以每个文件导入一张独立的临时表最后再合并。服务器IO能力机械硬盘并行意义不大SSD可以适当并行。并行度控制一般2到4比较合适再多收益递减。一个简单的并行实现#!/bin/bash MAX_PARALLEL3 count0 for csv_file in /var/lib/mysql-files/*.csv; do filename$(basename $csv_file) table_nameorders_${filename%.csv} mysql your_database -e CREATE TABLE IF NOT EXISTS ${table_name} LIKE orders_staging; LOAD DATA INFILE ${csv_file} INTO TABLE ${table_name} FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY \ LINES TERMINATED BY \n IGNORE 1 LINES; count$((count 1)) if [ $((count % MAX_PARALLEL)) -eq 0 ]; then wait fi done wait这个脚本每个文件导入一张独立表避免锁竞争每3个文件等一次。最后再用INSERT INTO ... SELECT把各表数据合并到主表。5. 常见问题与排查技巧实录5.1 导入报错速查表报错信息原因解决办法The MySQL server is running with the --secure-file-priv option文件不在允许目录把文件移到secure_file_priv目录或修改配置Cant get stat of file文件路径错误或权限不足检查路径确保mysql用户有读权限Incorrect integer value字段类型不匹配或空值检查CSV对应列用SET子句处理空值Data too long for column字段长度不够扩大列长度或截断数据Duplicate entry for key主键或唯一键冲突导入前清空表或用IGNORE关键字跳过重复Row X doesnt contain data for all columns列数不匹配检查字段列表和CSV列数是否一致中文乱码编码不一致指定CHARACTER SET或提前转码5.2 导入速度慢的排查思路如果导入速度远低于预期按这个顺序排查第一看是不是带着索引导入的。用SHOW INDEX FROM table_name;确认。如果有多个二级索引考虑先删索引再导入。第二看innodb_flush_log_at_trx_commit是不是1。默认值1在导入场景下是性能杀手临时改成2。第三看磁盘IO是不是瓶颈。用iostat -x 1观察如果%util接近100%说明磁盘跑满了这时候再怎么调参数也没用得从硬件或者并行策略上想办法。第四看是不是在跑LOAD DATA LOCAL INFILE。本地文件导入走网络传输速度受网络带宽限制。把文件传到服务端本地再导。第五看表上有没有触发器。触发器会在每行插入时执行开销巨大。导入前禁用触发器。5.3 数据质量兜底脏数据怎么处理CSV文件来自不同系统脏数据几乎不可避免。常见的脏数据包括字段缺失、类型不对、超长、特殊字符。处理脏数据有两个思路思路一用SET子句做转换。LOAD DATA支持在字段列表里用变量和表达式LOAD DATA INFILE ... INTO TABLE orders_staging FIELDS TERMINATED BY , LINES TERMINATED BY \n IGNORE 1 LINES (order_id, user_id, amount, created_at) SET user_id IF(user_id , NULL, user_id), amount IF(amount , 0, amount);这里用user_id和amount接收原始值然后在SET里做判断和转换。空字符串转成NULL或0避免导入报错。思路二先导入全字符串的临时表。建一张所有列都是VARCHAR的临时表先把数据原样导进去再用SQL做清洗和转换最后插入正式表。这个方案适合脏数据多、清洗逻辑复杂的场景。-- 临时表全用VARCHAR CREATE TABLE orders_raw ( order_id VARCHAR(50), user_id VARCHAR(50), amount VARCHAR(50), created_at VARCHAR(50) ); -- 原样导入 LOAD DATA INFILE ... INTO TABLE orders_raw ...; -- 清洗后插入正式表 INSERT INTO orders_staging (order_id, user_id, amount, created_at) SELECT CAST(order_id AS UNSIGNED), CAST(NULLIF(user_id, ) AS UNSIGNED), CAST(NULLIF(amount, ) AS DECIMAL(10,2)), STR_TO_DATE(NULLIF(created_at, ), %Y-%m-%d %H:%i:%s) FROM orders_raw WHERE order_id REGEXP ^[0-9]$;实操心得临时表方案虽然多了一步但排查问题方便得多。数据有问题时原始值还在临时表里可以反复清洗重试不用重新导入文件。对于来源不可靠的CSV我一般都用这个方案。5.4 导入后的数据校验导入完成不代表万事大吉必须做校验。校验分三个层次行数校验CSV文件的行数减去表头应该等于导入后的表行数。# 统计CSV行数 wc -l orders_202401.csv-- 统计表行数 SELECT COUNT(*) FROM orders_staging;抽样校验随机取几行和CSV里的原始数据对比。SELECT * FROM orders_staging ORDER BY RAND() LIMIT 5;聚合校验对金额、数量这类数值字段做SUM或AVG和CSV里用命令行算出来的结果对比。# CSV里金额列求和假设是第3列 awk -F, NR1 {sum$3} END {print sum} orders_202401.csvSELECT SUM(amount) FROM orders_staging;两个结果一致说明数据完整性和准确性都没问题。6. 几个容易被忽略的细节6.1 文件权限与目录归属LOAD DATA INFILE是以MySQL服务进程的用户身份读文件的。如果文件是root创建的权限是600mysql用户读不了就会报Cant get stat of file。解决办法是给文件加上读权限或者把文件属主改成mysql。chmod 644 /var/lib/mysql-files/*.csv # 或者 chown mysql:mysql /var/lib/mysql-files/*.csv6.2 大文件的内存占用LOAD DATA INFILE本身是流式读取的不会把整个文件加载到内存。但如果表上有大的TEXT或BLOB字段或者用了复杂的SET表达式内存占用会上升。导入期间用top观察mysqld进程的内存如果持续增长不释放可能是innodb_buffer_pool_size设置过大或者有内存泄漏需要关注。6.3 binlog的影响如果数据库开了binlogLOAD DATA的每一行都会写binlog这会显著增加IO开销。如果导入的数据不需要主从同步可以临时关闭binlogSET sql_log_bin 0; -- 执行导入 SET sql_log_bin 1;注意关闭binlog只对当前会话有效且需要SUPER权限。生产环境操作前要确认不会影响主从复制。6.4 导入中断后的续传如果导入到一半中断了不要直接重跑整个文件会重复导入。正确的做法是先确认已经导入了多少行SELECT COUNT(*) FROM orders_staging;用tail -n N跳过已导入的行生成剩余部分的文件。导入剩余部分。# 假设已导入50000行加上表头从50002行开始 tail -n 50002 orders_202401.csv orders_202401_remain.csv这个操作要求CSV的行顺序和导入顺序一致且没有并行导入。如果用了并行或者顺序不确定就只能清空表重来。7. 我的实操体会这套方案我在几个不同的项目里用过导入过累计几十G的CSV数据。最大的体会是导入速度的瓶颈往往不在LOAD DATA本身而在前期的准备和参数配置。把索引处理、日志刷盘、编码这几个点搞定速度能差出好几倍。另一个体会是不要迷信并行。我试过把并行度开到8结果数据库IO跑满其他查询全部超时导入本身也没快多少。后来降到3整体反而更稳。并行度这个东西得根据服务器实际配置来调没有万能值。最后日志和校验不能省。批量导入最怕的就是某个文件悄悄失败了你没发现后面分析数据时才发现少了一块。脚本里记录日志、导入后做行数和聚合校验这两个习惯帮我省了很多事后排查的时间。