PHP实现多行文本框内容批量插入MySQL数据库的需求

嘿，这个批量插入的问题我之前做航空相关项目时也碰到过——单条循环插入确实会把数据库和应用服务器的资源占满，尤其是上千条的时候。给你几个实用的解决方案，亲测能大幅降低资源消耗：

单条插入的最大问题是每一条都要发起一次数据库请求，建立连接、执行SQL、返回结果，几千次下来开销极大。优化的核心就是把多次请求合并成少数几次。

1. 使用批量INSERT语句（最常用方案） #

把所有需要插入的记录合并成一个INSERT语句，只和数据库交互一次。举个例子，最终生成的SQL会是这样：

INSERT INTO spottings (location, date, registration)
VALUES 
('Manchester Airport', '2018-05-10', 'G-TUIA'),
('Manchester Airport', '2018-05-10', 'G-TUIB'),
('Manchester Airport', '2018-05-10', 'G-TDCS'),
... -- 剩下的注册号
('Manchester Airport', '2018-05-10', 'CS-DSQ');

具体实现步骤： #

1. 把前端输入的多行注册号文本分割成数组（支持空格、换行符分割）
2. 对数组去重+过滤空值（可选，但能避免重复插入无效记录）
3. 用参数绑定的方式拼接批量INSERT语句（绝对不能直接拼接字符串，防止SQL注入）

比如用PHP+PDO的代码示例：

// 获取前端输入
$location = trim($_POST['LOCATION']);
$date = trim($_POST['DATE']);
$regInput = trim($_POST['REGISTRATION(S)']);

// 分割注册号：支持空格、换行、制表符分隔
$registrations = preg_split('/\s+/', $regInput);
// 去重+过滤空值
$registrations = array_filter(array_unique($registrations));

// 准备批量插入的占位符和参数
$placeholders = [];
$bindValues = [];
foreach ($registrations as $reg) {
    $placeholders[] = '(?, ?, ?)';
    $bindValues[] = $location;
    $bindValues[] = $date;
    $bindValues[] = $reg;
}

// 执行批量插入
try {
    $pdo->beginTransaction(); // 开启事务提升效率
    $sql = "INSERT INTO spottings (location, date, registration) VALUES " . implode(', ', $placeholders);
    $stmt = $pdo->prepare($sql);
    $stmt->execute($bindValues);
    $pdo->commit();
    echo "成功插入 " . $stmt->rowCount() . " 条记录";
} catch (PDOException $e) {
    $pdo->rollBack();
    echo "插入失败：" . $e->getMessage();
}

2. 分批次插入（避免SQL语句过长） #

如果一次性插入2000条，可能会超过数据库的max_allowed_packet限制（MySQL默认是4MB），导致SQL执行失败。这时候可以分批次插入，比如每500条为一批：

$batchSize = 500;
$totalRegs = count($registrations);
$successCount = 0;

try {
    $pdo->beginTransaction();
    for ($i = 0; $i < $totalRegs; $i += $batchSize) {
        $batchRegs = array_slice($registrations, $i, $batchSize);
        $placeholders = [];
        $bindValues = [];
        foreach ($batchRegs as $reg) {
            $placeholders[] = '(?, ?, ?)';
            $bindValues[] = $location;
            $bindValues[] = $date;
            $bindValues[] = $reg;
        }
        $sql = "INSERT INTO spottings (location, date, registration) VALUES " . implode(', ', $placeholders);
        $stmt = $pdo->prepare($sql);
        $stmt->execute($bindValues);
        $successCount += $stmt->rowCount();
    }
    $pdo->commit();
    echo "成功插入 " . $successCount . " 条记录";
} catch (PDOException $e) {
    $pdo->rollBack();
    echo "插入失败：" . $e->getMessage();
}

这样既减少了交互次数，又不会让单条SQL语句过大。

3. 使用数据库原生批量导入工具（超大数据量场景） #

如果偶尔需要插入上万条记录，用LOAD DATA INFILE（MySQL）或者COPY（PostgreSQL）这类原生工具速度会更快——它们直接读取文件导入，比执行SQL语句高效得多。

示例（MySQL）： #

1. 先生成CSV文件：

$csvFile = fopen('spottings_temp.csv', 'w');
// 写入表头（可选，导入时可以忽略）
fputcsv($csvFile, ['location', 'date', 'registration']);
foreach ($registrations as $reg) {
    fputcsv($csvFile, [$location, $date, $reg]);
}
fclose($csvFile);

2. 执行导入SQL：

LOAD DATA INFILE '/path/to/spottings_temp.csv'
INTO TABLE spottings
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS; -- 忽略表头行

注意：需要确保数据库用户有FILE权限，且文件路径是数据库服务器能访问到的路径（或者用LOAD DATA LOCAL INFILE读取客户端文件）。

额外优化建议 #

• 添加唯一索引：在spottings表的(location, date, registration)字段上建立唯一索引，这样即使重复插入也会自动忽略（可以用INSERT IGNORE或者ON DUPLICATE KEY UPDATE处理冲突）。
• 输入验证：插入前验证DATE格式是否合法、注册号是否符合规则（比如用正则匹配航空注册号格式），避免脏数据插入。
• 调整数据库配置：临时增大max_allowed_packet（MySQL）、开启事务批量提交，进一步提升插入速度。

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