I have two columns in table users namely registerDate and lastVisitDate
which consist of datetime data type. I would like to do the following.
- Set registerDate defaults value to MySQL NOW()
- Set lastVisitDate default value to
0000-00-00 00:00:00
Instead of null which it uses by default.
Because the table already exists and has existing records, I would like to use Modify table. I've tried using the two piece of code below, but neither works.
ALTER TABLE users MODIFY registerDate datetime DEFAULT NOW()
ALTER TABLE users MODIFY registerDate datetime DEFAULT CURRENT_TIMESTAMP;
It gives me Error : ERROR 1067 (42000): Invalid default value for 'registerDate'
Is it possible for me to set the default datetime value to NOW() in MySQL?
Best Answer
As of MySQL 5.6.5, you can use the
DATETIME
type with a dynamic default value:Or even combine both rules:
Reference:
http://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html
http://optimize-this.blogspot.com/2012/04/datetime-default-now-finally-available.html
Prior to 5.6.5, you need to use the
TIMESTAMP
data type, which automatically updates whenever the record is modified. Unfortunately, however, only one auto-updatedTIMESTAMP
field can exist per table.See: http://dev.mysql.com/doc/refman/5.1/en/create-table.html
If you want to prevent MySQL from updating the timestamp value on
UPDATE
(so that it only triggers onINSERT
) you can change the definition to: