$host = 'localhost';
$user = 'root';
$pass = '123456';
$db = 'mydb';
$charset = 'utf8mb4';
$mysqli = new mysqli($host, $user, $pass, $db);
if ($mysqli->connect_errno) {
throw new Exception("連線失敗: " . $mysqli->connect_error);
}
$mysqli->set_charset($charset); // 關鍵:設定連線編碼,防亂碼
$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false, // 真實預處理
];
$pdo = new PDO($dsn, $user, $pass, $options);
| 類型 | 函數/參數 | 特點 | 適用場景 |
|---|---|---|---|
| 短連線 | mysqli_connect / new PDO | 請求結束自動關閉,資源隔離好 | Web 開發預設選擇 |
| 長連線 | mysqli_pconnect / PDO::ATTR_PERSISTENT => true | 進程級複用,減少握手開銷 | 高併發、連線建立慢(如 SSL、遠端 DB) |
⚠️ 長連線風險:連線數耗盡、事務殘留、狀態污染(需 mysqli::change_user() 重置)。
try {
$pdo = new PDO($dsn, $user, $pass, $options);
$pdo->exec("SET NAMES $charset");
} catch (PDOException $e) {
error_log("DB 連線失敗: " . $e->getMessage());
die('系統維護中,請稍後再試');
}
utf8mb4(支援 Emoji)。SET NAMES utf8mb4(MySQLi 用 set_charset,PDO 用 DSN charset 參數)。<meta charset="utf-8">。function query($sql, $params = []) {
global $pdo;
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
return $stmt->fetchAll();
}
$users = query("SELECT * FROM users WHERE status = ? AND role = ?", [1, 'admin']);