MySQL技术内幕：时间和日期数据类型-技术开发专区

MySQL技术内幕：时间和日期数据类型

作者：姜承尧编辑：王玉圆 2012-09-05 00:05 IT168网站原创

【IT168 技术】MySQL数据库中有五种与日期和时间有关的数据类型，各种日期数据类型所占空间如表2-1所示。

MySQL技术内幕：时间和日期数据类型

　　DATETIME和 DATE

　　DATETIME占用8字节，是占用空间最多的一种日期类型。它既显示了日期，同时也显示了时间。其可以表达的日期范围为“1000-01-01 00：00：00”到“9999-12-31 23：59：59”。

　　DATE占用3字节，可显示的日期范围为“1000-01-01”到“9999-12-31”。

　　在MySQL数据库中，对日期和时间输入格式的要求是非常宽松的，以下的输入都可以视为日期类型。

　　·2011-01-01 00：01：10

　　·2011/01/01 00+01+10

　　·20110101000110

　　·11/01/01 00@01@10

　　其中，最后一种类型中的“11”有些模棱两可，MySQL数据库将其视为2011还是1911呢?下面来做个测试：

mysql> SELECT CAST('11/01/01 00@01@10' AS DATETIME) AS datetime\G;
*************************** 1. row ***************************
datetime: 2011-01-01 00:01:10
1 row in set (0.00 sec)

　　可以看到数据库将其视为离现在最近的一个年份，这可能不是一个非常好的习惯。如果没有特别的条件和要求，还是在输入时按照标准的“YYYY-MM-DD HH：MM：SS”格式来进行。在MySQL 5.5版本之前(包括5.5版本)，数据库的日期类型不能精确到微秒级别，任何的微秒数值都会被数据库截断，例如：

mysql> CREATE TABLE t ( a DATETIME );
Query OK, 0 rows affected (0.25 sec)

