如何用PHP高效导入带外键的CSV大数据至MySQL多表

我之前也遇到过类似的大型CSV多表导入问题，结合MySQL原生特性和Zend/Doctrine的最佳实践，整理了几个高效方案，应该能帮到你：

一、最快方案：LOAD DATA + 临时表 + 原生批量SQL #

这是利用MySQL原生最快的导入能力，同时解决外键关联问题的最优解，完全避开循环插入的低效问题：

步骤1：创建临时表 #

先创建一个和CSV结构完全匹配的临时表，不需要外键、不必要的索引（减少导入时的开销）：

CREATE TEMPORARY TABLE temp_import_data (
    csv_unique_id VARCHAR(255),
    csv_col1 VARCHAR(255),
    csv_sub_col1 INT,
    -- 对应CSV的所有列，根据实际情况定义字段类型
    PRIMARY KEY (csv_unique_id) -- 仅保留必要的唯一标识，方便后续关联
) ENGINE=InnoDB;

步骤2：用LOAD DATA批量导入CSV到临时表 #

这一步是整个流程中最快的环节，MySQL原生支持，比循环插入快几个数量级：

LOAD DATA INFILE '/path/to/your/large_file.csv'
INTO TABLE temp_import_data
FIELDS TERMINATED BY ',' ENCLOSED BY '\"' -- 根据你的CSV格式调整分隔符、包裹符
LINES TERMINATED BY '\n'
IGNORE 1 LINES -- 如果CSV有表头，跳过第一行
(csv_unique_id, csv_col1, csv_sub_col1); -- 按CSV列顺序对应临时表字段

注意：如果遇到文件权限问题，可以用LOAD DATA LOCAL INFILE，或者确保MySQL进程有权限读取目标文件。

步骤3：批量插入主表 + 关联子表 #

假设你有主表main_table（带自增主键id）和子表sub_table（外键main_id关联main_table.id），通过临时表的唯一标识关联：

1. 先插入主表（自动去重重复的主记录）：

INSERT INTO main_table (unique_identifier, col1)
SELECT DISTINCT csv_unique_id, csv_col1 FROM temp_import_data;

2. 再通过关联临时表和主表，批量插入子表：

INSERT INTO sub_table (main_id, sub_col1)
SELECT m.id, t.csv_sub_col1
FROM temp_import_data t
JOIN main_table m ON t.csv_unique_id = m.unique_identifier; -- 用双方的唯一标识关联

步骤4：清理临时表 #

导入完成后直接删除临时表：

DROP TEMPORARY TABLE temp_import_data;

二、结合Zend/Doctrine的优化方案 #

如果必须在Zend+Doctrine环境下操作，避开ORM的实体映射开销，直接用Doctrine DBAL执行原生SQL，兼顾框架环境和导入效率：

示例代码（Zend+Doctrine DBAL） #

// 获取Doctrine DBAL连接
$connection = $entityManager->getConnection();

// 1. 创建临时表
$connection->executeQuery("
    CREATE TEMPORARY TABLE temp_import_data (
        csv_unique_id VARCHAR(255),
        csv_col1 VARCHAR(255),
        csv_sub_col1 INT
    ) ENGINE=InnoDB;
");

// 2. 执行LOAD DATA导入CSV
$connection->executeQuery("
    LOAD DATA INFILE '/path/to/your/file.csv'
    INTO TABLE temp_import_data
    FIELDS TERMINATED BY ',' ENCLOSED BY '\"'
    LINES TERMINATED BY '\n'
    IGNORE 1 LINES
    (csv_unique_id, csv_col1, csv_sub_col1);
");

// 3. 批量插入主表和子表（用事务包裹保证原子性）
$connection->beginTransaction();
try {
    // 插入主表
    $connection->executeQuery("
        INSERT INTO main_table (unique_identifier, col1)
        SELECT DISTINCT csv_unique_id, csv_col1 FROM temp_import_data;
    ");
    // 插入子表
    $connection->executeQuery("
        INSERT INTO sub_table (main_id, sub_col1)
        SELECT m.id, t.csv_sub_col1
        FROM temp_import_data t
        JOIN main_table m ON t.csv_unique_id = m.unique_identifier;
    ");
    $connection->commit();
} catch (\Exception $e) {
    $connection->rollBack();
    throw $e;
}

// 4. 删除临时表
$connection->executeQuery("DROP TEMPORARY TABLE temp_import_data;");

三、通用优化技巧（进一步提速） #

• 禁用外键约束：导入前执行SET FOREIGN_KEY_CHECKS = 0;，完成后再执行SET FOREIGN_KEY_CHECKS = 1;，避免每次插入子表时的外键检查开销。
• 临时禁用索引：导入前删除主表和子表的非必要索引，导入完成后重建，因为插入时维护索引会大幅拖慢速度。
• 大事务包裹：把所有导入操作放在一个事务里，减少磁盘IO的提交次数（MySQL默认每执行一次DML就提交一次，大事务可以合并多次写入）。
• 分块处理超大型CSV：如果CSV文件超过10GB，可以手动分割成多个小文件，或者在临时表中分批查询插入（比如LIMIT 100000 OFFSET 0循环处理），避免内存溢出。

为什么之前的方案慢？ #

• 循环执行普通查询：每次查询都要建立网络连接、解析SQL、执行，累计开销极大，完全不适合百万级数据。
• Doctrine ORM：ORM的实体映射、生命周期回调、缓存机制带来了大量额外开销，对于批量导入场景，原生SQL/DBAL才是正确选择。

内容的提问来源于stack exchange，提问作者Intellect