这篇文章主要介绍了MySQL中使用group by 是总是出现1055的错误,小编通过查阅相关资料才把问题解决,今天小编记录下分享到爱安网平台,需要的朋友可以参考下

因为在MySQL中使用group by 是总是出现1055的错误,这就导致了必须去查看是什么原因了,查询了相关的资料,现在将笔记记录下来,以便后面可以参考使用:

sql_mode:简而言之就是:它定义了你MySQL应该支持的sql语法,对数据的校验等等

select @@sql_mode:使用该命令我们可以查看我们当前数据库的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_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

下面我们来看看sql_mode的各个值的含义:

ONLY_FULL_GROUP_BY:

对于GROUP BY聚合操作,如果在SELECT中的列,没有在GROUP BY中出现,那么将认为这个SQL是不合法的,因为列不在GROUP BY从句中

因为有only_full_group_by,所以我们要在MySQL中正确的使用group by语句的话,只能是select column1 from tb1 group by column1(即只能展示group by的字段,其他均都要报1055的错)

实例:

mysql> select * from tt1;
+----+-------+--------+
| id | name | gender |
+----+-------+--------+
| 1 | xiong |   0 |
| 2 | ying |   0 |
| 3 | cai  |   0 |
| 4 | zhang |   0 |
| 5 | li  |   1 |
| 6 | wang |   1 |
+----+-------+--------+
6 rows in set (0.00 sec)
 
mysql> select id,name from tt1 group by name;
ERROR 1055 (42000):

mysql> select * from tt1;
+----+-------+--------+
| id | name | gender |
+----+-------+--------+
| 1 | xiong |   0 |
| 2 | ying |   0 |
| 3 | cai  |   0 |
| 4 | zhang |   0 |
| 5 | li  |   1 |
| 6 | wang |   1 |
+----+-------+--------+
6 rows in set (0.00 sec)
mysql> select name from tt1 group by name;
+-------+
| name |
+-------+
| cai  |
| li  |
| wang |
| xiong |
| ying |
| zhang |
+-------+
6 rows in set (0.00 sec)

所以我们要使用能正确的使用group by 的话就必须删除掉only_full_group_by

set sql_mode=(select replace(@@sql_mode,'ONLY_FULL_GROUP_BY','')); 可以使用该语句来将空格替换掉only_full_group_by,这样我们就可以使用

mysql> SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
Query OK, 0 rows affected (0.00 sec)
 
mysql> select id,name from tt1 group by name;
+----+-------+
| id | name |
+----+-------+
| 3 | cai  |
| 5 | li  |
| 6 | wang |
| 1 | xiong |
| 2 | ying |
| 4 | zhang |
+----+-------+
6 rows in set (0.00 sec)

但是这种方法只是做了暂时的修改,我们可以更改配置文件my.ini

sql_mode= STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

STRICT_TRANS_TABLES:

在该模式下,如果一个值不能插入到一个事务表中,则中断当前的操作,对非事务表不做任何限制

NO_ZERO_IN_DATE:

在严格模式,不接受月或日部分为0的日期。如果使用IGNORE选项,我们为类似的日期插入'0000-00-00'。在非严格模式,可以接受该日期,但会生成警告。

NO_ZERO_DATE:

在严格模式,不要将 '0000-00-00'做为合法日期。你仍然可以用IGNORE选项插入零日期。在非严格模式,可以接受该日期,但会生成警告

ERROR_FOR_DIVISION_BY_ZERO:

在严格模式,在INSERT或UPDATE过程中,如果被零除(或MOD(X,0)),则产生错误(否则为警告)。如果未给出该模式,被零除时MySQL返回NULL。如果用到INSERT IGNORE或UPDATE IGNORE中,MySQL生成被零除警告,但操作结果为NULL

NO_AUTO_CREATE_USER:

防止GRANT自动创建新用户,除非还指定了密码。

NO_ENGINE_SUBSTITUTION:

如果需要的存储引擎被禁用或未编译,那么抛出错误。不设置此值时,用默认的存储引擎替代,并抛出一个异常

