MySQL中CASE语句内字符串日期转换后比较结果异常问题

核心错误点 #

1. 日期比较值写法错误：你写的01/01/2023会被MySQL当作除法运算（1÷1÷2023≈0.000494），而非日期值，导致比较逻辑完全失效。
2. 日期转换可能失败：如果STR_TO_DATE(regionexpires,'%d.%m.%Y')的格式符与regionexpires字段实际存储的字符串格式不匹配，会返回NULL。而NULL与任何值比较都会返回FALSE，这就是所有结果显示为No的直接原因。

解决步骤 #

1. 先确认字段实际格式

执行以下语句查看regionexpires的真实存储格式（比如是dd.mm.yyyy还是mm.dd.yyyy）：

SELECT regionexpires FROM roster LIMIT 10;

2. 修正SQL语句

假设regionexpires确实是dd.mm.yyyy格式，修正后的代码如下：

SELECT 
    CONCAT(suffix, " ", lname, " ", fname, " ", name) AS "Name",
    id AS "Member ID",
    CONCAT(address1, " ", address2, " ") AS "Address Line 1", 
    CONCAT(city, " ", state, " - ", zip) AS "Address Line 2", 
    CASE
        -- 使用MySQL标准日期字符串，自动识别为日期类型
        WHEN STR_TO_DATE(regionexpires, '%d.%m.%Y') > '2023-01-01' THEN 'Yes'
        ELSE 'No'
    END AS "Subscriber?"
FROM roster
LIMIT 100000;

3. 处理脏数据（可选）

如果存在格式不匹配的脏数据（转换后返回NULL），可以先判断非空再比较，避免误判：

CASE
    WHEN STR_TO_DATE(regionexpires, '%d.%m.%Y') IS NOT NULL 
         AND STR_TO_DATE(regionexpires, '%d.%m.%Y') > '2023-01-01' THEN 'Yes'
    ELSE 'No'
END AS "Subscriber?"

额外建议 #

• 尽量避免将日期存储为字符串类型，后续可考虑将regionexpires字段修改为DATE类型，从根源上避免这类问题。
• 使用STR_TO_DATE时，务必确保格式符与存储字符串完全匹配（比如%d代表两位日，%m代表两位月，%Y代表四位年）。

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