企业官网建设流程全解析

1. 项目概述：为什么LOAD DATA INFILE是数据工程师的“瑞士军刀”

在数据处理的日常里，我们经常遇到一个场景：手头有一份几百万行的CSV或TXT文件，需要快速、完整地导入到MySQL数据库里。用程序一行行INSERT？那太慢了，光是网络I/O和事务开销就让人等得心焦。用图形化工具点点点？文件一大就卡死，还容易因为编码问题导致乱码。这时候，MySQL自带的一个“神器”就该登场了——LOAD DATA INFILE命令。

简单来说，LOAD DATA INFILE是MySQL提供的一条从服务器本地文本文件高速批量导入数据到数据库表的SQL语句。它的速度有多快？在我的实际项目中，导入一个包含1000万行、大小约2GB的CSV文件到InnoDB表，通过精心调优的LOAD DATA INFILE，耗时可以控制在3分钟以内，这比传统的INSERT语句快了不止一个数量级。它绕过了SQL解析和网络通信的层层开销，直接以接近磁盘I/O极限的速度将数据“灌”入表中，是数据迁移、日志分析、报表初始化等场景下的首选方案。

然而，这把“瑞士军刀”虽然锋利，但新手直接上手很容易割伤自己。文件路径权限不对、字符集混乱、字段分隔符错位、主键冲突……任何一个细节没处理好，都会导致导入失败或数据错乱。网上能找到的教程往往只给出一个最简单的成功案例，对背后复杂的参数和层出不穷的错误语焉不详。这篇文章，我就结合自己这些年踩过的坑和填过的坑，把LOAD DATA INFILE从核心原理、完整实操到错误排查，给你一次讲透。无论你是运维、开发还是数据分析师，下次再面对海量数据文件时，都能从容地用它搞定。

2. 核心原理与前置条件：理解“高速”背后的机制

在动手之前，我们必须先搞清楚LOAD DATA INFILE为什么快，以及它运行需要哪些前提条件。知其然更要知其所以然，这能帮助我们在遇到问题时快速定位根源。

2.1 工作机制：绕过SQL层的“数据管道”

普通的INSERT INTO table VALUES (...), (...), ...语句，即使使用多值插入，也需要经过以下步骤：

1. 客户端将SQL语句通过网络发送到MySQL服务器。
2. 服务器端的SQL解析器对语句进行词法、语法分析。
3. 优化器生成执行计划。
4. 存储引擎（如InnoDB）开始处理每一行数据：检查约束、更新索引、写入事务日志（redo log）、最终写入数据页。这个过程伴随着大量的事务管理和锁竞争。

而LOAD DATA INFILE的工作流程则精简得多：

1. 文件读取：MySQL服务器进程（mysqld）直接从操作系统文件系统读取指定的数据文件。这意味着文件必须位于MySQL服务器所在的主机上，客户端无法直接读取自己本机的文件（除非使用LOAD DATA LOCAL INFILE，但这引入了安全性和配置问题，后面会详述）。
2. 流式解析：服务器按照我们指定的格式（如字段分隔符FIELDS TERMINATED BY ','、行分隔符LINES TERMINATED BY '\n'）对文件进行流式解析，将文本行拆分成一个个字段值。
3. 批量应用：解析出的数据会以大批量的方式直接发送给存储引擎。对于InnoDB，它会利用其变更缓冲区（Change Buffer）来优化非唯一二级索引的更新，并可能采用一种更高效的方式来处理自增主键的分配和页的填充，极大地减少了离散I/O和锁的粒度。

这种“直达”的方式，避免了SQL解析、网络传输和单行事务的开销，是性能提升的关键。

2.2 关键前置条件与权限检查

不是在任何情况下都能随意使用这个命令的。以下是必须满足的条件，请务必在操作前逐一核对：

1. FILE权限：执行该命令的MySQL用户必须拥有FILE全局权限。这个权限允许MySQL服务器进程读取服务器主机上的文件。

   -- 查看当前用户权限 SHOW GRANTS FOR CURRENT_USER; -- 或 SHOW GRANTS; -- 授予FILE权限（需要GRANT权限的用户执行） GRANT FILE ON *.* TO 'your_username'@'your_host'; FLUSH PRIVILEGES;

   注意：FILE权限是一个高危权限，因为它允许用户读取服务器上任何MySQL进程有权限读取的文件。在生产环境中，应仅将其授予受信任的、专门用于数据导入的用户，并限定其来源主机（如'importer'@'192.168.1.100'）。

