linux mysql 1055_网站报错- 数据库代码错误 Error Number: 1055 解决方法

将数据库从MySQL更改为MySQLI并获得错误——发生了数据库错误 错误代码 Error Number: 1055

错误号:1055Expression #23 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'zipbizzlatestdb.pia.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

SELECT list的表达式#23不在GROUP BY子句中，并且包含非聚合列'zipbizzlatestdb.pia。不依赖于GROUP BY子句中的列的id';这与sql_mode=only_full_group_by不兼容SELECT *,o.id as id, GROUP_CONCAT(pia.applicantid) as applicants

FROM `pr_opportunity` as o

LEFT OUTER JOIN pr_internal_applicant as pia

ON o.id = pia.positionid

WHERE o.approval_status='Approved'

and DATE(o.deadline) > DATE(NOW())

GROUP BY o.id

Filename: /var/www/html/singlecodebase/Feb152017/models/mod_common.php

文件名:/var/www/html/singlecodebase / Feb152017 /模型/ mod_common.php

Line Number: 6999

行号:6999

My model file mod_common is as below:

我的模型文件mod_common如下:function get_opportunity_list()

{

$sql = "SELECT *,o.id as id, GROUP_CONCAT(pia.applicantid) as applicants

FROM `".$this->myTables['opportunity']."` as o

LEFT OUTER JOIN pr_internal_applicant as pia

ON o.id = pia.positionid

WHERE o.approval_status='Approved'

and DATE(o.deadline) > DATE(NOW())

GROUP BY o.id";

$query=$this->db->query($sql);

if($query->num_rows() > 0){

$rows = $query->result();

}

return $rows;

}

No clue how to solve this error

不知道如何解决这个错误

2 个解决方案

#1

2

它发生在mysql 5.7中，所以请检查这个链接[https://www.sitepoint.com/quick tip-how-to- permanentchange-sql -mode-in-mysql/]，并按照它对我来说很有用的步骤进行操作。

Or open file

或打开的文件vi /etc/mysql/my.cnf

Add these line at the bottom of the file

在文件的底部添加这些行[mysqld]

# ... other stuff will probably be here

sql_mode = "STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

and save then restart mysql

保存然后重新启动mysqlsudo service mysql restartNote not for window os

注意不是窗口操作系统

#2

0

In reading your SQL, you are asking to return every column (i.e. *), as well as the id a second time (o.id is already retrieved by *), then you are querying for the aggregate function of GROUP_CONCAT(pia.applicantid). However, your group by Clause only requests to aggregate by (group by) o.id.

在读取SQL时，您要求第二次返回每个列(例如*)以及id (o)。id已经被*检索到，然后您要查询GROUP_CONCAT(pi .applicantid)的聚合函数。但是，您的group by子句只请求按(group by) o.id聚合。

SQL doesn't like this because it doesn't know what to do with the other fields in your dataset that you are querying for using *.

SQL不喜欢这样，因为它不知道如何处理要使用*查询的数据集中的其他字段。

I suspect you want to see every applicantid related to o.id in an aggregate fashion. If so, this is your query and it will work.

我想你希望看到所有与o相关的应用程序。id的集合形式。如果是这样，这就是您的查询，它将会起作用。SELECT o.id as id, GROUP_CONCAT(pia.applicantid) as applicants

FROM `".$this->myTables['opportunity']."` as o

LEFT OUTER JOIN pr_internal_applicant as pia

ON o.id = pia.positionid

WHERE o.approval_status='Approved'

and DATE(o.deadline) > DATE(NOW())

GROUP BY o.id"

If you want to group by other fields, then just add them specifically in your select as well as your group by. All non-aggregate fields need to be in both the select and the group by. This is part of the ANSI Standard and should be true for all SQL flavors. I believe mysql 5.7 and up is more on this than the prior versions, but I'd follow this as a rule.

如果您想按其他字段进行分组，那么只需在select和group by中添加它们。所有非聚合字段都需要同时位于select和group by中。这是ANSI标准的一部分，应该适用于所有SQL风格。我相信mysql 5.7和up比之前的版本更多，但我将遵循这条规则。

Here's some supplemental reading regarding the topic. Hope this helps. MySQL 5.7 Documentation regarding Group by

这里有一些关于这个话题的补充读物。希望这个有帮助。MySQL 5.7关于Group by的文档