How to use CURRENT_TIMESTAMP in MySQL

Use of CURRENT_TIMESTAMP

As we all know, MySQL's date type can use CURRENT_TIMESTAMP to specify the default value, but this is related to the MySQL version and the specific type of the date. Only versions after 5.6 can use CURRENT_TIMESTAMP as the default value of DATETIME.

For example:

ALTER TABLE t_user ADD update_time DATETIME DEFAULT CURRENT_TIMESTAMP

In versions prior to 5.6, when CURRENT_TIMESTAMP is used as the default value, the following error will occur

[Err] 1067 - Invalid default value for 'update_time'

Prior to MySQL 5.6.5, DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP only applies to TIMESTAMP, and at most one TIMESTAMP field in a table is allowed to use this feature. As of MySQL 5.6.5, DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP applies to both TIMESTAMP and DATETIME columns, and to an unlimited number of columns.

timestamp using CURRENT_TIMESTAMP reports an error

The project has the following error:

Error updating database.

Cause:com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column 'createTime' cannot be null

The data model is as follows:

  /* Creation time cannot be empty */
  createTime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
  /* Update time cannot be empty*/
  updateTime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update time',

After a series of troubleshooting, it was found that the problem was caused by the global variable "explicit_defaults_for_timestamp" in different versions of MySQL database.

-- View the default value of explicit_defaults_for_timestamp SHOW GLOBAL VARIABLES LIKE "explicit_defaults_for_timestamp"; 

-- Modify the default value of explicit_defaults_for_timestamp SET @@global.explicit_defaults_for_timestamp=OFF;

When the parameter value is "ON":

The above is my personal experience. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.