每日MySQL之013:TIMESTAMP和DATETIME列的自动初始化与自动更新
来源:互联网 发布:最近的网络流行词 编辑:程序博客网 时间:2024/05/18 11:47
TIMESTAMP 和 DATETIME 列可以自动地被初始化和更新为当前的日期/时间。
对于 TIMESTAMP 和 DATETIME 列,可以把当前时间戳设置为其默认值、自动更新值或者两者都设置:
--对于auto-initialized 列,如果新插入的记录没有指定值,那么设置其值为当前时间戳。
--对于auto-updated 列,如果这条记录的其他列的值发生变化,那么这一列就会被更新为发生变化时的时间戳。如果不想被更新,需要显式地指定当前值。
如何开启:
要想设置这个属性,可以在列定义的时候,指定 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP ,两者出现的顺序随意。
测试1:自动初始化和自动更新
mysql> CREATE TABLE t1 (
-> id int,
-> ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
-> );
mysql> INSERT INTO t1 VALUES (1, default, default);
mysql> INSERT INTO t1(id) VALUES (2);
mysql> select * from t1;
+------+---------------------+---------------------+
| id | ts | dt |
+------+---------------------+---------------------+
| 1 | 2017-08-07 02:18:37 | 2017-08-07 02:18:37 |
| 2 | 2017-08-07 02:18:41 | 2017-08-07 02:18:41 |
+------+---------------------+---------------------+
2 rows in set (0.00 sec)
插入数据时,选择了默认值,被自动初始化为插入时的时间戳。
mysql> update t1 set id=id*2 where id = 2;
mysql> select * from t1;
+------+---------------------+---------------------+
| id | ts | dt |
+------+---------------------+---------------------+
| 1 | 2017-08-07 02:18:37 | 2017-08-07 02:18:37 |
| 4 | 2017-08-07 02:19:10 | 2017-08-07 02:19:10 |
+------+---------------------+---------------------+
2 rows in set (0.00 sec)
更新第二条记录的id字段,导致第二条记录的ts列和dt列被自动更新
mysql> update t1 set id = id * 2 where id = 1;
mysql> select * from t1;
+------+---------------------+---------------------+
| id | ts | dt |
+------+---------------------+---------------------+
| 2 | 2017-08-07 02:19:28 | 2017-08-07 02:19:28 |
| 4 | 2017-08-07 02:19:10 | 2017-08-07 02:19:10 |
+------+---------------------+---------------------+
2 rows in set (0.00 sec)
更新第一条记录的id字段,导致第一条记录的ts列和dt列被自动更新
测试2:只设置自动初始化
mysql> CREATE TABLE t2 (
-> id int,
-> ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
-> dt DATETIME DEFAULT CURRENT_TIMESTAMP
-> );
mysql> INSERT INTO t2 VALUES (1, default, default);
mysql> INSERT INTO t2(id) VALUES (2);
mysql> select * from t2;
+------+---------------------+---------------------+
| id | ts | dt |
+------+---------------------+---------------------+
| 1 | 2017-08-07 02:19:51 | 2017-08-07 02:19:51 |
| 2 | 2017-08-07 02:19:55 | 2017-08-07 02:19:55 |
+------+---------------------+---------------------+
2 rows in set (0.00 sec)
mysql> update t2 set id=id*2 where id = 2;
mysql> select * from t2;
+------+---------------------+---------------------+
| id | ts | dt |
+------+---------------------+---------------------+
| 1 | 2017-08-07 02:19:51 | 2017-08-07 02:19:51 |
| 4 | 2017-08-07 02:19:55 | 2017-08-07 02:19:55 |
+------+---------------------+---------------------+
2 rows in set (0.00 sec)
ts列和td列只设置了自动初始化,所以当其他字段变化时,不会自动更新
mysql> update t2 set id = id * 2 where id = 1;
mysql> select * from t2;
+------+---------------------+---------------------+
| id | ts | dt |
+------+---------------------+---------------------+
| 2 | 2017-08-07 02:19:51 | 2017-08-07 02:19:51 |
| 4 | 2017-08-07 02:19:55 | 2017-08-07 02:19:55 |
+------+---------------------+---------------------+
2 rows in set (0.00 sec)
示例3:只设置自动更新
Database changed
mysql> CREATE TABLE t3 (
-> id int,
-> ts TIMESTAMP DEFAULT '1997-01-01 00:00:01' ON UPDATE CURRENT_TIMESTAMP,
-> dt DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
-> );
Query OK, 0 rows affected (0.97 sec)
mysql> INSERT INTO t3 VALUES (1, default, default);
mysql> INSERT INTO t3(id) VALUES (2);
mysql> select * from t3;
+------+---------------------+------+
| id | ts | dt |
+------+---------------------+------+
| 1 | 1997-01-01 00:00:01 | NULL |
| 2 | 1997-01-01 00:00:01 | NULL |
+------+---------------------+------+
2 rows in set (0.00 sec)
ts列被初始化为指定的默认值,dt列被初始化为NULL.
mysql> update t3 set id=id*2 where id = 2;
mysql> select * from t3;
+------+---------------------+---------------------+
| id | ts | dt |
+------+---------------------+---------------------+
| 1 | 1997-01-01 00:00:01 | NULL |
| 4 | 2017-08-07 02:21:14 | 2017-08-07 02:21:14 |
+------+---------------------+---------------------+
2 rows in set (0.00 sec)
更新了第二条记录,第二条记录的ts列和dt列被自动更新
mysql> update t3 set id = id * 2 where id = 1;
mysql> select * from t3 ;
+------+---------------------+---------------------+
| id | ts | dt |
+------+---------------------+---------------------+
| 2 | 2017-08-07 02:21:30 | 2017-08-07 02:21:30 |
| 4 | 2017-08-07 02:21:14 | 2017-08-07 02:21:14 |
+------+---------------------+---------------------+
2 rows in set (0.00 sec)
更新了第一条记录,第一条记录的ts列和dt列被自动更新
对于 TIMESTAMP 和 DATETIME 列,可以把当前时间戳设置为其默认值、自动更新值或者两者都设置:
--对于auto-initialized 列,如果新插入的记录没有指定值,那么设置其值为当前时间戳。
--对于auto-updated 列,如果这条记录的其他列的值发生变化,那么这一列就会被更新为发生变化时的时间戳。如果不想被更新,需要显式地指定当前值。
如何开启:
要想设置这个属性,可以在列定义的时候,指定 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP ,两者出现的顺序随意。
测试1:自动初始化和自动更新
mysql> CREATE TABLE t1 (
-> id int,
-> ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
-> );
mysql> INSERT INTO t1 VALUES (1, default, default);
mysql> INSERT INTO t1(id) VALUES (2);
mysql> select * from t1;
+------+---------------------+---------------------+
| id | ts | dt |
+------+---------------------+---------------------+
| 1 | 2017-08-07 02:18:37 | 2017-08-07 02:18:37 |
| 2 | 2017-08-07 02:18:41 | 2017-08-07 02:18:41 |
+------+---------------------+---------------------+
2 rows in set (0.00 sec)
插入数据时,选择了默认值,被自动初始化为插入时的时间戳。
mysql> update t1 set id=id*2 where id = 2;
mysql> select * from t1;
+------+---------------------+---------------------+
| id | ts | dt |
+------+---------------------+---------------------+
| 1 | 2017-08-07 02:18:37 | 2017-08-07 02:18:37 |
| 4 | 2017-08-07 02:19:10 | 2017-08-07 02:19:10 |
+------+---------------------+---------------------+
2 rows in set (0.00 sec)
更新第二条记录的id字段,导致第二条记录的ts列和dt列被自动更新
mysql> update t1 set id = id * 2 where id = 1;
mysql> select * from t1;
+------+---------------------+---------------------+
| id | ts | dt |
+------+---------------------+---------------------+
| 2 | 2017-08-07 02:19:28 | 2017-08-07 02:19:28 |
| 4 | 2017-08-07 02:19:10 | 2017-08-07 02:19:10 |
+------+---------------------+---------------------+
2 rows in set (0.00 sec)
更新第一条记录的id字段,导致第一条记录的ts列和dt列被自动更新
测试2:只设置自动初始化
mysql> CREATE TABLE t2 (
-> id int,
-> ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
-> dt DATETIME DEFAULT CURRENT_TIMESTAMP
-> );
mysql> INSERT INTO t2 VALUES (1, default, default);
mysql> INSERT INTO t2(id) VALUES (2);
mysql> select * from t2;
+------+---------------------+---------------------+
| id | ts | dt |
+------+---------------------+---------------------+
| 1 | 2017-08-07 02:19:51 | 2017-08-07 02:19:51 |
| 2 | 2017-08-07 02:19:55 | 2017-08-07 02:19:55 |
+------+---------------------+---------------------+
2 rows in set (0.00 sec)
mysql> update t2 set id=id*2 where id = 2;
mysql> select * from t2;
+------+---------------------+---------------------+
| id | ts | dt |
+------+---------------------+---------------------+
| 1 | 2017-08-07 02:19:51 | 2017-08-07 02:19:51 |
| 4 | 2017-08-07 02:19:55 | 2017-08-07 02:19:55 |
+------+---------------------+---------------------+
2 rows in set (0.00 sec)
ts列和td列只设置了自动初始化,所以当其他字段变化时,不会自动更新
mysql> update t2 set id = id * 2 where id = 1;
mysql> select * from t2;
+------+---------------------+---------------------+
| id | ts | dt |
+------+---------------------+---------------------+
| 2 | 2017-08-07 02:19:51 | 2017-08-07 02:19:51 |
| 4 | 2017-08-07 02:19:55 | 2017-08-07 02:19:55 |
+------+---------------------+---------------------+
2 rows in set (0.00 sec)
示例3:只设置自动更新
Database changed
mysql> CREATE TABLE t3 (
-> id int,
-> ts TIMESTAMP DEFAULT '1997-01-01 00:00:01' ON UPDATE CURRENT_TIMESTAMP,
-> dt DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
-> );
Query OK, 0 rows affected (0.97 sec)
mysql> INSERT INTO t3 VALUES (1, default, default);
mysql> INSERT INTO t3(id) VALUES (2);
mysql> select * from t3;
+------+---------------------+------+
| id | ts | dt |
+------+---------------------+------+
| 1 | 1997-01-01 00:00:01 | NULL |
| 2 | 1997-01-01 00:00:01 | NULL |
+------+---------------------+------+
2 rows in set (0.00 sec)
ts列被初始化为指定的默认值,dt列被初始化为NULL.
mysql> update t3 set id=id*2 where id = 2;
mysql> select * from t3;
+------+---------------------+---------------------+
| id | ts | dt |
+------+---------------------+---------------------+
| 1 | 1997-01-01 00:00:01 | NULL |
| 4 | 2017-08-07 02:21:14 | 2017-08-07 02:21:14 |
+------+---------------------+---------------------+
2 rows in set (0.00 sec)
更新了第二条记录,第二条记录的ts列和dt列被自动更新
mysql> update t3 set id = id * 2 where id = 1;
mysql> select * from t3 ;
+------+---------------------+---------------------+
| id | ts | dt |
+------+---------------------+---------------------+
| 2 | 2017-08-07 02:21:30 | 2017-08-07 02:21:30 |
| 4 | 2017-08-07 02:21:14 | 2017-08-07 02:21:14 |
+------+---------------------+---------------------+
2 rows in set (0.00 sec)
更新了第一条记录,第一条记录的ts列和dt列被自动更新
DB2中也有类似的功能:
db2 "create table t1(id int, UPDATETIME TIMESTAMP NOT NULL GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP)"db2 "insert into t1(id) values(100)"db2 "insert into t1(id) values(200)"db2 "select * from t1"ID UPDATETIME ----------- -------------------------- 100 2017-08-07-02.47.52.576055 200 2017-08-07-02.47.53.894663 2 record(s) selected.db2 "update t1 set id = id+1 where id < 200"db2 "select * from t1"ID UPDATETIME ----------- -------------------------- 101 2017-08-07-02.48.25.674785 200 2017-08-07-02.47.53.894663 2 record(s) selected.注意一点,DB2里使用的是时间并非真正意义上的操作系统时间,而是VTS时间,也就是说,如果你将时间调整到未来,再调整回来,则UPDATETIME的时间戳保持在未来,而非当下。
阅读全文
0 0
- 每日MySQL之013:TIMESTAMP和DATETIME列的自动初始化与自动更新
- MySql timestamp/datetime自动更新和初始化
- 高性能mysql之TIMESTAMP与DATETIME
- mySQL timestamp 与datetime 的区别
- mysql 中的datetime和timestamp的比较
- mysql 中的datetime和timestamp的比较
- mysql中 datetime 和timestamp的区别
- MySQL中datetime和timestamp的区别
- MySQL中DATETIME和TIMESTAMP的区别
- mysql 中的datetime和timestamp的比较
- Mysql 的日期类型:DATETIME和TIMESTAMP
- mysql中datetime 和timestamp的区别
- mysql中TIMESTAMP和DATETIME的比较
- MySQL的DATETIME, TIMESTAMP类型列的默认值
- mysql datetime与timestamp区别
- mysql中TIMESTAMP和DATETIME
- MYSQL-TIMESTAMP数据类型的默认值与自动更新问题
- mysql中timestamp,datetime,int类型的区别与优劣
- CAS 交互流程
- hbase 和 hive 学习
- 计算机网络通讯
- HDU 4614 线段树(区间更新)+二分
- hdu 4635(tarjan)
- 每日MySQL之013:TIMESTAMP和DATETIME列的自动初始化与自动更新
- 秒速五厘米(为情怀而补的题)
- 【扩展欧几里得】poj 1061 青蛙的约会
- 1.Android灯光系统_led_class驱动
- C++ STL之字符串容器string用法详解
- Linux下常见的软件安装方式
- jsp写一个简单的登录界面,有默认值
- Centos 7下源码安装python 3.6
- LeetCode 7. Reverse Integer