PDO使用占位符绑定查询字符串时触发Truncated incorrect DOUBLE value错误

PDO预处理语句的占位符仅支持绑定数据值，不能用来绑定SQL语法片段（比如HAVING后的条件逻辑）。

当你把包含FIND_IN_SET的完整条件字符串通过:qry_str占位符绑定后，数据库会将该字符串视为普通文本值，而非可执行的SQL逻辑。此时HAVING :qry_str相当于让数据库把attributes的计算结果（布尔值）与长字符串做比较，数据库尝试将字符串转换为数值类型时，就触发了Truncated incorrect DOUBLE value错误——虽然提示里提到了datetime，但本质是类型不匹配导致的转换失败。

方案1：安全拼接SQL片段（需做注入防护） #

如果必须使用字符串拼接，要确保生成条件的过程安全，比如对group_id和value_id做整数转换，避免SQL注入：

// 假设筛选参数来自用户输入，先做安全处理
$filterPairs = [
    ['group' => 6, 'value' => 47],
    ['group' => 4, 'value' => 176],
    ['group' => 9, 'value' => 218],
];
$conditions = [];
foreach ($filterPairs as $pair) {
    // 强制转成整数，避免注入风险
    $groupId = intval($pair['group']);
    $valueId = intval($pair['value']);
    $conditions[] = "FIND_IN_SET('{$groupId}-{$valueId}', attributes)";
}
$qry_str = implode("\n    AND ", $conditions);

// 正常拼接SQL并执行
$temp_sql = <<<"TEMP"
CREATE TEMPORARY TABLE IF NOT EXISTS `temp_attr` (
    SELECT product_id, GROUP_CONCAT(CONCAT(group_id, '-', IF (custom_value != '', custom_value, value_id)) SEPARATOR ',') AS attributes
    FROM `products_attributes`
    GROUP BY `product_id`
    HAVING $qry_str
);
TEMP;
$pdo->query($temp_sql);

方案2：拆分条件，单独绑定每个参数 #

把每个FIND_IN_SET的目标值单独用占位符绑定，既保留预处理的安全性，又能正确执行SQL逻辑：

$filterValues = ['6-47', '4-176', '9-218'];
$havingConditions = [];
// 生成带占位符的条件
foreach ($filterValues as $index => $val) {
    $havingConditions[] = "FIND_IN_SET(:filter{$index}, attributes)";
}
$havingClause = implode("\n    AND ", $havingConditions);

$temp_sql = <<<"TEMP"
CREATE TEMPORARY TABLE IF NOT EXISTS `temp_attr` (
    SELECT product_id, GROUP_CONCAT(CONCAT(group_id, '-', IF (custom_value != '', custom_value, value_id)) SEPARATOR ',') AS attributes
    FROM `products_attributes`
    GROUP BY `product_id`
    HAVING $havingClause
);
TEMP;

// 预处理并绑定每个参数
$sth = $pdo->prepare($temp_sql);
foreach ($filterValues as $index => $val) {
    $sth->bindValue(":filter{$index}", $val, PDO::PARAM_STR);
}
$sth->execute();

方案3：重构查询，去掉临时表（更优） #

直接将属性筛选逻辑整合到主查询中，无需临时表，代码更简洁高效：

$sql = "SELECT
            m.recommended_price   AS msrp,
            m.purchase_price      AS cost,
            pp.USD                AS regular_price,
            pc.USD                AS special_price,
            pc.start_date         AS start_date,
            pc.end_date           AS end_date,
            pl.permalink          AS permalink,
            pi.name               AS description,
            m.sku                 AS sku,
            m.default_category_id AS cat,
            m.id                  AS prod_id
        FROM `products`                    AS m
        LEFT JOIN `products_prices`        AS pp  ON m.id = pp.product_id
        LEFT JOIN `products_campaigns`     AS pc  ON m.id = pc.product_id
        LEFT JOIN `permalinks`             AS pl  ON (m.id = pl.resource_id AND pl.resource = 'product')
        LEFT JOIN `products_info`          AS pi  ON (m.id = pi.product_id)
        LEFT JOIN `products_to_categories` AS ptc ON (m.id = ptc.product_id)
        INNER JOIN (
            SELECT product_id
            FROM `products_attributes`
            GROUP BY `product_id`
            HAVING FIND_IN_SET(:filter1, GROUP_CONCAT(CONCAT(group_id, '-', IF (custom_value != '', custom_value, value_id)) SEPARATOR ','))
                AND FIND_IN_SET(:filter2, GROUP_CONCAT(CONCAT(group_id, '-', IF (custom_value != '', custom_value, value_id)) SEPARATOR ','))
                AND FIND_IN_SET(:filter3, GROUP_CONCAT(CONCAT(group_id, '-', IF (custom_value != '', custom_value, value_id)) SEPARATOR ','))
        ) AS pa ON m.id = pa.product_id
        WHERE ptc.category_id = :cat
        AND m.status = 1
        LIMIT 55;
";

$sth = $pdo->prepare($sql);
$sth->bindValue(':filter1', '6-47', PDO::PARAM_STR);
$sth->bindValue(':filter2', '4-176', PDO::PARAM_STR);
$sth->bindValue(':filter3', '9-218', PDO::PARAM_STR);
$sth->bindValue(':cat', 100, PDO::PARAM_INT);
$sth->execute();
$data = $sth->fetchAll();

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