2. 文件路径与权限：

   • 路径：INFILE子句后面跟的文件路径，是MySQL服务器所在操作系统上的路径，不是客户端机器的路径。例如，你的MySQL跑在Linux服务器/data/mysql/目录下，那么你的数据文件也必须上传到这个服务器的某个位置，比如/tmp/data.csv。
   • 操作系统权限：运行MySQL服务（通常是mysql用户或mysqld进程所属用户）必须对该数据文件有读权限，对文件所在目录有执行权限。这是最常见的失败原因之一。

     # 在MySQL服务器上检查 ls -l /tmp/data.csv # 应确保 mysql 用户可读 # 如：-rw-r--r-- 1 root root 100M Jul 1 10:00 /tmp/data.csv # 如果属主是root，需要更改权限或属主 sudo chown mysql:mysql /tmp/data.csv sudo chmod 644 /tmp/data.csv

3. secure_file_priv系统变量：这是MySQL的一个安全限制。它定义了LOAD DATA INFILE和SELECT ... INTO OUTFILE可以访问的目录。

   -- 查看当前设置 SHOW VARIABLES LIKE 'secure_file_priv';

   • 如果值为NULL，则禁止使用这些语句（常见于较新版本的默认安装）。
   • 如果值为一个目录路径（如/var/lib/mysql-files/），则只能从该目录导入或导出到该目录。
   • 如果值为空字符串''，则表示不限制（安全性较低，不推荐）。解决方案：要么将数据文件移动到secure_file_priv指定的目录下，要么在确保安全的前提下，在MySQL配置文件（如my.cnf）中修改该变量并重启服务。

   [mysqld] secure_file_priv = /your/safe/directory/

3. 命令语法深度解析与实战参数调优

一个完整的LOAD DATA INFILE语句包含多个子句，每个子句都控制着导入过程的一个关键环节。下面我们拆解每一个部分。

3.1 基础语法框架

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name' [REPLACE | IGNORE] INTO TABLE tbl_name [CHARACTER SET charset_name] [FIELDS [TERMINATED BY 'string'] [[OPTIONALLY] ENCLOSED BY 'char'] [ESCAPED BY 'char'] ] [LINES [STARTING BY 'string'] [TERMINATED BY 'string'] ] [IGNORE number {LINES | ROWS}] [(col_name_or_user_var [, col_name_or_user_var] ...)] [SET col_name = expr [, col_name = expr] ...]

3.2 核心子句详解与配置示例

3.2.1[LOCAL]关键字：本地与服务器文件之辨

• 不加LOCAL：'file_name'是MySQL服务器主机上的路径。这是最高效的模式，因为服务器直接磁盘读取。
• 加LOCAL：'file_name'是客户端主机上的路径。文件内容会通过客户端连接传输到服务器，再由服务器处理。这带来了便利，但也引入了安全风险（服务器可能加载客户端上的恶意文件）和性能开销。使用LOCAL通常需要额外的客户端库支持和配置，且受local_infile系统变量控制。

  -- 在服务器端启用local_infile（如果需要的话） SET GLOBAL local_infile = 1;

  实操心得：在可控的内网环境或一次性导入任务中，为了方便可以使用LOCAL。但在生产环境或自动化脚本中，我强烈建议先将文件上传到服务器指定目录（如secure_file_priv目录），然后使用不带LOCAL的命令。这样更安全，性能也更好。

3.2.2[REPLACE | IGNORE]关键字：主键/唯一键冲突处理

这是数据合并时的关键策略。

• REPLACE：如果导入的数据行与表中现有行的主键或唯一键冲突，则删除原有行，并插入新行。相当于先DELETE再INSERT。
• IGNORE：如果冲突，则静默跳过该行导入，不报错，继续后续行。
• 两者都不指定：默认行为是报错，并终止整个导入操作。

  注意事项：REPLACE对于有自增主键的表要小心。如果替换了一行，该行的自增ID会被新的ID覆盖，可能导致业务逻辑混乱。IGNORE则可能导致数据不完整。最佳实践是，在导入前确保源数据主键唯一，或先导入到临时表，再通过INSERT ... ON DUPLICATE KEY UPDATE ...进行更精细的合并。

3.2.3CHARACTER SET字符集设置

