php Laravel：语法错误或访问冲突：1055 错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40917189/
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
Laravel : Syntax error or access violation: 1055 Error
提问by Karthikvijayaveni
I want use WhereIn and Groupby in Same Query to fetch Result.
我想在同一查询中使用 WhereIn 和 Groupby 来获取结果。
I've tried this:
我试过这个：
$loadids=explode("#@*",$reciptdet->loading_id);
$loadingdatas=DB::table('loading')->groupBy('vehicle_no')->whereIn('id',$loadids)->get();
But I got this error message:
但我收到此错误消息：
SQLSTATE[42000]: Syntax error or access violation: 1055 'sbrtpt.loading.id' isn't in GROUP BY (SQL: select * from loading where id in (14, 15, 16) group by vehicle_no)
SQLSTATE[42000]: 语法错误或访问冲突：1055 'sbrtpt.loading.id' is not in GROUP BY (SQL: select * from loading where id in (14, 15, 16) group by vehicle_no)
回答by Husam
Short answer
简答
In config\database.php--> "mysql"array
在config\database.php-->"mysql"数组中
Set 'strict' => falseto disable all.
设置'strict' => false为全部禁用。
.... or
.... 或者
You can leave 'strict' => trueand add modes to "mysql"option in
您可以离开'strict' => true并添加模式以"mysql"选择
'mysql' => [
...
....
'strict' => true,
'modes' => [
//'ONLY_FULL_GROUP_BY', // Disable this 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'
],
]
Detailed answer
详细解答
You may not need to disable all strictoptions ... Kindly have a look on this answerabout this issue.
您可能不需要禁用所有严格选项......请查看有关此问题的答案。
回答by Antonio Carlos Ribeiro
This is probably a SQL_MODEproblem. In your config/database.php, in the connection, change
这可能是SQL_MODE问题。在你的config/database.php，在连接中，改变
strict => false
As in
如
'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' => false,
'engine' => null,
],
回答by Zakhele
I was having this problem also but after changing 'strict' => true, to 'strict' => false, the error disappeared.
我也有这个问题，但改变后 'strict' => true，对 'strict' => false，错误消失。
You can find this setting in:
您可以在以下位置找到此设置：
config\database.php
配置\数据库.php
'mysql' => [
...
'strict' => false,
...
]
回答by cespon
Without modifiying config\database.php file
无需修改 config\database.php 文件
Set 'strict' => falsein the config\database.phpcould be a security issue. So, a simple Laravel solution could be first call get()and then groupBy('vehicle_no):
设置'strict' => false在config\database.php可能是一个安全问题。所以，一个简单的 Laravel 解决方案可以先调用get()，然后groupBy('vehicle_no)：
$loadids = explode("#@*", $reciptdet->loading_id);
$loadingdatas = DB::table('loading')->whereIn('id', $loadids)->get();
$grouped = $loadingdatas->groupBy('vehicle_no');
回答by Thungdemo
Whenever using groupBy in eloquent, always include the column name used in the groupBy function in the select() function.
每当在 eloquent 中使用 groupBy 时，请始终在 select() 函数中包含 groupBy 函数中使用的列名。
$loadids=explode("#@*",$reciptdet->loading_id);
$loadingdatas=DB::table('loading')->select('vehicle_no')->groupBy('vehicle_no')->whereIn('id',$loadids)->get();//add select('vehicle_no')
Also it is a bad practice to disable strict mode in the config file. Doing so may cause corrupt data to enter the database such as invalid dates without any warnings.Don't do that unless absolutely necessary.
在配置文件中禁用严格模式也是一种不好的做法。这样做可能会导致损坏的数据进入数据库，例如没有任何警告的无效日期。除非绝对必要，否则不要这样做。
回答by Debiprasad
This restriction makes sense as when you use GROUP BYin MySQL, it returns one row for each value in the columns used in GROUP BY. So, the values of other columns in the selected rows do not make sense to use anywhere. So, it's always recommended to use the best practice and I would recommend not to disable MySQL Strict Mode.
这个限制是有道理的，因为当你GROUP BY在 MySQL 中使用时，它为 中使用的列中的每个值返回一行GROUP BY。因此，所选行中其他列的值在任何地方使用都没有意义。因此，始终建议使用最佳实践，我建议不要禁用 MySQL 严格模式。
Often developers may need rows of a query grouped by the value of a column. Here they don't need only one row per the unique values of the columns. But they need multiple rows grouped by the unique values of a particular column. For some reason, they use groupByQuery Builder method of Laravel which generates a MySQL GROUP BYquery and the developers encounter the above error.
通常，开发人员可能需要按列值分组的查询行。在这里，它们不需要每个列的唯一值仅一行。但是他们需要按特定列的唯一值分组的多行。出于某种原因，他们使用groupByLaravel 的 Query Builder 方法生成一个 MySQLGROUP BY查询，开发人员遇到了上述错误。
The solution to their problem is to use groupByCollection method instead. For example,
他们的问题的解决方案是改用groupByCollection 方法。例如，
$loadingData = DB::table('loading')
->whereIn('id', $loadIds)
->get()
->groupBy('vehicle_no');
This will give them the desired result.
这会给他们想要的结果。
回答by Gouda Elalfy
update config/database.php
更新 config/database.php
set:
放：
'mysql' => [
'strict' => false,
],
instead of:
代替：
'mysql' => [
'strict' => true,
],
and don't forget to clear cache:
并且不要忘记清除缓存：
php artisan config:cache
回答by hamed hossani
add \Schema::defaultStringLength(191);to bootmethod
添加\Schema::defaultStringLength(191);到boot方法
class AppServiceProvider extends ServiceProvider
{
/**
* Register any application services.
*
* @return void
*/
public function register()
{
//
}
/**
* Bootstrap any application services.
*
* @return void
*/
public function boot()
{
//
\Schema::defaultStringLength(191);
}
}
