CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP :当我更新这条记录的时候,这条记录的这个字段将会改变。即时间变为了更新时候的时间。(注意一个UPDATE设置一个列为它已经有的值,这将不引起TIMESTAMP列被更新,因为如果你设置一个列为它当前的值,MySQL为了效率而忽略更改。)
如果有多个TIMESTAMP列,只有第一个自动更新。
接下来我们看几个实际的例子:
#1创建一个有两个timestamp字段的表dj1。
root@localhost:test >create table dj1 (a char(1), b timestamp ,c timestamp);
Query OK, 0 rows affected (0.01 sec)
#2插入两行数据,仅赋值于列A
root@localhost:test >insert into dj1 values (1,null,null);
Query OK, 1 row affected (0.00 sec)
root@localhost:test >insert into dj1 values (2,null,null);
Query OK, 1 row affected (0.00 sec)
#3查询插入数据,b,c两列都使用current timestamp作为默认值。
root@localhost:test >select * from dj1;
+------+---------------------+---------------------+
| a | b | c |
+------+---------------------+---------------------+
| 1 | 2009-09-09 13:48:40 | 2009-09-09 13:48:40 |
| 2 | 2009-09-09 13:48:44 | 2009-09-09 13:48:44 |
+------+---------------------+---------------------+
2 rows in set (0.00 sec)
#4更新一行数据,发现b列timestamp被自动更新,而c列保持不变。
root@localhost:test >update dj1 set a=9 where a=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
root@localhost:test >select * from dj1;
+------+---------------------+---------------------+
| a | b | c |
+------+---------------------+---------------------+
| 9 | 2009-09-09 13:49:08 | 2009-09-09 13:48:40 |
| 2 | 2009-09-09 13:48:44 | 2009-09-09 13:48:44 |
+------+---------------------+---------------------+
2 rows in set (0.00 sec)
#5再更新一列,仍然如#4
root@localhost:test >update dj1 set a=8 where a=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
root@localhost:test >select * from dj1;
+------+---------------------+---------------------+
| a | b | c |
+------+---------------------+---------------------+
| 9 | 2009-09-09 13:49:08 | 2009-09-09 13:48:40 |
| 8 | 2009-09-09 13:49:36 | 2009-09-09 13:48:44 |
+------+---------------------+---------------------+
2 rows in set (0.00 sec)
#6在b列上创建唯一索引
root@localhost:test >create unique index dj1_idx_u1 on dj1(b);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
#7更新所有行a列,报唯一性冲突。
root@localhost:test >update dj1 set a=1;
ERROR 1062 (23000): Duplicate entry '2009-09-09 13:54:45' for key 'dj1_idx_u1'
结论:
1.MySQL默认表的第一个timestamp字段为NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP属性,必须显式定义改变这种行为。
2.MySQL只允许一个timestamp字段拥有[DEFAULT CURRENT_TIMESTAMP |ON UPDATE CURRENT_TIMESTAMP]属性。 我的理解为要么都是DEFAULT CURRENT_TIMESTAMP 要么都是DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
3.修改字段属性值
show create table tbl_ledgerrecord;
alter table tbl_ledgerrecord change intoStorageDate intoStorageDate timestamp DEFAULT CURRENT_TIMESTAMP;
关于MySQL数据库TIMESTAMP设置默认值的灵活运用的知识就介绍到这里了,希望本次的介绍能够对您有所收获!
声明: 此文观点不代表本站立场;转载须要保留原文链接;版权疑问请联系我们。