Continuing my little tour of possibly overlooked new MySQL 5.6 features, I came across this oft-requested gem:  For eons, MySQL has supported TIMESTAMP columns that could be automatically populated at row insertion and/or row update time.  It’s a handy feature to have, but it has suffered from a pretty significant limitation – only one TIMESTAMP column per table could be automatically updated, forcing you to choose whether leverage this functionality for record creation or last updated time – or bypass the MySQL feature and write the logic to maintain a TIMESTAMP or other temporal column via your application code.  Well, all that has changed with 5.6.5, and you can now do this:

mysql> CREATE TABLE v (
->  a INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
->  b INT,
->  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
->  updated TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
->  );
Query OK, 0 rows affected (0.22 sec)

mysql> INSERT INTO v (a, b) VALUES (NULL, 1);
Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM v;
+---+------+---------------------+---------------------+
| a | b    | created             | updated             |
+---+------+---------------------+---------------------+
| 1 |    1 | 2012-05-29 11:25:20 | 0000-00-00 00:00:00 |
+---+------+---------------------+---------------------+
1 row in set (0.02 sec)

mysql> UPDATE v SET b = b+1;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM v;
+---+------+---------------------+---------------------+
| a | b    | created             | updated             |
+---+------+---------------------+---------------------+
| 1 |    2 | 2012-05-29 11:25:20 | 2012-05-29 11:25:43 |
+---+------+---------------------+---------------------+
1 row in set (0.00 sec)

For the record, here’s the error message you get when you try to create the same table in 5.5:

mysql> CREATE TABLE v (
    ->  a INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->  b INT,
    ->  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ->  updated TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ->  );
ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP
column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

In addition, 5.6.5 expands the automatic population to DATETIME columns, as well

mysql> DROP TABLE v;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE v (
->  a INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
->  b INT,
->  created DATETIME DEFAULT CURRENT_TIMESTAMP,
->  updated DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
->  );
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> INSERT INTO v (a, b) VALUES (NULL, 1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM v;
+---+------+---------------------+---------+
| a | b    | created             | updated |
+---+------+---------------------+---------+
| 1 |    1 | 2012-05-29 11:39:41 | NULL    |
+---+------+---------------------+---------+
1 row in set (0.00 sec)

Many thanks to the MySQL Engineering team in general, and Martin in particular, for implementing this needed feature!

You can read more about this important change in Martin’s blog, the 5.6.5 change logs, and the 5.6 documentation.