被 MySQL sql_mode 深深伤害（上）

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

而经常刺痛我们的，应该数 MySQL 的 sql_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_DATE 和 NO_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                                            |
    +------------------------------------------------------------------+
    1 row in set (0.00 sec)

(我对结果进行了换行)

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

ONLY_FULL_GROUP_BY

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

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

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

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

在严格模式，不要将 '0000-00-00' 做为合法日期

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

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

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

如果设置了 INSERT IGNORE 或 UPDATE IGNORE 中，MySQL 则生成除零警告，但操作结果为 NULL
NO_AUTO_CREATE_USER

防止 GRANT 自动创建新用户

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

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

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

Java 面试宝典是大明哥全力打造的 Java 精品面试题，它是一份靠谱、强大、详细、经典的 Java 后端面试宝典。它不仅仅只是一道道面试题，而是一套完整的 Java 知识体系，一套你 Java 知识点的扫盲贴。

它的内容包括：

大厂真题：Java 面试宝典里面的题目都是最近几年的高频的大厂面试真题。
原创内容：Java 面试宝典内容全部都是大明哥原创，内容全面且通俗易懂，回答部分可以直接作为面试回答内容。
持续更新：一次购买，永久有效。大明哥会持续更新 3+ 年，累计更新 1000+，宝典会不断迭代更新，保证最新、最全面。
覆盖全面：本宝典累计更新 1000+，从 Java 入门到 Java 架构的高频面试题，实现 360° 全覆盖。
不止面试：内容包含面试题解析、内容详解、知识扩展，它不仅仅只是一份面试题，更是一套完整的 Java 知识体系。
宝典详情：https://www.yuque.com/chenssy/sike-java/xvlo920axlp7sf4k
宝典总览：https://www.yuque.com/chenssy/sike-java/yogsehzntzgp4ly1
宝典进展：https://www.yuque.com/chenssy/sike-java/en9ned7loo47z5aw

目前 Java 面试宝典累计更新 400+ 道，总字数 42w+。大明哥还在持续更新中，下图是大明哥在 2024-12 月份的更新情况：

想了解详情的小伙伴，扫描下面二维码加大明哥微信【daming091】咨询

同时，大明哥也整理一套目前市面最常见的热点面试题。微信搜[大明哥聊 Java]或扫描下方二维码关注大明哥的原创公众号[大明哥聊 Java] ，回复【面试题】即可免费领取。

阅读全文

被 MySQL sql_mode 深深伤害（ 上 ）

sql_mode 是什么 ？

MySQL 查看 sql_mode

被 MySQL sql_mode 深深伤害（上）

sql_mode 是什么？