含不同WHERE子句的UNION ALL双SELECT如何正确绑定参数？

将两个SELECT查询通过UNION ALL组合，两个查询使用不同的WHERE子句条件，尝试绑定参数后执行返回空结果。代码如下：

$curdate = date('Y-m-d H:i:s');
$year = "2024";
$month = "12";
$day = "03";
$paystat = "Paid";
$paymtstat = "Pending";

$query = "(SELECT ukimID, screeningdate, paymentstatus, regdeadlinetime
            FROM ukmedscreening 
            WHERE YEAR(screeningdate)=? 
            AND MONTH(screeningdate)=? 
            AND DAY(screeningdate)=? 
            AND regdeadlinetime>?) 
    UNION ALL
        (SELECT ukimID, screeningdate, paymentstatus, regdeadlinetime 
        FROM ukmedscreening 
        WHERE YEAR(screeningdate)=? 
        AND MONTH(screeningdate)=? 
        AND DAY(screeningdate)=? 
        AND regdeadlinetime<? 
        AND paymentstatus=?)";

$stmt = $connQlife->prepare($query);
$stmt->bind_param('ssss', $year, $month, $day, $curdate); // Param 1
$stmt->bind_param('sssss', $year, $month, $day, $curdate, $paystat); // Param 2
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($ukimID, $screeningdate, $paymentstatus, $regdeadlinetime);
$stmt->fetch();
$totalnumrows = $stmt->num_rows;
$stmt->close();

疑问：UNION/UNION ALL要求查询返回的列数及数据类型一致，但不确定WHERE条件的预编译语句参数数量是否必须相同。目前调用了两次bind_param却无法得到正确结果，想咨询是否可以仅使用一次bind_param？若可以，当WHERE条件参数数量不同时该如何操作？

核心问题 #

不能多次调用bind_param——第二次调用会完全覆盖第一次的参数绑定，导致实际绑定的参数数量与SQL中?的总数不匹配，最终执行后无结果返回。

正确做法 #

预编译语句中的所有?是按全局顺序计数的，不管属于哪个SELECT子句。你的SQL里总共有9个?（第一个SELECT有4个，第二个SELECT有5个），因此只需要一次调用bind_param，按?出现的顺序传入所有参数即可。

修正后的代码 #

$curdate = date('Y-m-d H:i:s');
$year = "2024";
$month = "12";
$day = "03";
$paystat = "Paid";
$paymtstat = "Pending";

$query = "(SELECT ukimID, screeningdate, paymentstatus, regdeadlinetime
            FROM ukmedscreening 
            WHERE YEAR(screeningdate)=? 
            AND MONTH(screeningdate)=? 
            AND DAY(screeningdate)=? 
            AND regdeadlinetime>?) 
    UNION ALL
        (SELECT ukimID, screeningdate, paymentstatus, regdeadlinetime 
        FROM ukmedscreening 
        WHERE YEAR(screeningdate)=? 
        AND MONTH(screeningdate)=? 
        AND DAY(screeningdate)=? 
        AND regdeadlinetime<? 
        AND paymentstatus=?)";

$stmt = $connQlife->prepare($query);
// 一次绑定所有9个参数，类型字符串对应9个字符串类型
$stmt->bind_param('sssssssss', $year, $month, $day, $curdate, $year, $month, $day, $curdate, $paystat);
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($ukimID, $screeningdate, $paymentstatus, $regdeadlinetime);

// 循环获取所有结果（原代码仅fetch一次，只能拿到第一条数据）
while ($stmt->fetch()) {
    // 处理每一行数据，比如存入数组或直接输出
    // 示例：echo "$ukimID | $screeningdate\n";
}
$totalnumrows = $stmt->num_rows;
$stmt->close();

额外优化建议 #

避免在WHERE条件中使用YEAR()/MONTH()/DAY()函数，这类函数会导致screeningdate字段的索引失效，影响查询性能。可以直接构造日期范围进行比较：

WHERE screeningdate >= ? AND screeningdate < ?

对应的参数可以是$dateStart = "$year-$month-$day 00:00:00";和$dateEnd = "$year-$month-".($day+1)." 00:00:00";，这样能利用字段索引提升查询效率。

内容的提问来源于stack exchange，提问作者Agbogidi Michael