MySQL 5.5及以上5.6默认值 [英] MySQL 5.5 & 5.6 default values

问题描述

我想对MySQL 5.5&中默认值的行为进行一些说明. 5.6.可以说，我们在MySQL 5.5服务器上具有下表:

CREATE TABLE `test` (
`TestColumn` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=INNODB DEFAULT CHARSET=utf8;

我能够运行以下查询而不会出现问题:

INSERT INTO `test` VALUES (NULL);

哪个将创建以下行:

 TestColumn
 2014-02-20 14:55:05

现在，如果我在MySQL 5.6服务器上重复相同的测试，则插入将失败:

Error Code: 1048
Column 'TestColumn' cannot be null

我了解到时间戳自动初始化已在5.6中更改( http ://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html )，但是我无法解决的问题是如何复制5.6中5.5中看到的行为.

不确定是否重要，但是我们的5.6服务器已将explicit_defaults_for_timestamp设置为ON.

理想情况下，我所追求的是解决此问题的方法，但是，如果有人对它有更好的理解并能加以解释，那也是很有用的.

谢谢.

我们正在使用MySQL 5.6.13

解决方案

您上面5.5中显示的行为实际上是一个错误.此问题已在5.6.11中修复.在发行说明5.6.11 处，最底部它是错误 68472 和16394472(我实际上找不到链接)的一部分. /p>

如果关闭explicit_defaults_for_timestamp标志，则应该像5.5中那样工作

从5.5更新到5.6时，这与我的数据库完全一样.

I'd like some clarification on the behaviour of default values in MySQL 5.5 & 5.6. Lets say we have the following table on a MySQL 5.5 server:

CREATE TABLE `test` (
`TestColumn` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=INNODB DEFAULT CHARSET=utf8;

I am able to run the following query without issue:

INSERT INTO `test` VALUES (NULL);

Which creates the following row:

 TestColumn
 2014-02-20 14:55:05

Now if I repeat the same test on a MySQL 5.6 server, the insert fails:

Error Code: 1048
Column 'TestColumn' cannot be null

I understand that timestamp automatic initilisation has changed in 5.6 (http://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html), but what I can't work out is how to replicate the behaviour seen in 5.5 in 5.6.

Not sure if it matters but our 5.6 server has explicit_defaults_for_timestamp set to ON.

Ideally what I'm after is a solution to this, but if anyone has a better understand and can explain it that would be useful as well.

Thanks.

EDIT: We are using MySQL 5.6.13

解决方案

The behavior you showed above in 5.5 was actually a bug. This was fixed in 5.6.11. Check out the release notes for 5.6.11 at the very bottom. It was part of bugs 68472 and 16394472 (which I can't actually find a link to).

If you turn the explicit_defaults_for_timestamp flag off, it SHOULD work as it did in 5.5

This was the exact same issue that I had with our DB when we updated from 5.5 to 5.6.

这篇关于MySQL 5.5及以上5.6默认值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！