PrestaShop 1.7中Db::getInstance()->Affected_Rows()返回值异常求助

问题现象 #

• 从PrestaShop 1.6迁移到1.7后，某条UPDATE语句执行时，Db::getInstance()->Affected_Rows()返回大于0的数值（如94），但直接在MySQL中执行该SQL，实际受影响行数为0。
• 仅该语句和另一语句存在此问题，尝试重新连接数据库无效。
• 打印DbPDO对象发现last_query字段并非目标UPDATE语句，而是一条查询店铺URL的SQL：SELECT domain, domain_ssl FROM ps17_shop_url WHERE main = 1 AND id_shop = 1 LIMIT 1。

涉事代码 #

<?php
include(dirname(__FILE__).'/config/config.inc.php');
//products without imgs
$sql9="
UPDATE `"._DB_PREFIX_."product_shop` ps 
SET ps.`dm_google_active` = '0' 
WHERE ps.id_product IN (         
SELECT * FROM (Select DISTINCT(ps.id_product) 
from `"._DB_PREFIX_."product_shop` ps
LEFT JOIN `"._DB_PREFIX_."image_shop` pi 
ON pi.id_product=ps.id_product
WHERE pi.id_product IS NULL) AS wihoutImg
)";
    
if (!Db::getInstance()->execute($sql9)) {
    die('error! removing products without imgs from XML google');
} else {
     echo "Cron [removing products without imgs from XML google] done. <br> -> Rows Afected: ".Db::getInstance()->Affected_Rows()."</br>";
 }

执行输出 #

Cron [removing products without imgs from XML google] done. -> Rows Affected: 94

DbPDO对象打印信息 #

DbPDO Object ( [link:protected] => PDO Object ( ) [result:protected] => PDOStatement Object ( 
[queryString] => UPDATE `ps17_product_shop` ps SET ps.`dm_google_active` = '0' WHERE ps.id_product IN ( SELECT * FROM (Select DISTINCT(ps.id_product) from `ps17_product_shop` ps LEFT JOIN `ps17_image_shop` pi ON pi.id_product=ps.id_product WHERE pi.id_product IS NULL) AS wihoutImg ) ) 
[server:protected] => *** 
[user:protected] => *** 
[password:protected] => *** 
[database:protected] => *** 
[is_cache_enabled:protected] => 
[last_query:protected] => SELECT domain, domain_ssl FROM ps17_shop_url WHERE main = 1 AND id_shop = 1 LIMIT 1 
[last_query_hash:protected] => [last_cached:protected] => ) 

原因分析 #

1. PrestaShop 1.7启动时会自动执行一些内部查询（比如获取店铺URL配置），这些查询会覆盖全局Db实例的last_query记录，同时Affected_Rows()方法依赖于最后一次执行的查询，导致返回的不是目标UPDATE语句的结果。
2. 原UPDATE语句没有过滤已为0的记录，即使MySQL实际未修改任何行，execute()仍会返回成功，但后续内部查询的干扰让Affected_Rows()返回了错误数值。

解决步骤 #

1. 立即获取目标语句的受影响行数 #

修改代码，在执行UPDATE后直接从语句对象获取受影响行数，避免被后续内部查询覆盖：

<?php
include(dirname(__FILE__).'/config/config.inc.php');
//products without imgs
$sql9="
UPDATE `"._DB_PREFIX_."product_shop` ps 
SET ps.`dm_google_active` = '0' 
WHERE ps.id_product IN (         
SELECT * FROM (Select DISTINCT(ps.id_product) 
from `"._DB_PREFIX_."product_shop` ps
LEFT JOIN `"._DB_PREFIX_."image_shop` pi 
ON pi.id_product=ps.id_product
WHERE pi.id_product IS NULL) AS wihoutImg
) AND ps.dm_google_active != '0'"; // 只修改需要变更的记录，让行数统计更准确
       
$db = Db::getInstance();
$stmt = $db->execute($sql9);
if (!$stmt) {
    die('error! removing products without imgs from XML google');
} else {
    // 从执行后的PDO语句对象获取真实受影响行数
    $affectedRows = $db->result->rowCount();
    echo "Cron [removing products without imgs from XML google] done. <br> -> Rows Affected: ".$affectedRows."</br>";
}

2. 使用独立Db连接（可选） #

如果是Cron任务，可创建独立Db实例，避免和系统内部查询共用全局实例：

// 替代Db::getInstance()，创建独立连接
$db = new DbPDO(_DB_SERVER_, _DB_USER_, _DB_PASSWD_, _DB_NAME_);
$stmt = $db->execute($sql9);
$affectedRows = $db->result->rowCount();

3. 验证SQL逻辑 #

直接在MySQL中执行带AND ps.dm_google_active != '0'的SQL，确认实际受影响行数，确保和代码返回值一致。

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