PHP Session 存儲至資料庫完整實現 (PostgreSQL / MySQL 双版本)

為什麼要將 Session 存入資料庫？

• 多伺服器叢集環境下共享 Session (避免檔案系統同步延遲)。
• 統一管理、監控、清理過期 Session。
• 支援自定義欄位 (如 `user_id`, `ip_address`) 便於審計。

方案一：PostgreSQL 版 (使用 UPSERT)

資料表與函數定義

CREATE UNLOGGED TABLE php_session (
    sess_id VARCHAR(32) PRIMARY KEY,
    modify_time TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    sess_data TEXT DEFAULT ''
);
CREATE INDEX CONCURRENTLY idx_php_session_modify_time ON php_session(modify_time);

-- 寫入/更新 (UPSERT)
CREATE OR REPLACE FUNCTION set_session(VARCHAR, TEXT) RETURNS VOID AS $$
WITH upsert AS (
    UPDATE php_session SET modify_time = NOW(), sess_data = $2 WHERE sess_id = $1 RETURNING 1
)
INSERT INTO php_session (sess_id, modify_time, sess_data)
SELECT $1, NOW(), $2 WHERE NOT EXISTS (SELECT 1 FROM upsert);
$$ LANGUAGE SQL;

-- 讀取
CREATE OR REPLACE FUNCTION get_session(VARCHAR) RETURNS TEXT AS $$
SELECT sess_data FROM php_session WHERE sess_id = $1
$$ LANGUAGE SQL;

-- 刪除
CREATE OR REPLACE FUNCTION del_session(VARCHAR) RETURNS VOID AS $$
DELETE FROM php_session WHERE sess_id = $1
$$ LANGUAGE SQL;

-- GC 清理 30 天前
CREATE OR REPLACE FUNCTION gc_session() RETURNS VOID AS $$
DELETE FROM php_session WHERE modify_time < NOW() - INTERVAL '30 days'
$$ LANGUAGE SQL;

PHP Session Handler 註冊

session_set_save_handler(
    function ($savePath, $sessionName) { return true; }, // open
    function () { return true; },                        // close
    function ($id) {                                     // read
        $stmt = pg_query_params(SESSION_CONN, 'SELECT get_session($1)', [$id]);
        return pg_fetch_result($stmt, 0, 0) ?: '';
    },
    function ($id, $data) {                              // write
        pg_query_params(SESSION_CONN, 'SELECT set_session($1, $2)', [$id, $data]);
        return true;
    },
    function ($id) {                                     // destroy
        pg_query_params(SESSION_CONN, 'SELECT del_session($1)', [$id]);
        return true;
    },
    function ($maxlifetime) { return true; }             // gc (由 DB 函數定時觸發)
);
register_shutdown_function('session_write_close');
session_start();

方案二：MySQL 版 (PDO + 類封裝)

資料表

CREATE TABLE `sessioninfo` (
    `sid` VARCHAR(255) NOT NULL PRIMARY KEY,
    `value` TEXT NOT NULL,
    `expiration` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Handler 類實現

class MySessionHandler implements SessionHandlerInterface {
    private $pdo, $table, $expire = 1440; // 24 分鐘

    public function __construct(PDO $pdo, string $table) {
        $this->pdo = $pdo; $this->table = $table;
    }
    public function open($path, $name) { return true; }
    public function close() { return true; }

    public function read($id) {
        $stmt = $this->pdo->prepare("SELECT value FROM {$this->table} WHERE sid = ? AND expiration > NOW()");
        $stmt->execute([$id]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        if ($row) {
            // 讀取時延長過期時間
            $this->pdo->prepare("UPDATE {$this->table} SET expiration = DATE_ADD(NOW(), INTERVAL ? SECOND) WHERE sid = ?")
                ->execute([$this->expire, $id]);
            return $row['value'];
        }
        return '';
    }

    public function write($id, $data) {
        $stmt = $this->pdo->prepare("
            INSERT INTO {$this->table} (sid, value, expiration) VALUES (?, ?, DATE_ADD(NOW(), INTERVAL ? SECOND))
            ON DUPLICATE KEY UPDATE value = VALUES(value), expiration = VALUES(expiration)
        ");
        return $stmt->execute([$id, $data, $this->expire]);
    }

    public function destroy($id) {
        return $this->pdo->prepare("DELETE FROM {$this->table} WHERE sid = ?")->execute([$id]);
    }

    public function gc($maxlifetime) {
        return $this->pdo->prepare("DELETE FROM {$this->table} WHERE expiration < NOW()")->execute();
    }
}

// 使用
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8mb4', 'root', 'pwd');
$handler = new MySessionHandler($pdo, 'sessioninfo');
session_set_save_handler($handler, true); // true = 使用 OOP 介面
session_start();

關鍵比較