mysql修改sql_mode报错的解决

目录
  • 一 ERR 1067引发的血案
  • 二 问题的源头sql_mode
  • 三 设置sql_mode

一 ERR 1067引发的血案

今天在Navicat中运行sql语句创建数据表出现了错误Err 1067。而这条语句在有些同事的mysql上是正确的,但是在有些人那里就报错。你说气不气人。

原因竟然是timestamp的默认值不正确。

查阅资料得知,mysql5.7版本中有了一个STRICT mode(严格模式),而在此模式下默认是不允许设置日期的值为全0值的,所以想要

解决这个问题,就需要修改sql_mode的值。

二 问题的源头sql_mode

我们可以进入到mysql中一探sql_mode的究竟。首先进入到mysql的安装目录下的bin目录,使用管理员用户登录mysql数据库。

使用命令 mysql –h localhost –u root–p  其中-h是指定主机名或IP地址, -u是指定用户, -p是使用密码登录。

使用命令 select @@sql_mode; 可以查看sql_mode的值。如果输入了命令却没有反应,只是单单出现了 -> ,那么我想

你多半是应该像我一样,没有输入“;”。

通过上图中的结果我们可以看到sql_mode中有NO_ZERO_IN_DATE和NO_ZERO_DATE,在命令行中输入

set sql_mode=(select replace(@@sql_mode,'NO_ZERO_IN_DATE,NO_ZERO_DATE','')); 可以修改sql_mode。

之后可以查看一下sql_mode的值。可以发现已经成功去掉了NO_ZERO_IN_DATE和NO_ZERO_DATE。

重新运行了一下建表的sql语句,发现没什么卵用,依然Err 1067。不要想什么姿势不对的问题了,只是全局的sql_mode没有设置而已,而这里设置的sql_mode对大局根本没有影响。

使用命令select @@global.sql_mode; 可以查看全局sql_mode的值。

剩下的操作与之前的sql_mode设置是同理可证的,只是将之前sql_mode的地方都换成了@@global.sql_mode,如图。

完成设置之后可以在Navicat中重新运行一下sql语句了,不过在那之前要先重新连接数据库,不然依然Rrr 1067。

别问我为什么,叫我雷锋就好。运行结果如下:

好了,这次表是创建成功了的,而且换了一个错误Err 1055,翻译过来就是“无法给包含一个非聚合的列information_schema.

PROFILING.SEQ进行分组,这个功能不再依赖分组,且与新的规则不兼容sql_mode=only_full_group_by”。也说了这是由于

sql_mode中的“ONLY_FULL_GROUP_BY”导致的。可以再次修改sql_mode。

删除之前创建好的表,重新连接数据库,运行sql语句,然后就棒棒棒了。

当然这种解决方法只是扬汤止沸,一旦重启mysql数据库,之前费了狼劲设置的一堆值一夜回到解放前。

这也是有解决办法的,下面会给大家介绍。

三 设置sql_mode

可以通过修改配置文件的方式设置sql_mode,这样在数据库重启之后sql_mode的值也不会改变。

首先我们需要知道的是mysql的配置文件的加载顺序。进入到数据库安装目录的bin目录下,使用命令

mysqld --verbose –help可以看到,不过这个命令的输出结果太长了,我暂时没有找到更合适的命令来查看。

加载顺序如图:

这些配置文件在加载时,后加载的会将之前加载的配置文件中的相同的值覆盖。不过我只在mysql的安装目录下找到了

一个名字很像的配置文件,其余的都没有找到。

将这个文件备份好之后,修改名称为my.ini,与给出的加载配置文件顺序中的文件对应。然后打开文件,我这里的配置

文件中的sql_mode只有两个值。

重启数据库后,使用命令查看sql_mode的值,发现与配置文件中完全吻合,搞定!

附加几种常见的sql_mode值的介绍:

几种常见的mode介绍

  • ONLY_FULL_GROUP_BY:出现在select语句、HAVING条件和ORDER BY语句中的列,必须是GROUP BY的列或者依赖于GROUP BY列的函数列。
  • NO_AUTO_VALUE_ON_ZERO:该值影响自增长列的插入。默认设置下,插入0或NULL代表生成下一个自增长值。如果用户希望插入的值为0,而该列又是自增长的,那么这个选项就有用了。
  • STRICT_TRANS_TABLES:在该模式下,如果一个值不能插入到一个事务表中,则中断当前的操作,对非事务表不做限制
  • NO_ZERO_IN_DATE:这个模式影响了是否允许日期中的月份和日包含0。如果开启此模式,2016-01-00是不允许的,但是0000-02-01是允许的。它实际的行为受到 strict mode是否开启的影响。
  • NO_ZERO_DATE:设置该值,mysql数据库不允许插入零日期。它实际的行为受到 strictmode是否开启的影响。
  • ERROR_FOR_DIVISION_BY_ZERO:在INSERT或UPDATE过程中,如果数据被零除,则产生错误而非警告。如果未给出该模式,那么数据被零除时MySQL返回NULL
  • NO_AUTO_CREATE_USER:禁止GRANT创建密码为空的用户
  • NO_ENGINE_SUBSTITUTION:如果需要的存储引擎被禁用或未编译,那么抛出错误。不设置此值时,用默认的存储引擎替代,并抛出一个异常
  • PIPES_AS_CONCAT:将”||”视为字符串的连接操作符而非或运算符,这和Oracle数据库是一样的,也和字符串的拼接函数Concat相类似
  • ANSI_QUOTES:启用ANSI_QUOTES后,不能用双引号来引用字符串,因为它被解释为识别符

