在PHP中获取INSERT INTO .. ON DUPLICATE KEY UPDATE语句的插入与更新行数

嗨，这个问题确实挺实用的——毕竟用INSERT ... ON DUPLICATE KEY UPDATE批量操作时，光知道总受影响行数有时候满足不了需求，我来分享几个可行的方案：

方法一：利用MySQL的返回行数特性计算 #

MySQL对INSERT ... ON DUPLICATE KEY UPDATE的返回规则是：

• 成功插入一条新记录时，受影响行数为 1
• 成功更新一条已有记录时（默认情况下，若更新后值和原值相同不算受影响，后面说解决办法），受影响行数为 2

基于这个规则，我们可以用公式计算插入和更新的数量：
假设你这次批量操作一共提交了total_records条待插入的记录（这个数值你在构造SQL时肯定知道，比如你的数据数组长度），执行语句后用$stmt->rowCount()得到总受影响行数total_affected，那么：

• 插入行数 = 2 * total_records - total_affected
• 更新行数 = total_affected - total_records

举个例子：你提交了5条记录，其中3条插入成功，2条更新成功，那么total_affected = 3*1 + 2*2 = 7，代入公式后得到插入行数3、更新行数2，完全匹配实际情况。

注意点： #

如果你的更新操作中，新值和原有值完全相同，MySQL默认不会把它算作“受影响”，这时候rowCount()会返回0而不是2，导致计算出错。解决办法有两个：

1. 执行语句前先设置SQL模式，强制MySQL将值相同的更新也计入受影响行数：

$pdo->exec("SET sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'");

2. 在ON DUPLICATE KEY UPDATE里强制修改一个一定会变化的字段，比如updated_at = CURRENT_TIMESTAMP，这样即使其他字段值不变，时间字段的更新也会让MySQL返回2。

方法二：直接查询MySQL的ROW_COUNT()函数 #

执行完INSERT ... ON DUPLICATE KEY UPDATE语句后，紧接着执行一条查询获取ROW_COUNT()的值，这个函数返回的就是最后一次DML操作的受影响行数，和PDO的rowCount()结果一致，之后同样用上面的公式计算即可：

// 准备并执行批量插入更新语句
$stmt = $pdo->prepare("INSERT INTO your_table (col1, col2) VALUES (?,?), (?,?) ON DUPLICATE KEY UPDATE col1=VALUES(col1), col2=VALUES(col2)");
// 绑定参数并执行（这里省略参数绑定的具体代码）
$stmt->execute();

// 获取受影响行数
$total_affected = $pdo->query("SELECT ROW_COUNT()")->fetchColumn();

// 计算插入和更新行数（total_records为本次提交的总记录数）
$inserted = 2 * $total_records - $total_affected;
$updated = $total_affected - $total_records;

方法三：自定义标记字段（适合精准追踪场景） #

如果你需要更明确的区分插入和更新记录，可以在表中新增一个is_updated字段（比如tinyint类型），插入时默认设为0，更新时设为1。操作完成后直接查询统计：

-- 先执行插入更新操作
INSERT INTO your_table (col1, col2, is_updated) VALUES (?, ?, 0), (?, ?, 0) ON DUPLICATE KEY UPDATE col1=VALUES(col1), col2=VALUES(col2), is_updated=1;

-- 统计本次操作的插入和更新行数
SELECT 
    SUM(CASE WHEN is_updated = 0 THEN 1 ELSE 0 END) AS inserted,
    SUM(CASE WHEN is_updated = 1 THEN 1 ELSE 0 END) AS updated
FROM your_table
WHERE created_at >= NOW() - INTERVAL 5 MINUTE; -- 可根据插入时间或特定标识筛选本次操作的记录

这个方法需要额外字段和后续查询，适合对追踪精度要求极高的场景。

备注：内容来源于stack exchange，提问作者Dliv