MySQL日期和时间数据类型及格式
2020-04-22 本文已影响0人
月饮沙
本文问题
- MySQL中包含哪些代表日期和时间的数据类型？
- 日期和时间可以用哪些格式表示？
- 各种日期和时间的有效值范围是多少？
-
DATETIME和TIMESTAMP有什么区别和联系？ - 哪种日期格式可以自动更新？规则是怎样的？
- 设置日期列的自动更新时，可以使用哪些函数来表示当前时间？
- 如何控制对日期和时间无效值的处理？
- 用不同格式表示
TIME列值时有什么注意事项？
日期和时间数据类型
MySQL中可以使用以下5种数据类型表示日期或时间DATE,TIME,DATETIME,TIMESTAMP,YEAR。每种数据类型都有有效值范围，超出范围的值会自动转换为'零'值。
可以用多种格式表示日期和时间，但是日期部分顺序必须为年月日。
有效值范围及'零'值
| 数据类型 | 有效值范围 | '零'值 |
|---|---|---|
| DATA | '1000-01-01' - '9999-12-31' |
'0000-00-00' |
| TIME | '-838:59:59.000000' - '838:59:59.000000' |
'00:00:00' |
| DATETIME | '1000-01-01 00:00:00.000000' - '9999-12-31 23:59:59.999999' |
'0000-00-00 00:00:00' |
| TIMESTAMP | '1970-01-01 00:00:01.000000' - '2038-01-19 03:14:07.999999' |
'0000-00-00 00:00:00' |
| YEAR |
1901 - 2155,0000
|
0000 |
无效值的处理
默认情况下，MySQL会将超出范围的时间和日期转换为对应的'零'值。但是超出范围的TIME值会转换为最近的极限值（±838:59:59）。
可以通过设置sql_mode来控制MySQL是否允许无效日期：
-
是否允许日期中的零值
-
NO_ZERO_IN_DATE
是否允许在日期的月或日中包含0值 -
NO_ZERO_DATE
是否允许'0000-00-00'作为有效日期
效果：
- 未启用，允许插入并且不会产生警告
- 启用，允许插入但是会产生警告
- 和严格模式（
STRICT_ALL_TABLES|STRICT_TRANS_TABLES）同时启用 ：不允许插入，除非使用INSERT IGNORE或者UPDATE IGNORE，使用IGNORE会将无效日期作为0000-00-00插入。
-
-
检查日期合理性
-
ALLOW_INVALID_DATES
不全面检查日期是否合理，只检查月（1-12）和日（1-31）的范围
应用于DATE和DATETIME列。TIMESTAMP列不受影响，必须是个有效日期。
-
时间精度
TIME,DATETIME和TIMESTAMP值可以包含小数位，最多精确到微秒（6位）。小数部分与其他时间之间必须使用小数点（.）来进行分隔。
可以在创建表时使用type_name(fsp)来指定精度，例如：
CREATE TABLE t1 (t TIME(3), dt DATETIME(6), ts TIMESTAMP(0));
精度（fsp）的取值范围是0-6，默认值为0
不同数据类型的特点
DATE,DATETIME,TIMESTAMP区别和联系
这三种数据类型都能用来表示日期，不同的是，DATE只能表示日期，没有时间部分，而另外两种都能表示日期和时间。
DATETIME和TIMESTAMP都可以同时表示日期和时间，DATETIME的取值范围更广，是'1000-01-01 00:00:00.000000' - '9999-12-31 23:59:59.999999',而TIMESTAMP的取值范围只有'1970-01-01 00:00:01.000000' - '2038-01-19 03:14:07.999999'
DATETIME和TIMESTAMP的区别：
- 取值范围不同
- 默认值和是否自动更新不同
| 类型 | 取值范围 | 默认值 | 是否自动更新 |
|---|---|---|---|
DATETIME |
1000-01-01 00:00:00-9999-12-31 23:59:59
|
NULL |
否 |
TIMESTAMP |
1970-01-01 00:00:01-2038-01-19 03:14:07
|
CURRENT_TIMESTAMP |
是 |
mysql> create table test_date (
-> id int primary key,
-> dt1 datetime,
-> dt2 datetime default current_timestamp,
-> dt3 datetime default current_timestamp on update current_timestamp,
-> dt4 datetime on update current_timestamp,
-> dt5 datetime not null on update current_timestamp,
-> ts1 timestamp,
-> ts2 timestamp default current_timestamp,
-> ts3 timestamp default current_timestamp on update current_timestamp,
-> ts4 timestamp on update current_timestamp,
-> ts5 timestamp null on update current_timestamp
-> );
Query OK, 0 rows affected (0.04 sec)
mysql> desc test_date;
+-------+-----------+------+-----+---------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------------------+-----------------------------+
| id | int(11) | NO | PRI | NULL | |
| dt1 | datetime | YES | | NULL | |
| dt2 | datetime | YES | | CURRENT_TIMESTAMP | |
| dt3 | datetime | YES | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| dt4 | datetime | YES | | NULL | on update CURRENT_TIMESTAMP |
| dt5 | datetime | NO | | NULL | on update CURRENT_TIMESTAMP |
| ts1 | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| ts2 | timestamp | NO | | CURRENT_TIMESTAMP | |
| ts3 | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| ts4 | timestamp | NO | | 0000-00-00 00:00:00 | on update CURRENT_TIMESTAMP |
| ts5 | timestamp | YES | | NULL | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+---------------------+-----------------------------+
11 rows in set (0.00 sec)
DATETIME和TIMESTAMP列值可以自动初始化或者更新为当前时间
DATETIME和TIMESTAMP的自动初始化和自动更新
- 自动初始化：
在插入数据时，如果该列没有值，则设置一个默认值。默认值可以设置为当前时间，也可以设置为一个固定值。 - 自动更新：
当行中的其他列值进行变更时，如果没有指定该列值，该列值自动更新为当前时间。
使用DEFAULT来设置默认值，使用ON UPDATE来设置自动更新值。以下函数都可以用来表示当前时间：
CURRENT_TIMESTAMP,CURRENT_TIMESTAMP(), NOW(), LOCALTIME, LOCALTIME(), LOCALTIMESTAMP, LOCALTIMESTAMP().
DEFAULT和ON UPDATE可以单独配置。只配置ON UPDATE时，根据列是否允许NULL值，自动配置默认值
示例：
# 只设置默认值
CREATE TABLE t1 (
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
dt DATETIME DEFAULT CURRENT_TIMESTAMP
);
# 设置默认值和自动更新
CREATE TABLE t1 (
ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP,
dt DATETIME DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP
);
# 只设置自动更新
# 允许NULL的默认值就是NULL，不允许NULL的默认值就是0
CREATE TABLE t1 (
ts1 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- default 0
ts2 TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP -- default NULL
);
CREATE TABLE t1 (
dt1 DATETIME ON UPDATE CURRENT_TIMESTAMP, -- default NULL
dt2 DATETIME NOT NULL ON UPDATE CURRENT_TIMESTAMP -- default 0
);
TIME
TIME的取值范围是'-838:59:59'- '838:59:59'。取值范围超过24小时，是因为TIME类型不仅可以表示一天中的某个时间，也可以用来表示时间间隔。
TIME格式
需要注意TIME的格式，单独:分隔符表示时分，但是无分隔符的字符串或者表示秒。
'11:12'='11:12:00'
'1112'或1112=00:11:12
默认情况下，超过TIME范围但是有效的值被转换为最近的极限值。无效值被转换为00:00:00
YEAR
YEAR(2) 由于无法准确表示年份，MySQL5.7版本起已不建议使用，建议使用YEAR(4)
日期和时间格式
日期和时间值可以使用字符串或者数字来进行表示
如果不包含分隔符的字符串或者数字作为日期或时间是没有意义的，会转换为相应数据类型的'零'值
DATE格式
-
'YYYY-MM-DD'或者'YY-MM-DD'任何标点符号都可以作为分隔符。如'2020-04-22',2020%04%22 -
'YYYYMMDD'或者'YYMMDD'不包含分隔符的字符串也可以作为日期格式。如'20200422' -
YYYYMMDD或者YYMMDD数值可以也可以用作日期。如20200422
DATETIME和TIMESTAMP格式
-
'YYYY-MM-DD hh:mm:ss'或者'YY-MM-DD hh:mm:ss'也是任何标点符号都可以作为分隔符，但是日期和时间的分隔符不能相同。例如'2020-04-22 14:32:30','2020^04^22 14*32*30'
日期和时间之间可以通过T来进行分隔，'2020-04-22 14:32:30'等于'2020-04-22T14:32:30' -
'YYYYMMDDhhmmss'或者'YYMMDDhhmmss'不包含分隔符的字符串也可以。如'20200422143230' -
YYYYMMDDhhmmss或者YYMMDDhhmmss数字也可以。如20200422143230
注意事项 -
DATETIME和TIMESTAMP值可以包含小数，最多精确到微秒（6位）。小数部分与其他时间之间必须使用小数点（.）来进行分隔 - 只包含两位数字年份的日期时间是不确定的，MySQL根据以下规则将其转换为具体年份
-
70-99=1970-1999 -
00-69=2000-2069
-
TIME格式
-
'D hh:mm:ss','hh:mm:ss'，'hh:mm'，'D hh:mm'，'D hh'，或'ss'。D代表天，取值范围是0-34 -
'hhmmss'无分隔字符串格式 -
hhmmss,mmss,ss数字格式
问题答案
- MySQL中包含哪些代表日期和时间的数据类型？
DATE,DATETIME,TIMESTAMP,TIME,YEAR - 日期和时间可以用哪些格式表示？
有分割字符串，无分割字符串，数字。
'YYYY-MM-DD hh:mm:ss','YYYYMMDDhhmmss',YYYYMMDDhhmmss - 各种日期和时间的有效值范围是多少？
-
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' - '2038-01-19 03:14:07' -
TIME:'-838:59:59' - '838:59:59' -
YEAR:1901-2155
2038-01-19 03:14:08是32位系统的最大时间
-
DATETIME和TIMESTAMP有什么区别和联系？- 共同点
两者都可以用来表示时间和日期
两者都可以设置默认值为当前时间以及自动更新值为当前时间 - 不同点
取值范围不同：
DATETIME:1000-01-01 00:00:00-9999-12-31 23:59:59
TIMESTAMP:1970-01-1 00:00:01-2038-01-19 03:14:07
不带参数创建时的属性不同：
DATETIME:NULL DEFAULT NULL
TIMESTAMP:NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
- 共同点
- 哪种日期格式可以自动更新？规则是怎样的？
TIMESTAMP和DATETIME都可以自动更新。
当其他列的值改变时，并且没有指定该列值，列值会自动更新为当前时间。
如果使用了UPDATE语句，但是语句没有对值进行更改，则时间不会更新。 - 设置日期列的自动更新时，可以使用哪些函数来表示当前时间？
CURRENT_TIMESTAMP
CURRENT_TIMESTAMP()
NOW()
LOCALTIME
LOCALTIME()
LOCALTIMESTAMP
LOCALTIMESTAMP() - 如何控制对日期和时间无效值的处理？
默认情况下，无效值会转换为对应数据类型的'零'值。超出有效范围的TIME列值会转换为极限值±838:59:59
使用sql_mode可以改变数据库对无效日期的处理
ALLOW_INVALID_DATE:对于DATE和DATETIME列值，只检查月和日的范围(1-12,1-31)
NO_ZERO_DATE和NO_ZERO_IN_DATE控制'零'值和月日中包含0的值是否有效。
当sql_mode中包含严格模式STRICT_ALL_TABLES或者STRICT_TRANS_TABLES时，并包含这两个选项时，值无效并且不允许插入。可以使用INSERT IGNORE或UPDATE INGORE强制插入值，无效值转换为0000-00-00
当sql_mode中不包含严格模式时，无论是否包含这两个选项，都可以将值插入到表中，区别是，包含这两个选项会产生警告，不包含这两个选项不会产生警告。 - 用不同格式表示
TIME列值时有什么注意事项？
用:分割字符串的表示时分'11:12'='11:12:00'
没有分割的字符串或者数字从秒开始'1112'或者1112表示'00:11:12'