到此这篇关于mysql修改sql_mode报错的解决 的文章就介绍到这了,更多相关mysql sql_mode内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

时间: 2021-09-14

Mysql之SQL Mode用法详解

一.Mysql SQL Mode简介 通常来说MySQL服务器能够工作在不同的SQL模式下,并能针对不同的客户端以不同的方式应用这些模式.这样,应用程序就能对服务器操作进行量身定制以满足自己的需求.这类模式定义了MySQL应支持的SQL语法,以及应该在数据上执行何种确认检查.这样,就能在众多不同的环境下.与其他数据库服务器一起更容易地使用MySQL.可以使用"--sql-mode="modes""选项,通过启动mysqld来设置默认的SQL模式.而从MySQL 4.

MySQL5.7中的sql_mode默认值带来的坑及解决方法

在正常项目开发过程中,如果MySQL版本从5.6升级到5.7版本.作为DBA在考虑数据库版本升级带来的影响时,一般会有几个注意点: sql_mode optimizer_switch 本文主要内容是MySQL升级到5.7版本之后,由于默认的 sql_mode 值带来的坑以及对应的解决方案. 案例一:ONLY_FULL_GROUP_BY 问题描述 MySQL版本从5.6升级至5.7之后,部分SQL执行报错,报错信息如下: ERROR 1055 (42000): Expression #3 of X

MySQL sql_mode的使用详解

前言 相信看过上一篇文章<MySQL案例:一个数据丢失惨案>的童鞋,都应该意识到,sql_mode是一个非常关键的配置,接下来就带来该配置项的详细解析. sql_mode详解 sql_mode,会直接影响SQL语法支持和数据校验,它包含非常多的选项,其中5.7版本的默认值是 "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,:ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_C

MySQL中SQL Mode的查看与设置详解

MySQL中SQL Mode的查看与设置 MySQL可以运行在不同的模式下,而且可以在不同的场景下运行不同的模式,这主要取决于系统变量 sql_mode 的值.本文主要介绍一下这个值的查看与设置,主要在Mac系统下. 对于每个模式的意义和作用,网上很容易找到,本文不做介绍. 按作用区域和时间可分为3个级别,分别是会话级别,全局级别,配置(永久生效)级别. 会话级别: 查看- select @@session.sql_mode; 修改- set @@session.sql_mode='xx_mod

MySQL关于sql_mode解析与设置讲解

昨晚在往MySQL数据库中插入一组数据时,出错了!数据库无情了给我报了个错误:ERROR 1365(22012):Division by 0:意思是说:你不可以往数据库中插入一个 除数为0的运算的结果.于是乎去谷歌了一番,总算是明白了其中的原因:是因为MySQL的sql_mode 模式限制着一些所谓的'不合法'的操作. 解析 这个sql_mode,简而言之就是:它定义了你MySQL应该支持的sql语法,对数据的校验等等.. 如何查看当前数据库使用的sql_mode: mysql> select

解决MySQL 5.7.9版本sql_mode=only_full_group_by问题

MySQL 5.7.9版本sql_mode=only_full_group_by问题 用到GROUP BY 语句查询时com.MySQL.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'col_user_6.a.START_TIME' which is not func

MySQL sql_mode修改不生效的原因及解决

前言 近期多次聊到sql_mode的话题,也是多次遇到相关问题,今天就趁热打铁,再给大家带来一个sql_mode的案例分享. 场景模拟 基于业务敏感性的考虑,下面涉及的表.存储过程等均非真实数据,但并不影响排查过程. (1)客户侧开发童鞋创建了一个存储过程,该存储过程没有严格遵守group by标准语法 session 1: mysql> delimiter // mysql> create procedure test_for_group_by() -> begin -> sel

mysql数据库中字符集乱码问题原因及解决

前言 有的时候我们在查看数据库数据时,会看到乱码.实际上,无论何种数据库只要出现乱码问题,这大多是由于数据库字符集设定的问题. 下面我们就介绍一下,数据库的字符集的设定及乱码问题的解决. mysql数据库的字符集 直白的说,字符就像是单个的文字,编码就像是给每个文字的编号,字符集就像是字符与编码的集合,校验规则就是字符集的对应的排序规则,字符集加上对应的校验规则就是语言.(每种字符集可以有多种校对规则,但都有一个默认的校对规则) mysql数据库可以通过设定字符集,来使用对应的字符集和检验规则来

