timestamp那点事

之前我们介绍了：MySQL数据库TIMESTAMP设置默认值的灵活运用，本次我们接着上次的内容介绍几个MySQL数据库TIMESTAMP设置默认值的几个应用实例，希望能够对您有所帮助。

1查看表定义，可以看到b列有个属性ON UPDATE CURRENT_TIMESTAMP，导致更新数据时，即便未涉及到该列，该列数据也被自动更新。另一方面，c列默认值是’0000-00-00 00:00:00’，实际插入已经被自动赋值为current_timestamp。

root@localhost:test >show create table dj1\G  
*************************** 1. row ***************************  
Table: dj1  
Create Table: CREATE TABLE `dj1` (  
`a` char(1) COLLATE utf8_bin DEFAULT NULL,  
`b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  
`c` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',  
UNIQUE KEY `dj1_idx_u1` (`b`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin 
1 row in set (0.00 sec) 

2创建表dj2，列b不带自动更新属性。

root@localhost:test >CREATE TABLE `dj2` (  
-> `a` char(1) COLLATE utf8_bin DEFAULT NULL,  
-> `b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ,  
-> `c` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',  
-> UNIQUE KEY `dj1_idx_u1` (`b`)  
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;  
Query OK, 0 rows affected (0.01 sec) 

3插入dj2测试数据

root@localhost:test >insert into dj2 values (1,null,null);   
Query OK, 1 row affected (0.00 sec)  
root@localhost:test >insert into dj2 values (2,null,null);   
Query OK, 1 row affected (0.00 sec) 

4查看dj2数据

root@localhost:test >select * from dj2;  
+------+---------------------+---------------------+  
| a | b | c |  
+------+---------------------+---------------------+  
| 1 | 2009-09-09 14:02:55 | 2009-09-09 14:02:55 |   
| 2 | 2009-09-09 14:03:00 | 2009-09-09 14:03:00 |   
+------+---------------------+---------------------+  
2 rows in set (0.00 sec) 

5dj2上创建唯一索引

root@localhost:test >create unique index dj2_idx_u1 on dj2(b);  
Query OK, 2 rows affected (0.02 sec)  
Records: 2 Duplicates: 0 Warnings: 0  
#更新数据成功  
root@localhost:test >update dj2 set a=9;  
Query OK, 2 rows affected (0.00 sec)  
Rows matched: 2 Changed: 2 Warnings: 0  
root@localhost:test >select * from dj2;  
+------+---------------------+---------------------+  
| a | b | c |  
+------+---------------------+---------------------+  
| 9 | 2009-09-09 14:02:55 | 2009-09-09 14:02:55 |   
| 9 | 2009-09-09 14:03:00 | 2009-09-09 14:03:00 |   
+------+---------------------+---------------------+  
2 rows in set (0.00 sec) 

MySQL数据库TIMESTAMP设置默认值是本文我们主要要介绍的内容，我们知道，CURRENT_TIMESTAMP ：当我更新这条记录的时候，这条记录的这个字段不会改变。

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设置默认值的灵活运用的知识就介绍到这里了，希望本次的介绍能够对您有所收获！