PHP-MySQL：如何基于另一表字段值批量插入指定行数数据

我需要实现这样的需求：当Input表中某条记录的Ticket_Piece字段值为n时，向Output表插入n条对应的数据，其中Ticket_Number字段格式为[Input表的ID]-1到[Input表的ID]-n。以下是具体的表结构、示例输入和预期结果，求PHP+MySQL的实现方案。

表结构 #

CREATE TABLE Input (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(128),
    Ticket_Piece INT
);

CREATE TABLE Output (
    Ticket_ID INT AUTO_INCREMENT PRIMARY KEY,
    Transaction_ID INT,
    Ticket_Number VARCHAR(23) UNIQUE,
    FOREIGN KEY (Transaction_ID)
        REFERENCES Input (ID)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);

示例输入 #

INSERT INTO Input (ID, Name, Ticket_Piece)
VALUES
(1, 'A', 2),
(2, 'B', 1),
(3, 'C', 3);

预期结果 #

方法一：纯MySQL实现（无需PHP） #

利用递归CTE（公共表表达式）生成连续数字序列，关联Input表筛选出需要插入的行，一次性完成批量插入。

WITH RECURSIVE number_seq AS (
    SELECT 1 AS num
    UNION ALL
    SELECT num + 1
    FROM number_seq
    WHERE num < (SELECT MAX(Ticket_Piece) FROM Input)
)
INSERT INTO Output (Transaction_ID, Ticket_Number)
SELECT 
    i.ID,
    CONCAT(i.ID, '-', ns.num)
FROM Input i
JOIN number_seq ns ON ns.num <= i.Ticket_Piece
ORDER BY i.ID, ns.num;

说明：

1. 递归CTEnumber_seq生成从1到Input表中最大Ticket_Piece值的连续数字序列；
2. 将Input表和数字序列关联，只保留数字小于等于对应行Ticket_Piece的组合；
3. 拼接Ticket_Number并插入到Output表。

方法二：PHP + MySQL实现 #

如果需要用PHP控制逻辑，可以先查询Input表的所有记录，然后循环生成批量插入的数据。

示例代码（PDO实现） #

<?php
// 数据库连接配置
$host = 'localhost';
$dbname = 'your_database';
$username = 'your_username';
$password = 'your_password';

try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8mb4", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // 查询Input表所有记录
    $stmt = $pdo->query("SELECT ID, Ticket_Piece FROM Input");
    $inputRecords = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // 准备批量插入的SQL语句
    $insertSql = "INSERT INTO Output (Transaction_ID, Ticket_Number) VALUES (?, ?)";
    $insertStmt = $pdo->prepare($insertSql);

    // 开启事务提高性能
    $pdo->beginTransaction();

    foreach ($inputRecords as $record) {
        $transactionId = $record['ID'];
        $pieceCount = $record['Ticket_Piece'];

        // 循环生成对应数量的票号
        for ($i = 1; $i <= $pieceCount; $i++) {
            $ticketNumber = "{$transactionId}-{$i}";
            $insertStmt->execute([$transactionId, $ticketNumber]);
        }
    }

    $pdo->commit();
    echo "批量插入完成";
} catch (PDOException $e) {
    $pdo->rollBack();
    die("错误: " . $e->getMessage());
}
?>

优化建议：
如果Input表数据量较大，建议使用批量插入（一次性拼接多个VALUES）减少数据库交互次数：

// 替换循环内的execute为收集数据后批量插入
$insertValues = [];
foreach ($inputRecords as $record) {
    $transactionId = $record['ID'];
    $pieceCount = $record['Ticket_Piece'];
    for ($i = 1; $i <= $pieceCount; $i++) {
        $ticketNumber = "{$transactionId}-{$i}";
        $insertValues[] = "({$transactionId}, '{$ticketNumber}')";
    }
}

if (!empty($insertValues)) {
    $batchSql = "INSERT INTO Output (Transaction_ID, Ticket_Number) VALUES " . implode(', ', $insertValues);
    $pdo->exec($batchSql);
}

内容的提问来源于stack exchange，提问作者Benjamin Joseph Bonnevie