timestamp类型

表结构

create table t1(
id int primary key auto_increment,
dt datetime,
ts timestamp
);

show create table

Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `dt` datetime DEFAULT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

 datetime类型，默认值为null,而timestamp类型，默认值是当前时间，"ON UPDATE CURRENT_TIMESTAMP"表示更新记录时，当前字段的值会刷新 

 

insert into t1 values ();

 执行结果 

+----+------+---------------------+
| id | dt   | ts                  |
+----+------+---------------------+
|  1 | NULL | 2018-06-26 16:22:51 |
+----+------+---------------------+

 

更新记录

update t1 set dt = 180626162707;

select * from t1;

执行结果

+----+---------------------+---------------------+
| id | dt                  | ts                  |
+----+---------------------+---------------------+
|  1 | 2018-06-26 16:27:07 | 2018-06-26 16:27:10 |
+----+---------------------+---------------------+ 

timestamp字段的值同步更新了。

 

TIMESTAMP变体：字段名 timestamp [默认值] [是否同步更新]

1.

ts timestamp

相当于

ts timestamp default currrent_timestamp on update current_timestamp

 创建记录时，使用当前时间，并且更新记录时同步更新该字段

2.

ts timestamp default current_timestamp

创建记录时使用当前时间，但是更新记录时，该字段值保持不变

3.

ts timestamp default "18-06-27 12:23:34"

创建记录时使用特定时间，但是更新记录时，该字段值保持不变

4.

ts timestamp on update current_timestamp

创建记录时，该字段值为"0",更新记录时才会将该字段值设置为当前时间

5.

ts timestamp default 'yyyy-mm-dd hh:mm:ss' on update current_timestamp

创建记录时，使用自定义的时间值，只有等到更新记录时，该字段值才会使用当前时间

 

 

create table t2( 
    id int primary key auto_increment,
    name varchar(20), 
    t1 timestamp, 
    t2 timestamp default current_timestamp, 
    t3 timestamp default '20180627123423', 
    t4 timestamp default current_timestamp on update current_timestamp, 
    t5 timestamp default '20180627123423' on update current_timestamp, 
    t6 timestamp on update current_timestamp 
);

 

结果：

Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `t1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `t2` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `t3` timestamp NOT NULL DEFAULT '2018-06-27 12:34:23',
  `t4` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `t5` timestamp NOT NULL DEFAULT '2018-06-27 12:34:23' ON UPDATE CURRENT_TIMESTAMP,
  `t6` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

 

转载于:https://www.cnblogs.com/bibiafa/p/9229751.html