MySQL无法创建外键的原因及解决方法

关联2张表时出现了无法创建外键的情况,从这个博客看到,问题出在第六点的Charset和Collate选项在表级和字段级上的一致性上.我的2张表的编码charset和collate不一致,2张表都执行执行SQL语句: alter table 表名 convert to character set utf8; 完美解决问题: ps:下面看下MySQL无法创建外键.查询外键的属性 MyISAM 和InnoDB 讲解 InnoDB和MyISAM是许多人在使用MySQL时最常用的两个表类型,这两个表类型各

python logging.basicConfig不生效的原因及解决

最近在写脚本时,明明在脚本里使用logging.basicConfig配置了log目录,可目录文件确实空的 import logging from Logger import logger as log # log.Log_Info('nihaohaohao') # 设置log的存储文件 logging.basicConfig(filename = os.path.join(os.getcwd(), 'logs/report_log.txt'), level = logging.DEBUG) l

IOS设备上给body绑定click事件不生效的原因及解决办法

事件背景: 最近在做一个移动端业务的时候碰到一个bug,在ios上对body绑定click事实现事件代理冒泡至某些元素上尽然不生效. 思考: 暂借助jquery展示下事件绑定代码,将所有标签含有data-tip属性的元素通过事件代理至body $('body').on('click','[data-tip]',function(e){ console.log($(this.).attr('data-tip')) }) 这样做在android和pc上都可以正常实现,但是在ios上面对部分标签尽然不

Mysql/MariaDB启动时处于进度条状态导致启动失败的原因及解决办法

今天打开网站突然发现网站无法打开,后来通过SSH登陆服务器发现MARIADB数据库没有启动成功,再次启动还是无法成功启动,一直处于启动进度条,进度条结束后提示ERROR.查看日志出现以下错误: InnoDB: Unable to lock ./ibdata1, error: 11 后经调试发现是因为MariaDB数据库所在分区已经满了,造成无法启动. 只有将MariaDB数据库存放数据目录移动到另外一个磁盘份额比较大的分区或者将当前分配删除一些不必要的文件. 移动办法: 1.停掉mysql服务器

MySQL动态修改varchar长度的方法

虽然这种情况不应该发生,通常像我们关系型数据库,我们应该是事先设计好,以后不能改动,但是由于之前工作的疏忽,其实说实话,也不仅仅是我个人的疏忽,主要是沟通上的原因,当然数据库毕竟是我设计的,所以,还是自我批评一下. 说一下情况:MySQL字段有个varchar值字段设置的太短了,设置了30个,(我依稀记得varchar是可扩展的,当然现实并不容忍我的依稀),所以我只能找一个方法在保证数据库数据不变的情况下,动态修改varchar字段的长度,找了一段时间,终于让我找到了. alter table

Mysql 报Row size too large 65535 的原因及解决方法

报错信息:Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535 向mysql的表插件一个字段 类型为text时,或修改一个字段类型为text时,报出上面的错误.其实我对这个错误的原因理解也不是很深,给出一些我查到的解释吧 大意是数据表中有一个设定长度为64K的字段索引,当表中字段(不知道是字段名字还是什么)不能超过这个长度,65,535所说明的是针对的是整个表的

mysql 无法联接常见故障及原因分析

===================================================================================================== 故障现象 : 无法连接 mysql 错误信息 :ERROR 2003 (HY000): Can't connect to MySQL server on 'hostxxxxx' (10061) 原因 : mysqld数据库服务没有启动. 检查 :在windows 的任务管理器,或者 unix/l

在Ubuntu/Linux环境下使用MySQL开放/修改3306端口和开放访问权限

操作系统:Ubuntu 17.04 64位 MySQL版本:MySQL 5.7 一.查看3306端口是否开放 netstat -an|grep 3306 如果看到下图这样的,说明端口并未打开: 二.修改访问权限 进入目录"etc/mysql/mysql.conf.d/",如下图所示: 在这个目录下,有一个配置文件"mysqld.cnf",如下图所示: 打开这个配置文件: sudo vim mysqld.cnf 文件打开后有一大段注释说明,不用去管它,直接看到下图中的

MySQL取消了Query Cache的原因

MySQL之前有一个查询缓存Query Cache,从8.0开始,不再使用这个查询缓存,那么放弃它的原因是什么呢?在这一篇里将为您介绍. MySQL查询缓存是查询结果缓存.它将以SEL开头的查询与哈希表进行比较,如果匹配,则返回上一次查询的结果.进行匹配时,查询必须逐字节匹配,例如 SELECT * FROM t1; 不等于select * from t1;,此外,一些不确定的查询结果无法被缓存,任何对表的修改都会导致这些表的所有缓存无效.因此,适用于查询缓存的最理想的方案是只读,特别是需要检查