資料表結構
CREATE TABLE `users` (
`id` INT(3) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) NOT NULL,
`password` VARCHAR(50) NOT NULL,
`address` VARCHAR(50),
`tel` VARCHAR(20),
`email` VARCHAR(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
PDO 封裝類(建議生產環境使用)
class DB {
private static ?PDO $pdo = null;
public static function get(): PDO {
if (!self::$pdo) {
self::$pdo = new PDO(
'mysql:host=localhost;dbname=test;charset=utf8mb4',
'phpdb', 'phpdb',
[PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
);
}
return self::$pdo;
}
}
C - Create (新增)
$sql = "INSERT INTO users (name, password, address, tel, email)
VALUES (?, ?, ?, ?, ?)";
$stmt = DB::get()->prepare($sql);
$stmt->execute(['王強', '123', '深圳', '8163445', 'wang@wel.com']);
echo "新增成功,ID: " . DB::get()->lastInsertId();
R - Read (查詢)
// 單筆
$stmt = DB::get()->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([1]);
$user = $stmt->fetch();
// 列表
$users = DB::get()->query("SELECT * FROM users ORDER BY id DESC")->fetchAll();
U - Update (修改)
$sql = "UPDATE users SET tel = ? WHERE name = ?";
$stmt = DB::get()->prepare($sql);
$affected = $stmt->execute(['1234567', '王強']);
echo "修改筆數: $affected";
D - Delete (刪除)
$sql = "DELETE FROM users WHERE name = ?";
$stmt = DB::get()->prepare($sql);
$affected = $stmt->execute(['王強']);
echo "刪除筆數: $affected";
安全鐵律
- 永不拼接 SQL 字串,全面使用預處理
prepare() + execute([...])。
- 密碼必須雜湊:
password_hash($pwd, PASSWORD_DEFAULT) 存入,password_verify($input, $hash) 驗證。
- 最小權限原則:資料庫布號僅擁有必要資料表的 CRUD 權限,無 DROP/ALTER。
- 錯誤不顯示給用戶:
display_errors=Off,記錄日誌供開發者排查。
遷移自舊 mysql_* 函數對照
| 舊函數 (PHP 7.0 移除) | MySQLi 替代 | PDO 替代 |
mysql_connect | new mysqli() | new PDO() |
mysql_query | $mysqli->query() / prepare() | $pdo->query() / prepare() |
mysql_fetch_assoc | $stmt->fetch_assoc() | $stmt->fetch() |
mysql_num_rows | $stmt->num_rows | $stmt->rowCount() (僅限 SELECT) |
mysql_real_escape_string | $mysqli->real_escape_string() | 不需要 (預處理自動處理) |