透過資料庫記錄使用者最後活動時間,定期清理逾期記錄,即可實作線上人數統計與顯示線上用戶列表。本文提供完整實作範例,並說明效能優化與擴展方向。
CREATE TABLE `online_users` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`username` VARCHAR(50) DEFAULT NULL, -- 登入用戶:用戶名;訪客:NULL
`ip_address` VARBINARY(16) NOT NULL, -- IPv4: 4 bytes, IPv6: 16 bytes
`user_agent` VARCHAR(255) DEFAULT NULL, -- 瀏覽器識別
`first_seen` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
`last_seen` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`is_member` TINYINT(1) DEFAULT 0, -- 1=會員, 0=訪客
INDEX `idx_last_seen` (`last_seen`),
INDEX `idx_ip` (`ip_address`),
INDEX `idx_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
online.php<?php
// online.php - 共用邏輯,每頁 require_once
require_once 'db.php'; // PDO 連線
session_start();
$ONLINE_TIMEOUT = 180; // 3 分鐘無活動視為離線
$now = time();
$cutoff = $now - $ONLINE_TIMEOUT;
// 1. 取得/建立識別資訊
$is_member = !empty($_SESSION['user_id']);
$username = $is_member ? $_SESSION['username'] : null;
$ip = $_SERVER['REMOTE_ADDR'] ?? '0.0.0.0';
$ua = substr($_SERVER['HTTP_USER_AGENT'] ?? '', 0, 255);
// IPv4 轉二進位儲存(節省空間、加快索引)
$ip_bin = inet_pton($ip) ?: inet_pton('0.0.0.0');
try {
// 2. 更新或插入記錄
if ($is_member) {
// 會員:以 username 為鍵
$stmt = $pdo->prepare('
INSERT INTO online_users (username, ip_address, user_agent, is_member, last_seen)
VALUES (?, ?, ?, 1, FROM_UNIXTIME(?))
ON DUPLICATE KEY UPDATE last_seen = FROM_UNIXTIME(?), user_agent = ?
');
$stmt->execute([$username, $ip_bin, $ua, $now, $now, $ua]);
} else {
// 訪客:以 IP 為鍵(需唯一索引或複合索引)
$stmt = $pdo->prepare('
INSERT INTO online_users (ip_address, user_agent, is_member, last_seen)
VALUES (?, ?, 0, FROM_UNIXTIME(?))
ON DUPLICATE KEY UPDATE last_seen = FROM_UNIXTIME(?), user_agent = ?
');
$stmt->execute([$ip_bin, $ua, $now, $now, $ua]);
}
// 3. 清理逾期記錄(機率性執行,避免每請求都 DELETE)
if (mt_rand(1, 100) <= 5) { // 5% 機率
$pdo->exec("DELETE FROM online_users WHERE last_seen < DATE_SUB(NOW(), INTERVAL {$ONLINE_TIMEOUT} SECOND)");
}
} catch (PDOException $e) {
error_log('Online tracking error: ' . $e->getMessage());
// 靜默失敗,不影響主流程
}
online_list.php<?php
require_once 'db.php';
require_once 'online.php'; // 先更新自己狀態
$stmt = $pdo->query('
SELECT
username,
INET_NTOA(ip_address) AS ip,
user_agent,
is_member,
last_seen,
TIMESTAMPDIFF(SECOND, last_seen, NOW()) AS idle_seconds
FROM online_users
WHERE last_seen >= DATE_SUB(NOW(), INTERVAL 180 SECOND)
ORDER BY is_member DESC, last_seen DESC
');
$users = $stmt->fetchAll();
?>
<!DOCTYPE html>
<html lang="zh-TW">
<head>
<meta charset="UTF-8">
<title>線上用戶列表</title>
<style>
table { border-collapse: collapse; width: 100%; }
th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }
th { background: #f5f5f5; }
.member { font-weight: bold; color: #2c7; }
.guest { color: #666; }
.idle { color: #999; font-size: 0.9em; }
</style>
</head>
<body>
<h1>目前線上:<?= count($users) ?> 人</h1>
<table>
<thead>
<tr><th>類型</th><th>帳號/IP</th><th>IP 位址</th><th>最後活動</th><th>閒置時間</th><th>User Agent</th></tr>
</thead>
<tbody>
<?php foreach ($users as $u): ?>
<tr class="<?= $u['is_member'] ? 'member' : 'guest' ?>">
<td><?= $u['is_member'] ? '👤 會員' : '👻 訪客' ?></td>
<td><?= $u['is_member'] ? htmlspecialchars($u['username']) : '—' ?></td>
<td><?= htmlspecialchars($u['ip']) ?></td>
<td><?= $u['last_seen'] ?></td>
<td class="idle"><?= $u['idle_seconds'] ?> 秒前</td>
<td><?= htmlspecialchars($u['user_agent']) ?></td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
</body>
</html>
| 策略 | 說明 |
|---|---|
| 機率性清理 | 不每請求執行 DELETE,用 mt_rand(1,100) <= 5 5% 機率觸發 |
| IP 儲存為二進位 | VARBINARY(16) + inet_pton()/inet_ntop(),索引更小更快 |
| Redis 替代方案 | 高流量站點改用 Redis Hash + TTL,HSET online:users {uid} {json}、EXPIRE 180 |
| 分區表 | 超大流量可按日期分區,歷史資料歸檔 |
| 非同步寫入 | 用訊息佇列 (Redis List、RabbitMQ) 批次寫入 DB |
<?php
$redis = new Redis();
$redis->connect('127.0.0.1', 6379);
$key = 'online:users';
$timeout = 180;
$user_id = $_SESSION['user_id'] ?? 'guest_' . $_SERVER['REMOTE_ADDR'];
$user_data = json_encode([
'username' => $_SESSION['username'] ?? 'Guest',
'ip' => $_SERVER['REMOTE_ADDR'],
'ua' => $_SERVER['HTTP_USER_AGENT'] ?? '',
'time' => time(),
'is_member' => !empty($_SESSION['user_id'])
]);
// 寫入 Hash 並設定 TTL
$redis->hSet($key, $user_id, $user_data);
$redis->expire($key, $timeout);
// 讀取全部
$all = $redis->hGetAll($key);
$online = array_map('json_decode', $all, true);
$count = count($online);
current_url 顯示用戶正在瀏覽哪頁資料庫版適合中小型網站、需持久化記錄的場景;高併發建議改用 Redis 或專門的即時分析服務。