MySql出现错误：ERROR 1055 (42000) 和 MYSQL的WARNINGS 和 ERRORS查询细节

最新推荐文章于 2024-08-16 15:24:49 发布

Data_IT_Farmer

最新推荐文章于 2024-08-16 15:24:49 发布

阅读量6.9k

点赞数 3

分类专栏： mysql 数据库 Python 文章标签： mysql warnings

本文链接：https://blog.csdn.net/helloxiaozhe/article/details/78570016

版权

Python 同时被 3 个专栏收录

84 篇文章 10 订阅

订阅专栏

mysql

64 篇文章 12 订阅

订阅专栏

数据库

64 篇文章 7 订阅

订阅专栏

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();

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命令，然后就可以正常处理。

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