date,datetime,timestamp区别

date:

只有日期，没有时间，范围'1000-01-01' 到 '9999-12-31'。

 

datetime:

有日期，有时间，范围 '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59' ，包含一百万分之一秒小数位。

 

TIMESTAMP：

有日期，有时间，范围 '1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC，包含一百万分之一秒小数。explicit_defaults_for_timestamp =false 时，update为null操作会自动转换为current_timestamp

 

datetime和timestamp特性：

1. 自动初始，要求设置初始值 CURRENT_TIMESTAMP(), NOW(), LOCALTIME, LOCALTIME(), LOCALTIMESTAMP, and

LOCALTIMESTAMP().

2. 自动更新，更改其它列，会更新TIMESTAMP列。避免影响，要设置default值

 

1.列的属性DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP，默认是current_timestamp ,并且会自动更新为current_timestamp

CREATE TABLE t1 (

ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

);

 

2.列的属性DEFAULT CURRENT_TIMESTAMP 和 没有 ON UPDATE CURRENT_TIMESTAMP，默认是current_timestamp ,并且不会自动更新为current_timestamp

CREATE TABLE t1 (

ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

dt DATETIME DEFAULT CURRENT_TIMESTAMP

);

 

给个默认常量，不会自动更新为current_timestamp

CREATE TABLE t1 (

ts TIMESTAMP DEFAULT 0,

dt DATETIME DEFAULT 0

);

 

3. 设置默认常量并ON UPDATE CURRENT_TIMESTAMP，为会设置为默认值，更新会自动更新为current_timestamp

CREATE TABLE t1 (

ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP,

dt DATETIME DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP

);

 

4.ON UPDATE CURRENT_TIMESTAMP ，没有默认常量值。自动更新为current timestamp

timestamp 默认值是 0 ，除非指定为null

CREATE TABLE t1 (

ts1 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- default 0

ts2 TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP -- default NULL

);

 

DATETIME 默认为null ,除非指定为not null ,默认为0

CREATE TABLE t1 (

dt1 DATETIME ON UPDATE CURRENT_TIMESTAMP, -- default NULL

dt2 DATETIME NOT NULL ON UPDATE CURRENT_TIMESTAMP -- default 0

);