MySQL错误：1525.错误的DATETIME值：''

a2mppw5e 于 5个月前发布在 Mysql

关注(0)|答案(2)|浏览(58)

我的问题是--

SELECT ins.*, ic.* 
FROM insurance_data AS ins 
LEFT JOIN insurance_companies AS ic ON ins.provider = ic.id 
LEFT JOIN phone_numbers AS ph ON (ic.id = ph.foreign_id AND ph.type = 2) 
WHERE (ins.termination_date IS NULL OR ins.termination_date = "" OR 
       ins.termination_date = "0000-00-00") AND ins.provider IS NOT NULL 
 AND ins.provider > 0 
ORDER BY ins.date DESC LIMIT 1

字符串
我已经在MySQL中设置SQL模式为“ALLOW_INVALID_DATES，ONLY_FULL_GROUP_BY，ERROR_FOR_DIVISION_BY_ZERO，NO_ENGINE_SUBSTITUTION”.但我仍然得到错误错误代码：1525.错误的DATETIME值：“”.
我在Windows 10上使用MySQL版本8.0.35。我在MySQL工作台中运行查询。

mysql

来源：https://stackoverflow.com/questions/77659787/mysql-error-1525-incorrect-datetime-value

2条答案

按热度按时间

zhte4eai1#

我修改了查询，删除了对termination_date字段中空字符串（''）的检查，只关注NULL和大于'0000-00-00'的有效日期。希望这有助于修复错误>>

SELECT ins.*, ic.* 
FROM insurance_data AS ins 
LEFT JOIN insurance_companies AS ic ON ins.provider = ic.id 
LEFT JOIN phone_numbers AS ph ON (ic.id = ph.foreign_id AND ph.type = 2) 
WHERE (ins.termination_date IS NULL OR ins.termination_date > '0000-00-00') 
  AND ins.provider IS NOT NULL 
  AND ins.provider > 0 
ORDER BY ins.date DESC 
LIMIT 1;

字符串

赞(0）回复(0）举报 5个月前

q5lcpyga2#

您已禁用strict_transs_mode并启用ALLOW_INVALID_DATES，因此空字符串将作为'0000-00- 00 00：00：00'写入数据库，但这不适用于针对空字符串的日期检查
https://dbfiddle.uk/_b6rynV1

赞(0）回复(0）举报 5个月前