php 如何解决“ORDER BY 子句不在 SELECT 列表中”导致 MySQL 5.7 使用 SELECT DISTINCT 和 ORDER BY
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36829911/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to resolve "ORDER BY clause is not in SELECT list" caused MySQL 5.7 with SELECT DISTINCT and ORDER BY
提问by kurama
I installed the new Ubuntu and my code has got a problem with MySQL.
我安装了新的 Ubuntu，但我的代码在 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
It seems MySQL 5.7 does'nt allow a request like:
似乎 MySQL 5.7 不允许这样的请求：
select .... distinct with order by rand(), p.products_date_added DESC
If I use this it works:
如果我使用它，它会起作用：
select distinct .... with order by rand(),
How to resolve this situation ?
如何解决这种情况？
My SQL request in PHP
我在 PHP 中的 SQL 请求
$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);
回答by Justin Cherniak
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 serverand remove "only_full_group_by" either for the duration of boot, by running the query
如果您可以控制服务器并且正在运行无法轻松更改的遗留代码，则可以通过运行查询来调整服务器的 SQL 模式并在启动期间删除“only_full_group_by”
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
or by adding sql_mode=''to your my.cnf file.
或添加sql_mode=''到您的 my.cnf 文件中。
Obviously its better to change your code if you have the possibility, but if not, this will disable that warning.
显然，如果有可能，最好更改您的代码，但如果没有，这将禁用该警告。
回答by Wolfack
In order to fix the issue open the following file:
要解决此问题，请打开以下文件：
/etc/mysql/mysql.conf.d/mysqld.cnf
and add the following line under [mysqld] block
并在 [mysqld] 块下添加以下行
sql-mode=""
回答by Kaizoku Gambare
With MAMP PRO
使用 MAMP PRO
You cannot edit your my.cnf file directly.
You must use the MAMP PRO interface to edit your my.cnf file.
In the menu go to File > Edit Template > MySQL > my.cnf.
Then add sql_mode=''under the [mysqld]key
您不能直接编辑 my.cnf 文件。您必须使用 MAMP PRO 界面来编辑 my.cnf 文件。在菜单中转到文件 > 编辑模板 > MySQL > my.cnf。然后sql_mode=''在[mysqld]key下添加
回答by rapaelec
If you have phpMyAdmin:
如果您有 phpMyAdmin：
1-go to the Variables tabs
1-转到变量选项卡
2-search label "sql mode"
2-搜索标签“sql模式”
3-edit the content and delete the mode : "ONLY_FULL_GROUP_BY"
3-编辑内容并删除模式：“ONLY_FULL_GROUP_BY”
4-save
4-保存
NB: don't forget to verify the comma separator
注意：不要忘记验证逗号分隔符
回答by agm1984
To get ->distinct('some_column')to work for me, I needed to disable ONLY_FULL_GROUP_BYoption in mysql modes.
为了->distinct('some_column')为我工作，我需要ONLY_FULL_GROUP_BY在 mysql 模式下禁用选项。
Rather than edit the mysql config file on the filesystem, I followed the instructions at Laravel : Syntax error or access violation: 1055 Errorand added this to config/database.php:
我没有在文件系统上编辑 mysql 配置文件，而是按照Laravel 中的说明进行操作：语法错误或访问冲突：1055 错误并将其添加到config/database.php：
'strict' => true,
'modes' => [
// 'ONLY_FULL_GROUP_BY', // disabled to allow grouping by one column
'STRICT_TRANS_TABLES',
'NO_ZERO_IN_DATE',
'NO_ZERO_DATE',
'ERROR_FOR_DIVISION_BY_ZERO',
'NO_AUTO_CREATE_USER',
'NO_ENGINE_SUBSTITUTION'
],
In my project, the modesproperty was non-existent, so I simply pasted that bad boy in there.
在我的项目中，该modes属性不存在，所以我只是将那个坏男孩粘贴在那里。
NOTE: Make sure you understand that wiping the modes entirely is undesirable, and basically not strict mode, so you will lose debugging warning/error messages about stuff like varchar length exceeded. The trick is to avoid
sql_mode="". Notice how above I am using 6 modes and explicitly omittingONLY_FULL_GROUP_BY.
注意：确保您了解完全擦除模式是不可取的，并且基本上不是严格模式，因此您将丢失有关超出 varchar 长度之类的调试警告/错误消息。诀窍是避免
sql_mode="". 请注意上面我是如何使用 6 种模式并明确省略ONLY_FULL_GROUP_BY.
回答by Dipanwita Kundu
Try this:
尝试这个：
SELECT 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
GROUP BY p.products_id,p.products_price
LIMIT :products_limit
回答by Sarvagna Shukla
This worked for me that @pvgoran suggested
这对我有用，@pvgoran 建议
SET @@sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
You can run this when you want.
您可以在需要时运行它。
