如何将3GB/1100万行的大TXT文件导入MySQL？求高效替代方案

一、推荐：用MySQL原生LOAD DATA INFILE（最快方案） #

PHP逐行解析导入慢的核心原因是频繁的数据库交互和应用层开销，MySQL自带的LOAD DATA INFILE直接从磁盘读取数据写入存储引擎，速度能提升几十倍甚至上百倍。

步骤1：创建匹配的表结构 #

先根据你的TXT字段定义表（字段名、数据类型按需调整）：

CREATE TABLE user_data (
    id BIGINT,
    col2 VARCHAR(255),
    col3 VARCHAR(255),
    phone VARCHAR(50),
    col5 VARCHAR(255),
    col6 VARCHAR(255),
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    gender VARCHAR(10),
    profile_url VARCHAR(255),
    col11 VARCHAR(255),
    username VARCHAR(100),
    full_name VARCHAR(200),
    description TEXT,
    clinic_name VARCHAR(200),
    profession VARCHAR(100),
    col17 VARCHAR(255),
    address TEXT,
    institution VARCHAR(100),
    email VARCHAR(255),
    col21 INT,
    col22 INT,
    col23 INT,
    date1 DATETIME,
    date2 DATETIME,
    col26 VARCHAR(255),
    col27 VARCHAR(255),
    col28 VARCHAR(255),
    col29 VARCHAR(255),
    col30 VARCHAR(255),
    col31 VARCHAR(255),
    col32 VARCHAR(255),
    col33 VARCHAR(255),
    col34 VARCHAR(255),
    col35 VARCHAR(255)
);

步骤2：执行LOAD DATA命令 #

针对你的TXT格式（双引号包裹字段、逗号分隔），执行以下SQL：

-- 先关闭不必要的检查，提升导入速度
SET autocommit = 0;
SET unique_checks = 0;
SET foreign_key_checks = 0;

LOAD DATA INFILE '/绝对路径/你的数据文件.txt'
INTO TABLE user_data
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 0 LINES; -- 如果文件第一行是表头，改成IGNORE 1 LINES

-- 恢复设置并提交
COMMIT;
SET autocommit = 1;
SET unique_checks = 1;
SET foreign_key_checks = 1;

注意事项 #

• 如果MySQL的secure_file_priv参数限制了文件路径，需要把TXT文件放到允许的目录，或者修改my.cnf（my.ini）中的该参数后重启MySQL。
• 若远程连接MySQL，需在命令中添加LOCAL关键字：LOAD DATA LOCAL INFILE，同时确保客户端和服务端开启了local_infile参数。

二、生成SQL文件（若必须转SQL格式） #

如果需要生成可执行的SQL文件，优先用命令行工具或轻量脚本，避免PHP逐行处理：

方法1：用Python脚本生成批量INSERT（稳定高效） #

Python的csv模块能正确解析带双引号的CSV格式，还能处理特殊字符转义：

import csv

# 替换为你的表名
TABLE_NAME = "user_data"

with open("data.txt", "r", encoding="utf-8") as infile, open("data.sql", "w", encoding="utf-8") as outfile:
    writer = outfile
    is_first = True
    writer.write(f"INSERT INTO {TABLE_NAME} VALUES ")
    
    for row in csv.reader(infile, quotechar='"', delimiter=','):
        # 转义SQL特殊字符，空值替换为NULL
        escaped_cols = []
        for col in row:
            if not col:
                escaped_cols.append("NULL")
            else:
                # 转义反斜杠和单引号
                escaped_col = col.replace("\\", "\\\\").replace("'", "\\'")
                escaped_cols.append(f"'{escaped_col}'")
        
        row_str = f"({','.join(escaped_cols)})"
        if is_first:
            writer.write(row_str)
            is_first = False
        else:
            writer.write(f",{row_str}")
    
    writer.write(";\n")

方法2：用awk快速生成（适合无特殊字符的场景） #

如果你的数据里没有单引号等需要转义的字符，可以用awk快速生成：

awk -F'"' '{printf "INSERT INTO user_data VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);\n",$2,$4,$6,$8,$10,$12,$14,$16,$18,$20,$22,$24,$26,$28,$30,$32,$34,$36,$38,$40,$42,$44,$46,$48,$50,$52,$54,$56,$58,$60,$62,$64,$66,$68,$70}' data.txt > data.sql

为什么PHP逐行导入慢？ #

PHP逐行处理时，每一行都要执行一次INSERT语句，涉及频繁的数据库连接、SQL解析、网络传输（如果是远程数据库），这些开销累加后，处理1100万行数据会变得异常缓慢。而原生LOAD DATA或批量INSERT能大幅减少这些开销。

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