mysql中TIMESTAMP设置默认值的灵活运用

曾经终败给现在 2022-09-17 13:11 224阅读 0赞

默认值：

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'  
  
\#8查看表定义，可以看到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)  
  
\#9创建表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)  
  
\#10插入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)  
  
\#11查看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)  
  
\#12dj2上创建唯一索引  
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)  
  
\#13创建表dj3，b列默认值为CURRENT\_TIMESTAMP，c列默认值为CURRENT\_TIMESTAMP带自动更新属性，报错，不允许行为。  
root@localhost:test >CREATE TABLE \`dj3\` (  
\-> \`a\` char(1) COLLATE utf8\_bin DEFAULT NULL,  
\-> \`b\` timestamp NOT NULL DEFAULT CURRENT\_TIMESTAMP ,  
\-> \`c\` timestamp NOT NULL DEFAULT CURRENT\_TIMESTAMP ON UPDATE CURRENT\_TIMESTAMP,  
\-> UNIQUE KEY \`dj1\_idx\_u1\` (\`b\`)  
\-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8\_bin;  
ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT\_TIMESTAMP in DEFAULT or ON UPDATE clause  
  
\#14创建表dj4，b列默认值为CURRENT\_TIMESTAMP，c列默认值为'0000-00-00 00:00:00'带自动更新属性，报错，不允许行为。  
root@localhost:test >CREATE TABLE \`dj4\` (  
\-> \`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' ON UPDATE CURRENT\_TIMESTAMP,  
\-> UNIQUE KEY \`dj1\_idx\_u1\` (\`b\`)  
\-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8\_bin;  
ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT\_TIMESTAMP in DEFAULT or ON UPDATE clause  
  
\#15创建表dj5，b列默认值为CURRENT\_TIMESTAMP带自动更新属性，c列默认值为CURRENT\_TIMESTAMP，报错，不允许行为。  
root@localhost:test >CREATE TABLE \`dj5\` (  
\-> \`a\` char(1) COLLATE utf8\_bin DEFAULT NULL,  
\-> \`b\` timestamp NOT NULL DEFAULT CURRENT\_TIMESTAMP ON UPDATE CURRENT\_TIMESTAMP,  
\-> \`c\` timestamp NOT NULL DEFAULT CURRENT\_TIMESTAMP ,  
\-> UNIQUE KEY \`dj1\_idx\_u1\` (\`b\`)  
\-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8\_bin;  
ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT\_TIMESTAMP in DEFAULT or ON UPDATE clause  
  
conclusion：  
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;