日期和时间类型
Mysql数据中总共有5种于日期和时间有关的数据类型
| 类型 | 所占空间 |
| — | — |
| DATETIME | 8个字节 |
| TIMESTAMP | 4个字节 |
| DATE | 3个字节 |
| TIME | 3个字节 |
| YEAR | 1个字节 |
DATITIME和DATE
DATETIME是占用空间最多的一种类型,占用了8个字节,可以显示日期同时也可以显示时间,其可以表达的范围为1000-01-01 00:00:00到9999-12-31 23:59:59。而DATE只可以表示日期,不可以显示时间,其可以表达的范围为1000-01-01到9999-12-31。
在mysql数据库中,对日期和输入格式的要求是十分宽松的,以下的输入都可以视为日期类型
2011-01-01 00:01:00
2011/01/01 00+01+10
20110101000110
11/01/01 00@01@10
其中,最后一种类型的年上面的11是表示离当前时间最近的年份。
SELECT CAST(‘11/01/01 00@01/10’ AS DATETIME) AS datetime;
数据库的日期类型不能精确到微秒级别,任何的微妙数值都会被数据库截断。
CREATE TABLE t2(
a DATETIME
)
//插入微妙级别的数据
INSERT INTO t2 SELECT ‘2011-01-01 00:01:10.123456’;
//查询插入的数据
SELECT a FROM t2;
不过mysql数据库提供了函数MICROSECOND函数来提取日期中的微秒值。
SELECT MICROSECOND(‘2011-01-01 00:01:10.123456’);
mysql的CAST函数在强制转换到DATETIME时也会截断毫秒数,在插入之后同样会截断。
mysql从5.6.4版本开始,mysql增加了对秒的小数部分(fractional second)的支持,其具体语法是type_name(fsp)。
其中,type_name可以是TIME、DATETIME、和TIMESTAMP。fsp表示支持秒的小数部分的精度,最大为6,精确到微秒;默认是为0,表示没有小数部分,同时这也是为了兼容之前的DATETIEM和TIMESTAMP的用法,而对于一些时间函数,也增加了对fsp的支持,比如CURTIME()、SYSDATE()和UTC_TIMESTAMP()。
SELECT CURTIME(4) AS ‘time’;
#### TIMESTAMP
TIMESTAMP和DATETIME显示的效果是一样的,都是固定的YYYY-MM-DD HH:MM:SS类型,但TIMESTAMP只有4个字节,那是因为显示范围的不同,TIMESTAMP的显示范围是从UNIX的时间纪元开始(1970-01-01 00:00:00 UTC)到2038-01-19 03:14:07 UTC(Integer类型是4个字节,也就是32位,能表示的最长时间是68年,UTC是世界统一时间),而其实际存储的内容是1970-01-01 00:00:00到当前时间的毫秒数。
TIMESTAMP和DATETIME的不同
- 在建表时,列为TIMESTAMP类型的可以设置默认值而DATETIME不行
//虽然执行这条语句不会报错,但表中的a列信息还是没有默认值
CREATE TABLE t3(
a DATETIME DEFAULT ‘2011-01-01 00:01:10’
)
//不会报错,且有默认值信息
CREATE TABLE t4(
b TIMESTAMP DEFAULT ‘2011-01-01 00:01:10’
)
- 在更新表时,可以设置TIMESTAMP类型的列自动更新为当前时间
CREATE TABLE t5(
a INT ,
b TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
INSERT INTO t5(a) VALUES(1);
SELECT * FROM t5;
//执行UPDATE时更新为当前时间的例子
CREATE