如何更新MySQL表的时间戳字段？
让我们首先创建一个表-
mysql> create table DemoTable -> ( -> PunchOut timestamp, -> PunchStatus tinyint(1) -> );Query OK, 0 rows affected (0.51 sec)
使用插入命令在表中插入一些记录-
mysql> insert into DemoTable values('2019-01-31 6:30:10',1);Query OK, 1 row affected (0.22 sec)mysql> insert into DemoTable values('2019-02-06 4:10:13',0);Query OK, 1 row affected (0.14 sec)mysql> insert into DemoTable values('2018-12-16 03:00:30',0);Query OK, 1 row affected (0.16 sec)mysql> insert into DemoTable values('2016-11-25 02:10:00',1);Query OK, 1 row affected (0.22 sec)使用selectstatemen显示表中的所有记录-
mysql> select *from DemoTable;
输出结果
+---------------------+-------------+ | PunchOut | PunchStatus | +---------------------+-------------+ | 2019-01-31 06:30:10 | 1 | | 2019-02-06 04:10:13 | 0 | | 2018-12-16 03:00:30 | 0 | | 2016-11-25 02:10:00 | 1 | +---------------------+-------------+ 4 rows in set (0.00 sec)
这是更新MySQL表的timestamp字段的查询。我们使用PunchStatus0将字段设置为当前日期-
注意-当前日期和时间是2019-06-3013:43:45
mysql> update DemoTable set PunchOut=now() where PunchStatus=0;Query OK, 2 rows affected (0.19 sec)Rows matched: 2 Changed: 2 Warnings: 0
让我们再次检查表记录-
mysql> select *from DemoTable;
输出结果
+---------------------+-------------+ | PunchOut | PunchStatus | +---------------------+-------------+ | 2019-01-31 06:30:10 | 1 | | 2019-06-30 13:43:45 | 0 | | 2019-06-30 13:43:45 | 0 | | 2016-11-25 02:10:00 | 1 | +---------------------+-------------+ 4 rows in set (0.00 sec)