⚠️ 重要提醒:mysql_* 函數已棄用/移除
原文使用 mysql_connect()、mysql_query() 等函數,PHP 5.5 棄用、PHP 7.0 完全移除。
現代專案**必須使用 mysqli 或 PDO**,支援預處理語句防止 SQL 注入。
現代寫法一:mysqli(程序風格)
<?php
$host = 'localhost';
$user = 'root';
$pass = '';
$dbname = 'test';
$mysqli = new mysqli($host, $user, $pass, $dbname);
if ($mysqli->connect_errno) {
die("連線失敗: " . $mysqli->connect_error);
}
$mysqli->set_charset('utf8mb4'); // 重要:設定字元集
// 1. 更新資料(預處理防注入)
$stmt = $mysqli->prepare("UPDATE friends SET name = ? WHERE id = ?");
$stmt->bind_param('si', $name, $id);
$name = 'Andy';
$id = 1;
$stmt->execute();
echo "更新筆數: " . $stmt->affected_rows;
$stmt->close();
// 2. 插入資料並取得自增 ID
$stmt = $mysqli->prepare("INSERT INTO friends (name, sex, birthday, address) VALUES (?, ?, ?, ?)");
$stmt->bind_param('ssss', $name, $sex, $birthday, $address);
$name = '陳靖仇';
$sex = '男';
$birthday = '1959-08-23';
$address = '天之痕';
$stmt->execute();
echo "最後插入 ID: " . $mysqli->insert_id;
$stmt->close();
$mysqli->close();
?>
現代寫法二:PDO(物件導向、支援多資料庫)
<?php
$dsn = 'mysql:host=localhost;dbname=test;charset=utf8mb4';
$user = 'root';
$pass = '';
try {
$pdo = new PDO($dsn, $user, $pass, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false, // 使用真實預處理
]);
// 1. 更新
$stmt = $pdo->prepare("UPDATE friends SET name = :name WHERE id = :id");
$stmt->execute([':name' => 'Andy', ':id' => 1]);
echo "更新筆數: " . $stmt->rowCount();
// 2. 插入
$stmt = $pdo->prepare("
INSERT INTO friends (name, sex, birthday, address)
VALUES (:name, :sex, :birthday, :address)
");
$stmt->execute([
':name' => '陳靖仇',
':sex' => '男',
':birthday' => '1959-08-23',
':address' => '天之痕'
]);
echo "最後插入 ID: " . $pdo->lastInsertId();
} catch (PDOException $e) {
die("資料庫錯誤: " . $e->getMessage());
}
預處理語句優勢
| 特性 | 傳統拼接 SQL | 預處理語句 |
| SQL 注入防護 | 無(極高風險) | 內建防護,參數與 SQL 碼分離 |
| 重複執行效能 | 每次解析編譯 | 編譯一次,多次執行只綁定參數 |
| 資料型別處理 | 手動加引號/轉義 | 自動根據型別處理 |
| 可讀性 | 字串拼接難閱讀 | SQL 結構清晰 |
交易控制
<?php
// mysqli 交易
$mysqli->begin_transaction();
try {
$mysqli->query("INSERT INTO logs (msg) VALUES ('start')");
$mysqli->query("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
$mysqli->query("UPDATE accounts SET balance = balance + 100 WHERE id = 2");
$mysqli->commit();
} catch (Exception $e) {
$mysqli->rollback();
throw $e;
}
// PDO 交易
$pdo->beginTransaction();
try {
$pdo->exec("INSERT INTO logs (msg) VALUES ('start')");
$pdo->exec("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
$pdo->exec("UPDATE accounts SET balance = balance + 100 WHERE id = 2");
$pdo->commit();
} catch (Exception $e) {
$pdo->rollBack();
throw $e;
}
常見錯誤避坑
- ❌ 直接拼接變數:`"INSERT INTO t VALUES ('$name')"` → ✅ 預處理 `?` / `:name`
- ❌ 忘記設定 charset → ✅ `set_charset('utf8mb4')` / DSN 加 `charset=utf8mb4`
- ❌ 不檢查連線/執行結果 → ✅ 檢查 `connect_errno`、啟用 `ERRMODE_EXCEPTION`
- ❌ 手動 `mysql_real_escape_string()` → ✅ 預處理自動處理