务必指定与数据文件编码一致的字符集。否则，中文等非ASCII字符就会变成乱码。

• 常见的文件编码：utf8mb4（推荐，支持完整Unicode包括emoji）、gbk、latin1。
• 如何检查文件编码？在Linux下可以用file或enca命令。

  file -i data.csv # 输出可能为：data.csv: text/plain; charset=utf-8

  -- 在LOAD DATA语句中指定 LOAD DATA INFILE '/tmp/data.csv' INTO TABLE my_table CHARACTER SET utf8mb4 ...;

3.2.4FIELDS和LINES子句：定义文件格式

这是解析文件的“地图”，必须与文件实际格式严丝合缝。

1. FIELDS 子句：

• TERMINATED BY：字段分隔符。CSV常用','，TSV（Tab分隔）常用'\t'。
• ENCLOSED BY：字段引用符。很多CSV文件会用双引号"把字段括起来，特别是字段内包含分隔符时。例如："Smith, John", 28, "Engineer"。
• OPTIONALLY：可选修饰符，用在ENCLOSED BY前。表示只有被引用符括起来的字段才按引用符处理，否则忽略。对于混合格式（有的字段有引号，有的没有）很实用。
• ESCAPED BY：转义字符。默认是反斜杠\。如果字段值中包含分隔符或引用符本身，就需要用它转义，如"Say \"Hello\""。

2. LINES 子句：

• TERMINATED BY：行分隔符。在Linux/Unix上是\n，在Windows上是\r\n，在旧Mac上是\r。这是最常见的错误来源之一。如果文件是在Windows生成但导入到Linux服务器，不指定\r\n会导致最后一行解析错误或整行被当作一个字段。
• STARTING BY：行起始符。较少用，可用于跳过每行开头固定的字符（如日志时间戳）。

格式配置示例：假设有一个复杂的CSV文件data.csv，内容如下：

id,name,description,salary 1,"Doe, John","He said: \"Hello World!\"",5000 2,Smith Jane,Software Engineer,6000

对应的LOAD DATA语句应为：

LOAD DATA INFILE '/tmp/data.csv' INTO TABLE employees CHARACTER SET utf8mb4 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' -- 注意，在SQL字符串中反斜杠需要转义 LINES TERMINATED BY '\n' IGNORE 1 LINES -- 跳过标题行 (id, name, description, salary);

3.2.5IGNORE number LINES：跳过文件头部

用于跳过标题行（Header）。例如IGNORE 1 LINES。如果文件有多个注释行，就忽略对应的行数。

3.2.6 列列表与SET子句：数据映射与转换

• (col_name_or_user_var, ...)：指定文件中的字段按顺序对应到表的哪些列。如果省略，则默认文件中的字段顺序必须与表定义中的列顺序完全一致。这是一个极易出错的地方，特别是表结构发生过变更时。
• SET子句：可以对导入的数据进行实时计算或转换。

  LOAD DATA INFILE '/tmp/data.txt' INTO TABLE orders (order_id, product_name, raw_price) -- 文件只提供这三个字段 SET final_price = raw_price * 0.9, -- 打九折 import_time = NOW(); -- 记录导入时间

4. 完整实战流程：从文件准备到导入验证

让我们通过一个模拟真实业务的例子，走一遍全流程。场景：将一份用户行为日志CSV导入到分析库。

4.1 步骤一：在MySQL服务器上准备数据文件

假设我们通过scp或sftp将文件user_logs_20230701.csv上传到了服务器的/var/lib/mysql-files/目录（该目录通常是secure_file_priv的默认值）。 文件内容预览：

timestamp,user_id,action,device,extra_info 2023-07-01 08:01:23,1001,login,"iPhone 13","{"network":"4G"}" 2023-07-01 08:02:45,1002,view_item,Android,"{"item_id": "A123"}" 2023-07-01 08:05:11,1001,purchase,Windows PC,"{"amount": 299.00, "payment": "credit_card"}"

4.2 步骤二：在MySQL中创建目标表

根据文件内容设计表结构。注意字段类型和长度。

CREATE TABLE `user_behavior_logs` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键', `log_time` datetime NOT NULL COMMENT '行为时间', `user_id` int(11) NOT NULL COMMENT '用户ID', `action` varchar(50) NOT NULL COMMENT '行为类型', `device` varchar(100) DEFAULT NULL COMMENT '设备信息', `extra_info_json` json DEFAULT NULL COMMENT '额外信息(JSON格式)', `import_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '导入时间', PRIMARY KEY (`id`), KEY `idx_user_time` (`user_id`,`log_time`), KEY `idx_time` (`log_time`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户行为日志表';

