如果日期不正确，mysql 会插入一个空日期 [英] mysql inserts an empty date if the date isn't correct

问题描述

好的，

所以我尝试将日期2015-02-29"(不存在，因为 2015 年不是闰年)插入 mysql 日期字段.它没有出现各种错误，而是简单地插入了0000-00-00"作为日期.

So I tried inserting the date '2015-02-29' (which doesn't exist, as 2015 is not a leap year) into a mysql date field. Instead of an error of sorts, it simply inserted '0000-00-00' as the date.

为什么会发生?我怎样才能防止或抓住这个?我必须如何配置日期列?

Why does it occur? How can I prevent or catch this? How do I have to configure the date column?

谢谢

数据已上传为 CSV 文件并使用 LOAD DATA INFILE 插入.

The data was uploaded as a CSV file and inserted using LOAD DATA INFILE.

然而，我们可以假设以下查询:

We can assume the following query however:

INSERT INTO TableName ('date') VALUES ('2015-02-09')

推荐答案

您可以使用 sql_mode 来改变 MySQL 处理无效日期的行为.

You can play with the sql_mode to change the behaviour how MySQL handles the invalid date.

根据MySQL手册，当您插入不正确的日期值时会发生这种情况:

According to the MySQL manual, this happens when you insert an incorrect date value:

默认情况下，当 MySQL 遇到一个日期或时间类型的值时超出范围或对该类型无效，它将转换值为该类型的零"值.例外是超出范围的 TIME 值被剪裁到适当的端点时间范围.

By default, when MySQL encounters a value for a date or time type that is out of range or otherwise invalid for the type, it converts the value to the "zero" value for that type. The exception is that out-of-range TIME values are clipped to the appropriate endpoint of the TIME range.

因此插入了一个零值，您实际上会收到一个警告.您可以使用 show warnings 命令看到这一点.

So a zero value is inserted and you actually get a warning about that. You can see that with the show warnings command.

insert into TableName (`date`) values ('2015-02-29');
-- 1 row(s) affected
show warnings
-- Warning  1264    Out of range value for column 'date' at row 1

如果需要，您可以更改此行为以存储无效值(MySQL 手册):

You can change this behaviour to store the invalid value, if you need so (MySQL manual):

通过将SQL模式设置为合适的值，可以指定更多确切地说，您希望 MySQL 支持什么样的日期.(见节5.1.7，服务器 SQL 模式".)通过启用 ALLOW_INVALID_DATES SQL 模式，您可以让 MySQL 接受某些日期，例如2009-11-31".当您想存储一个可能错误"的值时，这很有用用户已在数据库中指定(例如，在 Web 表单中)以备将来处理.

By setting the SQL mode to the appropriate value, you can specify more exactly what kind of dates you want MySQL to support. (See Section 5.1.7, "Server SQL Modes".) You can get MySQL to accept certain dates, such as '2009-11-31', by enabling the ALLOW_INVALID_DATES SQL mode. This is useful when you want to store a "possibly wrong" value which the user has specified (for example, in a web form) in the database for future processing.

例如，以下应该有效:

set @@sql_mode='allow_invalid_dates';
insert into TableName (`date`) values ('2015-02-29');
-- 1 row(s) affected

或者，你也可以改变加载数据的行为 或 insert 报错:

Or, you can also change the behaviour of load data or insert to report the error:

对空或不正确的字段值的处理与仅处理不同描述 SQL 模式是否设置为限制值.例如，如果 sql_mode='TRADITIONAL，则转换为空值或这样的值因为数字列的x"会导致错误，而不是转换为 0.(使用 LOCAL 时，会出现警告而不是错误，即使使用限制性的 sql_mode 值，因为服务器无法停止操作中途传输文件.)

Treatment of empty or incorrect field values differs from that just described if the SQL mode is set to a restrictive value. For example, if sql_mode='TRADITIONAL, conversion of an empty value or a value such as 'x' for a numeric column results in an error, not conversion to 0. (With LOCAL, warnings occur rather than errors, even with a restrictive sql_mode value, because the server has no way to stop transmission of the file in the middle of the operation.)

示例:

set @@sql_mode='traditional';
insert into TableName (`date`) values ('2015-02-29');
Error Code: 1292. Incorrect date value: '2015-02-29' for column 'date' at row 1

这篇关于如果日期不正确，mysql 会插入一个空日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！