如何解决"ORDER BY子句不在SELECT列表中"?导致SELECT DISTINCT和ORDER BY的MySQL 5.7 [英] How to resolve "ORDER BY clause is not in SELECT list" caused MySQL 5.7 with SELECT DISTINCT and ORDER BY

问题描述

我安装了新的Ubuntu，并且我的代码出现MySQL问题.

I installed the new Ubuntu and my code has got a problem with MySQL.

( ! ) Warning: PDOStatement::execute(): SQLSTATE[HY000]: General error: 3065 
Expression #2 of ORDER BY clause is not in SELECT list, references column 'clicshopping_test_ui.p.products_date_added' which is not in SELECT list; this is incompatible with DISTINCT 
in /home/www//boutique/includes/OM/DbStatement.php on line 97s

似乎MySQL 5.7不允许这样的请求:

It seems MySQL 5.7 does'nt allow a request like:

select .... distinct with  order by rand(), p.products_date_added DESC

如果我使用它，它将起作用:

If I use this it works:

select distinct .... with  order by rand(), 

如何解决这种情况?

我在PHP中的SQL请求

My SQL request in PHP

 $Qproduct = $OSCOM_PDO->prepare('select distinct p.products_id,
            p.products_price
            from :table_products p left join :table_specials s on p.products_id = s.products_id
            where products_status = :products_status
            and products_view = :products_view
            and p.products_archive = :products_archive
            order by rand(),
            p.products_date_added DESC
            limit :products_limit');
                  $Qproduct->bindInt(':products_status', 1);
                  $Qproduct->bindInt(':products_view', 1);
                  $Qproduct->bindInt(':products_archive', 0);
                  $Qproduct->bindInt(':products_limit', 
                  (int)MODULE_FRONT_PAGE_NEW_PRODUCTS_MAX_DISPLAY);

推荐答案

如果您可以控制服务器并且正在运行旧版代码，则无法轻松更改，则可以

If you have control of the server and you are running legacy code you can't easily change, you can adjust the SQL mode of the server and remove "only_full_group_by" either for the duration of boot, by running the query

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

或通过在您的my.cnf文件中添加sql_mode=''.

or by adding sql_mode='' to your my.cnf file.

如果可能的话，显然最好更改代码，但是如果没有，这将禁用该警告.

Obviously its better to change your code if you have the possibility, but if not, this will disable that warning.

这篇关于如何解决"ORDER BY子句不在SELECT列表中"?导致SELECT DISTINCT和ORDER BY的MySQL 5.7的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！