这是一次惨痛的教训,所以要记录一下,也和大家分享一下解决方案。
由于操作不当,把我自己的数据库给完全删除重装了。数据也没备份。哎
在解决这些需要改动配置的时候建议都先备份一下数据。
备份数据,备份数据,备份数据。
问题描述
在MySQL数据库下,执行SQL插入语句报错或者进入数据库时。出现1055错误信息。
错误原因
在MySQL5.7之后,sql_mode中默认存在ONLY_FULL_GROUP_BY,SQL语句未通过ONLY_FULL_GROUP_BY语义检查所以报错。
ONLY_FULL_GROUP_BY:ONLY_FULL_GROUP_BY要求select语句中查询出来的列必须是明确的(其他语句也是一样)。
以SQL语句select columes from table group by list为例:columns必须是聚集函数或者在group by后的表达式list中,并且list中必须包含主键,否则也会报错。
insert、update、delete语句都会报错(但不影响SQL语句的执行),因为这三种语句执行之前也会执行查询操作。
以主键为id的表为例:
SELECT count(1) FROM customer GROUP BY name;该SQL执行成功,因为count是聚集函数;
SELECT * FROM customer GROUP BY name;该SQL执行失败,因为*中包含主键id,而group by后的表达式中并没有包含id
SELECT name FROM customer GROUP BY name;该SQL执行成功,因为name包含在group by后的表达式中
SELECT name, contact FROM customer GROUP BY name;该SQL执行失败,因为contact没有包含在group by后的表达式中
解决方案
一、永久解决
1)在MySQL下执行SELECT @@sql_mode语句
2)将查询结果中的ONLY_FULL_GROUP_BY去掉然后复制,打开MySQL的配置文件,将sql_mode的值设置为复制的值(若没有sql_mode在[mysqld]下方添加一行即可)。
MySQL配置文件所在位置:安装版可通过windows服务所对应mysql启动项,查看其对应属性->可执行文件路径,获取my.ini路径。
免安装版一般在其根目录下。(默认是my-default.ini,必须将名字改为my.ini才能生效)
ubuntu
vim /etc/mysql/mysql.conf.d/mysqld.cnf
安装位置不同的,找到这个mysqld.cnf这个文件然后修改即可
若没有sql_mode在[mysqld]下方添加一行即可
sql_mode=…(删掉ONLY_FULL_GROUP_BY的那段)
3)重新MySQL服务即可生效!
临时方案
连接上mysql后,删除ONLY_FULL_GROUP_BY模式即可:
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));