1. 首页
  2. mysql基础

03-三、被 MySQL sql_mode 深深伤害( 上 )

常在海边走,哪有不湿鞋,用 MySQL 多了,就常常会遇见各种错误,尤其是不同版本带来的错误。

而经常刺痛我们的,应该数 MySQLsql_mode 了,比如在 记不住的 MySQL DISTINCT 的用法 ( 上 ) 的示例数据下执行下面的语句会报错


mysql> SELECT DISTINCT(user),fruit FROM fruits GROUP BY user,fruit; ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.fruits.fruit' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

这个错误的意思是说,在 sql_mode=only_full_group_by 模式下,所选取的字段,必须出现在 GROUP BY 子句中。

这种要求其实也很好理解,如果选取的列不在 GROUP BY 子句中,那么它的值就是随机的,没啥大意义。

还有另一种情况的 sql_mode 错误,就是在 5.7 以上的版本中,timestamp 类型的默认值不能全为 0 ,及 0000-00-00 00:00:00

比如,执行下面的语句会报错


mysql> CREATE TABLE `test`.`user` ( `id` int AUTO_INCREMENT, `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (`id`) ); ERROR 1067 (42000): Invalid default value for 'created_at'

出现这个问题的原因,是因为 sql_mode 包含了 NO_ZERO_IN_DATENO_ZERO_DATE,而在这两个模式下,timestamp 的年份只能是 1970-2038,对了,上限在某些机子上已经不是 2038

字符这个错误的办法很简单,就是把 timestamp 类型的默认值改成 1970-01-01 00:00:00


mysql> CREATE TABLE `test`.`user` ( -> `id` int AUTO_INCREMENT, -> `created_at` datetime NOT NULL DEFAULT '1970-01-01 00:00:00', -> PRIMARY KEY (`id`) -> ); Query OK, 0 rows affected (0.10 sec)

这很反直觉,但很有效,只不过在判断 timestamp 是否为空的时候,可能就要判断是否等于 `1970-01-01 00:00:00

还有很多很多 sql_mode 引起的错误,小编一时就想不起来了

sql_mode 是什么 ?

屡屡受 sql_mode 欺负,那么它到底是什么 ?

官网对此的解释是 「 Modes affect the SQL syntax MySQL supports and the data validation checks it performs 」

翻译成中文就是 「 模式会影响 MySQL 支持的 SQL 语法以及它执行的数据验证检查 」

白话一点就是 sql_mode 决定了哪些 SQL 语句可以被执行,哪些不能被执行

MySQL 查看 sql_mode

其实,官方还有一段话


The MySQL server can operate in different SQL modes, and can apply these modes differently for different clients, depending on the value of the sql_mode system variable. DBAs can set the global SQL mode to match site server operating requirements, and each application can set its session SQL mode to its own requirements

这句话的意思是说

「 MySQL 服务器可以在不同的 SQL 模式下运行,并且可以针对不同的客户端以不同的方式应用这些模式,具体取决于 sql_mode 系统变量的值 」。

那你肯定和我一样好奇,就是当前连接的 sql_mode 到底是什么 ?

好在 MySQL 提供了一些命令用于查询和设置 sql_mode,我们可以使用下面的命令查看当前 MySQL 连接的 sql_mode


mysql> select @@sql_mode; +-----------------------------------------------------------------+ | @@sql_mode | +-----------------------------------------------------------------+ | ONLY_FULL_GROUP_BY, |STRICT_TRANS_TABLES, |NO_ZERO_IN_DATE, |NO_ZERO_DATE, |ERROR_FOR_DIVISION_BY_ZERO, |NO_AUTO_CREATE_USER, |NO_ENGINE_SUBSTITUTION | +------------------------------------------------------------------+ row in set (0.00 sec)

(我对结果进行了换行)

是不是看到了熟悉的身影,其实 sql_mode 远远不止这些值。我们暂时只对常见的几个值做下解释吧

1、 ONLY_FULL_GROUP_BY

对于 GROUP BY 聚合操作,如果 SELECT中 的列,没有在 GROUP BY 中出现,那么将认为这个 SQL 是不合法的
2、 STRICT_TRANS_TABLES

在该模式下,如果一个值不能插入到一个事务表中,则中断当前的操作,对非事务表不做任何限制
3、 NO_ZERO_IN_DATE

在严格模式,不接受月或日部分为0的日期。

如果使用 IGNORE 选项或非严格模式,则可以插入类似的日期 ‘0000-00-00’,但会生成警告
4、 NO_ZERO_DATE

在严格模式,不要将 ‘0000-00-00’ 做为合法日期

如果使用 IGNORE 选项或非严格模式,则可以插入类似的日期 ‘0000-00-00’,但会生成警告
5、 ERROR_FOR_DIVISION_BY_ZERO

严格模式下,在 INSERTUPDATE 过程中,如果除零(或 MOD(X,0) ),则产生错误(否则为警告)

如果未给出该模式,除零时MySQL返回 NULL

如果设置了 INSERT IGNOREUPDATE IGNORE 中,MySQL 则生成除零警告,但操作结果为 NULL
6、 NO_AUTO_CREATE_USER

防止 GRANT 自动创建新用户

但如果通知指定了密码,则有可能会创建新用户
7、 NO_ENGINE_SUBSTITUTION

如果需要的存储引擎被禁用或未编译,那么抛出错误

不设置此值时,会默认的存储引擎替代,并抛出一个异常

希望读者能够给小编留言,也可以点击[此处扫下面二维码关注微信公众号](https://www.ycbbs.vip/?p=28 "此处扫下面二维码关注微信公众号")

看完两件小事

如果你觉得这篇文章对你挺有启发,我想请你帮我两个小忙:

  1. 关注我们的 GitHub 博客,让我们成为长期关系
  2. 把这篇文章分享给你的朋友 / 交流群,让更多的人看到,一起进步,一起成长!
  3. 关注公众号 「方志朋」,公众号后台回复「666」 免费领取我精心整理的进阶资源教程
  4. JS中文网,Javascriptc中文网是中国领先的新一代开发者社区和专业的技术媒体,一个帮助开发者成长的社区,是给开发者用的 Hacker News,技术文章由为你筛选出最优质的干货,其中包括:Android、iOS、前端、后端等方面的内容。目前已经覆盖和服务了超过 300 万开发者,你每天都可以在这里找到技术世界的头条内容。

    本文著作权归作者所有,如若转载,请注明出处

    转载请注明:文章转载自「 Java极客技术学习 」https://www.javajike.com

    标题:03-三、被 MySQL sql_mode 深深伤害( 上 )

    链接:https://www.javajike.com/article/1619.html

« 04-四、被 MySQL sql_mode 深深伤害( 中 )
02-二、记不住的 MySQL DISTINCT 的用法 ( 下 )»

相关推荐

QR code