MySQL CASE子句使用拼接转换的日期时间列别名查询返回0行问题

问题根因 #

MySQL查询的执行逻辑中，SELECT 子句的优先级低于同层级的条件判断逻辑，你在 SELECT 中定义的别名 FULLDATETIME 无法被同层级的 CASE 子句识别，导致条件永远不成立，最终返回0行结果。

修复方案 #

方案1：直接复用转换逻辑（最简单） #

直接把日期时间拼接转换的逻辑重复写在CASE的判断条件中即可：

$sql = "SELECT TR.PK_COL_REM_ID, TR.COL_REM_TITLE,TR.COL_REM_DESCRIPTION, TR.COL_REM_DATE, 
        TR.COL_REM_TIME,TR.COL_REM_STATUS, MA.COL_ALARM_SET_DATE,MA.COL_ALARM_SET_TIME, 
        CAST(CONCAT(TR.COL_REM_DATE,' ',TR.COL_REM_TIME) AS DATETIME) AS FULLDATETIME,
        CASE WHEN MA.COL_ALARM_USER = '".$empId."' AND CAST(CONCAT(TR.COL_REM_DATE,' ',TR.COL_REM_TIME) AS DATETIME) >= CURRENT_TIMESTAMP() THEN 1 
        ELSE 0 END AS ALARMSTATUS FROM `TBL_REMINDERS` AS TR LEFT JOIN 
        TBL_MY_ALARM AS MA ON TR.PK_COL_REM_ID = MA.FK_COL_REM_ID ORDER BY TR.COL_REM_DATE DESC";

方案2：子查询封装（易维护） #

先通过子查询提前计算出FULLDATETIME，外层查询再使用别名做条件判断，避免重复写转换逻辑：

$sql = "SELECT *,
        CASE WHEN COL_ALARM_USER = '".$empId."' AND FULLDATETIME >= CURRENT_TIMESTAMP() THEN 1 
        ELSE 0 END AS ALARMSTATUS 
        FROM (
            SELECT TR.PK_COL_REM_ID, TR.COL_REM_TITLE,TR.COL_REM_DESCRIPTION, TR.COL_REM_DATE, 
            TR.COL_REM_TIME,TR.COL_REM_STATUS, MA.COL_ALARM_SET_DATE,MA.COL_ALARM_SET_TIME,MA.COL_ALARM_USER,
            CAST(CONCAT(TR.COL_REM_DATE,' ',TR.COL_REM_TIME) AS DATETIME) AS FULLDATETIME
            FROM `TBL_REMINDERS` AS TR LEFT JOIN 
            TBL_MY_ALARM AS MA ON TR.PK_COL_REM_ID = MA.FK_COL_REM_ID
        ) AS temp
        ORDER BY COL_REM_DATE DESC";

注意事项 #

如果COL_REM_DATE或COL_REM_TIME存在空值，CONCAT拼接结果会为空，转换DATETIME会失败，可以用IFNULL给字段加默认值避免该问题，示例：CONCAT(IFNULL(TR.COL_REM_DATE,'1970-01-01'),' ',IFNULL(TR.COL_REM_TIME,'00:00:00'))。

内容的提问来源于stack exchange，提问作者Leigh Barro