php 在laravel eloquent 中如何解决与sql_mode=only_full_group_by 不兼容的问题？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/43776758/
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 can I solve incompatible with sql_mode=only_full_group_by in laravel eloquent?
提问by samuel toh
My laravel eloquent is like this :
我的 Laravel 口才是这样的：
$products = Product::where('status', 1)
->where('stock', '>', 0)
->where('category_id', '=', $category_id)
->groupBy('store_id')
->orderBy('updated_at', 'desc')
->take(4)
->get();
When executed, there exist error like this :
执行时，存在如下错误：
SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'myshop.products.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SQL: select * from
productswherestatus= 1 andstock> 0 andcategory_id= 5 group bystore_idorder byupdated_atdesc limit 4)
SQLSTATE[42000]：语法错误或访问冲突：1055 SELECT 列表的表达式 #1 不在 GROUP BY 子句中，并且包含非聚合列“myshop.products.id”，该列在功能上不依赖于 GROUP BY 子句中的列；这与 sql_mode=only_full_group_by 不兼容（SQL：select * from
productswherestatus= 1 andstock> 0 andcategory_id= 5 group bystore_idorder byupdated_atdesc limit 4）
How can I solve it?
我该如何解决？
回答by naabster
I had a similar Problem and solved it by disabling mysql strict mode in the database connection setting.
我有一个类似的问题，并通过在数据库连接设置中禁用 mysql 严格模式来解决它。
'connections' => [
'mysql' => [
// Behave like MySQL 5.6
'strict' => false,
// Behave like MySQL 5.7
'strict' => true,
]
]
You can find even more configuration settings in this blog post by Matt Stauffer
您可以在Matt Stauffer 的这篇博文中找到更多配置设置
回答by AnasSafi
In folder config=> database.phpmake sure mysql strictis false, like this
在文件夹config=> database.php确保 mysql strict是false，像这样
'mysql' => [
'driver' => 'mysql',
'host' => env('DB_HOST', '127.0.0.1'),
'port' => env('DB_PORT', '3306'),
'database' => env('DB_DATABASE', 'forge'),
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
'unix_socket' => env('DB_SOCKET', ''),
'charset' => 'utf8',
'collation' => 'utf8_general_ci',
'prefix' => '',
'strict' => false,
'engine' => null,
],
if strict is true, make it false then clear config cash by run this command in cmd
如果严格为真，则将其设为假，然后通过在 cmd 中运行此命令清除配置现金
php artisan config:clear
php工匠配置：清除
回答by O.Tadj
I solved this problem by adding the "modes" option and setting only the modes I want to be enabled in config => database.php
我通过添加“模式”选项并仅设置我想在config => database.php 中启用的模式解决了这个问题
'mysql' => [
...
'modes' => [
'STRICT_ALL_TABLES',
'ERROR_FOR_DIVISION_BY_ZERO',
'NO_ZERO_DATE',
'NO_ZERO_IN_DATE',
'NO_AUTO_CREATE_USER',
],
],
See more details in this tutorial
在本教程中查看更多详细信息
回答by Stefano Zanini
That's because latest versions of MySQL behave like most dbms already do regarding group byclauses; the general rule is
那是因为最新版本的 MySQL 的行为就像大多数 dbms 已经在group by子句方面所做的一样；一般规则是
if you're using
group by, all columns in yourselectmust be either present in thegroup byor aggregated by an aggregation function (sum,count,avgand so on)
如果你使用
group by，你的所有列select必须是存在于group by或聚合函数汇总（sum，count，avg等）
Your current query is grouping by store_id, but since you're selecting everything the rule above is not respected.
您当前的查询按 分组store_id，但由于您选择了所有内容，因此不遵守上述规则。
回答by amiron
In the .envfile ADD variable: DB_STRICT=false.
在.env文件中添加变量：DB_STRICT=false.
And REPLACE in file from the location: config/database.php, next codes 'strict' => trueON
'strict' => (env('DB_STRICT', 'true') === 'true' ? true : false).
并从以下位置替换文件：config/database.php，下一个代码'strict' => trueON
'strict' => (env('DB_STRICT', 'true') === 'true' ? true : false)。
good luck.
祝你好运。
回答by Akbar Mirsiddikov
set
放
'strict' => false
'strict' => false
in your config/database.php file. In array connections => mysql =>
在您的 config/database.php 文件中。在数组中connections => mysql =>
in my case I'm using mysql 5.7 Laravel 5.7
就我而言，我使用的是 mysql 5.7 Laravel 5.7
回答by Shashi Gharti
I solved it by setting modes in config/database.php file.
我通过在 config/database.php 文件中设置模式来解决它。
Set modes as follows:
设置模式如下：
'modes' => [
'STRICT_TRANS_TABLES',
'NO_ZERO_IN_DATE',
'NO_ZERO_DATE',
'ERROR_FOR_DIVISION_BY_ZERO',
'NO_ENGINE_SUBSTITUTION',
]
for mysql driver
对于 mysql 驱动程序
'mysql' => [
'driver' => 'mysql',
'host' => env('DB_HOST', 'localhost'),
'port' => env('DB_PORT', '3306'),
'database' => env('DB_DATABASE', 'forge'),
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => '',
'strict' => true,
'engine' => null,
'modes' => [
'ONLY_FULL_GROUP_BY',
'STRICT_TRANS_TABLES',
'NO_ZERO_IN_DATE',
'NO_ZERO_DATE',
'ERROR_FOR_DIVISION_BY_ZERO',
'NO_ENGINE_SUBSTITUTION',
]
],
回答by Mayank Pandeyz
Check the query:
检查查询：
Product::where('status', 1)
->where('stock', '>', 0)
->where('category_id', '=', $category_id)
->groupBy('store_id')
->orderBy('updated_at', 'desc')
->take(4)
->get();
here you are grouping the data by store_idand fetching all columns in the result set which is not allowed. To solve it either select store_idor aggregate function on it or change the system variable sql_mode=only_full_group_byto SET sql_mode = ''.
在这里，您将数据分组store_id并获取结果集中不允许的所有列。要解决它，请选择store_id或聚合函数或将系统变量更改sql_mode=only_full_group_by为SET sql_mode = ''.
回答by Godfrey Makori
#Have the following method in your helper file
if (!function_exists('set_sql_mode')) {
/**
* @param string $mode
* @return bool
*/
function set_sql_mode($mode = '')
{
return \DB::statement("SET SQL_MODE=''");
}
}
Then call set_sql_mode(''); just before eloquent/query
然后调用 set_sql_mode(''); 就在雄辩/查询之前
回答by Yvan
To select only aggregated columns, use the one of the raw methods provided by Laravel. For example:
要仅选择聚合列，请使用Laravel 提供的原始方法之一。例如：
Product::selectRaw('store_id')
->where('status', 1)
->groupBy('store_id')
->get();
