本文转载自微信公众号《数据与云》，作者数据与云。转载本文请联系数据和云公众号。时间真的存在吗？有人认为，时间只是人类构想出来的一个概念，是用来衡量事物变化的标准。对于数据库而言，时间与数据息息相关。让我们来看看MySQL时间漩涡的内部情况。一、时间类型的字段MySQL时间类型字段：以下是容易被忽略的内容：TIMESTAMP数据存储方式：MySQL将TIMESTAMP值从当前时区转换为UTC进行存储，从UTC返回当前时区进行检索.（这不适用于其他类型，例如DATETIME。）默认情况下，每个连接的当前时区是服务器的时间。可以在每个连接的基础上设置时区。只要时区设置保持不变，就会返回存储的相同值。如果您存储时间戳值，然后更改时区并检索该值，则检索到的值与存储的值不同。发生这种情况是因为转换不是在两个方向上使用相同的时区完成的。当前时区可以用作time_zone系统变量的值。TIMESTAMP和SQL_MODE组合sql_mode也会影响时间戳值：mysql>CREATETABLEts(idINTEGERNOTNULLAUTO_INCREMENTPRIMARYKEY,colTIMESTAMPNOTNULL)AUTO_INCREMENT=1;mysql>SHOWVARIABLESLIKE'%sql_mode%';+----------------+-------------------+|Variable_name|Value|+----------------+----------------------+|sql_mode|STRICT_TRANS_TABLES|+----------------+--------------------+mysql>INSERTINTOTs(col)VALUES('1969-01-0101:01:10');ERROR1292(22007):Incorrectdatetimevalue:'1969-01-0101:01:10'forcolumn'col'atrow1mysql>SETsql_mode="";QueryOK,0rowsaffected(0.00sec)mysql>SHOWVARIABLESLIKE'%sql_mode%';+----------------+--------+|变量名|值|+----------------+--------+|sql_mode||+----------------+--------+mysql>INSERTINTOTs(col)VALUES('1969-01-0101:01:10'),('2999-01-0101:01:10');QueryOK,2rowsaffected,2warnings(0.01秒）记录：2重复：0警告：2mysql>showwarnings;+--------+------+------------------------------------------+|级别|代码|消息|+---------+--------+----------------------------------------------+|Warning|1264|Outofrangevalueforcolumn'col'atrow1||Warning|1264|Outofrangevalueforcolumn'col'atrow2|+--------+------+--------------------------------------------+mysql>SELECT*FROMTS;+----+--------------------+|id|col|+----+---------------------+|1|0000-00-0000:00:00||2|0000-00-0000:00:00|+----+--------------------+2rowsinset(0.00sec)通过控制sql_mode，超过时间戳限制值仍然插入，但是用0补空。对于STRICT_TRANS_TABLES，MySQL会将一个无效值转换为最接近的有效值，并对调整后的值进行插值。如果缺少某个值，MySQL将为该列数据类型插入一个隐式默认值。2.默认启用explicit_defaults_for_timestamp时间处理机制。在MySQL8.0.22中，尝试将NULL插入到声明为TIMESTAMPNOTNULL的列中将被错误拒绝。1）当禁用explicit_defaults_for_timestamp时：未明确声明为NULL属性的时间戳列将自动声明为NOTNULL属性。允许将NULL分配给此类列并将该列设置为当前时间戳。在MySQL8.0.22中，尝试将NULL插入到声明为TIMESTAMPNOTNULL的列中将被错误拒绝。如果表中的第一列未使用NULL属性或显式DEFAULT或ONUPDATE属性进行声明，则它会自动使用默认的CURRENT_TIMESTAMP属性和ONUPDATECURRENT_TIMESTAMP属性进行声明。如果未使用NULL属性或显式默认属性明确声明，则TIMESTAMP自动声明为默认值“0000-00-0000:00:00”（“零”时间戳）。根据启用的是严格SQL模式还是NO_ZERO_DATESQL模式，默认值“0000-00-0000:00:00”可能无效。2)explicit_defaults_for_timestamp已启用：无法为TIMESTAMP指定NULL值以将其设置为当前时间戳。要指定当前时间戳，请设置为CURRENT_TIMESTAMP或同义词，例如NOW()。未使用notNULL属性显式声明的TIMESTAMP列将自动使用NULL属性声明并允许空值。使用NOTNULL属性声明的时间戳列不允许空值。如果启用了严格SQL模式，则为此类列指定NULL的插入将导致单行插入错误，或者对于禁用严格SQL模式的多行插入，将导致“0000-00-0000:00:00”错误。在任何情况下，将NULL值分配给列都不会将其设置为当前时间戳。使用NOTNULL属性显式声明且没有显式默认属性的时间戳列被认为没有默认值。对于没有为此类列指定显式值的插入行，结果取决于SQL模式。如果启用严格SQL模式，则会发生错误。如果未启用严格SQL模式，则使用默认隐式值“0000-00-0000:00:00”声明该列，并出现警告。时间戳类型字段不会自动使用默认的CURRENT_TIMESTAMP属性或更新CURRENT_TIMESTAMP属性声明。这些属性必须明确指定。测试：CREATETABLE`test1`(idbigintnotnullAUTO_INCREMENTCOMMENT'主键ID',namevarchar(20)COMMENT'主键ID',create_timeTIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'crtime',PRIMARYKEY(id))ENGINE=InnoDBAUTO_INCREMENT=1;SHOWVARIABLESLIKE'explicit_defaults_for_timestamp';SETGLOBALexplicit_defaults_for_timestamp=ON;SETGLOBALexplicit_defaults_for_timestamp=OFF;INSERTINTOtest1(id,name,create_time)VALUES(1,'Kit',NULL);3.MySQL系统配置系统相关事件参数包括3个：mysql>showglobalvariableswhereVariable_namelike'%time_zone%'orVariable_namelike'log_timestamp%';+---------------+--------+|Variable_name|Value|+------------------+--------+|system_time_zone|CST||time_zone|SYSTEM||log_timestamps|UTC|+----------------+---------+3rowsinset(0.00sec)1)系统时区：服务器启动时，会尝试自动确定主机的时区并使用它来设置system_time_zone系统变量。之后该值不会改变。2）time_zone：全称time_zone，表示服务器当前运行所在的时区。time_zone初始值为“SYSTEM”，表示服务器时区与系统时区一致。如果设置为SYSTEM，如MySQL函数调用将调用一个系统库来确定当前系统时区。此调用可能受到全局互斥锁的保护，从而导致争用。高CPU使用率问题。设置会话时区会影响时区敏感时间值的显示和存储。这包括由NOW()或CURTIME()等函数显示的值，以及存储在时间戳列中和从中检索的值。timestamp列的值将从会话时区转换为UTC以供存储，并从UTC转换为会话时区以供检索。会话时区设置不影响UTC_TIMESTAMP()等函数显示的值，也不影响DATE、time或DATETIME列中的值。这些数据类型的值也不存储在UTC中；时区仅在从时间戳值转换时适用于它们。备注：MySQL也提供了将时区导入MySQL系统库的方法。通过mysql_tzinfo_to_sql程序加载/usr/share/zoneinfom下的时区信息。mysql>SELECTCOUNT(*)FROMmysql.time_zone_name;+--------+|COUNT(*)|+---------+|0|+----------+##mysql_tzinfo_to_sql工具导入时区值。shell>mysql_tzinfo_to_sql/usr/share/zoneinfo|mysql-urootmysqlmysql>SELECTCOUNT(*)FROMmysql.time_zone_name;+----------+|COUNT(*)|+-----------+|1780|+----------+3)log_timestamps此变量控制写入错误日志的消息和写入文件的一般查询日志和慢速查询日志消息中时间戳的时区。它不影响写入表(mysql.general_logmysql.slow_log)的一般查询日志和慢速查询日志消息的时区。允许的log_timestamps值是UTC（默认值）和SYSTEM（本地系统时区）。注：UTC一般指协调世界时。协调世界时，又称世界统一时间、世界标准时间、国际协调时间，是UTC+8小时=中国时间。当然，这个值需要和系统记录的时间保持一致，才能更好的管理。#设置时区，改成东八区SETGLOBALtime_zone='+8:00';建议：[mysqld]log_timestamps=SYSTEMdefault-time_zone='+8:00'mysql>showglobalvariableswhereVariable_namelike'%time_zone%'orVariable_namelike'log_timestamp%';+------------------+--------+|Variable_name|Value|+--------------+--------+|log_timestamps|SYSTEM||system_time_zone|CST||time_zone|+08:00|+----------------+--------+总结从时间类型、参数、系统时区学习，如何在MySQL中设置和使用时间，尤其是没有特殊要求的时候，sql_mode不要轻易改变。作者简介崔虎龙，云和恩墨MySQL技术顾问，长期服务于金融、游戏、物流等行业的数据中心，设计数据存储架构，熟悉数据中心运行管理、自动化运维和管理的流程和规范。维护等。擅长MySQL、Redis、MongoDB数据库高可用设计和运维故障排除、备份恢复、升级迁移、性能优化。自学通过MySQLOCP5.6和MySQLOCP5.7认证。2年以上开发经验，10年数据库运维工作经验，其中8年全职MySQL工作；曾担任项目经理、数据库经理、数据仓库架构师、MySQL技术专家、DBA等职位；相关行业：金融（银行业、理财）、物流、游戏、医疗、重工等莫天伦原文链接：https://www.modb.pro/db/53474
