Editing 排除 MySQL 技術疑難問題 (section)

=== 錯誤 1055: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ===
訊息
<pre>
查詢發生錯誤 (1055): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.posts.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
</pre>

遇到錯誤的查詢
<pre>
-- 建立範例資料表
CREATE TABLE posts (
    id INT,
    post_id VARCHAR(10),
    author VARCHAR(50),
    content TEXT,
    likes INT,
    post_time DATETIME
);

-- 插入範例資料
INSERT INTO posts VALUES
(1, 'A123', 'Alice', '第一篇文章', 10, '2024-01-01'),
(2, 'A123', 'Bob', '推推', 5, '2024-01-02'),
(3, 'A123', 'Bob', '讚讚', 3, '2024-01-03');

-- 這會報錯
SELECT 
    id,          -- 問題: 沒有在 GROUP BY 中
    post_id,
    author,
    GROUP_CONCAT(content SEPARATOR "\n") AS content,     -- 問題: 沒有在 GROUP BY 中
    likes        -- 問題: 沒有在 GROUP BY 中
FROM posts
GROUP BY post_id, author;
</pre>

解決方案：修正後的查詢 (1) SELECT 中的每個欄位要麼放在 GROUP BY 中、或 (2) 使用彙總函數 (如 MAX, MIN, SUM, COUNT, GROUP_CONCAT)
<pre>
-- 方法一：取消 `sql_mode=only_full_group_by`:
有兩種方法可以取消 `sql_mode=only_full_group_by`:

1. 暫時性修改 (僅對當前會話有效)
```sql
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
```

2. 永久性修改 (修改 MySQL 設定檔)
- 找到 MySQL 設定檔 (通常是 `my.cnf` 或 `my.ini`)
- 在 `[mysqld]` 區段加入或修改:
```ini
[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
```

注意事項:
1. 修改設定檔後需要重啟 MySQL 服務才會生效
2. 不建議取消這個設定，因為:
   - 可能導致查詢結果不可預測
   - 違反 SQL 標準
   - 可能在未來版本中產生相容性問題

建議的做法是修正 SQL 查詢而不是更改 SQL mode。

-- 方法二: 把所有欄位加入 GROUP BY
SELECT 
    id,
    post_id,
    author,
    content,
    likes
FROM posts
GROUP BY post_id, author, id, content, likes;

-- 方法三: 對非 GROUP BY 的欄位使用彙總函數
SELECT 
    MAX(id) as id,
    post_id,
    author,
    GROUP_CONCAT(content) as contents,
    SUM(likes) as total_likes
FROM posts
GROUP BY post_id, author;
</pre>