MySQL修复无效的日期 [英] mysql fix invalid dates

问题描述

我的桌子上有一些无效的日期.我听说我可以禁用严格模式或使用不起作用的sql_mode ALLOW_INVALID_DATES(已通过选择@@ global.sql_mode确认并重新启动服务器).

I have some invalid dates in my table. I've heard I can disable strict mode or use sql_mode ALLOW_INVALID_DATES which doesn't work (have confirmed with select @@global.sql_mode and restarted server).

我的问题是，我不能解决这些无效"的日期吗?我将无法在每台服务器上禁用严格模式.

My question is, can I not fix these 'invalid' dates? I'm not going to be able to disable strict mode on every server.

select * from table
>> Mysql2::Error: Invalid date: 1900-00-00

mysql --version
mysql  Ver 14.14 Distrib 5.1.61, for debian-linux-gnu (i686) using readline 6.1

mysql> select @@global.sql_mode;
+---------------------+
| @@global.sql_mode   |
+---------------------+
| ALLOW_INVALID_DATES |

推荐答案

看起来您的错误消息来自您的MySQL客户端，而不是服务器.因此，设置服务器严格模式不会帮助您在此客户端上显示这些日期.

It looks like your error message is coming from your MySQL client, not the server. So, setting server strict mode isn't going to help you display these dates with this client.

似乎您的数据中有一些2012-09-31或2013-02-29样式日期.它们的格式正确，但否则是错误的.在MySQL的5.0.2之前的版本中，这些没有正确地捕获到您的数据中.现在，设置为ALLOW_INVALID_DATES的服务器不再使用它们，而是将它们转换为"0000-00-00".客户正在加紧他们.

It seems like you have some 2012-09-31 or 2013-02-29 style dates in your data. They're correctly formatted but otherwise are wrong. In pre 5.0.2 versions of MySQL these didn't get caught correctly going into your data. Now, your server, set to ALLOW_INVALID_DATES isn't gagging on them, but instead is converting them to '0000-00-00'. And the client is gacking on them.

您要清理的第一步是确定有问题的行.您可以尝试一下.

Your first step to cleaning this up is to identify the offending rows. You could try this.

首先，打开ALLOW_INVALID_DATES

然后，运行此查询以在表中四处查看.不要使用SELECT *

Then, run this query to look around in your table. Don't use SELECT *

  SELECT col,col,col,DATE_FORMAT(datecol,'%Y-%m-%d') 
    FROM mytable
   ORDER BY DATE_FORMAT(datecol,'%Y-%m-%d') 

尝试从结果集中找出哪些日期是垃圾.它们可能会在此select语句中排在第一位，但是您将不得不仔细考虑一下才能找到它们.

Try to figure out from the result set which dates are garbage. They may be placed first in this select statement, but you're going to have to muck around a little bit to find them.

接下来，弄清楚如何修复它们.删除行?将该日期更改为1941-12-07(以臭名昭著的日期)?我们无法告诉您您需要在这里做什么.

Next, figure out how you want to fix them. Delete the rows? Change the date to 1941-12-07 (the date that lives in infamy)? We can't tell you what you need to do here.

然后修复它们.如果只有一两个，请一一修复.

Then, fix them. If there are only one or two, fix them one by one.

  UPDATE mytable
     SET datecol='whatever replacement date'
   WHERE id='the id of the offending row.'

或

  DELETE FROM mytable
        WHERE id='the id of the offending row.'

如果有成千上万个，则可以使用以下方法对其进行批量修复. 但是，请首先在测试服务器上非常仔细地解决问题，然后再执行此操作.如果您输入有误，则会破坏您的桌子.

If there are thousands of them, you could bulk fix them with something like this. But don't do this without first working through the problem very carefully on a test server. If you make a mistake you will trash your table.

  UPDATE mytable
     SET datecol='whatever replacement date'
   WHERE '0000-00-00' = DATE_FORMAT(datecol,'%Y-%m-%d')

完成问题纠正后，请返回并执行SELECT *，以确保所有问题都得到了解决.

After you've finished correcting your problems, go back and do your SELECT *, to make sure you got them all.

然后禁用ALLOW_INVALID_DATES，再也不会重新启用它.

Then disable ALLOW_INVALID_DATES and never re-enable it again.

那应该清理烂摊子.请注意，现实世界中的数据总会包含一些不完美的行.

That should clean up the mess. Notice that real-world data always some rows that aren't perfect in it.

这篇关于MySQL修复无效的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！