MySQL字符类型datetime与timestamp

这片博客来详细分区一下这哥俩！

首先来说明这两个字符类型：

DATETIME	8	1000-01-01 00:00:00 ~9999~12-31 23:59:59	0000-00-00 00:00:00
TIMESTAMP（包含时区信息）	4	1970-01-01 08:00:01~2038-01-19 11:14:07	0000-00-00 00:00:00

如上直观的看到timestamp类型占用了更少的字节，但是timestamp表示的时间却是有限的。

这两个值都可以自动初始化并且更新为当前的时间戳，对于表中的任何TIMESTAMP或 DATETIME列，您可以将当前时间戳分配为默认值，自动更新值或两者：

首先说这两个类型都可以做的事：这两个类型都可以设置默认值，也都可以设置自动更新。下面举例说明。

                auto_increment    )

当向表中插入一条数据时，t1~t4会插入当前时间戳的默认值！

mysql> insert into tb5(t5) select NULL;Query OK, 1 row affected (0.05 sec)Records: 1  Duplicates: 0  Warnings: 0mysql> select * from tb5;  #t1~t4因为是以当前时间戳为默认值的，因此相等。+---------------------+---------------------+---------------------+---------------------+------+----+| t1                  | t2                  | t3                  | t4                  | t5   | t6 |+---------------------+---------------------+---------------------+---------------------+------+----+| 2019-01-09 13:58:10 | 2019-01-09 13:58:10 | 2019-01-09 13:58:10 | 2019-01-09 13:58:10 | NULL |  1 |+---------------------+---------------------+---------------------+---------------------+------+----+1 row in set (0.00 sec)mysql>

下面我们修改插入的这一行数值，就可以发现不同了！

mysql> update tb5 set t5="a" where t6=1;Query OK, 1 row affected (0.01 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from tb5;     #看到了什么不一样的地方。t1和t3的值会自动修改，而t2和t4的值不会的。+---------------------+---------------------+---------------------+---------------------+------+----+| t1                  | t2                  | t3                  | t4                  | t5   | t6 |+---------------------+---------------------+---------------------+---------------------+------+----+| 2019-01-09 14:00:49 | 2019-01-09 13:58:10 | 2019-01-09 14:00:49 | 2019-01-09 13:58:10 | a    |  1 |+---------------------+---------------------+---------------------+---------------------+------+----+1 row in set (0.00 sec)

default current_timestamp: 只是会设置字段值为当前时间戳，不会随着记录中其他字段的更新而更新。

on update current_timestamp: 则是表示字段值，会随着记录中其他字段值更新而更新为当前时间戳。

官方文档上说timestamp默认数值为0，datetime默认值为null，但是下面语句却执行失败！

                ( auto_increment

上面的t1和t3只要有对应记录中字段数值的改变那么t1和t3的值就会更新。如果想不让他们自动自动更新，除了修改其默认属性（也就是不添加on update current_timestamp）外，MySQL还有一个变量控制这个行为：explicit_defaults_for_timestamp，其默认值为

mysql show variables  "explicit Variable_name                    Value  explicit_defaults_for_timestamp      row   ( sec)#默认数值为off，把其设置为on即可！

测试一下：

mysql> set global explicit_defaults_for_timestamp="ON";       #设置这个参数值为ONQuery OK, 0 rows affected (0.00 sec)mysql> set session explicit_defaults_for_timestamp="ON";Query OK, 0 rows affected (0.00 sec)mysql> show variables like "%explicit%";       +---------------------------------+-------+| Variable_name                   | Value |+---------------------------------+-------+| explicit_defaults_for_timestamp | ON    |+---------------------------------+-------+1 row in set (0.01 sec)mysql> insert into tb5(t5) select NULL;              #插入数据，Query OK, 1 row affected (0.00 sec)Records: 1  Duplicates: 0  Warnings: 0mysql> select * from tb5;                            #查看其第二行的数据+---------------------+---------------------+---------------------+---------------------+------+----+| t1                  | t2                  | t3                  | t4                  | t5   | t6 |+---------------------+---------------------+---------------------+---------------------+------+----+| 2019-01-09 14:00:49 | 2019-01-09 13:58:10 | 2019-01-09 14:00:49 | 2019-01-09 13:58:10 | a    |  1 || 2019-01-09 14:44:08 | 2019-01-09 14:44:08 | 2019-01-09 14:44:08 | 2019-01-09 14:44:08 | NULL |  2 |+---------------------+---------------------+---------------------+---------------------+------+----+2 rows in set (0.00 sec)mysql> update tb5 set t5="b" where t6=2;              #更改第二行的字段值Query OK, 1 row affected (0.01 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from tb5;                             #可以看到t1和t3的值还是自动更新了+---------------------+---------------------+---------------------+---------------------+------+----+| t1                  | t2                  | t3                  | t4                  | t5   | t6 |+---------------------+---------------------+---------------------+---------------------+------+----+| 2019-01-09 14:00:49 | 2019-01-09 13:58:10 | 2019-01-09 14:00:49 | 2019-01-09 13:58:10 | a    |  1 || 2019-01-09 14:44:41 | 2019-01-09 14:44:08 | 2019-01-09 14:44:41 | 2019-01-09 14:44:08 | b    |  2 |+---------------------+---------------------+---------------------+---------------------+------+----+2 rows in set (0.00 sec)

原因是什么？explicit_defaults_for_timestamp的数值会影响表结构，当表结构已经创建，那么再设置这个数值是不会起作用的。并且在定义的时候不需要显示指定on update current_timestamp参数。

可以参考这片博客：https://www.cnblogs.com/JiangLe/p/6956865.html

二者的不同

上面提到的都是这两个类型都可以做的事，下面来说明二者的不同之处。

1：上面已经提到的二者占用的大小不同，datetime占用8个字节，而timestamp占用4个字节。

2：还有就是表示的时间范围不同。

3：timestamp的时间显示值依赖于时区；如果在多个时区存储或访问数据，timestamp和datetime的行为将很不一样。timestamp提供的数值和时区有关系，而datetime则是文本表示的日期和时间。

除了特殊的行为，通常也应该尽量使用TIMESTAMP。

如果要存储比秒更小粒度的日期和时间怎么办？MySQL目前没有提供合适的数据类型，但是可以使用自己的存储格式；可以使用BIGINT类型存储微妙级别的问题，或者使用DOUBLE存储秒之后的小数部分。也可以使用mariadb或者percona server。

二者的不同

更多相关文章

随机推荐