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

分手后的思念是犯贱 2022-06-05 05:23 170阅读 0赞

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

或者

**(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 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