MySQL报错1525：DATE值''无效，未发现对应值求解释

报错信息 #

General error: 1525 Incorrect DATE value: ''

问题SQL语句 #

select count(*) as aggregate from (
    (select `payments`.`id`, null AS user_id, `order_id`, `payment_via_id` as `via_id`, null AS type_id, null AS product_type_id, null AS employee_id, `sponsorship_client_id`, `value`, `date`, `orders`.`code` as `description`, `note`, null AS employee_name, null AS receipt_path, `payments`.`confirmed_at`, `payments`.`is_confirmed`, `payments`.`created_at`, `payments`.`updated_at` 
    from `payments` 
    inner join `orders` on `payments`.`order_id` = `orders`.`id` where `payments`.`date` is not null) 
    union all 
    (select `id`, `user_id`, null AS order_id, `expense_via_id` as `via_id`, `expense_type_id` as `type_id`, `product_type_id`, `employee_id`, null AS sponsorship_client_id, `value`, `date`, `description`, null AS note, `employee_name`, `receipt_path`, `confirmed_at`, `is_confirmed`, `created_at`, `updated_at` from `expenses`)
    ) AS merged where `user_id` = 3 or `user_id` is null and date(`created_at`) = 2022-02-01

错误原因分析 #

• 日期条件写法错误：SQL最后一行的date(created_at) = 2022-02-01是核心问题。这里的2022-02-01没有加单引号，MySQL会将其视为算术运算：2022 - 2 - 1 = 2019，随后尝试把数值2019转换成DATE类型。这种转换不符合DATE格式规范，进而触发了Incorrect DATE value: ''的错误提示（MySQL处理无效日期转换时，常会抛出这类空字符串相关的错误）。

• 潜在的数据/类型兼容问题：虽然你认为没有使用空字符串作为DATE值，但可能payments或expenses表的date字段中实际存储了空字符串数据；或者UNION ALL合并两个子查询后，date字段类型被隐式转换，导致解析异常。不过这个可能性远低于第一种。

修复方案 #

1. 修正日期条件写法：给日期字符串添加单引号，确保MySQL将其识别为日期格式：

date(`created_at`) = '2022-02-01'

2. 优化查询性能（可选）：如果created_at字段有索引，建议用范围查询替代date()函数，这样可以利用索引提升性能：

`created_at` >= '2022-02-01' AND `created_at` < '2022-02-02'

内容的提问来源于stack exchange，提问作者João