mysql“日期时间非空默认值”1970-01-01“ ”变为 0000-00-00 00:00:00
我创建了一个列,
`date_start` datetime NOT NULL DEFAULT '1970-01-01'
但是当我使用 LOAD DATA 命令从 CSV 文件上传数据且 date_start
为空白条目时,保存的值是 0000-00-00 00:00:00 ?
I have a column created as
`date_start` datetime NOT NULL DEFAULT '1970-01-01'
However when I upload data from a CSV file with the LOAD DATA command with a blank entry for date_start
the value saved is 0000-00-00 00:00:00 ?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(5)
NULL
和“空白条目”在 MySql 中并不总是被同等对待。对于datetime
数据类型,空白条目会自动转换为 0000-00-00 00:00:00。您可以将其视为一种类型转换形式,其中空白被类型转换为零。在您的数据文件中,尝试使用
DEFAULT
关键字替换所有空白日期。NULL
and 'a blank entry' are not always treated the same in MySql. In the case of thedatetime
data type, blank entries are automatically converted to 0000-00-00 00:00:00. You can think of this as a form of typecasting, where a blank gets typecast to zero.In your data file, try replacing all blank dates with the
DEFAULT
keyword.默认情况下,MySQL SQL 模式允许零日期。
我相信 LOAD DATA INFILE 命令正在读取旨在作为 DATETIME 的空白位置,并在插入之前自动使用零日期。这可以解释为什么您的默认约束没有被应用 - 插入时该值不为空。
我认为你有两个选择:
SET SQL_MODE='NO_ZERO_DATE'
The MySQL SQL mode by default allows zero dates.
My belief is that the LOAD DATA INFILE command is reading the blank position intended to be a DATETIME, and automatically using a zero date before the insertion. This would explain why your default constraint isn't being applied - the value isn't null when being inserted.
I think you have two options:
SET SQL_MODE='NO_ZERO_DATE'
datetime
表示您的列以Ymd H:i:s
(0000-00-00 00:00:00
) 格式存储日期和时间。date
表示您的列仅以Ymd
(0000-00-00
) 格式存储日期。datetime
means your column stores date and time in formatY-m-d H:i:s
(0000-00-00 00:00:00
).date
means your column stores just date in formatY-m-d
(0000-00-00
).如果插入数据时未指定值,则使用默认值,但当您从 CSV 文件插入数据时,它会为字段指定值,因此不使用默认值。
空白条目对于日期时间字段来说是无效值,因此将使用值
0000-00-00 00:00:00
来代替,就像任何无法解析为有效值的值一样日期(或部分日期)。If you don't specify a value when inserting data, the default is used, but when you insert the data from the CSV file it does specify a value for the field, so the default is not used.
A blank entry is an invalid value for a datetime field, so the value
0000-00-00 00:00:00
is used instead, just as with any value that can't be parsed to a valid date (or a partial date).MySQL 有一个令人讨厌的(在我看来)行为,在某些情况下,如果给定一个无效值存储在列中,它将存储一个预定义的“错误值”。例如,在日期或日期时间上下文中,它将存储您注意到的“零日期”;对于 ENUM 列,它将存储“”(对应于数字上下文中的 0)作为错误值。
虽然它不太优雅,但您可以尝试进行导入并允许插入无效的零日期,然后发出
MySQL has the annoying (in my opinion) behaviour that under some circumstances, if given an invalid value to store in a column, it will store a predefined "error value" instead. For example, in a date or datetime context, it will store the "zero date" you have noticed; for an ENUM column it will store '' (corresponds to 0 in a numeric context) as an error value.
Although it is inelegant, you might try doing the import and allowing the invalid zero dates to be inserted, and then issuing