怎么用mysql实现批量导入数据_mysql实战技巧

LOAD DATA INFILE 是最快批量导入方式，但仅读服务器端文件；本地导入需用 LOAD DATA LOCAL INFILE 并开启 local_infile；注意行/字段分隔符、编码（UTF-8 without BOM）、secure_file_priv 限制及索引优化。

LOAD DATA INFILE 语句是最快的方式

直接从本地或服务端文件批量导入数据，比逐条 INSERT 快一个数量级。但注意：它默认只读取 MySQL 服务端的文件系统路径，不是你本地电脑的路径。

• 如果用 LOAD DATA INFILE，文件必须放在 MySQL 服务器所在机器上，且 MySQL 进程有读取权限
• 想从客户端本地导入？改用 LOAD DATA LOCAL INFILE，但需服务端开启 local_infile=ON（在 my.cnf 中配置并重启，或运行 SET GLOBAL local_infile = ON，后者需要 FILE 权限）
• 字段分隔符、行结束符要和文件实际格式严格一致，常见错误是 Windows 的 \r\n 和 Linux 的 \n 混用导致最后一列丢失

LOAD DATA LOCAL INFILE '/path/to/data.csv'
INTO TABLE users
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(id, name, email, created_at);

用 INSERT ... VALUES 批量插入时别一次插太多

单条 INSERT 语句带多组值是可以的，但堆叠几万行会触发 max_allowed_packet 限制，也可能锁表太久。

• 推荐每批 1000–5000 行，具体看单行数据大小；可通过 SHOW VARIABLES LIKE 'max_allowed_packet'; 查当前上限
• 确保目标表没有过多索引，尤其是唯一索引——每插一行都会校验，批量时开销翻倍
• 导入前可临时禁用唯一性检查：SET unique_checks=0;，导入后再 SET unique_checks=1;（记得配合 COMMIT，否则可能不生效）

INSERT INTO logs (ts, level, msg) VALUES
(1717023456, 'INFO', 'started'),
(1717023457, 'WARN', 'timeout'),
(1717023458, 'ERROR', 'db fail');

处理编码和特殊字符最容易出错

CSV 或 TXT 文件常因编码不一致导致中文变乱码、字段错位，甚至导入中断。

• 务必确认文件是 UTF-8 without BOM；用 file -i filename 或 VS Code 底部编码提示验证
• 建表时显式指定字符集：CREATE TABLE ... DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
• 导入语句中加上 CHARACTER SET utf8mb4，否则即使表支持，LOAD DATA 仍可能按默认 latin1 解析
• 字段含逗号、换行、双引号时，必须用 ENCLOSED BY '"' 并确保导出程序做了正确转义

替代方案：mysqlimport 命令行工具

本质是 LOAD DATA INFILE 的封装，适合运维脚本调用，但灵活性略低。

• 文件名必须和表名一致（如导入到 orders 表，文件得叫 orders.txt），除非加 --fields-terminated-by 等参数覆盖
• 不支持跳过首行（IGNORE 1 ROWS），需提前用 sed 或 tail -n +2 处理
• 权限模型和 LOAD DATA INFILE 一致，同样受 se

  

  cure_file_priv 限制——查 SELECT @@secure_file_priv; 知道能读哪些路径

mysqlimport --local --fields-terminated-by=',' \
  --lines-terminated-by='\n' \
  --ignore-lines=1 \
  --user=root --password \
  mydb /tmp/users.csv

真正卡住人的往往不是语法，而是文件路径权限、编码隐式转换、以及 secure_file_priv 这种默认关死的开关——动手前先 SELECT @@secure_file_priv, @@local_infile; 看一眼，省掉大半排查时间。