做了中文注释。
在mysql的应用中出现了奇怪的现象,当创建表之后,应用时,每次update某些字段是,都会看到第一个timestamp字段自动更新。查看他的类型后,发现default值是CURRENT_TIMESTAMP。仍然不知所云。查阅文档后,发现:当创建表,仅指明类型timestamp 时,对于表中的两个timestamp 字段的处理是不一样的;对第一个timestamp,它赋予了默认值CURRENT_TIMESTAMP,并自动更新,对其他的则是0000-00-00 00:00:00。
如下示例:
mysql> create table test(id int,t1 timestamp,t2 timestamp);
Query OK, 0 rows affected (0.03 sec)
mysql> desc test;
+-------+-----------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------------------+-------+
| id | int(11) | YES | | NULL | |
| t1 | timestamp | YES | | CURRENT_TIMESTAMP | |
| t2 | timestamp | YES | | 0000-00-00 00:00:00 | |
+-------+-----------+------+-----+---------------------+-------+
3 rows in set (0.00 sec)
create操作实际执行的是:
create table test(id int,t1 timestamp default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,t2 timestamp);
对于这个问题,mysql官方文档给出了详细的说明:
In a CREATE TABLE statement, the first TIMESTAMP column can be declared in any of the following ways:
1,With both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses, the column has the current timestamp for its default value, and is automatically updated.
当有DEFAULT CURRENT_TIMESTAMP 和ON UPDATE CURRENT_TIMESTAMP 两个表达式定义时,列会将当前的timestamp作为他的缺省值,并自动更新
2,With neither DEFAULT nor ON UPDATE clauses, it is the same as DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.
如果没有DEFAULT 且没有 ON UPDATE表达式,效果与DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 相同。
With a DEFAULT CURRENT_TIMESTAMP clause and no ON UPDATE clause, the column has the current timestamp for its default value but is not automatically updated.
如果有DEFAULT CURRENT_TIMESTAMP 表达式,没有ON UPDATE 表达式,则这一列会将当前的timestamp作为缺省值,但不自动更新。
3,With no DEFAULT clause and with an ON UPDATE CURRENT_TIMESTAMP clause, the column has a default of 0 and is automatically updated.
如果没有DEFAULT 表达式,但是有ON UPDATE 表达式,则这一列会有一个0的缺省值,但不自动更新。
4,With a constant DEFAULT value, the column has the given default and is not automatically initialized to the current timestamp. If the column also has an ON UPDATE CURRENT_TIMESTAMP clause, it is automatically updated; otherwise, it has a constant default and is not automatically updated.
如果有一个DEFAULT常量,则该列会使用这个给定的缺省值,且不会自动更新。如果有ON UPDATE,则会自动更新,否则不自动更新。
In other words, you can use the current timestamp for both the initial value and the auto-update value, or either one, or neither. (For example, you can specify ON UPDATE to enable auto-update without also having the column auto-initialized.) The following column definitions demonstrate each of the possiblities:
Auto-initialization and auto-update: (自动初始化,自动更新)
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
Auto-initialization only: (仅自动初始化)
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
Auto-update only: (仅自动更新)
ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP
Neither: (既不初始化,也不更新)
ts TIMESTAMP DEFAULT 0