MySQL 关于 TIMESTAMP 长度的问题

今天在数据库创建表的时候遇到了一个问题,在执行创建表语句的时候报了如下错误1294 - Invalid ON UPDATE clause for 'version' column, Time: 0.010000s。在查询官方文档后，发现在MySQL 5.6.4 之后 TIMESTAMP 发生了改变

MySQL 5.6.4 后

TIMESTAMP[(fsp)]

时间戳。范围是'1970-01-01 00:00:01.000000'UTC到'2038-01-19 03:14:07.999999'UTC。 TIMESTAMP值存储为自纪元（'1970-01-01 00:00:00'UTC）以来的秒数。TIMESTAMP不能代表值'1970-01-01 00:00:00'，因为它等于从历元开始的0秒，而值0保留用于表示’ 00:00 -00-00 00:00:00’，即" 0 "时间戳值。

fsp 可以给出0~6范围内的可选值以指定小数秒(微妙)精度。值为0表示没有小数部分。如果省略，则默认精度为0。

官方文档片段

• TIMESTAMP[(fsp)]

A timestamp. The range is '1970-01-01 00:00:01.000000' UTC to '2038-01-19 03:14:07.999999' UTC. TIMESTAMP values are stored as the number of seconds since the epoch ('1970-01-01 00:00:00' UTC). A TIMESTAMP cannot represent the value '1970-01-01 00:00:00' because that is equivalent to 0 seconds from the epoch and the value 0 is reserved for representing '0000-00-00 00:00:00', the “zero” TIMESTAMP value.

As of MySQL 5.6.4, an optional fsp value in the range from 0 to 6 may be given to specify fractional seconds precision. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0.

The way the server handles TIMESTAMP definitions depends on the value of the explicit_defaults_for_timestamp system variable (see Section 5.1.7, “Server System Variables”).

If explicit_defaults_for_timestamp is enabled, there is no automatic assignment of the DEFAULT CURRENT_TIMESTAMP or ON UPDATE CURRENT_TIMESTAMPattributes to any TIMESTAMP column. They must be included explicitly in the column definition. Also, any TIMESTAMP not explicitly declared as NOT NULL permits NULLvalues.

If explicit_defaults_for_timestamp is disabled, the server handles TIMESTAMP as follows:

Unless specified otherwise, the first TIMESTAMP column in a table is defined to be automatically set to the date and time of the most recent modification if not explicitly assigned a value. This makes TIMESTAMP useful for recording the timestamp of an INSERT or UPDATE operation. You can also set any TIMESTAMP column to the current date and time by assigning it a NULL value, unless it has been defined with the NULL attribute to permit NULL values.

Automatic initialization and updating to the current date and time can be specified using DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP column definition clauses. By default, the first TIMESTAMP column has these properties, as previously noted. As of MySQL 5.6.5, any TIMESTAMP column in a table can be defined to have these properties. Before 5.6.5, at most one TIMESTAMP column per table can have them, but it is possible to suppress them for the first column and instead assign them to a different TIMESTAMP column. See Section 11.3.5, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”.

explicit_defaults_for_timestamp is available as of MySQL 5.6.6. Before 5.6.6, the server handles TIMESTAMP as discussed forexplicit_defaults_for_timestamp disabled. Those behaviors, while they remain the default, are nonstandard and are deprecated as of 5.6.6. For discussion regarding upgrading to an installation with explicit_defaults_for_timestamp enabled, see Section 2.11.3, “Changes in MySQL 5.6”.