设计要点：extra_info字段在CSV里是JSON字符串，我们直接使用MySQL的JSON数据类型来存储，便于后续查询。import_at用于记录数据导入时间，这是一个好习惯。

4.3 步骤三：执行LOAD DATA INFILE命令

现在，组装我们的命令。注意文件中的extra_info是JSON字符串，我们需要用SET子句将其转换为JSON类型。

-- 首先，确认文件权限和路径 -- 假设 secure_file_priv 就是 '/var/lib/mysql-files/' LOAD DATA INFILE '/var/lib/mysql-files/user_logs_20230701.csv' INTO TABLE user_behavior_logs CHARACTER SET utf8mb4 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' IGNORE 1 LINES -- 跳过标题行 (@log_time, @user_id, @action, @device, @extra_info_json) -- 用用户变量临时接收 SET log_time = STR_TO_DATE(@log_time, '%Y-%m-%d %H:%i:%s'), -- 转换时间格式 user_id = @user_id, action = @action, device = NULLIF(@device, ''), -- 如果device字段为空字符串，则存为NULL extra_info_json = JSON_UNQUOTE(@extra_info_json), -- 去除JSON字符串两端的引号，使其成为有效的JSON import_at = CURRENT_TIMESTAMP; -- 使用SET子句中的值，会覆盖列的DEFAULT值

关键点解析：

1. 用户变量（@var）：在列列表中，我们可以使用用户变量来临时存储从文件读取的原始值。这给了我们极大的灵活性，可以在SET子句中对这些变量进行加工。
2. STR_TO_DATE：文件中的时间戳是字符串，需要用这个函数按指定格式解析成MySQL的DATETIME类型。
3. NULLIF：一个实用的函数，如果第一个参数等于第二个参数，则返回NULL。这里用于处理可能存在的空设备字段。
4. JSON_UNQUOTE：因为文件中的JSON被双引号括着，读进来是一个带引号的字符串（如"{\"network\":\"4G\"}"）。JSON_UNQUOTE去掉外层的引号，使其变成{"network":"4G"}，这样才能被正确存入JSON列。

4.4 步骤四：验证导入结果

执行完成后，不要假设一切顺利，务必进行检查。

-- 1. 检查导入行数 SELECT ROW_COUNT(); -- 上一条语句影响的行数 SELECT COUNT(*) FROM user_behavior_logs; -- 表总行数 -- 2. 随机抽查几行数据，看格式是否正确 SELECT * FROM user_behavior_logs LIMIT 3\G -- 使用\G垂直输出，便于查看JSON字段 -- 检查log_time是否为正确的DATETIME，extra_info_json是否能被JSON函数解析 SELECT id, JSON_EXTRACT(extra_info_json, '$.network') FROM user_behavior_logs WHERE action = 'login'; -- 3. 检查数据完整性（对比源文件行数） -- 可以在导入前用 `wc -l` 命令计算文件行数（减去标题行）

5. 高频错误代码与排查指南大全

即使准备得再充分，错误也难免会发生。下面是我整理的最常见的错误、原因及解决方法。

5.1 错误代码 1290 (HY000): The MySQL server is running with the --secure-file-priv option

问题描述：执行命令时直接报此错误，无法继续。原因分析：这是最经典的错误。MySQL的secure_file_priv变量限制了文件导入/导出的目录。解决方案：

1. 查询当前设置：SHOW VARIABLES LIKE 'secure_file_priv';
2. 方法A（推荐）：将你的数据文件移动到该变量指定的目录下。例如，如果值是/var/lib/mysql-files/，就把文件拷到那里。
3. 方法B（修改配置，需重启）：如果需要永久修改，编辑MySQL配置文件（如/etc/my.cnf或/etc/mysql/mysql.conf.d/mysqld.cnf），在[mysqld]段添加或修改：

   [mysqld] secure_file_priv = /your/desired/directory/

   然后重启MySQL服务：sudo systemctl restart mysql。注意，将目录设置为''（空字符串）可以禁用此限制，但有安全风险，不推荐生产环境使用。

5.2 错误代码 13 (HY000): Can't get stat of '/path/to/file.csv' (Errcode: 13 - Permission denied)

