如何在Mysql字段中设置默认的当前日期时间

Question

I am working on one project having frontend as PHP and backend MySql with XAMPP server.

I have one field named "created" with datatype as DATETIME in MySQl.I required to pass defauld current datetime value to this field.

I knew that there are some function in PHP to add current datetime to MySql database like

date_default_timezone_set("America/Chicago");

$date = date('Y-m-d H:i:s');

In some case(possible by mistake),PHP code may not pass datetime in mysql and defaul it is taking "0000-00-00 00:00:00" by MySql.

Can any one guide me to what word I need to add as default value for this created field.

I have tried to add CURRENT_TIMESTAMP as default value.

But It give me error :

 #1067 - Invalid default value for 'created' 

Can anyone give me direction on this.

Thanks.

Answer 1

If you want to use the ON UPDATE CURRENT_TIMESTAMP, you should make the field a TIMESTAMP and not a DATETIME.

Or, consider using a trigger for this situation: http://dev.mysql.com/doc/refman/5.0/en/trigger-syntax.html

(as slugonamission said in this thread: MySQL is not allowing ON UPDATE CURRENT_TIMESTAMP for a DATETIME field)