Description:
When adding a TIMESTAMP column to an existing table (with existing rows in it) with the
definition: `ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, the
values are created as 0000-00-00 00:00:00.I would have expected CURRENT_TIMESTAMP to be inserted.Is this a but or expected behaviour? I don't find anything in the docs.How to repeat:
USE TEST;
CREATE TABLE `tablename1` (
`id` bigint(20) NOT NULL auto_increment,
`string` varchar(50) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;INSERT INTO tablename1 values (1,'a'), (2,'b');ALTER TABLE tablename1 add column `ts` timestamp NOT NULL default CURRENT_TIMESTAMP on
update CURRENT_TIMESTAMP after `string`;SELECT * from tablename1;returns:
id string ts
------ ------ -------------------
1 a 0000-00-00 00:00:00
2 b 0000-00-00 00:00:00
Suggested fix:
Insert CURRENT_TIMESTAMP to the new database TIMESTAMP fields.以上是原文。嗯,详情请在如下地址看:
http://bugs.mysql.com/bug.php?id=17392似乎很多神奇的方法都没救……只能多写一行——UPDATE ...
所谓神奇的方法:比如:
set SQL_mode = 'STRICT_ALL_TABLES,NO_ZERO_IN_DATE';
和
set global SQL_mode = 'STRICT_ALL_TABLES,NO_ZERO_IN_DATE';
都没救……
When adding a TIMESTAMP column to an existing table (with existing rows in it) with the
definition: `ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, the
values are created as 0000-00-00 00:00:00.I would have expected CURRENT_TIMESTAMP to be inserted.Is this a but or expected behaviour? I don't find anything in the docs.How to repeat:
USE TEST;
CREATE TABLE `tablename1` (
`id` bigint(20) NOT NULL auto_increment,
`string` varchar(50) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;INSERT INTO tablename1 values (1,'a'), (2,'b');ALTER TABLE tablename1 add column `ts` timestamp NOT NULL default CURRENT_TIMESTAMP on
update CURRENT_TIMESTAMP after `string`;SELECT * from tablename1;returns:
id string ts
------ ------ -------------------
1 a 0000-00-00 00:00:00
2 b 0000-00-00 00:00:00
Suggested fix:
Insert CURRENT_TIMESTAMP to the new database TIMESTAMP fields.以上是原文。嗯,详情请在如下地址看:
http://bugs.mysql.com/bug.php?id=17392似乎很多神奇的方法都没救……只能多写一行——UPDATE ...
所谓神奇的方法:比如:
set SQL_mode = 'STRICT_ALL_TABLES,NO_ZERO_IN_DATE';
和
set global SQL_mode = 'STRICT_ALL_TABLES,NO_ZERO_IN_DATE';
都没救……
似乎alter tbname add xxx xxx(x) [not null] default xxx
是会更新数据的啊……
只有
alter tbname change COLUMN xxxxxx default xxx
这个是不会更新数据的吧……