问题描述：文件明明存在，但MySQL说权限不够。原因分析：运行MySQL服务的系统用户（通常是mysql）对目标文件或文件所在路径的父目录没有足够的读取或执行权限。排查与解决：

1. 确认MySQL服务进程的用户：ps aux | grep mysqld。通常是mysql。
2. 检查文件及其父目录的权限：

   ls -la /path/to/ ls -la /path/to/file.csv

3. 确保mysql用户至少对文件有读(r)权限，对文件所在的所有上级目录都有执行(x)权限。

   # 常用修正命令 sudo chown mysql:mysql /path/to/file.csv # 更改文件属主 sudo chmod 644 /path/to/file.csv # 设置文件权限为rw-r--r-- sudo chmod +x /path/to/ # 确保目录对mysql用户有执行权

   踩坑记录：有一次在CentOS上，文件放在/tmp下，但/tmp目录的权限是drwxrwxrwt，最后一位t是粘滞位，虽然mysql用户有执行权，但某些安全配置下仍可能出问题。最稳妥的办法还是使用MySQL专用的目录（如secure_file_priv指定的目录）。

5.3 错误代码 2 (HY000): File '/path/to/file.csv' not found (Errcode: 2)

问题描述：找不到文件。原因分析：

1. 路径拼写错误，或文件确实不存在于该路径。
2. 使用了相对路径。LOAD DATA INFILE通常需要绝对路径。
3. 如果使用了LOCAL关键字，文件是在客户端机器上，但客户端没有找到该文件。解决方案：

• 使用绝对路径。
• 在服务器上使用ls -l命令确认文件是否存在且路径正确。
• 对于LOCAL，确保客户端程序（如mysql命令行客户端）有权限读取该文件。

5.4 错误代码 29 (HY000): File '/path/to/file.csv' not found (Errcode: 29 - Illegal seek)

问题描述：这个错误比较诡异，文件存在，权限也对，但报“非法定位”。原因分析：文件可能是一个符号链接（symlink），而MySQL出于安全考虑，默认不允许跟随符号链接。解决方案：

1. 检查文件是否为符号链接：ls -l /path/to/file.csv，如果第一个字符是l，那就是链接。
2. 要么使用链接指向的实际文件路径，要么在MySQL配置中启用--symbolic-links选项（不推荐，有安全风险）。

5.5 错误代码 1062 (23000): Duplicate entry 'XXX' for key 'PRIMARY'

问题描述：导入过程中断，提示主键重复。原因分析：要导入的数据中包含与表中现有数据重复的主键或唯一键值，并且你没有使用IGNORE或REPLACE选项。解决方案：

• 方案一（忽略重复）：在命令中加入IGNORE关键字。重复的行会被跳过，但会记录警告。执行后可以用SHOW WARNINGS;查看跳过了多少行。
• 方案二（替换重复）：在命令中加入REPLACE关键字。旧行会被删除，新行插入。注意自增ID的变化和可能的外键约束。
• 方案三（精细合并）：这是最推荐的做法。先将数据导入到一个临时表（CREATE TEMPORARY TABLE tmp LIKE target_table;），然后在临时表和目标表之间使用INSERT ... ON DUPLICATE KEY UPDATE ...语句，可以自由决定冲突时更新哪些字段。

  -- 先导入到临时表 LOAD DATA INFILE ... INTO TEMPORARY TABLE tmp ...; -- 再合并到目标表 INSERT INTO target_table SELECT * FROM tmp ON DUPLICATE KEY UPDATE some_column = VALUES(some_column), another_column = VALUES(another_column);

5.6 错误代码 1366 (HY000): Incorrect string value: '\xE4\xB8\xAD\xE6\x96\x87' for column 'name' at row 1

问题描述：中文字符导入后变成乱码或直接报错。原因分析：字符集不匹配“三连杀”——文件编码、连接编码、表字段编码不一致。系统性排查：

1. 确认文件编码：如前所述，用file -i或编辑器查看。
2. 确认MySQL连接编码：执行SHOW VARIABLES LIKE 'character_set_%';，关注character_set_client、character_set_connection、character_set_results。建议在连接时或会话中设置为utf8mb4。

   SET NAMES utf8mb4;

3. 确认表和列的字符集：

   SHOW CREATE TABLE your_table;

