前言： 前面文章我们介绍过日期和时间字段的查询方法，最近遇到日期值为零的问题。原来了解过和 sql_mode 参数设置有关，但还不是特别清楚，本篇文章将探究下MySQL怎么处理日期值为零的问题。...1.问题描述 这里我们说的日期为零值是指年、月、日为零，即'0000-00-00'。...显然，这是不合法的日期值，但由于设计问题或历史遗留问题，有时候数据库中有类似日期值为零的数据，默认情况下插入零值日期会报错，可以通过修改参数sql_mode模式来避免该问题。...其中 NO_ZERO_IN_DATE、NO_ZERO_DATE这两个变量影响MySQL对日期零值的处理。...3.结论及建议 简单总结下，NO_ZERO_DATE模式影响'0000-00-00'日期的插入，NO_ZERO_IN_DATE模式影响除'0000-00-00'外的月、日为零的日期的插入。
从MySQL8.0.22开始，插入值的月份、日 部分或两者都不能为零，这是强制执行的，不管服务器SQL模式如何设置。...此示例演示如何使用不同的时区设置将带有时区偏移的datetime值插入TIMESTAMP和datetime列，然后检索它们： mysql>CREATE TABLE ts ( -> id...如果SQL模式允许此转换，则将无效的日期、日期时间或时间戳值转换为相应类型的“零”值（’0000-00-00’或’0000-00-00 00:00:00’）。...* MySQL不接受TIMESTAMP值在day或month列中包含零的值或不是有效日期的值。唯一的例外是特殊的“零”值 ‘0000-00-00 00:00:00’，如果SQL模式允许该值。...精确的行为取决于是否启用了严格SQL模式和NO_ZERO_DATE无零日期SQL模式； * 包含两位数年份值的日期是不明确的，因为世纪是未知的。
如果启用此模式，则将零件的日期插入'0000-00-00'并产生警告。 如果启用了此模式和严格模式，则不允许包含零件的日期，插入会产生错误，除非IGNORE给出。...严格模式会影响日期中除零，零日期和零的处理，如下所示： 严格模式影响零除的处理，其中包括 ： MOD(N,0) 对于数据更改操作（INSERT， UPDATE）： 如果没有启用严格模式，则除以零插入...对于INSERT IGNORE和UPDATE IGNORE，零部件日期被插入 '0000-00-00'（这被认为是有效的IGNORE），并产生一个警告。...+严格模式 严格模式 下表显示了如何控制服务器是否允许'0000-00-00'MySQL 5.7.4到5.7.7以及MySQL 5.7.4到5.7.7以外的其他版本的有效日期。...对于这种模式设置，请在执行语句时期望这些差异： 在5.6中，服务器插入零部件的日期 '0000-00-00'并产生一个警告。
除了月日可零，MySQL 还支持设置年月日都零的值 0000-00-00，对于日期非必填的情况比较有用，因为此时它比单纯的 NULL 更有语义。...可通过开启 MySQL 的 NO_ZERO_DATE 模式来禁用这个全零的值。 各日期时间零值格式如下，但实际时用时，直接简写成一个 0 效果是等效的。...在写入时，对于非法的日期时间值，将自动存成零值，即 '0000-00-00' 或 '0000-00-00 00:00:00'。...如果这这个日期列设置 10:45:15 则会认为是非法值，因为 45 不是一个合法的月份值，所以存储时变成零值 0000-00-00。 日期时间与毫秒的分界符必需是小数点。...默认 MySQL 除了检查日月值是否有有效范围 1~ 31,1~12。还会将两者结合进来检查，比如 4 月没有 31。所以对于日期 2004-04-31 算是非法的，会变成零值 0000-00-00。
设置该模式之后，只能通过插入 null 值产生自增或者不插入对应的值。插入0值存储的还是0 而非下一个自增值。...主键存放0值并不是一个非常好的选择。 NO_ZERO_IN_DATE 该模式决定了date类型的值(年月日)中，年不是0值，日期和月份是否可以是0值的情况。...比如 2020-00-23 ，2020-00-00 这样的值是否可以成为合法的日期值。该模式的对数据合法性的影响依然和是否设置了 严格模式有关。...在严格模式下，不接受月或日部分为0的日期。如果使用IGNORE选项，我们为类似的日期插入'0000-00-00'。在非严格模式，可以接受该日期，但会生成警告。...插入时会直接报错。但是如果用IGNORE选项可以插入零日期，但是会生成警告。 严格模式下，使用insert 插入'0000-00-00' 直接报错。
MySQL配置中sql_mode的作用 不知道你有没有踫到过这种问题，在 MySQL8 默认的情况下，我们之前习惯的为 DateTime 类型指定的 0000-00-00 这种格式是无法插入或者修改数据的...value: '0000-00-00 00:00:00' for column 'created_at' at row 1 很明显，默认情况下，0000 这种形式插入日期是不行的，这时我们就可以修改...sql_mode ，让它回到 MySQL5 的时代，可以直接插入这种形式的日期数据。...这里提到了一个严格模式的概念，严格模式控制 MySQL 如何处理 INSERT 或 UPDATE 等更改语句中的无效或缺失值，比如上面我们说过的日期和除零问题，如果没有 STRICT_TRANS_TABLES...在转移或升级到 MySQL8 之后，其实最常见的问题就是上面说过的日期问题，0格式日期这种形式其实是已经过时的方式了，也是不推荐的方式，所以在 MySQL8 中会默认在严格模式下禁用这种形式的日期存储，
全部sql_mode MySQL版本<=5.6.5 默认为空 可选值如下： 参数 含义及示例 ALLOW_INVALID_DATES 允许无效的日期插入到数据库(无效的日期插入时候会有warning，但仍能插入...-00-00这种日期的插入，但是会有warnings不启用的话，表示允许0000-00-00这种日期的插入，不会有warnings 如果启动NO_ZERO_DATE模时,还启用了strict模式，则0000...';允许插2015-00-02、2015-03-00 这种非法日期，但是实际上插入的是0000-00-00。...但二者还有些区别： 严格模式控制MySQL如何处理非法或丢失的输入值。有几种原因可以使一个值为非法。例如，数据类型错误，不适合列，或超出范围。...对于STRICT_TRANS_TABLES，MySQL将非法值转换为最接近该列的合法值并插入调整后的值。如果值丢失，MySQL在列中插入隐式默认值。
它可以设置MySQL如何处理查询和数据的方式。在MySQL中，可以通过SET语句来设置sql_mode。...NO_ZERO_DATE：禁止在日期或时间列中插入“0000-00-00”或“00:00:00”的值。ONLY_FULL_GROUP_BY：启用SQL标准中对GROUP BY的更严格限制。...通过设置sql_mode，我们可以确保MySQL在执行查询时遵循严格的规则，从而避免意外的错误。...例如，如果我们尝试在日期列中插入“0000-00-00”或“00:00:00”的值，MySQL将会抛出错误，而不是插入这些无效的值。...另外，如果我们尝试使用GROUP BY子句对未在SELECT语句中列出的列进行分组，MySQL将会抛出错误。这可以帮助我们避免分组错误，确保查询结果的正确性。
（6）ERROR_FOR_DIVISION_BY_ZERO：该选项决定被零整除的返回值；如果不启用，那么被零整除的返回值为null且不告警；如果启用但在非严格模式下，那么被零整除的返回值为null且产生告警...（18）NO_ZERO_DATE：该选项决定‘0000-00-00’是否可以插入；如果不启用，那么‘0000-00-00’可以插入且不告警；如果启用但在非严格模式下，那么‘0000-00-00’可以插入但会产生告警...；如果启用且在非严格模式下，那么‘0000-00-00’不能插入会直接报错。...总结 通过上面的学习，相信大家对sql_mode各选项，都有较为详细的了解；关于sql_mode如何设置，个人建议如下： （1）对于5.5/5.6版本，sql_mode建议参照5.7默认值进行设置； （...；其实这一点，也可以从官方默认值看出来，随着MySQL版本的迭代，sql_mode的设置也是越来越严格。
特别是，插入DATETIME或TIMESTAMP列的值中的任何小数部分都会被存储而不是被丢弃。...如果 SQL 模式允许转换，无效的 DATE、DATETIME 或 TIMESTAMP 值会被转换为相应类型的 "零 "值（'0000-00-00' 或 '0000-00-00 00:00:00'）。...非严格模式只检查**月是否在 1 至 12 的范围内，日是否在 1 至 31 的范围内**。这对于在三个不同字段中获取年、月、日，并准确存储用户插入的内容而不进行日期验证的网络应用程序可能很有用。...MySQL 不接受在日或月列中包含零的 TIMESTAMP 值，也不接受不是有效日期的值。...这一规则的唯一例外是特殊的 "零 "值'0000-00-00 00:00:00'，如果 SQL 模式允许该值的话。
ERROR_FOR_DIVISION_BY_ZERO ：如果这个模式未启用，那么零除操作将会插入空值并且不会产生警告；如果这个模式启用，零除操作插入空值并产生警告；如果这个模式和严格模式都启用，零除从操作将会产生一个错误...NO_AUTO_VALUE_ON_ZERO : 这个值影响自增列，正常情况下可以通过插入“0”值或者null空值来生成自增序列的下一个值。...NO_ZERO_DATE :这个模式影响着插入的’0000-00-00’值是否作为合法的数值，这个结果和是否启用严格模式有关。...NO_ZERO_IN_DATE ：这个模式影响着日期中的月份和天数是否可以为0（注意年份是非0的），这个模式也取决于严格模式是否被启用。如果这个模式未启用，date中的零部分被允许并且插入没有警告。...如果这个模式启用，dates中的零部分插入被作为“0000-00-00”并且产生一个警告。如果这个模式和严格模式被启用，则dates中的零部分不被允许并且插入产生错误，除非ignore也被使用。
【1】MySQL中的日期时间类型 MySQL中常用的几种时间类型有：date、datetime、time、year、timestamp 数据类型 占用字节 最小值 最大值 零值表示 date 4 1000...但是有零值，0000-00-00 00:00:00； date：日期，就是datetime中的date部分； time：时间(段)，指定的某个区间之间，从-时间到+时间(有负时间表示)； timestamp...', 这时插入时间通常会是我们常见到的：2020-08-29 12:52:16格式。...如2002，如果写为 2002-01-15，将会进行计算，插入结果为1986 ---- 【2】日期时间类型格式化 ① DATE_FORMAT( )函数 可以使用date_format( )函数进行时间的转换...(SYSDATE(),'%Y-%m-%d %H:%i:%s') from dual; ② date_format( ) 转换格式 格式 描述 %a 缩写星期名 %b 缩写月名 %c 月，数值 %D 带有英文前缀的月中的天
# 背景 mysql版本5.7.8，需要创建新表，研发提供的sql文件，执行后报错如下： ERROR 1067 (42000): Invalid default value for 'deleted_at...' 就猜测到时因为5.7版本的mysql默认的时间戳不允许输入0000-00-00 00:00:00这种格式，之前碰到过类似问题，需要修改配置 查看了研发提供的sql文件，果真如此： ‘create_at...` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '注册时间', # 解决 1....5.7+版本无法输入0的时间戳 NO_ZERO_IN_DATE：在严格模式下，不允许日期和月份为零 NO_ZERO_DATE：设置该值，mysql数据库不允许插入零日期，插入零日期会抛出错误而不是警告...执行原来的sql文件，成功
如果使用IGNORE选项，我们为类似的日期插入'0000-00-00'。在非严格模式，可以接受该日期，但会生成警告。...NO_ZERO_DATE 在严格模式，不要将 '0000-00-00'做为合法日期。你仍然可以用IGNORE选项插入零日期。...如果未给出该模式，被零除时MySQL返回NULL。...对于本文开头中提到的错误，可以先把sql_mode设置为ANSI模式，这样便可以插入数据，而对于除数为0的结果的字段值，数据库将会用NULL值代替。...TRADITIONAL 严格模式，当向mysql数据库插入数据时，进行数据的严格校验，保证错误数据不能插入，报error错误，而不仅仅是警告。用于事物时，会进行事物的回滚。
重启后失效，因为MySQL重启时会重新读取配置文件里对应值，如果需永久生效需要修改配置文件里的值。...默认设置下，插入0或NULL代表生成下一个自增长值。如果用户 希望插入的值为0，而该列又是自增长的，那么这个选项就有用了。...STRICT_TRANS_TABLES 在该模式下，如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做限制 NO_ZERO_IN_DATE 在严格模式下，不允许日期或月份为零，只要日期的月或日中含有...0值都报错，但是‘0000-00-00’除外 NO_ZERO_DATE 设置该值，mysql数据库不允许插入零日期，插入零日期会抛出错误而不是警告。...如 果未给出该模式，那么数据被零除时MySQL返回NULL update table set num = 5 / 0 ; 设置该模式后会报错，不设置则修改成功，num的值为null NO_AUTO_CREATE_USER
什么是严格模式mysql5.7+的版本中，sql_mode为严格模式。比如必须给字段默认值，更新数据的长度必须符合限制等。...在默认设置下，插入 0 或 NULL 代表生成下一个自增长值。如果用户希望插入的值为0，而该列又是自增长值，那么该模式就起作用了。...NO_ZERO_DATE ：这个模式影响着插入的’0000-00-00’值是否作为合法的数值，这个结果和是否启用严格模式有关。...NO_ZERO_IN_DATE：这个模式影响着日期中的月份和天数是否可以为0(注意年份是非0的)，这个模式也取决于严格模式是否被启用。如果这个模式未启用，date中的零部分被允许并且插入没有警告。...如果这个模式启用，dates中的零部分插入被作为“0000-00-00”并且产生一个警告。如果这个模式和严格模式被启用，则dates中的零部分不被允许并且插入产生错误，除非ignore也被使用。
如果使用IGNORE选项，我们为类似的日期插入’0000-00-00’。在非严格模式，可以接受该日期，但会生成警告。...NO_ZERO_DATE： 在严格模式，不要将 '0000-00-00’做为合法日期。你仍然可以用IGNORE选项插入零日期。...不设置此值时，用默认的存储引擎替代，并抛出一个异常 NO_AUTO_VALUE_ON_ZERO 该值影响自增长列的插入。默认设置下，插入0或NULL代表生成下一个自增长值。...如果用户希望插入的值为0，而该列又是自增长的，那么这个选项就有用了。...如果不能将给定的值插入到事务表中，则放弃该语句。对于非事务表，如果值出现在单行语句或多行语句的第1行，则放弃该语句。
c) NO_ZERO_IN_DATE：禁止在日期中使用零值，例如'0000-00-00'，在严格模式下会被视为非法日期。...d) NO_ZERO_DATE：禁止使用零值表示日期的部分，例如'2000-00-00'，在严格模式下会被视为非法日期。...简单举例 以下是一些非严格模式下可能出现的案例情况： 1) 非严格的日期和时间插入：在非严格模式下，MySQL允许插入不符合日期和时间格式的值，会自动进行转换或舍入 eg: 不合法的日期值'0000...可以插入150，且插入后的值自动截断为 127 4) 非严格的零值插入：在非严格模式下，MySQL允许插入字符串类型的值到数值类型字段，会将非数值字符串转换为0 （建议已开启此类严格模式） eg:...，MySQL对外键约束的检查较为宽松，可能会允许插入或更新关联字段中不存在的值。
如何处理数据更改语句中的无效或缺失值，如INSERT或UPDATE。...一个值可能因多种原因无效。例如，它可能具有列的错误数据类型，或者它可能超出了范围。如果要插入的新行不包含定义中没有显式DEFAULT子句的非null列的值，则该值缺失。...NO_ZERO_IN_DATE NO_ZERO_IN_DATE模式会影响服务器是否允许年部分不为零但月或日部分为0的日期。...(该模式影响日期，如“2010-00-01”或“2010-01-00”，但不影响“0000-00-00”。要控制服务器是否允许'0000-00-00'，请使用NO_ZERO_DATE模式。)...NO_ZERO_DATE 上面的NO_ZERO_IN_DATE可以插入'0000-00-00'，如果使用了严格模式STRICT_TRANS_TABLES和NO_ZERO_DATE，那么就不可以插入'0000
，显示宽度只是指明 MySQL 最大可能显示的数字个数，数值的位数小于指定的宽度会由空格填充；如果插入了大于显示宽度的值，只要该值不超过该类型整数的取值范围，数值依然可以插入，而且能够显示出来。...显示宽度和数据类型的取值范围是无关的，显示宽度只是指明 MySQL 最大可能显示的数字个数，数值的位数小于指定的宽度会由空格填充；如果插入了大于显示宽度的值，只要该值不超过该类型整数的取值范围，数值依然可以插入...cpu 开销； 尽量避免 null：包含 null 值的列在 sql 语句中通常很难优化。...3.3 日期/时间类型 mysql(5.5)所支持的日期时间类型有：DATETIME、 TIMESTAMP、DATE、TIME、YEAR。...本篇文章此处主要讲解 DATETIME 类型 几种类型比较如下： 日期时间类型 占用空间 日期格式 最小值 最大值 零值表示 DATETIME 8 bytes YYYY-MM-DD HH:MM:SS 1000
领取专属 10元无门槛券
手把手带您无忧上云
云服务器
即时通信 IM
ICP备案
对象存储
实时音视频
