{"id":87,"date":"2013-01-09T23:47:00","date_gmt":"2013-01-10T04:47:00","guid":{"rendered":"http:\/\/en.latindevelopers.com\/ivancp\/?p=87"},"modified":"2013-01-10T10:58:40","modified_gmt":"2013-01-10T15:58:40","slug":"mysql-the-strange-case-of-a-timestamp-field","status":"publish","type":"post","link":"http:\/\/en.latindevelopers.com\/ivancp\/2013\/mysql-the-strange-case-of-a-timestamp-field\/","title":{"rendered":"MySQL, the strange case of a timestamp field"},"content":{"rendered":"

I discovered a feature (or bug) of MySQL timestamp fields. Maybe is documented in some place what I not read yet:<\/p>\n

When I add a new timestamp field to a table, MySQL magically adds some features to new timestamp field like a “trigger” and a default value to CURRENT_TIMESTAMP.<\/p>\n

There is test-case script:<\/p>\n

\r\n-- CREATING TABLE AND INSERT SOME DUMMY DATA\r\nmysql> create table t(\r\n    -> id int not null primary key auto_increment,\r\n    -> val varchar(50)\r\n    -> );\r\nQuery OK, 0 rows affected (0.15 sec)\r\n\r\nmysql> insert into t (val) values (\"foo\") ,(\"var\");\r\nQuery OK, 2 rows affected (0.08 sec)\r\nRecords: 2  Duplicates: 0  Warnings: 0\r\n\r\nmysql> select * from t;\r\n+----+------+\r\n| id | val  |\r\n+----+------+\r\n|  1 | foo  |\r\n|  2 | var  |\r\n+----+------+\r\n2 rows in set (0.00 sec)\r\n\r\n\r\n-- ADDING NEW TIMESTAMP FIELD AND ADD MORE DUMMY DATA\r\nmysql> alter table t add ts_field timestamp;\r\nQuery OK, 2 rows affected (0.35 sec)\r\nRecords: 2  Duplicates: 0  Warnings: 0\r\n\r\nmysql> insert into t (val) values (\"foo 2\") ,(\"var 2\");\r\nQuery OK, 2 rows affected (0.06 sec)\r\nRecords: 2  Duplicates: 0  Warnings: 0\r\n\r\n-- THERE IS THE MAGIC:\r\nmysql> select * from t;\r\n+----+-------+---------------------+\r\n| id | val   | ts_field            |\r\n+----+-------+---------------------+\r\n|  1 | foo   | 0000-00-00 00:00:00 |\r\n|  2 | var   | 0000-00-00 00:00:00 |\r\n|  3 | foo 2 | 2013-01-09 23:20:01 |    <---\r\n|  4 | var 2 | 2013-01-09 23:20:01 |    <---\r\n+----+-------+---------------------+\r\n4 rows in set (0.00 sec)\r\n<\/pre>\n
What happened? I dont know.<\/p>\n
The new table structure is:<\/p>\n
\r\nCREATE TABLE `t` (\r\n  `id` int(11) NOT NULL AUTO_INCREMENT,\r\n  `val` varchar(50) DEFAULT NULL,\r\n  `ts_field` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\r\n  PRIMARY KEY (`id`)\r\n) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1\r\n<\/pre>\n
It only happens if the table doesn't have another timestamp field yet.<\/p>\n
Now, this is useful? maybe. This is a bug? maybe.<\/p>\n
Update<\/strong>: This is a feature documented in https:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/timestamp-initialization.html<\/a>, sorry about my lapsus-post.<\/p>\n
<\/p>\n
Share this:<\/h3>