MySQL关于时间设置的注意事项

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

1. 时间类型的字段

MySQL时间类型字段:

下面是容易忽略的内容:

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

  • TIMESTAMP和SQL_MODE组合
    sql_mode也会影响timestamp值:

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 8.0.22中,如果试图在声明为TIMESTAMP NOT NULL的列中插入NULL,将会被拒绝,并产生错误。

1)explicit_defaults_for_timestamp被禁用时:

  • 没有使用NULL属性显式声明的时间戳列将自动使用NOT NULL属性声明。允许为这样的列赋值为NULL,并将该列设置为当前时间戳。在MySQL 8.0.22中,如果试图在声明为TIMESTAMP NOT NULL的列中插入NULL,将会被拒绝,并产生错误。

  • 如果表中的第一列没有使用NULL属性或显式的DEFAULT或ON UPDATE属性进行声明,则会自动使用默认的CURRENT_TIMESTAMP属性和ON UPDATE CURRENT_TIMESTAMP属性进行声明。

  • TIMESTAMP 如果没有显式地使用NULL属性或显式默认属性声明,则自动声明为默认的’0000-00-00 00:00:00’(“零”时间戳)。

  • 根据启用的是strict SQL模式还是NO_ZERO_DATE SQL模式,默认值“0000-00-00 00:00:00”可能无效。

2)explicit_defaults_for_timestamp被启用:

  • 不可能为TIMESTAMP指定NULL值来将其设置为当前时间戳。要指定当前时间戳,设置为CURRENT_TIMESTAMP或一个同义词,比如NOW()。

  • 没有使用not NULL属性显式声明的TIMESTAMP列将自动使用NULL属性声明并允许空值。

  • 使用NOT NULL属性声明的时间戳列不允许空值。对于为这样的列指定NULL的插入,如果启用了strict SQL模式,那么单行插入会出现错误,或者禁用了strict SQL模式的多行插入会插入’0000-00-00 00:00:00’。在任何情况下,为列赋值为NULL都不会将其设置为当前时间戳。

  • 使用NOT NULL属性显式声明且没有显式默认属性的时间戳列被视为没有默认值。对于未为此类列指定显式值的插入行,结果取决于SQL模式。如果启用了严格SQL模式,则会出现错误。如果没有启用严格的SQL模式,则使用默认隐式值’0000-00-00 00:00:00’声明该列,并出现警告。

  • timestamp类型字段 不会自动使用默认的CURRENT_TIMESTAMP属性或更新CURRENT_TIMESTAMP属性声明。这些属性必须显式指定。

测试:

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个:

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”,表示服务器时区与系统时区一致。

  • 如果设置为SYSTEM,如MySQL函数调用都会调用一个系统库来确定当前的系统时区。这个调用可能被一个全局互斥锁保护,从而导致争用。CPU使用率高问题。

  • 设置会话时区会影响时区敏感的时间值的显示和存储。这包括NOW()或CURTIME()等函数显示的值,以及存储在时间戳列中的值和从时间戳列检索到的值。时间戳列的值将从会话时区转换为UTC用于存储,从UTC转换为会话时区用于检索。

  • 会话时区设置不影响UTC_TIMESTAMP()等函数显示的值,也不影响DATE、time或DATETIME列中的值。这些数据类型的值也不存储在UTC;时区仅在从时间戳值转换时适用它们。

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

mysql> SELECT COUNT(*) FROM mysql.time_zone_name;

+----------+

| COUNT(*) |

+----------+

|        0 |

+----------+

##mysql_tzinfo_to_sql工具导入时区值。

shell>mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

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小时=中国时间。当然值需要跟系统记录时间一致,才能更好地管理。

#设置时区,更改为东八区

SET GLOBAL time_zone = '+8:00';  

建议:
MySQL配置文件my.cnf

[mysqld]
log_timestamps=SYSTEM
default-time_zone                  = '+8:00'
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、Redis、MongoDB数据库高可用设计和运维故障处理、备份恢复、升级迁移、性能优化。自学通过了MySQL OCP 5.6和MySQL OCP 5.7认证。2年多开发经验,10年数据库运维工作经验,其中专职做MySQL工作8年;曾经担任过项目经理、数据库经理、数据仓库架构师、MySQL技术专家、DBA等职务;涉及行业:金融(银行、理财)、物流、游戏、医疗、重工业等。

墨天轮原文链接:https://www.modb.pro/db/53474(复制到浏览器或者点击“阅读原文”立即查看)

END
(0)

相关推荐

  • mysql 新版驱动连接URL参数

    驱动包用的是mysql-connector-java-8.0.11.jar  新版的驱动类改成了com.mysql.cj.jdbc.Driver  新版驱动连接url也有所改动  I.指定时区 //北 ...

  • 无创呼吸机的使用流程、参数设置及注意事项(2020年3月)

    无创正压通气(non-invasive positive pressure ventilation) 无创正压通气是指不需建立人工气道(气管插管或气管切开),而是通过鼻面罩将呼吸机与病人相连接,由呼吸 ...

  • 李远达:“歇午”与“夜宴”:《红楼梦》微观时间设置的叙事潜能与文化意蕴

    "歇午"与"夜宴"是<红楼梦>中出现频率很高的两个生活场景.午间和夜晚分别是小说人物睡眠与宴饮发生的时段.从表面看,中午睡觉和晚上宴饮是贾府作息时间 ...

  • SSRS  根据提供时间 设置一个为固定时间段

    ​在用SSRS做报表时,很多时候会遇到时间需要限制在一定范围的情况,比如限制开始日期与结束日期这段时间不能超过1个星期,这个就比较典型. 愚处理限定范围的思路如下: 手动选择开始时间,结束时间则根据开 ...

  • 葡萄全年三次关键追肥时间 方法及注意事项

    葡萄在整个生长过程期间最少要经过三次追肥,这样才能为葡萄提供充足的养分.那么如何为葡萄合理追肥呢?具体内容如下: 1葡萄全年三次关键追肥的时间及方法 葡萄第一次追肥的时间应该是在春季萌芽前,这个时候葡 ...

  • 一加9pro自动息屏时间设置

    小伙伴们你们知道一加9pro如何打开自动锁屏,今天小编很乐意与大家分享一加9pro自动息屏时间设置步骤,感兴趣的可以来了解. 1.在手机设置菜单中点击[显示与亮度]. 2.点击[自动息屏]选项. 3. ...

  • 中国人真智慧,把高考时间设置在6月1日之后太明智了

    今年首批00后已经高考了,这么多年,你可曾思考过,为什么高考在6.1后? 马上到6月份了,那么6月份在61儿童节一过之后就开始正式进入高考了,今年也是首批00后进入高考了,一句话岁月不饶人呀,现在的0 ...

  • 示波器纹波测试的时间设置

    weixin_396145462020-12-31 11:51:01 文章标签:示波器纹波测试的时间设置 版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接 ...

  • 【热坛学习】出气孔设置的注意事项

    铸造微课堂 热加工行业论坛 今天     [热坛学习]出气孔设置的注意事项 出气孔是铸型型腔出气冒口.砂型和砂芯排气通道的总称. 出气孔分明.暗两种.明出气孔引出型外,与大气相通:暗出气孔不与大气相通 ...

  • 华为mate20怎么设置屏幕休眠时间?华为mate20屏幕亮屏时间设置

    华为mate20怎么设置屏幕亮屏时间?按照个人喜好设置手机屏幕亮着的时间,这样我们的手机用起来也更加的顺手,小编为大家带来设置的方法. 华为mate20屏幕亮屏时间设置 1.在mate20的手机桌面上 ...