Mysql问题解决汇总

Invalid default value for 'create_time'

[Error Code: 1067, SQL State: 42000] 

 

CURRENT_TIMESTAMP is only acceptable on TIMESTAMP fields. DATETIME fields must be left either with a null default value, or no default value at all - default values must be a constant value, not the result of an expression.

relevant docs: http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html

You can work around this by setting a post-insert trigger on the table to fill in a "now" value on any new records.

http://stackoverflow.com/questions/4489548/why-there-can-be-only-one-timestamp-column-with-current-timestamp-in-default-cla

 

This limitation, which was only due to historical, code legacy reasons, has been lifted in recent versions of MySQL:

Changes in MySQL 5.6.5 (2012-04-10, Milestone 8)

Previously, at most one TIMESTAMP column per table could be automatically initialized or updated to the current date and time. This restriction has been lifted. Any TIMESTAMP column definition can have any combination of DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses. In addition, these clauses now can be used with DATETIME column definitions. For more information, see Automatic Initialization and Updating for TIMESTAMP and DATETIME.

http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-5.html

 

 

http://stackoverflow.com/questions/11400147/mysql-current-timestamp-as-default

 

You can use two timestamp in one table. For default, use DEFAULT field first and then the rest timestamp fields.

Below query should work.

CREATE TABLE myTable

(

 id INT,

 date_registered TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

 date_validated TIMESTAMP

);