This is a simple and classic case that I am following on twitter and try to help a MySQL friend:
@danny said – how to automatically add the timestamp value of a new register and when that same register is altered, how to automatically alter that date? @wagnerbianchijr e.g. for a forum comment, I want to track when it was first posted AND when it was last edited.
In fact, this is really a classic and if you do not follow the rules available on this link, you will not be able to work properly with timestamp data type on conlums’ table. You cannot add two columns with same properties and to contour this problem you can create a table using properties on following way:
mysql> CREATE TABLE test.ts (
-> created TIMESTAMP DEFAULT 0,
-> updated TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> data char(30)
-> );
Query OK, 0 row affected (0.00 sec)
By default, MySQL defines TIMESTAMP columns as NOT NULL and stores the current timestamp in the column if you assign it a value of NULL.
mysql> INSERT INTO test.ts (created,data) VALUES (NULL, 'original value'); Query OK, 1 row affected (0.00 sec)
If you select table for data you’ll see that created has actual timestamp which register was created, the column update will count with 0000-00-00 00:00:00 and data will count with string value that was passed, original value. When you change or update the value of data, the column updated will receive automatically the new value.
mysql> UPDATE test.ts SET data='updated value'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0
Selecting data again, you’ll see actual register update filled into updated column and new value into data column.
Thus, creating a table like this and adjusting system to do this manipulation, everything will alright. If you have doubts, please, leave a comment.
Happy MySQL’ing!
