預設 PHP 將 Session 儲存在伺服器檔案系統,這在分散式架構或高併發場景下會造成效能瓶頸與同步困難。將 Session 存入 MySQL 不僅解決共享問題,還能利用資料庫索引高效清理過期資料。本文提供基於 PDO 的完整實作範例。
CREATE TABLE `session` (
`skey` CHAR(32) CHARACTER SET ascii NOT NULL,
`data` TEXT COLLATE utf8mb4_bin,
`expire` INT(11) NOT NULL,
PRIMARY KEY (`skey`),
KEY `index_session_expire` (`expire`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
<?php
// 設定常數避免在函式中使用 global
define('SESSION_DNS', 'mysql:host=localhost;dbname=db;charset=utf8mb4');
define('SESSION_USR', 'usr');
define('SESSION_PWD', 'pwd');
define('SESSION_MAXLIFETIME', ini_get('session.gc_maxlifetime'));
// 取得 PDO 連線(支援持久連線)
function getConnection() {
try {
return new PDO(SESSION_DNS, SESSION_USR, SESSION_PWD, [
PDO::ATTR_PERSISTENT => true,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_EMULATE_PREPARES => false,
]);
} catch (Exception $e) {
error_log('Session DB connection failed: ' . $e->getMessage());
return false;
}
}
// 註冊自定義 Session 處理器
session_set_save_handler(
'sessionMysqlOpen',
'sessionMysqlClose',
'sessionMysqlRead',
'sessionMysqlWrite',
'sessionMysqlDestroy',
'sessionMysqlGC'
);
session_start();
// --- 以下為各回調函式實作 ---
function sessionMysqlOpen($savePath, $sessionName) { return true; }
function sessionMysqlClose() { return true; }
function sessionMysqlRead($sessionId) {
try {
$dbh = getConnection();
if (!$dbh) return '';
$time = time();
// 先檢查是否存在且未過期
$stmt = $dbh->prepare('SELECT `count` FROM (SELECT COUNT(*) AS `count` FROM `session` WHERE `skey` = ? AND `expire` > ?) AS t');
$stmt->execute([$sessionId, $time]);
if ($stmt->fetchColumn() == 0) return '';
// 讀取資料
$stmt = $dbh->prepare('SELECT `data` FROM `session` WHERE `skey` = ? AND `expire` > ?');
$stmt->execute([$sessionId, $time]);
$row = $stmt->fetch(PDO::FETCH_ASSOC);
return $row ? $row['data'] : '';
} catch (Exception $e) {
return '';
}
}
function sessionMysqlWrite($sessionId, $data) {
try {
$dbh = getConnection();
if (!$dbh) return false;
$expire = time() + SESSION_MAXLIFETIME;
// 使用 REPLACE INTO 實現 upsert
$stmt = $dbh->prepare('REPLACE INTO `session` (`skey`, `data`, `expire`) VALUES (?, ?, ?)');
return $stmt->execute([$sessionId, $data, $expire]);
} catch (Exception $e) {
return false;
}
}
function sessionMysqlDestroy($sessionId) {
try {
$dbh = getConnection();
if (!$dbh) return false;
$stmt = $dbh->prepare('DELETE FROM `session` WHERE `skey` = ?');
return $stmt->execute([$sessionId]);
} catch (Exception $e) {
return false;
}
}
function sessionMysqlGC($maxLifetime) {
try {
$dbh = getConnection();
if (!$dbh) return false;
$expire = time() - $maxLifetime;
$stmt = $dbh->prepare('DELETE FROM `session` WHERE `expire` < ?');
return $stmt->execute([$expire]);
} catch (Exception $e) {
return false;
}
}
SessionMysqlHandler.php)index.php)最開頭 require_once 並呼叫 session_start()