mysql时间戳和datetime对比

MySQL时间设置注意事项的深入总结

时间真的存在吗?有观点认为,时间只是人类构想出来的一种概念,是用来衡量事物变化的标准。对于数据库来说时间伴随着数据并进。进入mysql时间漩涡中看看。

1.时间类型的字段

 

mysql时间类型字段:

mysql时间戳和datetime对比(MySQL时间设置注意事项的深入总结)

下面的容易忽略的内容:

timestamp保存数据方式:

mysql将timestamp值从当前时区转换为utc进行存储,并从utc返回到当前时区进行检索。
(这不适用于其他类型,比如datetime。)默认情况下,每个连接的当前时区是服务器的时间。时区可以在每个连接的基础上设置。只要时区设置保持不变,就会返回所存储的相同值。如果存储一个时间戳值,然后更改时区并检索该值,则检索到的值与存储的值不同。出现这种情况是因为没有在两个方向上使用相同的时区进行转换。当前时区可以作为time_zone系统变量的值。

mysql时间戳和datetime对比(MySQL时间设置注意事项的深入总结)

timestamp和sql_mode组合

sql_mode也会影响timestamp值:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • mysql> create table ts (
  •          id integer not null auto_increment primary key,
  •          col timestamp not null
  •      ) auto_increment = 1;
  •  
  • mysql> show variables like  '%sql_mode%';
  • +---------------+---------------------+
  • | variable_name | value               |
  • +---------------+---------------------+
  • | sql_mode      | strict_trans_tables |
  • +---------------+---------------------+
  • mysql>  insert into ts (col) values ('1969-01-01 01:01:10');
  • error 1292 (22007): incorrect datetime value: '1969-01-01 01:01:10' for column 'col' at row 1
  •  
  • mysql> set sql_mode="";
  • query ok, 0 rows affected (0.00 sec)
  • mysql> show variables like  '%sql_mode%';
  • +---------------+-------+
  • | variable_name | value |
  • +---------------+-------+
  • | sql_mode      |       |
  • +---------------+-------+
  • mysql>  insert into ts (col) values ('1969-01-01 01:01:10'),('2999-01-01 01:01:10');
  • query ok, 2 rows affected, 2 warnings (0.01 sec)
  • records: 2  duplicates: 0  warnings: 2
  • mysql> show warnings;
  • +---------+------+----------------------------------------------+
  • | level   | code | message                                      |
  • +---------+------+----------------------------------------------+
  • | warning | 1264 | out of range value for column 'col' at row 1 |
  • | warning | 1264 | out of range value for column 'col' at row 2 |
  • +---------+------+----------------------------------------------+
  •  
  • mysql> select * from ts;
  • +----+---------------------+
  • | id | col                 |
  • +----+---------------------+
  • |  1 | 0000-00-00 00:00:00 |
  • |  2 | 0000-00-00 00:00:00 |
  • +----+---------------------+
  • 2 rows in set (0.00 sec)
  • 通过控制sql_mode,超出timestamp限制值还是插入进去了,但采用的是0填空方式。
    对于strict_trans_tables, mysql将一个无效的值转换为最接近的有效值,然后插入调整后的值。如果缺少一个值,mysql将为列数据类型插入隐式的默认值。

    2.explicit_defaults_for_timestamp时间处理机制

     

    mysql时间戳和datetime对比(MySQL时间设置注意事项的深入总结)

    默认情况是启用。

    在mysql 8.0.22中,如果试图在声明为timestamp not null的列中插入null,将会被拒绝,并产生错误。

    1.explicit_defaults_for_timestamp被禁用时:

    2.explicit_defaults_for_timestamp被启用:

    mysql时间戳和datetime对比(MySQL时间设置注意事项的深入总结)

    测试:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • create table `test1`(
  • id bigint not null auto_increment comment '主键id',
  • name varchar(20) comment '主键id',
  • create_time timestamp  not null default current_timestamp comment 'cr time',
  • primary key(id)
  • )engine=innodb  auto_increment=1 ;
  • show variables like 'explicit_defaults_for_timestamp';
  • set  global  explicit_defaults_for_timestamp=on;
  • set  global  explicit_defaults_for_timestamp=off;
  • insert into test1(id,name,create_time) values(1,'kit',null);
  • 3.mysql系统配置

     

    系统相关事件参数包含3个:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • mysql>show global variables where variable_name like '%time_zone%' or variable_name like 'log_timestamp%';
  • +------------------+--------+
  • | variable_name    | value  |
  • +------------------+--------+
  • | system_time_zone | cst    |
  • | time_zone        | system |
  • | log_timestamps   | utc    |
  • +------------------+--------+
  • 3 rows in set (0.00 sec)
  • 1.system time zone:当服务器启动时,它尝试自动确定主机的时区,并使用它来设置system_time_zone系统变量。此后该值不会改变。

    2.time_zone:全time_zone表示服务器当前运行的时区。初始的time_zone值为“system”,表示服务器时区与系统时区一致。

    备注:mysql还提供时区导入到mysql系统库的方法。通过mysql_tzinfo_to_sql程序加载/usr/share/zoneinfom下的时区信息。

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • mysql> select count(*) from mysql.time_zone_name;
  • +----------+
  • | count(*) |
  • +----------+
  • |        0 |
  • +----------+
  • ##mysql_tzinfo_to_sql工具导入时区值

  • ?
  • 1
  • shell>mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql
  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • mysql> select count(*) from mysql.time_zone_name;
  • +----------+
  • | count(*) |
  • +----------+
  • |     1780 |
  • +----------+
  • 3.log_timestamps

    这个变量控制写入错误日志的消息以及写入文件的一般查询日志和慢速查询日志消息中的时间戳的时区。

    它不会影响一般查询日志的时区和慢速查询日志消息写入表(mysql。general_log mysql.slow_log)。

    允许的log_timestamps值是utc(默认值)和system(本地系统时区)

    备注:utc一般指协调世界时。协调世界时,又称世界统一时间、世界标准时间、国际协调时间,就是utc+8小时=中国时间

    当然值 需要跟系统记录时间一致,才能更好的管理。

  • ?
  • 1
  • 2
  • #设置时区,更改为东八区
  • set global time_zone = '+8:00';  
  • mysql时间戳和datetime对比(MySQL时间设置注意事项的深入总结)

    建议:

    mysql配置文件my.cnf

  • ?
  • 1
  • 2
  • 3
  • [mysqld]
  • log_timestamps=system
  • default-time_zone                  = '+8:00'
  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • mysql>show global variables where variable_name like '%time_zone%' or variable_name like 'log_timestamp%';
  • +------------------+--------+
  • | variable_name    | value  |
  • +------------------+--------+
  • | log_timestamps   | system |
  • | system_time_zone | cst    |
  • | time_zone        | +08:00 |
  • +------------------+--------+
  • 总结

     

    从时间类型,参数,系统时区了解到,mysql里时间应该怎样设置和使用。

    特别是无特殊要求sql_mode不要轻易改动。

    到此这篇关于mysql时间设置注意事项的文章就介绍到这了,更多相关mysql时间设置注意内容请搜索开心学习网以前的文章或继续浏览下面的相关文章希望大家以后多多支持开心学习网!

    原文链接:https://www.modb.pro/db/53474

    标签: