| mysql中datetime和timestamp的区别 | 您所在的位置：网站首页 › date和time的区别 › mysql中datetime和timestamp的区别 |
|
时间戳
时间不分东西南北、在地球的每一个角落，每一个国家都是一样的。 在中国时间过去5秒钟，美国同样也是过去了5秒钟，在地球的任何一个角度都是过去了5秒钟，时间戳就是相对于格林威治时间1970年01月01日00时00分00秒起至现在过去的总秒数，这个总秒数在世界上的任何地方都是相同，也就是说时间戳在世界上的任何地方都是相同的，时间戳没有时区的概念。 时间戳强调的是过去时，相对1970年01月01日00时00分00秒起过去了多长时间。 时区我们知道了在任何地方时间戳都是相同的。当我们问现在是什么时候，我们总不能说相对于1970年01月01日00时00分00秒过去了n秒，太模糊了，于是就有了时间的定义，我们可以说现在是2020年03月15号23点53分，方便我们记忆和理解。 时间和实践戳想比较，时间强调的是现在时，现在是何年何月何日，何时何分何妙，而时间戳强调的是过去时，过去了多少秒。 通常我们将每天太阳在最高点的时候定义为12点，而由于地球的自转，导致每个地方太阳出现在最高点的时间又不尽相同，为了统一时间（太阳最高点为12点）于是就出现了时区的概念。 我们中国处于东+8区，美国处于西-5区 最后总结一个表达式：实践戳+时区=当前时间 mysql的时区 查看mysql的时区 mysql> show variables like '%time_zone%'; +------------------+--------+ | Variable_name | Value | +------------------+--------+ | system_time_zone | CST | | time_zone | SYSTEM | +------------------+--------+可以看到Variable_name有俩个变量，system_time_zone是指系统默认的时区,time_zone指当前时区。 可以看到系统默认时区为CST，而当前时区是system,也就是当前时区为系统默认时区，也就是当前时区为CST。 那么CST时区是什么呢？ 修改mysql的时区有俩种方式 命令行修改 set global time_zone = '+8:00'; ##修改mysql全局时区为北京时间，即我们所在的东8区 set time_zone = '+8:00'; ##修改当前会话时区 flush privileges; #立即生效 配置文件my.conf修改 ##在[mysqld]区域中加上 default-time_zone = '+8:00' ## 重启mysql systemctl restart mysql 连接mysql客户端的时区mysql有时区，连接mysql的客户端同样也有时区的问题，例如php,java，我们以php为例介绍 查看php当前时区 echo date_default_timezone_get()在中国默认的时区是RPC,也就是+8:00。 修改php时区 date_default_timezone_set('Europe/Paris');php可用的时区，请看这篇文章。 操作系统时区操作系统也是有时区的,输入date -R查看操作系统时区。看到后面是+0800，也就是代表北京时间。 [root@m1 ~]# date -R Tue, 17 Mar 2020 11:41:46 +0800操作系统时区影响的是依附操作系统获取时间的操作，例如在shell脚本中获取时间-time=$(date "+%Y%m%d-%H%M%S")。 还有我们最常用的crontab定时任务，crontab中的时分秒肯定是依附于操作系统时区的。 以下俩点需要注意系统时区问题。 datetime和timestamp的区别 占用空间 类型 占据字节 表现形式 datetime 8字节 yyyy-mm-dd hh:mm:ss timestamp 4字节 yyyy-mm-dd hh:mm:ss 表示范围由于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 to 2038-01-19 03:14:07.999999存储形式 datetime类型的字段值写入的是什么，存储的就是什么，与时区没有关系。比如写入的是2020-03-10 08:09:30,存储的也是2020-03-10 08:09:30。 timestamp类型的字段值本质存储的是时间戳。比如写入的是2020-03-10 08:09:30，且mysql时区为+8,所以存储的是UNIX_TIMESTAMP(2020-03-10 00:09:30)的值，与时区有关。 表现形式 datetime和timestamp类型的字段，表现的都是yyyy-mm-dd hh:mm:ss，即用户从数据库获取到的数据都是yyyy-mm-dd hh:mm:ss类型的，不会因为timestamp本质存储的是时间戳，而用户获取的就是时间戳，他俩的表现形式是一样的。 timestamp类型的表现形式过程：FROM_UNIXTIME(value)+时区=时间，最终把时间展示出来。 写入形式 datetime和timestamp的写入形式都是一样的，都必须是时间格式的，不能因为timestamp本质存储的是时间戳，然后我们就可以直接写入时间戳。 timestamp类型的写入形式过程：UNIX_TIMESTAMP(时间-时区)=时间戳，然后把该时间戳存起来。 timestamp是不能直接写入时间戳的，实验如下,created_time是timestamp格式的。 INSERT INTO users(name,created_time) values('ff',1584417730); 1054 - Unknown column 'created_time' in 'field list' mysql> INSERT INTO users(name,created_at) values('ff','2020-03-10'); Query OK, 1 row affected (0.01 sec)时区 通过上面的分析我们知道了 类型 时区影响 datetime 没有影响 timestamp 有影响通过上面的分析，我们知道了mysql的时区大概作用于以下俩点 1. timestamp类型的数据，mysql实际存储的是时间戳,而表现形式却是时间，时间戳转化为时间肯定需要时区的转化。时间戳+时区=时间。 2. mysql中的函数FROM_UNIXTIME()(把时间戳转化为时间)和UNIX_TIMESTANMP()(把时间转化为时间戳)也会根据mysql时区进行的转换 mysql存储时间时到底选择datetime，timestamp还是int类型？ datetime和timestamp比大小 datetime类型字段 datetime类型字段存储的值的格式为时间格式，所以按照时间格式来比较大小即可。比如获取2020-03-01以后新注册的用户 select * from users where created_at > `2020-03-01`比如获取2020年以后新注册的用户 select * from users where created_at >= `2020` ### 或者 select * from users where year(created_at) > '2020'timestamp类型字段 timestamp类型的字段对外表现形式和datetime类型是完全一致，所以比较时间大小和datetime完全一致。 可能有的人认为timestamp存储形式是时间戳，那么能不能直接拿时间戳进行比较呢？比如如下sql,发现筛选的数据不对，所以timestamp类型的字段不能直接拿时间戳进行比较的 select * from users where created_at > 21313131 一次线上迁库发生的事故我们的数据库是使用timestamp类型来存储时间的，当前的数据库的时区为+8:00。 有一次我们需要迁库，迁移的那个库没太注意时区，迁移过去之后发现所有用户的每天获取的积分都不对了，获取积分的时间都少了8个小时。 最后反应过来可能是新的数据库的时区与老数据库不一致，查看后发现新数据库的时区果然不是+8:00，时区修改完之后就和之前的时间对上了。 所以存储时间的类型是很重要的，选择时需要考虑清楚。 总结如果数据库中有timestamp类型的字段，mysql数据库不管是迁库，还是集群，都一定要保证时区的相同。如果mysql集群中的数据库时区不一致，timestamp的字段将会造成数据不一致的情况发生。在迁移库或者搭建集群时一定检查时区，保证时区的相同。 中国时区默认是+8,所以不管是单节点mysql，还是mysql集群，我们第一件事就是应该将当前时区time_zone设置为+8:00。 你可能会问连接mysql的客户端的时区（php）与mysql时区不一致会有影响吗？ 首先这个问题肯定是针对timestamp类型的，datetime类型没有这个问题。 其次我们要知道timestamp类型的写入形式也是时间格式，也就是说，在php客户端写入数据库时，timestamp字段对应的值必须是datetime类型的。 就算php客户端和mysql时区不一致，也肯定不会出现以下这种情况： php写入的是2020-02-10 03:10:20,而数据库里面写入的是2020-02-10 11:10:20,因为timestamp的写入形式就是时间格式。简单理解就是php的如果发现数据库中的时间相比于now少了或者多了几个小时,此时肯定是php客户端时区的问题，与mysql时区没关系,理解了下面的俩个过程，肯定就理解了上面所说的了。 timestamp类型的表现形式过程：FROM_UNIXTIME(value)+时区=时间，最终把时间展示出来，存储格式才是时间戳。 timestamp类型的写入形式过程：UNIX_TIMESTAMP(php写入时间-时区)=时间戳，然后把该时间戳存起来。 其实能够发现，只要保证php的时区正确，mysql存入的时间就肯定正确，与mysql的时区并没有关系。mysql时区问题只有在迁移库或者在集群中才会体现。 |
| CopyRight 2018-2019 实验室设备网 版权所有 |