最近在看mysql5.7.18版本,关于sql mode 的配置,个人对其非常着迷,前后遇到过好几次;对于技术问题,我向来遇到问题我都是搞不清楚本质死不罢休的那种,所以还是写写,用文字记录下来整个细节:
安装好了mysql5.7.18版本以后,查看默认的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)
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)
mysql> select @@sql_mode\G;
*************************** 1. row ***************************
@@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)
上面的几个项,我们来一一分析并说明:
> ONLY_FULL_GROUP_BY
如果sql mode 中包括 only_full_group_by 的话,就是only_full_group_by 模式;
如果不包括only_full_group_by的话,就是非only_full_group_by模式;
首先我们来讲这个项的作用,然后再来说mysql 5.7以及后续版本默认开启这个项的缘故;
该项就是针对 group by 查询的,在group by 查询时,如果未开启 only_full_group_by 模式;
聚集函数或group by
具体详情可参考: http://www.ywnds.com/?p=8184
> STRICT_TRANS_TABLES
> NO_ZERO_IN_DATE
> NO_ZERO_DATE
> ERROR_FOR_DIVISION_BY_ZERO
> NO_AUTO_CREATE_USER
> NO_ENGINE_SUBSTITUTION
对sql mode 的详细说明,还是建议参考mysql官网上面的文档,非常的详细,不过都是英文的奥;
https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html
以及sql mode 在mysql 5.7.x版本中的变化:
https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#SQL Mode Changes in MySQL 5.7
http://www.ywnds.com/?p=8184