PHP 開發者常犯的 10+1 個 MySQL 錯誤與最佳實務指南

前言

資料庫是 Web 應用核心。PHP + MySQL 是經典組合，但許多開發者在資料庫設計、查詢、安全上踩坑。本文整理 11 大高頻錯誤，提供具體修正碼與最佳實務，適用 MySQL/MariaDB/Percona，亦可推廣至 PostgreSQL 等關聯式資料庫。

1. 使用 MyISAM 而非 InnoDB

錯誤：預設建表用 MyISAM，不支援外鍵、交易、行級鎖，寫入鎖全表。

正確：一律用 InnoDB (MySQL 5.5+ 預設)。支援 ACID、行級鎖、外鍵、MVCC、崩潰恢復。

CREATE TABLE users (...) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. 仍用舊 mysql_* 函式

錯誤：mysql_connect、mysql_query、mysql_fetch_assoc (PHP 7.0 移除)。

正確：mysqli (面向物件/程序) 或 PDO (資料庫抽象)。

// PDO 推薦：支援多資料庫、預備語句、異常處理
$pdo = new PDO('mysql:host=localhost;dbname=app;charset=utf8mb4', $user, $pass, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES => false, // 真實預備語句
]);
$stmt = $pdo->prepare('SELECT * FROM users WHERE email = ?');
$stmt->execute([$email]);
$user = $stmt->fetch();

3. 不過濾用戶輸入 → SQL 注入

錯誤：直接拼接變數 "SELECT * FROM users WHERE name='$name'"。

正確：永遠使用預備語句，參數綁定自動轉義。

// 錯誤
$sql = "SELECT * FROM users WHERE name='$_POST[name]'";
// 正確
$stmt = $pdo->prepare('SELECT * FROM users WHERE name = ?');
$stmt->execute([$_POST['name']]);

4. 不使用 UTF-8 (utf8mb4)

錯誤：預設 latin1、gbk、utf8 (僅支援 BMP，不支援 Emoji)。

正確：全鏈路 utf8mb4。

-- 資料庫/資料表/欄位
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
-- 連線
$pdo->exec("SET NAMES utf8mb4");
-- my.cnf
[client] default-character-set=utf8mb4
[mysql]  default-character-set=utf8mb4
[mysqld] character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

5. 該用 SQL 做的事用 PHP 做

錯誤：撈全表到 PHP 迴圈算平均、求和、去重、分頁。

正確：推給資料庫：AVG(), SUM(), COUNT(DISTINCT col), LIMIT/OFFSET, GROUP BY。

// 錯誤：撈 10 萬筆算平均
$rows = $pdo->query('SELECT price FROM products')->fetchAll();
$avg = array_sum(array_column($rows,'price'))/count($rows);

// 正確：一條 SQL
$avg = $pdo->query('SELECT AVG(price) FROM products')->fetchColumn();

6. 不優化查詢 (缺索引、全表掃描)

錯誤：WHERE email = '...' 無索引，百萬筆全表掃描。

正確：

• EXPLAIN 分析執行計畫，關注 type (避免 ALL)、key、rows。
• 索引策略：WHERE/JOIN/ORDER BY/GROUP BY 欄位加索引；複合索引遵循最左前綴；避免過度索引 (寫入變慢、佔空間)。
• 慢查詢日誌：slow_query_log=1, long_query_time=1，定期分析。

7. 資料型別選錯

8. SELECT * 濫用

錯誤：SELECT * FROM users 傳輸不必要欄位、破壞覆蓋索引、增加網路/記憶體。
正確：明確列出欄位 SELECT id, name, email FROM users。

9. 索引不足或過度

• 不足：WHERE 條件欄無索引 → 全表掃描。
• 過度：每欄都建索引 → INSERT/UPDATE/DELETE 變慢、磁碟暴增、優化器選錯索引。
• 原則：只為查詢模式建索引；定期 ANALYZE TABLE 更新統計；刪除未用索引 (DROP INDEX)。

10. 不備份 / 備份不可用

錯誤：從未備份、備份未驗證可還原、備份與主庫同機房。

正確：

1. 自動化：mysqldump --single-transaction --routines --triggers 或 Percona XtraBackup (熱備份)。
2. 異地：上傳至 S3/異地伺服器。
3. 定期演練：每季在測試環境 完整還原驗證。
4. 保留策略：每日全備 + 每小時增量 (binlog)，保留 30 天。

11. 不考慮其他資料庫選項

MySQL 非唯一選擇：

• PostgreSQL：進階資料型別 (JSONB, Array, GIS)、更強 SQL 標準相容、並行查詢。
• MariaDB：MySQL 相容、新功能更快 (ColumnStore, Spider)。
• SQLite：嵌入式、零配置、適合輕量/邊緣/測試。
• ClickHouse / Apache Doris：OLAP 分析查詢、百億級聚合秒級回應。
• Redis：熱點快取、Session、排行榜、分散式鎖。

總結清單 (Code Review 核對)

• [ ] Engine=InnoDB、Charset=utf8mb4
• [ ] 使用 PDO + 預備語句
• [ ] 無 SQL 注入風險
• [ ] 關鍵查詢有 EXPLAIN、有索引
• [ ] 型別正確 (DECIMAL、DATE、JSON)
• [ ] 無 SELECT *
• [ ] 索引適度、無冗餘
• [ ] 自動備份 + 異地 + 還原演練
• [ ] 評估是否需 PostgreSQL/ClickHouse 等專用庫

資料庫設計錯誤修正成本呈指數級增長：開發期 1×、測試期 10×、上線後 100×、資料遷移 1000×。先想清楚，再建表。