4. 在LOAD DATA语句中显式指定字符集：这是最关键的一步，确保MySQL按正确的编码解读文件。

   LOAD DATA INFILE ... CHARACTER SET utf8mb4 ...

   深度解析：CHARACTER SET子句指定的是文件的编码。MySQL会先用这个编码读取文件内容，然后在内部转换为连接字符集（character_set_connection），最后再转换为目标列的字符集进行存储。任何一步转换不支持（如从gbk直接转到latin1），都会导致乱码或错误。

5.7 错误现象：数据错列，所有数据都挤在第一列或列对应关系混乱

问题描述：导入后查询，发现本该在name列的数据跑到了id列，或者整行数据都被当作一个字段。原因分析：FIELDS和LINES子句的配置与文件实际格式不符。排查步骤：

1. 检查字段分隔符：用文本编辑器（如vim、notepad++）打开文件，查看是否真的是逗号,。有时文件可能使用制表符\t、分号;或竖线|。
2. 检查行分隔符：这是超级高频错误源。在Linux服务器上用cat -A命令查看文件，可以显示所有不可见字符。

   cat -A /path/to/file.csv

   • 如果行尾显示^M$，说明是Windows格式（\r\n），那么LINES TERMINATED BY应该设为\r\n。
   • 如果只显示$，说明是Unix/Linux格式（\n），设为\n。
   • 如果显示^M，说明是旧Mac格式（\r），设为\r。
3. 检查引用符和转义符：如果字段内包含分隔符或换行符，必须用引用符括起来。检查ENCLOSED BY和ESCAPED BY的设置是否与文件匹配。
4. 核对列列表：确认INTO TABLE后面指定的列列表顺序、数量是否与文件字段顺序完全对应。如果表有自增ID列但文件没有，需要在列列表中排除它，或者用SET子句处理。

5.8 性能问题：导入速度慢，甚至导致服务器负载飙升

问题描述：导入一个几G的文件，速度远低于预期，数据库服务器CPU或IO很高。原因分析与调优：

1. 关闭索引（针对超大表）：对于有大量二级索引的表，每插入一行都要更新索引，这是主要的性能瓶颈。可以在导入前先删除非唯一索引，导入完成后再重建。

   -- 导入前 ALTER TABLE your_table DROP INDEX idx_some_column; -- 执行 LOAD DATA ... -- 导入后 ALTER TABLE your_table ADD INDEX idx_some_column (some_column);

   警告：唯一索引和主键不能删除，否则会影响数据完整性。此操作需在业务低峰期进行，并评估重建索引的时间。

2. 调整事务提交：LOAD DATA默认是一个独立的事务。对于超大数据量，这个事务会非常巨大，产生庞大的undo log，可能撑满磁盘。可以分批次导入，或者使用mysql客户端的--innodb-batch-size选项（如果支持）来拆分事务。
3. 调整InnoDB参数（临时）：在导入会话中临时调整参数可以提升速度，但需谨慎，最好在从库或测试环境操作。

   SET foreign_key_checks = 0; -- 关闭外键检查（如果表有外键） SET unique_checks = 0; -- 关闭唯一性检查（确保数据本身唯一） SET sql_log_bin = 0; -- 如果是从库或不需要二进制日志，可以关闭（有主从复制时小心） -- 执行 LOAD DATA ... SET foreign_key_checks = 1; SET unique_checks = 1; SET sql_log_bin = 1;

4. 使用CONCURRENT选项（MyISAM引擎）：如果表是MyISAM引擎，使用LOAD DATA CONCURRENT可以在导入时允许其他会话读取表（但写入仍被阻塞）。InnoDB引擎此选项无效。
5. 硬件与系统层面：确保数据文件放在高速磁盘（如SSD）上。检查服务器磁盘IO使用率（iostat命令），避免导入期间其他IO密集型任务争抢资源。

6. 进阶技巧与场景化应用

掌握了基础操作和排错后，再看几个能显著提升效率和可靠性的进阶玩法。

6.1 从压缩文件直接导入

如果数据文件是gzip压缩的（.gz），我们不需要先解压。在Linux系统上，可以利用命名管道（named pipe）或进程替换（process substitution）来实现流式解压导入，这对于处理巨大的压缩文件非常节省磁盘空间。

