本文目录导读：
我来为你设计一个完整的PHP关注系统数据库方案。
基础数据库表设计
核心关注表（最简方案）
CREATE TABLE `follows` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`follower_id` BIGINT UNSIGNED NOT NULL COMMENT '关注者ID',
`following_id` BIGINT UNSIGNED NOT NULL COMMENT '被关注者ID',
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY `uk_follower_following` (`follower_id`, `following_id`),
KEY `idx_following_id` (`following_id`),
KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
增强版数据库设计
用户表（基础信息）
CREATE TABLE `users` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`username` VARCHAR(50) NOT NULL UNIQUE,
`nickname` VARCHAR(50) NOT NULL,
`avatar` VARCHAR(255),
`followers_count` INT UNSIGNED DEFAULT 0 COMMENT '粉丝数（冗余，便于查询）',
`following_count` INT UNSIGNED DEFAULT 0 COMMENT '关注数（冗余）',
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
关注关系表（增强版）
CREATE TABLE `follows` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`follower_id` BIGINT UNSIGNED NOT NULL COMMENT '关注者ID',
`following_id` BIGINT UNSIGNED NOT NULL COMMENT '被关注者ID',
`status` TINYINT DEFAULT 1 COMMENT '状态：1-关注，0-取消',
`source` VARCHAR(50) DEFAULT '' COMMENT '关注来源：recommend/search/share等',
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-- 联合唯一索引确保唯一关注关系
UNIQUE KEY `uk_follower_following` (`follower_id`, `following_id`),
-- 查询优化索引
KEY `idx_following_status` (`following_id`, `status`),
KEY `idx_created_at` (`created_at`),
-- 外键约束（可选，视性能需求）
-- FOREIGN KEY (`follower_id`) REFERENCES `users`(`id`),
-- FOREIGN KEY (`following_id`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
互相关注表（可选，提高查询效率）
CREATE TABLE `mutual_follows` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`user1_id` BIGINT UNSIGNED NOT NULL,
`user2_id` BIGINT UNSIGNED NOT NULL,
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY `uk_user1_user2` (`user1_id`, `user2_id`),
UNIQUE KEY `uk_user2_user1` (`user2_id`, `user1_id`),
KEY `idx_user1_id` (`user1_id`),
KEY `idx_user2_id` (`user2_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
关注通知表（可选）
CREATE TABLE `follow_notifications` (
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`user_id` BIGINT UNSIGNED NOT NULL COMMENT '接收通知的用户ID',
`follower_id` BIGINT UNSIGNED NOT NULL COMMENT '关注者ID',
`is_read` TINYINT DEFAULT 0 COMMENT '是否已读',
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
KEY `idx_user_is_read` (`user_id`, `is_read`),
KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
PHP操作类示例
Follow模型类
<?php
class FollowModel {
private $db;
public function __construct($db) {
$this->db = $db;
}
/**
* 关注用户
*/
public function follow($followerId, $followingId, $source = '') {
if ($followerId == $followingId) {
return ['error' => '不能关注自己'];
}
try {
// 开启事务
$this->db->beginTransaction();
// 插入关注记录
$sql = "INSERT INTO follows (follower_id, following_id, source)
VALUES (?, ?, ?)";
$stmt = $this->db->prepare($sql);
$stmt->execute([$followerId, $followingId, $source]);
// 更新计数（使用原子操作避免并发问题）
$this->updateCounts($followerId, $followingId, 'follow');
// 检查是否互相关注
$isMutual = $this->checkMutualFollow($followerId, $followingId);
// 创建通知
$this->createNotification($followingId, $followerId);
// 提交事务
$this->db->commit();
return [
'success' => true,
'is_mutual' => $isMutual
];
} catch (Exception $e) {
$this->db->rollBack();
return ['error' => $e->getMessage()];
}
}
/**
* 取消关注
*/
public function unfollow($followerId, $followingId) {
try {
$this->db->beginTransaction();
// 删除关注记录
$sql = "DELETE FROM follows WHERE follower_id = ? AND following_id = ?";
$stmt = $this->db->prepare($sql);
$stmt->execute([$followerId, $followingId]);
// 更新计数
$this->updateCounts($followerId, $followingId, 'unfollow');
$this->db->commit();
return ['success' => true];
} catch (Exception $e) {
$this->db->rollBack();
return ['error' => $e->getMessage()];
}
}
/**
* 获取用户的粉丝列表
*/
public function getFollowers($userId, $page = 1, $perPage = 20) {
$offset = ($page - 1) * $perPage;
$sql = "SELECT u.id, u.username, u.nickname, u.avatar,
f.created_at as followed_at,
CASE
WHEN EXISTS (
SELECT 1 FROM follows f2
WHERE f2.follower_id = ?
AND f2.following_id = u.id
) THEN 1 ELSE 0
END as is_following_back
FROM follows f
INNER JOIN users u ON f.follower_id = u.id
WHERE f.following_id = ? AND f.status = 1
ORDER BY f.created_at DESC
LIMIT ? OFFSET ?";
$stmt = $this->db->prepare($sql);
$stmt->execute([$userId, $userId, $perPage, $offset]);
return $stmt->fetchAll();
}
/**
* 获取用户关注的人
*/
public function getFollowing($userId, $page = 1, $perPage = 20) {
$offset = ($page - 1) * $perPage;
$sql = "SELECT u.id, u.username, u.nickname, u.avatar,
f.created_at as followed_at,
CASE
WHEN EXISTS (
SELECT 1 FROM follows f2
WHERE f2.follower_id = u.id
AND f2.following_id = ?
) THEN 1 ELSE 0
END as follows_you
FROM follows f
INNER JOIN users u ON f.following_id = u.id
WHERE f.follower_id = ? AND f.status = 1
ORDER BY f.created_at DESC
LIMIT ? OFFSET ?";
$stmt = $this->db->prepare($sql);
$stmt->execute([$userId, $userId, $perPage, $offset]);
return $stmt->fetchAll();
}
/**
* 检查是否已关注
*/
public function isFollowing($followerId, $followingId) {
$sql = "SELECT 1 FROM follows
WHERE follower_id = ? AND following_id = ? AND status = 1";
$stmt = $this->db->prepare($sql);
$stmt->execute([$followerId, $followingId]);
return $stmt->rowCount() > 0;
}
/**
* 更新关注计数
*/
private function updateCounts($followerId, $followingId, $type) {
if ($type == 'follow') {
$sql = "UPDATE users
SET followers_count = followers_count + 1
WHERE id = ?";
$stmt = $this->db->prepare($sql);
$stmt->execute([$followingId]);
$sql = "UPDATE users
SET following_count = following_count + 1
WHERE id = ?";
$stmt = $this->db->prepare($sql);
$stmt->execute([$followerId]);
} else {
// unfollow 时递减
$sql = "UPDATE users
SET followers_count = GREATEST(followers_count - 1, 0)
WHERE id = ?";
$stmt = $this->db->prepare($sql);
$stmt->execute([$followingId]);
$sql = "UPDATE users
SET following_count = GREATEST(following_count - 1, 0)
WHERE id = ?";
$stmt = $this->db->prepare($sql);
$stmt->execute([$followerId]);
}
}
/**
* 检查互相关注
*/
private function checkMutualFollow($user1, $user2) {
$sql = "SELECT 1 FROM follows
WHERE (follower_id = ? AND following_id = ?)
OR (follower_id = ? AND following_id = ?)
LIMIT 1";
$stmt = $this->db->prepare($sql);
$stmt->execute([$user1, $user2, $user2, $user1]);
return $stmt->rowCount() > 0;
}
/**
* 获取推荐关注
*/
public function getSuggestions($userId, $limit = 10) {
// 基于共同关注的人推荐
$sql = "SELECT u.id, u.username, u.nickname, u.avatar,
COUNT(DISTINCT f.following_id) as common_count,
u.followers_count
FROM follows f
INNER JOIN users u ON f.following_id = u.id
WHERE f.follower_id IN (
SELECT following_id FROM follows WHERE follower_id = ?
)
AND f.following_id != ?
AND f.following_id NOT IN (
SELECT following_id FROM follows WHERE follower_id = ?
)
GROUP BY u.id
ORDER BY common_count DESC, u.followers_count DESC
LIMIT ?";
$stmt = $this->db->prepare($sql);
$stmt->execute([$userId, $userId, $userId, $limit]);
return $stmt->fetchAll();
}
}
性能优化建议
缓存策略
// 使用 Redis 缓存粉丝数
class FollowCache {
public function getUserFollowCount($userId) {
$key = "user:{$userId}:follow_count";
// 优先从缓存获取
$count = Redis::get($key);
if ($count === false) {
// 从数据库获取并缓存
$count = DB::table('users')->where('id', $userId)->value('followers_count');
Redis::setex($key, 3600, $count);
}
return $count;
}
// 关注操作后更新缓存
public function invalidateFollowCache($userId) {
Redis::del("user:{$userId}:follow_count");
Redis::del("user:{$userId}:following_count");
}
}
索引优化建议
-- 组合索引优化联合查询 CREATE INDEX idx_follows_user_id ON follows (follower_id, status, created_at); CREATE INDEX idx_follows_following_id ON follows (following_id, status, created_at); -- 如果用户量大，考虑使用分区表 -- ALTER TABLE follows PARTITION BY HASH(follower_id) PARTITIONS 10;
注意事项
- 并发控制：使用事务和原子操作避免计数不一致
- 大V处理：对于有大量粉丝的用户，考虑异步更新计数
- 查询优化：使用EXPLAIN分析慢查询，优化索引
- 数据归档：定期清理长期不活跃用户的关注关系
- 缓存策略：热点数据使用Redis缓存，减少数据库压力
这个设计覆盖了基本的关注/取消关注、互相关注检测、粉丝/关注列表、推荐等核心功能，并考虑了性能优化，根据实际业务需求，你可以适当调整表结构和功能。
