我的mysql出现了only_full_group_by问题,网上一堆处理方案! 主要两种 一种修改配置表my.ini 另一种通过指令,屏蔽当前链接的only_full_group_by报错!我想永久解决这个问题,很显然第二种办法不行。可是我这里修改my.ini文件进行处理,重启mysql也不行,重启后查看还是有only_full_group_by,于是果断重装mysql,没说的!
先卸载,然后按照这篇文章的过程先配置好my.ini,然后再安装!
安装后按照文章提示进行查看,好了!
在window下,修改mysql安装目录下,例如我的在C:\myspace\mysql-5.7.17-winx64路径下的my.ini文件 参考红字部分的设置,其他设置仅供参考
[client]
#设置mysql客户端默认字符集default-character-set=utf8
[mysql]default-character-set=utf8
[mysqld]
#设置sql_mode 很重要 否则初始化好了以后进行查询操作会出现only_full_group_by异常 如果在初始化#的时候没有设置 则需要重新进行初始化
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'# Remove leading # and set to the amount of RAMforthe most important dataF
# cache in MySQL. Start at70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size=128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set as required.
# 设置mysql的安装目录
basedir= C:\myspace\mysql-5.7.17-winx64
# 设置mysql数据库的数据的存放目录
datadir= C:\myspace\mysql-5.7.17-winx64\data
#设置3306端口
port= 3306# 允许最大连接数
max_connections=200# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎default-storage-engine=INNODB
# server_id=.....
#默认是8小时,现在设置21天
wait_timeout=1814400interactive_timeout=1814400# Remove leading # to set options mainly usefulforreporting servers.
# The server defaults are fasterfortransactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size=128M
# sort_buffer_size=2M
# read_rnd_buffer_size=2M
# sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
在linux下
找到路径 /etc/mysql/mysql.conf.d 下的mysqld.cnf 这个就相当于window下的 my.ini配置文件
/etc/mysql/mysql.conf.d# vim mysqld.cnf (用vim打开次文件,在文件最后添加 sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION')
/etc/mysql/mysql.conf.d# sudo service mysql restart (保存退出,重启mysql)