# 方法一：使用命名管道（推荐，更直观） mkfifo /tmp/data_pipe.csv gzip -dc /path/to/bigfile.csv.gz > /tmp/data_pipe.csv & # 然后在另一个终端或后台，执行MySQL导入命令，从管道读取 mysql -u user -p db_name -e "LOAD DATA INFILE '/tmp/data_pipe.csv' INTO TABLE ..." # 导入完成后，删除管道文件 rm /tmp/data_pipe.csv # 方法二：在LOAD DATA中直接使用进程替换（仅支持某些Shell，如bash） # 注意：这需要MySQL有读取 /dev/fd/ 或类似文件描述符的权限，且secure_file_priv可能不允许。 # 以下命令在bash中且配置允许时可能有效： mysql -u user -p db_name -e "LOAD DATA INFILE '/dev/fd/3' INTO TABLE ..." 3< <(gzip -dc bigfile.csv.gz)

核心原理：gzip -dc是解压并输出到标准输出。mkfifo创建了一个先入先出的特殊文件，gzip向它写，LOAD DATA从它读，数据像水流一样通过，不会在磁盘上产生巨大的临时解压文件。

6.2 使用用户变量进行复杂数据清洗

SET子句配合用户变量非常强大，可以在导入时完成简单的ETL（提取、转换、加载）。

LOAD DATA INFILE '/path/to/dirty_data.csv' INTO TABLE clean_table FIELDS ... LINES ... (@raw_date, @dirty_number, @description) SET -- 清洗日期：将多种格式统一 clean_date = CASE WHEN @raw_date REGEXP '^[0-9]{4}-[0-9]{2}-[0-9]{2}$' THEN STR_TO_DATE(@raw_date, '%Y-%m-%d') WHEN @raw_date REGEXP '^[0-9]{2}/[0-9]{2}/[0-9]{4}$' THEN STR_TO_DATE(@raw_date, '%m/%d/%Y') ELSE NULL END, -- 清洗数字：移除货币符号和千分位逗号 clean_number = CAST(REPLACE(REPLACE(@dirty_number, '$', ''), ',', '') AS DECIMAL(10,2)), -- 清洗文本：去除首尾空格，将空字符串转为NULL clean_description = NULLIF(TRIM(@description), ''), -- 生成衍生字段：从描述中提取关键词 category = CASE WHEN @description LIKE '%error%' OR @description LIKE '%fail%' THEN 'ERROR' WHEN @description LIKE '%warning%' THEN 'WARN' ELSE 'INFO' END;

6.3 监控导入进度与性能

对于超长时间的导入，我们想知道进度。虽然LOAD DATA本身不提供进度条，但有一些间接方法：

1. 监控表行数增长：在另一个MySQL会话中，定期执行SELECT COUNT(*) FROM target_table;。可以配合watch命令（Linux）：

   watch -n 5 'mysql -u user -p密码 -e "SELECT COUNT(*) FROM db_name.target_table;"'

2. 监控文件读取进度（Linux）：使用lsof命令查看MySQL进程打开了哪个文件，以及文件的读取偏移量。

   sudo lsof -p $(pidof mysqld) | grep /path/to/your/datafile.csv # 输出中会显示文件大小和当前读取位置（OFFSET）

3. 监控服务器状态：使用SHOW PROCESSLIST;查看当前执行的命令状态。或者监控InnoDB状态变量：

   SHOW GLOBAL STATUS LIKE 'Innodb_rows_inserted%'; -- 在导入前后分别执行，差值就是插入的行数（注意这是全局的，不只针对当前表）。

6.4 与主从复制的协同

在配置了MySQL主从复制的环境中，LOAD DATA语句默认会被记录为二进制日志（binlog）中的LOAD DATA事件，并传输到从库执行。这保证了数据一致性。但需要注意：

• LOCAL关键字的影响：如果主库上使用了LOAD DATA LOCAL INFILE，由于文件在客户端，这个语句在binlog中会被转换为一个普通的LOAD DATA语句，但文件内容会以特殊的格式（Begin_load_query/Append_block/Exec_load_query事件）记录在binlog中。这要求主从库的secure_file_priv设置兼容，且从库能找到对应的“伪”文件路径，配置较为复杂。生产环境强烈建议避免在主库使用LOCAL。
• 从库性能：巨大的LOAD DATA操作在从库回放时同样耗时，可能导致主从延迟。可以考虑在从库上暂时关闭二进制日志（SET sql_log_bin=0;）再执行导入（如果拓扑结构允许），或者使用pt-online-schema-change等在线工具进行数据迁移，对复制更友好。