三、据说是MySQL5.0以上版本支持三种sql_mode模式:ANSI、TRADITIONAL和STRICT_TRANS_TABLES。

1、ANSI模式:宽松模式,更改语法和行为,使其更符合标准SQL。对插入数据进行校验,如果不符合定义类型或长度,对数据类型调整或截断保存,报warning警告。对于本文开头中提到的错误,可以先把sql_mode设置为ANSI模式,这样便可以插入数据,而对于除数为0的结果的字段值,数据库将会用NULL值代替。将当前数据库模式设置为ANSI模式:

 mysql> set @@sql_mode=ANSI; 

2、TRADITIONAL模式:严格模式,当向mysql数据库插入数据时,进行数据的严格校验,保证错误数据不能插入,报

error错误,而不仅仅是警告。用于事物时,会进行事物的回滚。 注释:一旦发现错误立即放弃INSERT/UPDATE。如果你使用非事务存储引擎,这种方式不是你想要的,因为出现错误前进行的数据更改不会“”,结果是更新“只进行了一部分”。

将当前数据库模式设置为TRADITIONAL模式:

mysql> set @@sql_mode=TRADITIONAL;

3、STRICT_TRANS_TABLES模式:严格模式,进行数据的严格校验,错误数据不能插入,报error错误。如果不能将给定的值插入到事务表中,则放弃该语句。对于非事务表,如果值出现在单行语句或多行语句的第1行,则放弃该语句。将当前数据库模式设置为STRICT_TRANS_TABLES模式:

mysql> set @@sql_mode=STRICT_TRANS_TABLES;

没有最好与最坏的模式,只有最合适的模式。需要根据自己的实际情况去选择那个最适合的模式!!!

另外说一点,这里的更改数据库模式都是session级别的,一次性,关了再开就不算数了!!!

也可以通过配置文件设置:vim /etc/my.cnf

在my.cnf(my.ini)添加如下配置:

[mysqld]
sql_mode='你想要的模式'

总结

以上所述是小编给大家介绍MySQL中使用group by 是总是出现1055的错误,希望对大家有所帮助!

最新资讯
百度投资智慧树 引入高校资源丰富知识类产品内容

百度投资智慧树 引入

百度于日前完成对智慧树的投资。
是真还了!罗永浩始于2018年年底的6个亿债务已还清4个亿(图)

是真还了!罗永浩始于20

今晚的脱口秀大会总决赛中,@罗永浩 表示其始于2018年年
井贤栋:未来新金融是以区块链建设的价值互联网(全文)

井贤栋:未来新金融是以

井贤栋称,过去的金融风控是防范人,靠抵押和担保穿上防弹
英雄联盟能提高你的视觉选择性注意力

英雄联盟能提高你的视

类似魔兽世界、帝国时代和全面战争等即时战略游戏拥有
如何加快5G用户发展速度?这里有一个全球近3亿用户的共同选择…

如何加快5G用户发展速

2020年对于5G商用规模的拓展成了运营商工作的重点。对
WeWork中国获2亿美元融资 挚信资本姜跃平任代理CEO

WeWork中国获2亿美元

WeWork宣布,WeWork中国获挚信资本2亿美元追加投资,挚信
最新文章
linux centos7安装mysql8的教程

linux centos7安装mys

这篇文章主要介绍了linux centos7安装mysql8的教程,本
Mysql高性能优化技能总结

Mysql高性能优化技能

这篇文章主要介绍了Mysql高性能优化技能总结的相关资
mysql 8.0.19 安装配置方法图文教程

mysql 8.0.19 安装配

这篇文章主要为大家详细介绍了mysql 8.0.19 安装配置
MySQL 的覆盖索引与回表的使用方法

MySQL 的覆盖索引与回

这篇文章主要介绍了MySQL 的覆盖索引与回表的使用方法
Windows10下mysql 8.0.19 winx64安装教程及修改初始密码

Windows10下mysql 8.0

这篇文章主要为大家详细介绍了Windows10下mysql 8.0.1
Workbench连接不上阿里云服务器Ubuntu的Mysql解决方法(已测)

Workbench连接不上阿

这两天为了解决workbench连接不上阿里云服务器的问题,