我的mysql出现了only_full_group_by问题,网上一堆处理方案! 主要两种 一种修改配置表my.ini 另一种通过指令,屏蔽当前链接的only_full_group_by报错!我想永久解决这个问题,很显然第二种办法不行。可是我这里修改my.ini文件进行处理,重启mysql也不行,重启后查看还是有only_full_group_by,于是果断重装mysql,没说的!
先卸载,然后按照这篇文章的过程先配置好my.ini,然后再安装!
https://blog.csdn.net/fenfenguai/article/details/82755650
安装后按照文章提示进行查看,好了!
在window下,修改mysql安装目录下,例如我的在C:myspacemysql-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 RAM for the most important dataF # cache in MySQL. Start at 70% 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:myspacemysql-5.7.17-winx64 # 设置mysql数据库的数据的存放目录 datadir = C:myspacemysql-5.7.17-winx64data #设置3306端口 port = 3306 # 允许最大连接数 max_connections=200 # 服务端使用的字符集默认为8比特编码的latin1字符集 character-set-server=utf8 # 创建新表时将使用的默认存储引擎 default-storage-engine=INNODB # server_id = ..... #默认是8小时,现在设置21天 wait_timeout=1814400 interactive_timeout=1814400 # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions 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)