mysql - ONLY_FULL_GROUP_BY ON 的 Symfony 查询

Question

我想检索按名称分组的城市结果的对象集合，我的查询是

public function getDistinctCitiesName()
{
    $qb = $this->createQueryBuilder("cc");
            ->add('groupBy', 'cc.name');

    return $qb;
}

运行时出现异常

request.CRITICAL: Uncaught PHP Exception Doctrine\DBAL\Exception\DriverException: "An exception occurred while executing 'SELECT c0_.id AS id_0, c0_.name AS name_1, c0_.postal_code AS postal_code_2, c0_.country AS country_3, c0_.state AS state_4, c0_.visible AS visible_5 FROM cities c0_ GROUP BY c0_.name': SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'www.c0_.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by"

我无法禁用 only_full_group_by 模式，有解决方案吗？

Answer 1

您需要从 mySQL 中禁用 ONLY_FULL_GROUP_BY 才能消除此异常。有两种选择供您选择:-

1:- 在学说配置中禁用它

options:
    1002: 'SET sql_mode=(SELECT REPLACE(@@sql_mode, "ONLY_FULL_GROUP_BY", ""))'

如果您将其添加到学说配置中，您将不会再遇到异常。

2:- 在 mySQL 中禁用它在 mysql 控制台中运行以下命令

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

或者从 phpmyadmin 中删除它 disable ONLY_FULL_GROUP_BY from phpmyadmin

这是我的blog关于这个话题。您可以查看并分享您的评论。

谢谢!