MySQL 拾遗

03、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 远远不止这些值。我们暂时只对常见的几个值做下解释吧

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；

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

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

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

6、 NO_AUTO_CREATE_USER；

防止 GRANT 自动创建新用户

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

7、 NO_ENGINE_SUBSTITUTION：

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

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

sql_mode 是什么 ？

MySQL 查看 sql_mode

sql_mode 是什么？