mysql> INSERT INTO t SELECT '2011-01-01 00:01:10.123456';
Query OK, 1 row affected (0.05 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM t\G;
*************************** 1. row ***************************
a: 2011-01-01 00:01:10
1 row in set (0.00 sec)

　　不过MySQL数据库提供了函数MICROSECOND来提取日期中的微秒值，示例如下：

mysql> SELECT MICROSECOND('2011-01-01 00:01:10.123456')\G;
*************************** 1. row ***************************
MICROSECOND('2011-01-01 00:01:10.123456'): 123456
1 row in set (0.00 sec)

　　有意思的是， MySQL的CAST函数在强制转换到DATETIME时会保留到微秒数，不过在插入后同样会截断，示例如下：

mysql> SELECT CAST('2011-02-01 00:01:10.123456' AS DATETIME) D\G;
*************************** 1. row ***************************
D: 2011-02-01 00:01:10.123456
1 row in set (0.00 sec)

mysql> INSERT INTO t
->SELECT CAST('2011-02-01 00:01:10.123456' AS DATETIME);
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM t\G;
*************************** 1. row ***************************
a: 2011-01-01 00:01:10
*************************** 2. row ***************************
a: 2011-02-01 00:01:10
2 rows in set (0.00 sec)

　　然而从MySQL 5.6.4版本开始，MySQL增加了对秒的小数部分(fractional second)的支持，具体语法为：

type_name（fsp）

　　其中，type_name的类型可以是TIME、DATETIME和TIMESTAMP。fsp表示支持秒的小数部分的精度，最大为6，表示微秒(microseconds);默认为0，表示没有小数部分，同时也是为了兼容之前版本中的TIME、DATETIME和TIMESTAMP类型。对于时间函数，如CURTIME()、SYSDATE()和UTC_TIMESTAMP()也增加了对fsp的支持，例如：

mysql> SELECT CURTIME(4) AS TIME\G;
*************************** 1. row ***************************
TIME: 10:22:37.4456
1 rows in set (0.00 sec)

　　注意　MariaDB 5.3版本就对TIME、DATETIME、TIMESTAMP类型的微秒部分进行了支持。详细的说明可以从http://kb.askmonty.org/en/microseconds-in-mariadb中得到。

${PageNumber}

　　TIMESTAMP

　　TIMESTAMP和DATETIME显示的结果是一样的，都是固定的“YYYY-MM-DD HH:MM:SS”的形式。不同的是，TIMESTAMP占用4字节，显示的范围为“1970-01-01 00:00:00”UTC到“2038-01-19 03:14:07”UTC。其实际存储的内容为“1970-01-01 00:00:00”到当前时间的毫秒数。

　　注意　UTC 协调世界时，又称世界统一时间、世界标准时间和国际协调时间。它从英文Coordinated Universal Time和法文Temps Universel Cordonné而来。

　　TIMESTAMP类型和DATETIME类型除了在显示时间范围上有所不同外，还有以下不同：

　　在建表时，列为TIMESTAMP的日期类型可以设置一个默认值，而DATETIME不行。

　　在更新表时，可以设置TIMESTAMP类型的列自动更新时间为当前时间。

　　首先来看一个默认设置时间的例子。

mysql> CREATE TABLE t
    -> ( a INT,
    ->   b TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    -> )ENGINE=InnoDB;
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO t (a) VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t\G;
*************************** 1. row ***************************
a: 1
b: 2011-02-02 16:42:57
1 row in set (0.01 sec)

　　接着来看一个执行UPDATE时更新为当前时间的例子。

mysql> CREATE TABLE t
     -> ( a INT ,
     ->  b TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
     -> );

mysql> INSERT INTO t SELECT 1,CURRENT_TIMESTAMP;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t\G;
*************************** 1. row ***************************
a: 1
b: 2011-02-02 16:45:40
1 row in set (0.01 sec)

# 等待一段时间

mysql> UPDATE t SET a=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM t\G;
*************************** 1. row ***************************
a: 2
b: 2011-02-02 16:47:39
1 row in set (0.00 sec)

　　可以发现在执行UPDATE操作后，b列的时间由原来的16:45:40更新为了16:47:39。如果执行了UPDATE操作，而实际上行并没有得到更新，那么是不会更新b列的，例如：

mysql> SELECT * FROM t\G;
*************************** 1. row ***************************
a: 2
b: 2011-02-02 16:47:39
1 row in set (0.00 sec)

mysql> UPDATE t SET a=2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

mysql> SELECT * FROM t\G;
*************************** 1. row ***************************
a: 2
b: 2011-02-02 16:47:39
1 row in set (0.00 sec)

　　可以看到执行UPDATE t SET a=2并没有改变行中的任何数据，显示Changed:0表示该行实际没有得到更新，故b列并不会进行相应的更新操作。

　　当然，可以在建表时将TIMESTAMP列设为一个默认值，也可以设为在更新时的时间，例如：

mysql> CREATE TABLE t (
   -> a INT,
   -> b TIMESTAMP DEFAULT ON UPDATE CURRENT_TIMESTAMP
   ->)ENGINE=InnoDB;

${PageNumber}

　　YEAR和TIME

　　YEAR类型占用1字节，并且在定义时可以指定显示的宽度为YEAR(4)或YEAR(2)，例如：

mysql> CREATE TABLE t ( a YEAR(2));
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO t SELECT '1990';

mysql> SELECT * FROM t;
+------+
| a |
+------+
| 90 |
+------+
1 row in set (0.00 sec)

　　对于YEAR(4)，其显示年份的范围为1901～2155;对于YEAR(2)，其显示年份的范围为1970～2070。在YEAR(2)的设置下，00～69代表2000～2069年。

　　TIME类型占用3字节，显示的范围为“-838：59：59”～“838：59：59”。有人会奇怪为什么TIME类型的时间可以大于23。因为TIME类型不仅可以用来保存一天中的时间，也可以用来保存时间间隔，同时这也解释了为什么TIME类型也可以存在负值。和DATETIME类型一样，TIME类型同样可以显示微秒时间，但是在插入时，数据库同样会进行截断操作，例如：

mysql> CREATE TABLE t ( a TIME );
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO t SELECT '14:40:20.123456';
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM t;
+----------+
| a |
+----------+
| 14:40:20 |
+----------+
1 row in set (0.00 sec)

关注我们