本文目录导读：
我来为你设计一个完整的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缓存，减少数据库压力
这个设计覆盖了基本的关注/取消关注、互相关注检测、粉丝/关注列表、推荐等核心功能，并考虑了性能优化，根据实际业务需求，你可以适当调整表结构和功能。
版权声明：NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
NBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
CBA直播
足球直播
足球直播
足球直播
足球直播
足球直播
足球直播
足球直播
足球直播
足球直播
足球直播
足球直播
足球直播
足球直播
足球直播
足球直播
足球直播
足球直播
足球直播
英超直播
英超直播
英超直播
英超直播
英超直播
英超直播
英超直播
英超直播
英超直播
英超直播
英超直播
英超直播
英超直播
英超直播
英超直播
英超直播
英超直播
英超直播
