搭建部落格時，被mysql的sql_mode中ONLY_FULL_GRO
1、背景
前兩天在阿里雲伺服器上搭建了自己的部落格，一切都很順利，今天在點選歸檔按鈕時，發現是報404。於是我把solo程式碼在本地執行起來，用本地的mysql資料庫，看是否有同樣的問題，結果是可以正常訪問的。那就看看伺服器上的solo日誌唄，結果發現了以下報錯：
Caused by: org.b3log.latke.repository.RepositoryException: java.sql.SQLSyntaxErrorException: Expression #20 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'solo.aa.oId' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
原來，這個問題出現在MySQL5.7後版本上，預設的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
那麼sql_mode 有哪些配置？都代表什麼意思？
2、sql_mode 配置解析
ONLY_FULL_GROUP_BY
對於GROUP BY聚合操作，如果在SELECT中的列，沒有在GROUP BY中出現，那麼這個SQL是不合法的，因為列不在GROUP BY從句中。簡而言之，就是SELECT後面接的列必須被GROUP BY後面接的列所包含。如：
select a,b from table group by a,b,c; (正確)
select a,b,c from table group by a,b; (錯誤)
這個配置會使得GROUP BY語句環境變得十分狹窄，所以一般都不加這個配置
- NO_AUTO_VALUE_ON_ZERO
該值影響自增長列的插入。預設設定下，插入0或NULL代表生成下一個自增長值。（不信的可以試試，預設的sql_mode你在自增主鍵列設定為0，該欄位會自動變為最新的自增值，效果和null一樣），如果使用者希望插入的值為0（不改變），該列又是自增長的，那麼這個選項就有用了。
- STRICT_TRANS_TABLES
在該模式下，如果一個值不能插入到一個事務表中，則中斷當前的操作，對非事務表不做限制。（InnoDB預設事務表，MyISAM預設非事務表；MySQL事務表支援將批處理當做一個完整的任務統一提交或回滾，即對包含在事務中的多條語句要麼全執行，要麼全部不執行。非事務表則不支援此種操作，批處理中的語句如果遇到錯誤，在錯誤前的語句執行成功，之後的則不執行；MySQL事務表有表鎖與行鎖非事務表則只有表鎖）
- NO_ZERO_IN_DATE
在嚴格模式下，不允許日期和月份為零
- NO_ZERO_DATE
設定該值，mysql資料庫不允許插入零日期，插入零日期會丟擲錯誤而不是警告。
- 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後，不能用雙引號來引用字串，因為它被解釋為識別符
3、測試
本地起一個資料庫，先檢視sql_mode模式：
mysql> select @@global.sql_mode;
+--------------------------------------------+
| @@global.sql_mode |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)
mysql> select @@session.sql_mode;
+--------------------------------------------+
| @@session.sql_mode |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)
建立一個測試的表：
mysql> CREATE TABLE IF NOT EXISTS `demo`(
-> `id` INT UNSIGNED AUTO_INCREMENT,
-> `rank` VARCHAR(100) NOT NULL,
-> `name` VARCHAR(40) NOT NULL,
-> `gender` TINYINT NOT NULL,
-> PRIMARY KEY ( `id` )
-> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| demo |
+----------------+
1 row in set (0.00 sec)
mysql> desc demo;
+--------+------------------+------+-----+---------+------------
| Field | Type | Null | Key | Default | Extra
+--------+------------------+------+-----+---------+------------
| id | int(10) unsigned | NO | PRI | NULL | auto_increm
| rank | varchar(100) | NO | | NULL |
| name | varchar(40) | NO | | NULL |
| gender | tinyint(4) | NO | | NULL |
+--------+------------------+------+-----+---------+------------
4 rows in set (0.01 sec)
插入測試資料：
mysql> insert into demo values(1, 'A', 'coderaction1', '20');
Query OK, 1 row affected (0.01 sec)
mysql> insert into demo values(2, 'B', 'coderaction2', '21');
Query OK, 1 row affected (0.00 sec)
mysql> insert into demo values(3, 'A', 'coderaction3', '22');
Query OK, 1 row affected (0.00 sec)
mysql> insert into demo values(4, 'C', 'coderaction4', '23');
Query OK, 1 row affected (0.00 sec)
mysql> insert into demo values(5, 'A', 'coderaction5', '21');
Query OK, 1 row affected (0.00 sec)
mysql> insert into demo values(6, 'C', 'coderaction6', '28');
Query OK, 1 row affected (0.01 sec)
mysql>
mysql> select * from demo;
+----+------+--------------+--------+
| id | rank | name | gender |
+----+------+--------------+--------+
| 1 | A | coderaction1 | 20 |
| 2 | B | coderaction2 | 21 |
| 3 | A | coderaction3 | 22 |
| 4 | C | coderaction4 | 23 |
| 5 | A | coderaction5 | 21 |
| 6 | C | coderaction6 | 28 |
+----+------+--------------+--------+
6 rows in set (0.00 sec)
分別執行以下sql命令：
mysql> select count(id) from demo order by rank;
+-----------+
| count(id) |
+-----------+
| 6 |
+-----------+
1 row in set (0.01 sec)
mysql> select count(id) from demo group by rank;
+-----------+
| count(id) |
+-----------+
| 3 |
| 1 |
| 2 |
+-----------+
3 rows in set (0.00 sec)
mysql> select count(rank),id from demo group by rank;
+-------------+----+
| count(rank) | id |
+-------------+----+
| 3 | 1 |
| 1 | 2 |
| 2 | 4 |
+-------------+----+
3 rows in set (0.00 sec)
mysql> select count(rank),id from demo group by id;
+-------------+----+
| count(rank) | id |
+-------------+----+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 1 | 4 |
| 1 | 5 |
| 1 | 6 |
+-------------+----+
6 rows in set (0.00 sec)
mysql>
可以看到上面四個sql都執行成功。
修改sql_mode，臨時修改sql_mode方式有兩種，一種是設定當前會話連線的session級別的sql_mode，另一個是global級別的sql_mode。
session級別
先來看看session級別的sql_mode，設定方式有兩種：
mysql> set session 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';
Query OK, 0 rows affected (0.00 sec)
mysql> set @@session.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';
Query OK, 0 rows affected (0.00 sec)
mysql> select @@session.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@session.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)
設定session級別sql_mode，當前session級別查詢到新的，下次重連後失效。
global級別
再看看global級別的sql_mode，設定方式有兩種：
mysql> set @@global.sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)
mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)
mysql> select @@global.sql_mode;
+------------------------------------------------------------------------------------------------------------------------+
| @@global.sql_mode |
+------------------------------------------------------------------------------------------------------------------------+
| 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)
設定global級別sql_mode，當前session級別查詢到還是舊的，所以執行命令時，還是按照舊配置。下次重連後利用新配置。
當我們設定完上面session級別的sql_mode，在其中加ONLY_FULL_GROUP_BY後，執行測試sql語句報錯：
mysql> select count(rank),id from demo group by rank;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.demo.id' which is not functionally dependen
t on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql> select count(rank),id from demo group by id;
+-------------+----+
| count(rank) | id |
+-------------+----+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 1 | 4 |
| 1 | 5 |
| 1 | 6 |
+-------------+----+
6 rows in set (0.00 sec)
這也驗證了：SELECT後面接的列必須被GROUP BY後面接的列所包含。
注意：透過session和global設定臨時生效的，即當mysql重啟後，都會失效。需要在mysql啟動配置檔案中預設設定。
4、解決辦法
除了上面測試時用到的臨時解決的兩種方法。要想mysql重啟後依然生效，需要在mysql的配置檔案，一般是my.cnf中的[mysqld]下面加sql_mode配置。因為我使用的是k8s部署的mysql，映象安裝和在宿主機上透過軟體包安裝有一定差別。但最終還是更改的my.cnf。
kubectl exec -ti mysql-75797cf796-84rdl bash
root@mysql-75797cf796-84rdl:/#
root@mysql-75797cf796-84rdl:/# cat /etc/mysql/my.cnf
# Copyright (c) 2016, Oracle and/or its affiliates. All rights reserved.
# .....
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
可以看到這裡包含了兩個目錄下的檔案，檢視一下，mysql.conf.d下，發現有我們需要更改的檔案
cat /etc/mysql/mysql.conf.d/mysqld.cnf
檢視並將該檔案用kubectl cp命令複製到宿主機上，修改後最終要掛載進入pod裡。
kubectl cp default/mysql-75797cf796-84rdl:/etc/mysql/mysql.conf.d/mysqld.cnf /data/blog-solo/mysql-config/mysqld.cnf
修改後檔案如下，主要關注sql_mode
root@mysql-75797cf796-84rdl:/# cd /etc/mysql/mysql.conf.d/
root@mysql-75797cf796-84rdl:/etc/mysql/mysql.conf.d# ls -l
total 4
-rw-r--r-- 1 root root 1671 Oct 26 11:40 mysqld.cnf
root@mysql-75797cf796-84rdl:/etc/mysql/mysql.conf.d# cat mysqld.cnf
# Copyright (c) 2014, 2016, Oracle and/or its affiliates. All rights reserved.
# ...
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
sql_mode = STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
#log-error = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
root@mysql-75797cf796-84rdl:/etc/mysql/mysql.conf.d#
最後修改mysql-deployment：
apiVersion: extensions/v1beta1
kind: Deployment
metadata:
name: mysql
spec:
replicas: 1
template:
metadata:
labels:
name: mysql
spec:
containers:
- name: mysql
image: mysql:5.7.28
imagePullPolicy: IfNotPresent
ports:
- containerPort: 3306
env:
- name: MYSQL_ROOT_PASSWORD
value: "password"
volumeMounts:
- name: mysql-config
mountPath: /etc/mysql/mysql.conf.d
- name: mysql-data
mountPath: /var/lib/mysql
volumes:
- name: mysql-config
hostPath:
path: /data/blog-solo/mysql-config/
- name: mysql-data
hostPath:
path: /data/blog-solo/mysql-data/
注意要把配置檔案和資料都掛載到宿主機上，否則pod重啟後就會丟失配置和資料。
4、參考
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/4822/viewspace-2824095/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- .NetCore WebApi + Vue +MySql搭建部落格NetCoreWebAPIVueMySql
- mysql中sql_mode值設定MySql
- node+koa2+mysql搭建部落格後臺MySql
- 搭建自己的技術部落格系列（四）部落格接入百度統計，隨時瞭解你部落格的PV/UV
- 我的部落格搭建筆記筆記
- 你的部落格可能被爬了
- MySQL5.7中的sql_mode預設值MySql
- Hexo 搭建部落格Hexo
- 搭建Hexo部落格相簿Hexo
- 搭建個人部落格
- 部落格搭建過程
- 如何搭建部落格
- 分享一下 vue + koa + mysql 搭建部落格之旅VueMySql
- 【部落格搭建】Typecho個人部落格搭建，快速安裝，超小白（很簡單的）
- Django搭建個人部落格：編寫部落格文章的Model模型Django模型
- mysql知識部落格MySql
- 基於Vue搭建自己的部落格Vue
- 我的個人部落格搭建之旅
- GitHub Pages 搭建部落格Github
- hexo 部落格搭建筆記Hexo筆記
- 個人部落格搭建( wordpress )
- 使用Docker搭建Chirpy部落格Docker
- 部落格搭建-圖床篇圖床
- Hexo部落格搭建記錄Hexo
- 2021年Wordpress部落格搭建
- MySQL的sql_mode合理設定MySql
- MySQL 的 sql_mode 合理設定MySql
- Django搭建個人部落格：在Windows中搭建開發環境DjangoWindows開發環境
- vps攜手hexo的部落格搭建之旅Hexo
- 為什麼要搭建自己的部落格
- 使用 hexo 搭建個人部落格Hexo
- 利用docker快速搭建hexo部落格DockerHexo
- 部落格搭建-自建Lychee圖床圖床
- 整合github、hexo搭建部落格GithubHexo
- Django搭建個人部落格：前言Django
- hexo搭建個人部落格心得Hexo
- 使用React搭建個人部落格React
- beego搭建個人部落格（二）Go