CodeIgniter 3中非聚合列GROUP BY报错的解决方法（不修改MySQL配置）

Hey there! I've run into this exact issue with CodeIgniter 3 before, so I know how frustrating it can be when you can't tweak MySQL server settings. Let's break down the CI-side fixes that work without touching your database config:

1. Temporarily Disable ONLY_FULL_GROUP_BY for the Current Connection #

MySQL's ONLY_FULL_GROUP_BY mode is what's throwing the error—it enforces strict grouping rules. You can turn it off only for the current database connection (so it won't affect other requests or applications) by running a quick SQL statement before your GROUP BY query:

// Adjust the SQL mode for this session first
$this->db->query("SET sql_mode = (SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''))");

// Now execute your GROUP BY query as normal
$query = $this->db->select('column1, column2, COUNT(column3) as total')
                  ->from('your_table')
                  ->group_by('column1')
                  ->get();

This removes ONLY_FULL_GROUP_BY from the active connection's SQL mode, letting your query run without strict checks. Since it's session-specific, it won't alter global MySQL settings.

2. Use MySQL's ANY_VALUE() Function for Non-Aggregate Columns #

If you want to follow MySQL's recommended approach (instead of disabling the mode), wrap your non-aggregate columns in ANY_VALUE(). This tells MySQL you explicitly want an arbitrary value from the grouped rows, bypassing the ONLY_FULL_GROUP_BY check:

$query = $this->db->select('column1, ANY_VALUE(column2) as column2, COUNT(column3) as total')
                  ->from('your_table')
                  ->group_by('column1')
                  ->get();

This is cleaner because it makes your intent clear in the query itself, rather than modifying connection settings.

3. Apply Fixes to Raw Queries #

If you're using raw SQL instead of CodeIgniter's query builder, the same logic applies:

Option A: Use ANY_VALUE() directly in the raw query #

$query = $this->db->query("
    SELECT column1, ANY_VALUE(column2), COUNT(column3) as total
    FROM your_table
    GROUP BY column1
");

Option B: Disable the mode first, then run the raw query #

$this->db->query("SET sql_mode = (SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''))");
$query = $this->db->query("
    SELECT column1, column2, COUNT(column3) as total
    FROM your_table
    GROUP BY column1
");

Quick Notes #

• If your CI app uses persistent database connections, the session-level SQL mode change might persist across requests. To avoid this, you can reset the mode after your query, or stick with the ANY_VALUE() method instead.
• ANY_VALUE() works in MySQL 5.7.5 and later, which is standard for most modern setups.

内容的提问来源于stack exchange，提问作者P. Nick