🚀 拒绝龟速插入！带你解锁 MySQL LOAD DATA 的极速传说

🚀 拒绝龟速插入！带你解锁 MySQL LOAD DATA 的极速传说

CoderJia 2 2025-12-19

在日常开发中，我们经常会遇到数据迁移、批量导入的场景。如果你的数据量只有几百条，写个 INSERT INTO 循环可能也就忍了。但如果是几十万、上百万甚至上亿条数据呢？

这时候，如果你还在用代码逐条 Insert，或者拼凑超长的 SQL 语句，那你的数据库大概已经在“口吐白沫”了。

今天，我要给你安利一个 MySQL 的核武器级命令 —— LOAD DATA。它的速度比普通的 Insert 快 20 倍 以上！

🧐 什么是 LOAD DATA？

简单来说，LOAD DATA 是 MySQL 提供的一种专门用于从文本文件（如 CSV、TXT）高速读取数据并插入到表中的机制。它绕过了很多 SQL 解析和优化的步骤，直接进行底层数据写入。

一句话总结：它是 MySQL 数据导入的“高铁专列”。

🛠️ 基础语法速览

最简单的用法长这样：

LOAD DATA INFILE '/path/to/your/file.csv'
INTO TABLE your_table_name;

但这通常不够用，因为真实世界的文件格式千奇百怪。下面是一个更通用的完整模板：

LOAD DATA [LOCAL] INFILE '文件路径'
INTO TABLE 表名
CHARACTER SET utf8mb4           -- 1. 指定字符集，防止乱码
FIELDS TERMINATED BY ','        -- 2. 字段分隔符（CSV通常是逗号）
ENCLOSED BY '"'                 -- 3. 字段引用符（比如"张三"）
LINES TERMINATED BY '\n'        -- 4. 换行符（Windows可能是\r\n）
IGNORE 1 LINES                  -- 5. 跳过表头（如果有的话）
(column1, column2, @var1)       -- 6. 指定列名，甚至做简单处理
SET column3 = STR_TO_DATE(@var1, '%Y-%m-%d'); -- 数据转换

💡 实战场景演示

假设我们有一个用户表 users：

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    created_at DATETIME
);

这个时候，运营小姐姐丢给你一个 100 万行的 CSV 文件 user_data.csv，内容如下：

Name,Email,JoinDate
"zhangsan","zhangsan@example.com","2023-10-01"
"lisi","lisi@example.com","2023-10-02"
...

第一步：准备 SQL

我们需要处理几个问题：

  1. 跳过第一行表头。
  2. 字段是用逗号隔开的。
  3. CSV 里的列顺序和数据库不完全一致（不需要 ID，数据库自增）。

SQL 如下：

LOAD DATA LOCAL INFILE '/tmp/user_data.csv'
INTO TABLE users
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(username, email, created_at);

第二步：关键参数解析

  1. LOCAL 关键字：

    • 不加 LOCAL：MySQL 会尝试从 数据库服务器 的本地文件系统读取文件。这通常需要 root 权限，且受 secure_file_priv 配置限制。
    • 加上 LOCAL：MySQL 客户端（也就是你的电脑或应用服务器）读取文件并发送给服务器。推荐使用，更灵活！

  2. FIELDS TERMINATED BY：

    • CSV 用 ,。
    • TSV 用 \t。

  3. IGNORE 1 LINES：

    • 如果文件带标题行，一定要加这个，否则标题也会被当成数据插进去。

⚠️ 避坑指南（血泪经验）

在使用 LOAD DATA 时，你可能会遇到以下几个“大坑”：

  1. Error 1148: The used command is not allowed with this MySQL version

    • 原因：为了安全，MySQL 默认可能禁用了 LOCAL 加载。
    • 解决：
      • 服务端：在 my.cnf 中设置 local_infile=1。
      • 客户端：连接时加上参数，例如 mysql --local-infile=1 -u root -p。

  2. Error 1290: The MySQL server is running with the --secure-file-priv option

    • 原因：你没用 LOCAL 关键字，且文件不在 MySQL 允许的目录下。
    • 解决：使用 LOAD DATA LOCAL INFILE，或者把文件移到 SHOW VARIABLES LIKE 'secure_file_priv'; 指向的目录。

  3. 中文乱码问题

    • 解决：确保文件编码（如 UTF-8）和命令中的 CHARACTER SET 一致。推荐统一使用 utf8mb4。

⚡ 性能优化小贴士

如果你要导入的数据量达到 千万级，仅靠 LOAD DATA 可能还不够，试试这几招：

  1. 关闭自动提交：SET autocommit=0;，导入完再 COMMIT;。
  2. 暂时移除索引：如果表很大，先删掉非主键索引，导完数据再重建索引，速度会快很多。
  3. 关闭唯一性校验：SET unique_checks=0;（确保数据本身没重复）。
  4. 关闭外键校验：SET foreign_key_checks=0;。

📝 总结

LOAD DATA 是 MySQL 批量导入的神器。它简单、粗暴、有效。

下次再遇到大批量数据迁移，别再傻傻写 Insert 脚本了，掏出这个命令，省下的时间去喝杯咖啡不香吗？☕️

如果你觉得这篇文章有用，记得点赞收藏哦！ 👇

MySQL