MySql出现错误:ERROR 1055 (42000) 和 MYSQL的WARNINGS 和 ERRORS查询细节
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'R.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
(1) . 背景:
一个定时运行的项目,从9月份上线以来一直正常运行,其中一个读取数据库的python脚本于昨天11月17日却报了错误,如下:
ERROR 1055 (42000)
遇到此问题,不要慌张,要打印出来异常的那条sql语句,然后人工判断语句是否有错误之处,确认无误之后,
在命令行窗口登录MySQL,执行错误的语句,看报什么错误,
具体问题具体分析解决.
mysql> select * from (select * from obj_questions_records order by utime desc ) as R group by user_id,uuid order by utime desc;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'R.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
或者
ERROR 1055 (42000):
mysql> show warnings;
+-------+------+---------+
| Level | Code | Message |
+-------+------+---------+
| Error | 1055 | |
+-------+------+---------+
1 row in set (0.00 sec)
mysql> show errors;
+-------+------+---------+
| Level | Code | Message |
+-------+------+---------+
| Error | 1055 | |
+-------+------+---------+
1 row in set (0.00 sec)
mysql>
(2). 问题原因: ONLY_FULL_GROUP_BY的意思是:对于GROUP BY聚合操作,如果在SELECT中的列,没有在GROUP BY中出现,那么这个SQL是不合法的,因为列不在GROUP BY从句中,也就是说查出来的列必须在group by后面出现否则就会报错,或者这个字段出现在聚合函数里面。
(3). 探索原因:
1) 查看mysql版本命令:select version();
1) 查看mysql版本命令:select version();
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.19 |
+-----------+
1 row in set (0.00 sec)
2) 查看sql_model参数命令:
mysql> SELECT @@GLOBAL.sql_mode;
+------------------------------------------------------------------------------------------------------------------------+
| @@GLOBAL.sql_mode |
+------------------------------------------------------------------------------------------------------------------------+
| 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)
mysql> SELECT @@SESSION.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@SESSION.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)
发现: 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
第一项默认开启ONLY_FULL_GROUP_BY了。导致出现了错误。现在要把它的默认关掉。
(4) 解决方法:
1) 命令行输入
mysql> 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' ;
Query OK, 0 rows affected (0.00 sec)
2) 然后就可以成功执行啦,如下:
mysql> select * from objective_questions_records order by utime desc
-> ;
+------+---------+----------------------------------+---------------------+----------+
| id | user_id | uuid | utime | is_right |
+------+---------+----------------------------------+---------------------+----------+
| 2145 | 548126 | 828a333 | 2017-08-12 11:20:36 | 6 |
| 2344 | 334243 | 3342432weowwej | 2017-08-12 11:19:27 | 4 |
| 1214 | 548126 | 828a333 | 2017-08-12 10:36:28 | 0 |
| 2133 | 1234545 | 29320a8813514a5bb5a28a605ddbc5a2 | 2017-08-08 10:38:09 | 3 |
| 1215 | 334243 | 8aac4907508d5d3d0150bbefc20e4ba5 | 2017-08-06 10:36:14 | 1 |
| 2921 | 334243 | 8aac4907508d5d3d0150bbefc20e4ba5 | 2017-08-02 10:36:27 | 0 |
| 1232 | 3748549 | 29320a8813514a5bb5a28a605ddbc5a2 | 2017-07-04 10:39:24 | 2 |
| 2134 | 334243 | 29320a8813514a5bb5a28a605ddbc5a2 | 2017-06-14 10:38:12 | 5 |
| 2919 | 334243 | 29320a8813514a5bb5a28a605ddbc5a2 | 2016-03-15 18:12:25 | 1 |
+------+---------+----------------------------------+---------------------+----------+
9 rows in set (0.00 sec)
注意,此时的修改可能是临时的,要想彻底解决这个问题,网上说,可以修改MySQL的配置文件,参见
http://blog.csdn.net/u283056051/article/details/52463948
这需要获得操作该数据库的管理员权限,如果没有,则可以在每次执行临时设置,具体如下:打开该数据库连接之后,执行数据操作之前,执行如下名sql命令,然后就可以正常处理。
MYSQL的WARNINGS 和 ERRORS查询细节 --SHOW ERRORS 语句只是显示上一个语句的错误,不同时显示警告以及注意事项。
举个例子:
这需要获得操作该数据库的管理员权限,如果没有,则可以在每次执行临时设置,具体如下:打开该数据库连接之后,执行数据操作之前,执行如下名sql命令,然后就可以正常处理。
def get_Records(self, sql):
try:
self.conn_get_records.connect()
set_sql_mode='''
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'
'''
self.conn_get_records.execute(set_sql_mode)
sql='''select * from (select * from obj_questions_records order by utime desc ) as R group by user_id,uuid order by utime desc'''
excute_result=self.conn_get_records.execute(sql)
return excute_result
except:
print sql
return 0
#exit(1)
finally:
self.conn_get_records.close()
(5) 其他显示sql错误的MySQL命令介绍MYSQL的WARNINGS 和 ERRORS查询细节 --SHOW ERRORS 语句只是显示上一个语句的错误,不同时显示警告以及注意事项。
举个例子:
mysql> show dfdafsadf;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'dfdafsadf' at line 1
这里就有个错误。关于如何显示她,已经很明显了。
mysql> show errors;
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Error | 1064 | You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'dfdafsadf' at line 1 |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show errors \G;
*************************** 1. row ***************************
Level: Error
Code: 1064
Message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'dfdafsadf' at line 1
1 row in set (0.00 sec)
ERROR:
No query specified
如果一下子有好多错误,而你又想只显示第二条的话:show errorw limit 1,1;
如果你想看到有错误的数目,前面的 1 rows in set 已经很明显了。
不过还有办法:
mysql> show count(*) errors;
+-----------------------+
| @@session.error_count |
+-----------------------+
| 1 |
+-----------------------+
1 row in set (0.00 sec)
mysql> show count(1) errors;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1) errors' at line 1
注意:这里的count(*)不能写成count(1).你还可以这样:
mysql> select @@error_count;
+---------------+
| @@error_count |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)
SHOW WARNINGS 显示上一个语句的错误、警告以及注意。基本语法和SHOW ERRORS大同小异。
不过要注意的是在MYSQL5后的大部分以前的WARNINGS直接被显示为ERRORS,如下:
mysql> show dfdafsadf;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'dfdafsadf' at line 1
mysql> show errors;
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Error | 1064 | You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'dfdafsadf' at line 1 |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show warnings;
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Error | 1064 | You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'dfdafsadf' at line 1 |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
参考网址:
http://blog.chinaunix.net/uid-259788-id-2139240.html
http://blog.csdn.net/u011409644/article/details/73611102
http://blog.csdn.net/u283056051/article/details/52463948
http://www.linuxidc.com/Linux/2017-08/146516.htm