PHP MySQL 增刪改查 (CRUD) 入門實例：從連接到事務的完整示範

基礎 CRUD 操作代碼

<?php
// 1. 連接數據庫
$con = @mysql_connect('localhost', 'root', 'root');
mysql_select_db('test', $con);

// 2. 查詢 (Read)
$userInfo = mysql_query("SELECT * FROM user", $con);
echo "<table>";
while ($row = mysql_fetch_assoc($userInfo)) {
    echo "<tr><td>{$row['id']}</td><td>{$row['username']}</td><td>{$row['password']}</td></tr>";
}
echo "</table>";
mysql_free_result($userInfo);

// 3. 刪除 (Delete)
/*
$d = @mysql_connect('localhost', 'root', 'root');
mysql_select_db('test', $d);
mysql_query("DELETE FROM user WHERE id=1", $d);
mysql_close($d);
*/

// 4. 插入 (Create)
/*
$i = @mysql_connect('localhost', 'root', 'root');
mysql_select_db('test', $i);
mysql_query("INSERT INTO user(username, password) VALUES('ad', 'ad')", $i);
mysql_close($i);
*/

// 5. 更新 (Update)
$u = @mysql_connect('localhost', 'root', 'root');
mysql_select_db('test', $u);
mysql_query("UPDATE user SET username='aaa' WHERE username='00'", $u);
mysql_close($u);
?>

⚠️ 關鍵警告：代碼使用已棄用的 mysql 擴展

現代化重寫：PDO 版本

<?php
// 數據庫配置
$dsn = 'mysql:host=localhost;dbname=test;charset=utf8';
$opt = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];
$pdo = new PDO($dsn, 'root', 'root', $opt);

// 查詢
$stmt = $pdo->query('SELECT id, username, email FROM user');
echo "<table>";
foreach ($stmt as $row) {
    echo "<tr><td>{$row['id']}</td><td>{$row['username']}</td><td>{$row['email']}</td></tr>";
}
echo "</table>";

// 插入 (預處理防注入)
$stmt = $pdo->prepare('INSERT INTO user(username, password) VALUES(?, ?)');
$stmt->execute(['newuser', password_hash('secret', PASSWORD_DEFAULT)]);
echo "新用戶 ID: " . $pdo->lastInsertId();

// 更新
$stmt = $pdo->prepare('UPDATE user SET email=? WHERE username=?');
$stmt->execute(['new@example.com', 'newuser']);

// 刪除
$stmt = $pdo->prepare('DELETE FROM user WHERE username=?');
$stmt->execute(['newuser']);

// 事務示例
try {
    $pdo->beginTransaction();
    $pdo->exec("INSERT INTO logs(action) VALUES('transfer')");
    $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;
}
?>

PDO 核心優勢

• 預處理語句：徹底防止 SQL 注入
• 異常處理：統一錯誤捕獲，支援事務回滾
• 多数據庫支援：輕鬆切換 MySQL/PostgreSQL/SQLite
• 獲取模式靈活：FETCH_ASSOC, FETCH_OBJ, FETCH_CLASS 等

mysqli 面向對象寫法對比

$mysqli = new mysqli('localhost', 'root', 'root', 'test');
$stmt = $mysqli->prepare('SELECT * FROM user WHERE id=?');
$stmt->bind_param('i', $id);
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) { /* ... */ }
$stmt->close();
$mysqli->close();

最佳實踐總結

1. 棄用 mysql_*：新項目嚴禁使用，舊項目計劃遷移
2. 優先選 PDO：可移植性強，API 統一
3. 必須用預處理：任何用戶輸入進 SQL 都要參數綁定
4. 錯誤模式設為異常：避免靜默失敗
5. 連接復用：長運行腳本使用持久連接或連接池