php - 存储过程中的准备好的语句不起作用

我试图在存储过程中执行准备好的语句。
但我有点问题。
下面是存储过程：

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `getAllUserLunchReport`(IN `start_date` DATETIME, IN `end_date` DATETIME)
BEGIN

SET GLOBAL group_concat_max_len=4294967295;
SET @SQL = NULL;
SET @start_date = DATE(start_date);
SET @end_date = DATE(end_date);

    SELECT
    GROUP_CONCAT(DISTINCT
        CONCAT(
        'SUM(CASE WHEN date = "',DATE(issuedDateTime),'" THEN lunchStatus ELSE 0 END) AS `',DATE(issuedDateTime),'`'
        )
    ) INTO @SQL

        FROM `lunch_status`
        WHERE DATE(issuedDateTime) BETWEEN @start_date AND @end_date;

    SET @SQL
    = CONCAT('SELECT userId, ', @SQL, '
    FROM
    (
        SELECT userId, lunchStatus, DATE(issuedDateTime) as date
        FROM `lunch_status`
        WHERE DATE(issuedDateTime) BETWEEN "',@start_date,'" AND "',@end_date,'"
    ) as a
    GROUP BY userId;');

PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END$$
DELIMITER ;

这里有个错误：

PDOStatement: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'NULL' at line 1

这是控制器：

public function weeklyLunchReport($date1=null, $date2=null)
{
    $user = AclHelper::getCurrentUser();
    $default_userID = intval($user['user_id']);
    $defDate = new DateTime();
    $default_date = $defDate->format('Y-m-d');

    $date1 = (isset($date1) && $date1 != '') ? $date1 : $default_date;
    $date2 = (isset($date2) && $date2 != '') ? $date2 : $default_date;

    $records = $this->db->exec("CALL getAllUserLunchReport ('$date1','$date2')");
    return $records;
}

也：
下面是SQL变量的值：

SELECT userId,
SUM(CASE WHEN date = "2018-06-03" THEN lunchStatus ELSE 0 END) AS `2018-06-03`,
SUM(CASE WHEN date = "2018-06-04" THEN lunchStatus ELSE 0 END) AS `2018-06-04`,
SUM(CASE WHEN date = "2018-06-06" THEN lunchStatus ELSE 0 END) AS `2018-06-06`,
SUM(CASE WHEN date = "2018-06-07" THEN lunchStatus ELSE 0 END) AS `2018-06-07`,
SUM(CASE WHEN date = "2018-06-08" THEN lunchStatus ELSE 0 END) AS `2018-06-08`
   FROM
   (
       SELECT userId, lunchStatus, DATE(issuedDateTime) as date
       FROM `lunch_status`
       WHERE DATE(issuedDateTime) BETWEEN "2018-06-03" AND "2018-06-09"
   ) as a
   GROUP BY userId;

单独运行此查询字符串将给出预期的结果集。
这个问题似乎很好，但我不知道哪里出了问题。
非常感谢您的帮助。提前谢谢。

最佳答案

我在mysql客户机中尝试了您的过程，因此为了排除与PHP相关的任何可能性：

mysql> create table lunch_status (userId int, lunchstatus int, issueddatetime datetime);

mysql> DELIMITER $$
mysql> CREATE DEFINER=`root`@`localhost` PROCEDURE `getAllUserLunchReport`(IN `start_date` DATETIME, IN `end_date` DATETIME)
    -> BEGIN ...

mysql> CALL getAllUserLunchReport ('2018-06-17','2018-06-17');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1
mysql> select @SQL;
+------+
| @SQL |
+------+
| NULL |
+------+

但当我尝试插入一行属于日期范围时：

mysql> insert into lunch_status values (123, 1, now());
Query OK, 1 row affected (0.02 sec)

mysql> CALL getAllUserLunchReport ('2018-06-17','2018-06-17');
+--------+------------+
| userId | 2018-06-17 |
+--------+------------+
|    123 |          1 |
+--------+------------+

问题是，当您要搜索的日期范围内没有行时，GROUP_CONCAT()会为透视表列列表生成空值。
然后CONCAT（）将空值转换成完整的SELECT语句，但是CONCAT('SELECT ...', NULL, 'FROM ...')会产生空值，因为将任何字符串与空值连接都会产生空值。
您需要确保GROUP_CONCAT()默认为一些非空字符串：

SELECT
    COALESCE(GROUP_CONCAT(DISTINCT
        CONCAT(
        'SUM(CASE WHEN date = "',DATE(issuedDateTime),'" THEN lunchStatus ELSE 0 END) AS `',DATE(issuedDateTime),'`'
        )
    ), '0 as `NoMatchingRows`') INTO @SQL

调用过程仍然不会返回结果集，因为表中没有与您给定的日期范围匹配的日期。但至少在尝试准备NULL时不会出现语法错误。

关于php - 存储过程中的准备好的语句不起作用，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/50898735/