mac本地Mysql8 不支持only_full_group_by报错解决方案

问题图片：

查看配置

查看本机my.cnf配置文件所在

☁  local  mysql --verbose --help | grep my.cnf
/etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf

配置文件所在位置是 /usr/local/etc/my.cnf

☁  local  cat /usr/local/etc/my.cnf
# Default Homebrew MySQL server config
[mysqld]
# Only allow connections from localhost
bind-address = 127.0.0.1
mysqlx-bind-address = 127.0.0.1

查看mysql安装路径

☁  local  ps -ef|grep mysql
  501   648     1   0 10:12上午 ??         0:00.04 /bin/sh /usr/local/opt/mysql/bin/mysqld_safe --datadir=/usr/local/var/mysql
  501   807   648   0 10:12上午 ??         1:41.05 /usr/local/opt/mysql/bin/mysqld --basedir=/usr/local/opt/mysql --datadir=/usr/local/var/mysql --plugin-dir=/usr/local/opt/mysql/lib/plugin --log-error=alex.err --pid-file=alex.pid
  501 11247 11176   0  7:34上午 ttys001    0:00.02 mysql -uroot -p
  501 11654 11264   0  8:00上午 ttys002    0:00.00 grep --color=auto --exclude-dir=.bzr --exclude-dir=CVS --exclude-dir=.git --exclude-dir=.hg --exclude-dir=.svn --exclude-dir=.idea --exclude-dir=.tox mysql

1、解决方案一（重启mysql失效）

1.1、终端

进入mysql
终端输入 ： mysql -uroot -p
然后输入密码进入mysql
输入：SELECT @@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_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+

通过以下脚本关闭 ：
SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY,',''));

再次查询 @@sql_mode 返回中应该已经没有该模式了。

mysql> SELECT @@sql_mode;
+----------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                         |
+----------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------------------------------------------+

navicat

或者在可视化工具navicat中执行

-- 支持full_group_by
set @@global.sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
set @@session.sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

或者

选择对应数据库，同上执行下面两句，可以查看和配置sql_mode
SELECT @@sql_mode;  
SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY,',''));

此方法重启mysql后失效。

附加：mac下如何重启

## 查看mac下mysql安装位置
☁  local  ps -ef|grep mysql
  501 11247 11176   0  7:34上午 ttys001    0:00.02 mysql -uroot -p
  501 12097 11176   0  8:08上午 ttys001    0:00.02 mysql -uroot -p
  501 18861     1   0  8:23上午 ttys002    0:00.03 /bin/sh /usr/local/Cellar/mysql/8.0.18_1/bin/mysqld_safe --datadir=/usr/local/var/mysql --pid-file=/usr/local/var/mysql/alex.pid
  501 18972 18861   0  8:23上午 ttys002    0:01.26 /usr/local/Cellar/mysql/8.0.18_1/bin/mysqld --basedir=/usr/local/Cellar/mysql/8.0.18_1 --datadir=/usr/local/var/mysql --plugin-dir=/usr/local/Cellar/mysql/8.0.18_1/lib/plugin --log-error=alex.err --pid-file=/usr/local/var/mysql/alex.pid
  501 19886 11264   0  8:25上午 ttys002    0:00.00 grep --color=auto --exclude-dir=.bzr --exclude-dir=CVS --exclude-dir=.git --exclude-dir=.hg --exclude-dir=.svn --exclude-dir=.idea --exclude-dir=.tox mysql
 
# basedir=/usr/local/Cellar/mysql/8.0.18_1是安装位置
☁  local  ll /usr/local/Cellar/mysql/8.0.18_1
total 1848
-rw-r--r--   1 wangzhao  staff   1.1K 11 23  2019 INSTALL_RECEIPT.json
-rw-r--r--   1 wangzhao  staff   399K  9 20  2019 LICENSE
-rw-r--r--   1 wangzhao  staff   399K  9 20  2019 LICENSE-test
-rw-r--r--   1 wangzhao  staff   101K  9 20  2019 LICENSE.router
-rw-r--r--   1 wangzhao  staff   687B  9 20  2019 README
-rw-r--r--   1 wangzhao  staff   687B  9 20  2019 README-test
-rw-r--r--   1 wangzhao  staff   700B  9 20  2019 README.router
drwxr-xr-x  40 wangzhao  staff   1.3K 11 23  2019 bin
-rw-r--r--   1 wangzhao  staff   543B 11 23  2019 homebrew.mxcl.mysql.plist
drwxr-xr-x   3 wangzhao  staff    96B  9 20  2019 include
drwxr-xr-x  16 wangzhao  staff   512B  9 20  2019 lib
drwxr-xr-x   6 wangzhao  staff   192B  9 20  2019 share
drwxr-xr-x   5 wangzhao  staff   160B 11 23  2019 support-files

☁  local  cd  /usr/local/Cellar/mysql/8.0.18_1/bin
☁  bin  ls
ibd2sdi                   mysql                     mysql_upgrade             mysqldumpslow             mysqlslap
innochecksum              mysql.server              mysqladmin                mysqlimport               mysqltest
lz4_decompress            mysql_client_test         mysqlbinlog               mysqlpump                 mysqltest_safe_process
my_print_defaults         mysql_config              mysqlcheck                mysqlrouter               mysqlxtest
myisam_ftdump             mysql_config_editor       mysqld                    mysqlrouter_keyring       perror
myisamchk                 mysql_secure_installation mysqld_multi              mysqlrouter_passwd        zlib_decompress
myisamlog                 mysql_ssl_rsa_setup       mysqld_safe               mysqlrouter_plugin_info
myisampack                mysql_tzinfo_to_sql       mysqldump                 mysqlshow

## 查看当前运行状态
☁  bin  mysql.server status
 SUCCESS! MySQL running (807)
 
# 重启
☁  bin  mysql.server restart
Shutting down MySQL
.... SUCCESS!
Starting MySQL
.. SUCCESS!

2、解决方案二（重永久失效）

修改my.conf配置文件

☁  bin  pwd
/usr/local/Cellar/mysql/8.0.18_1/bin

## 一定sudo
☁  bin  sudo vim /usr/local/etc/my.cnf
Password:

# 新增 
#[mysqld]
#sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

#重启
☁  bin  mysql.server restart
Shutting down MySQL
.. SUCCESS!
Starting MySQL
. SUCCESS!

OK，不再报错了。