Mac 的mysql5.7没有配置文件，如何解决only_full_group_by 问题

绝地灬酷狼 2022-05-19 08:39 176阅读 0赞

### 问题：使用group by 搜索报错（也可以设置mysql中文编码） ###

运行环境，MAC系统，mysql版本5.7.20   
解决过程：   
网上搜索答案，大概就是修改mysql配置文件my.cnf中的sql model,删除only\_full\_group\_by   
进入终端：   
输入   
mysql -u root -p   
password：mysql密码 进入mysql>   
输入 select @@global.sql\_mode;   
（分号别丢）   
查看sqlmodel 出现only\_full\_group\_by

参考1：[http://blog.csdn.net/fenfenguai/article/details/53941379][http_blog.csdn.net_fenfenguai_article_details_53941379]   
此篇作者是修改/etc/my.cnf 下的，   
sql\_mode=STRICT\_TRANS\_TABLES,NO\_ZERO\_IN\_DATE,NO\_ZERO\_DATE,ERROR\_FOR\_DIVISION\_BY\_ZERO,NO\_AUTO\_CREATE\_USER,NO\_ENGINE\_SUBSTITUTION   
但是我的以下两个文件都没有。   
/usr/local/mysql/support-files/my-default.cnf   
/etc/my.cnf   
参考2：[https://www.jianshu.com/p/628bcf8bb557][https_www.jianshu.com_p_628bcf8bb557]

**以下是解决方法：**

打开终端执行下面两条命令：   
cd /etc   
sudo vim my.cnf   
打开my.cnf   
把下面内容复制到my.cnf中   
ok，问题解决了！   
参考3：[https://stackoverflow.com/questions/16475118/how-to-fix-unknown-variable-sql-mode-ansi][https_stackoverflow.com_questions_16475118_how-to-fix-unknown-variable-sql-mode-ansi]

\# Example MySQL config file for medium systems.   
\#   
\# This is for a system with little memory (32M - 64M) where MySQL plays   
\# an important part, or systems up to 128M where MySQL is used together with   
\# other programs (such as a web server)   
\#   
\# MySQL programs look for option files in a set of   
\# locations which depend on the deployment platform.   
\# You can copy this option file to one of those   
\# locations. For information about these locations, see:   
\# [http://dev.mysql.com/doc/mysql/en/option-files.html][http_dev.mysql.com_doc_mysql_en_option-files.html]   
\#   
\# In this file, you can use all long options that a program supports.   
\# If you want to know which options a program supports, run the program   
\# with the “–help” option.   
\# The following options will be passed to all MySQL clients   
\[client\]   
default-character-set=utf8   
\#password = your\_password   
port = 3306   
socket = /tmp/mysql.sock   
\# Here follows entries for some specific programs   
\# The MySQL server   
\[mysqld\]   
character-set-server=utf8   
init\_connect=’SET NAMES utf8   
port = 3306   
socket = /tmp/mysql.sock   
skip-external-locking   
key\_buffer\_size = 16M   
max\_allowed\_packet = 1M   
table\_open\_cache = 64   
sort\_buffer\_size = 512K   
net\_buffer\_length = 8K   
read\_buffer\_size = 256K   
read\_rnd\_buffer\_size = 512K   
myisam\_sort\_buffer\_size = 8M   
character-set-server=utf8   
init\_connect=’SET NAMES utf8’   
init-command=”set sql\_mode=’STRICT\_TRANS\_TABLES,NO\_ZERO\_IN\_DATE,NO\_ZERO\_DATE,ERROR\_FOR\_DIVISION\_BY\_ZERO,NO\_AUTO\_CREATE\_USER,NO\_ENGINE\_SUBSTITUTION’”   
\# Don’t listen on a TCP/IP port at all. This can be a security enhancement,   
\# if all processes that need to connect to mysqld run on the same host.   
\# All interaction with mysqld must be made via Unix sockets or named pipes.   
\# Note that using this option without enabling named pipes on Windows   
\# (via the “enable-named-pipe” option) will render mysqld useless!   
\#skip-networking   
\# Replication Master Server (default)   
\# binary logging is required for replication   
log-bin=mysql-bin   
\# binary logging format - mixed recommended   
binlog\_format=mixed   
\# required unique id between 1 and 2^32 - 1   
\# defaults to 1 if master-host is not set   
\# but will not function as a master if omitted   
server-id = 1   
\# Replication Slave (comment out master section to use this)   
\#   
\# To configure this host as a replication slave, you can choose between   
\# two methods :   
\#   
\# 1) Use the CHANGE MASTER TO command (fully described in our manual) -   
\# the syntax is:   
\#   
\# CHANGE MASTER TO MASTER\_HOST=, MASTER\_PORT=,   
\# MASTER\_USER=, MASTER\_PASSWORD= ;   
\#   
\# where you replace , , by quoted strings and   
\# by the master’s port number (3306 by default).   
\#   
\# Example:   
\#   
\# CHANGE MASTER TO MASTER\_HOST=’125.564.12.1’, MASTER\_PORT=3306,   
\# MASTER\_USER=’joe’, MASTER\_PASSWORD=’secret’;   
\#   
\# OR   
\#   
\# 2) Set the variables below. However, in case you choose this method, then   
\# start replication for the first time (even unsuccessfully, for example   
\# if you mistyped the password in master-password and the slave fails to   
\# connect), the slave will create a master.info file, and any later   
\# change in this file to the variables’ values below will be ignored and   
\# overridden by the content of the master.info file, unless you shutdown   
\# the slave server, delete master.info and restart the slaver server.   
\# For that reason, you may want to leave the lines below untouched   
\# (commented) and instead use CHANGE MASTER TO (see above)   
\#   
\# required unique id between 2 and 2^32 - 1   
\# (and different from the master)   
\# defaults to 2 if master-host is set   
\# but will not function as a slave if omitted   
\#server-id = 2   
\#   
\# The replication master for this slave - required   
\#master-host =   
\#   
\# The username the slave will use for authentication when connecting   
\# to the master - required   
\#master-user =   
\#   
\# The password the slave will authenticate with when connecting to   
\# the master - required   
\#master-password =   
\#   
\# The port the master is listening on.   
\# optional - defaults to 3306   
\#master-port =   
\#   
\# binary logging - not required for slaves, but recommended   
\#log-bin=mysql-bin   
\# Uncomment the following if you are using InnoDB tables   
\#innodb\_data\_home\_dir = /usr/local/mysql/data   
\#innodb\_data\_file\_path = ibdata1:10M:autoextend   
\#innodb\_log\_group\_home\_dir = /usr/local/mysql/data   
\# You can set ..\_buffer\_pool\_size up to 50 - 80 %   
\# of RAM but beware of setting memory usage too high   
\#innodb\_buffer\_pool\_size = 16M   
\#innodb\_additional\_mem\_pool\_size = 2M   
\# Set ..\_log\_file\_size to 25 % of buffer pool size   
\#innodb\_log\_file\_size = 5M   
\#innodb\_log\_buffer\_size = 8M   
\#innodb\_flush\_log\_at\_trx\_commit = 1   
\#innodb\_lock\_wait\_timeout = 50   
\[mysqldump\]   
quick   
max\_allowed\_packet = 16M   
\[mysql\]   
no-auto-rehash   
\# Remove the next comment character if you are not familiar with SQL   
\#safe-updates   
default-character-set=utf8   
init-command=”set sql\_mode=’STRICT\_TRANS\_TABLES,NO\_ZERO\_IN\_DATE,NO\_ZERO\_DATE,ERROR\_FOR\_DIVISION\_BY\_ZERO,NO\_AUTO\_CREATE\_USER,NO\_ENGINE\_SUBSTITUTION’”   
\[myisamchk\]   
key\_buffer\_size = 20M   
sort\_buffer\_size = 20M   
read\_buffer = 2M   
write\_buffer = 2M   
\[mysqlhotcopy\]   
interactive-timeout

[http_blog.csdn.net_fenfenguai_article_details_53941379]: http://blog.csdn.net/fenfenguai/article/details/53941379
[https_www.jianshu.com_p_628bcf8bb557]: https://www.jianshu.com/p/628bcf8bb557
[https_stackoverflow.com_questions_16475118_how-to-fix-unknown-variable-sql-mode-ansi]: https://stackoverflow.com/questions/16475118/how-to-fix-unknown-variable-sql-mode-ansi
[http_dev.mysql.com_doc_mysql_en_option-files.html]: http://dev.mysql.com/doc/mysql/en/option-files.html