本文目录导读：
我来为你设计一个完整的PHP拉黑功能数据库方案,包含多种实现场景。
基础表结构设计
用户表（已有）
CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(50) NOT NULL, `nickname` varchar(50) DEFAULT NULL, `status` tinyint(1) DEFAULT '1' COMMENT '1正常 0禁用', `created_at` timestamp DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
拉黑记录表（核心表）
CREATE TABLE `user_blocks` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL COMMENT '拉黑操作发起人ID', `blocked_user_id` int(11) NOT NULL COMMENT '被拉黑用户ID', `block_type` tinyint(1) DEFAULT '1' COMMENT '拉黑类型：1临时 2永久', `reason` varchar(255) DEFAULT NULL COMMENT '拉黑原因', `block_status` tinyint(1) DEFAULT '1' COMMENT '状态：1生效 0解除', `blocked_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '拉黑时间', `expired_at` timestamp NULL DEFAULT NULL COMMENT '过期时间（临时拉黑时使用）', `unblocked_at` timestamp NULL DEFAULT NULL COMMENT '解除拉黑时间', `unblock_reason` varchar(255) DEFAULT NULL COMMENT '解除原因', `created_at` timestamp DEFAULT CURRENT_TIMESTAMP, `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `uk_user_blocked` (`user_id`, `blocked_user_id`, `block_status`), KEY `idx_blocked_user` (`blocked_user_id`), KEY `idx_user_id` (`user_id`), KEY `idx_expired_at` (`expired_at`), CONSTRAINT `fk_user_blocker` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`), CONSTRAINT `fk_user_blocked` FOREIGN KEY (`blocked_user_id`) REFERENCES `users`(`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户拉黑记录表';
功能扩展设计
拉黑日志表（可选，用于审计）
CREATE TABLE `block_logs` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `block_id` bigint(20) NOT NULL COMMENT '关联拉黑记录ID', `user_id` int(11) NOT NULL COMMENT '操作人ID', `action` varchar(20) NOT NULL COMMENT '操作：block/unblock', `old_status` tinyint(1) DEFAULT NULL, `new_status` tinyint(1) DEFAULT NULL, `remark` varchar(255) DEFAULT NULL, `created_at` timestamp DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_block_id` (`block_id`), KEY `idx_user_id` (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
批量拉黑表（管理后台用）
CREATE TABLE `batch_blocks` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `admin_id` int(11) NOT NULL COMMENT '管理员ID', `block_reason` varchar(255) DEFAULT NULL, `block_type` tinyint(1) DEFAULT '2' COMMENT '默认永久', `block_count` int(11) DEFAULT '0' COMMENT '拉黑数量', `created_at` timestamp DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
PHP核心代码示例
拉黑类核心方法
<?php
class UserBlock {
private $pdo;
public function __construct($pdo) {
$this->pdo = $pdo;
}
/**
* 执行拉黑操作
*/
public function blockUser($userId, $blockedUserId, $type = 1, $reason = '', $expiredAt = null) {
// 参数校验
if ($userId == $blockedUserId) {
throw new Exception('不能拉黑自己');
}
// 检查是否已拉黑
if ($this->isBlocked($userId, $blockedUserId)) {
throw new Exception('该用户已被拉黑');
}
$sql = "INSERT INTO user_blocks (user_id, blocked_user_id, block_type, reason, expired_at)
VALUES (?, ?, ?, ?, ?)";
try {
$stmt = $this->pdo->prepare($sql);
$stmt->execute([$userId, $blockedUserId, $type, $reason, $expiredAt]);
// 记录日志
$this->logAction(
$this->pdo->lastInsertId(),
$userId,
'block',
0,
1,
$reason
);
return true;
} catch (PDOException $e) {
throw new Exception('拉黑失败：' . $e->getMessage());
}
}
/**
* 解除拉黑
*/
public function unblockUser($userId, $blockedUserId, $unblockReason = '') {
$sql = "UPDATE user_blocks
SET block_status = 0, unblocked_at = NOW(), unblock_reason = ?
WHERE user_id = ? AND blocked_user_id = ? AND block_status = 1";
try {
$stmt = $this->pdo->prepare($sql);
$stmt->execute([$unblockReason, $userId, $blockedUserId]);
if ($stmt->rowCount() > 0) {
// 记录日志
$blockId = $this->getBlockId($userId, $blockedUserId);
$this->logAction($blockId, $userId, 'unblock', 1, 0, $unblockReason);
return true;
}
return false;
} catch (PDOException $e) {
throw new Exception('解除拉黑失败：' . $e->getMessage());
}
}
/**
* 检查是否已拉黑（含过期判断）
*/
public function isBlocked($userId, $blockedUserId) {
$sql = "SELECT id FROM user_blocks
WHERE user_id = ? AND blocked_user_id = ?
AND block_status = 1
AND (expired_at IS NULL OR expired_at > NOW())
LIMIT 1";
$stmt = $this->pdo->prepare($sql);
$stmt->execute([$userId, $blockedUserId]);
return $stmt->fetchColumn() !== false;
}
/**
* 获取用户的拉黑列表
*/
public function getBlockedUsers($userId, $limit = 20, $offset = 0) {
$sql = "SELECT b.id, u.id as user_id, u.nickname, u.username,
b.reason, b.block_type, b.blocked_at, b.expired_at
FROM user_blocks b
INNER JOIN users u ON b.blocked_user_id = u.id
WHERE b.user_id = ? AND b.block_status = 1
AND (b.expired_at IS NULL OR b.expired_at > NOW())
ORDER BY b.blocked_at DESC
LIMIT {$limit} OFFSET {$offset}";
$stmt = $this->pdo->prepare($sql);
$stmt->execute([$userId]);
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
/**
* 检查是否需要清理过期拉黑
*/
public function cleanExpiredBlocks() {
$sql = "UPDATE user_blocks
SET block_status = 0, unblocked_at = NOW(),
unblock_reason = '系统自动解除过期拉黑'
WHERE block_status = 1
AND expired_at IS NOT NULL
AND expired_at <= NOW()";
$stmt = $this->pdo->prepare($sql);
return $stmt->execute();
}
/**
* 记录操作日志
*/
private function logAction($blockId, $userId, $action, $oldStatus, $newStatus, $remark = '') {
$sql = "INSERT INTO block_logs (block_id, user_id, action, old_status, new_status, remark)
VALUES (?, ?, ?, ?, ?, ?)";
$stmt = $this->pdo->prepare($sql);
return $stmt->execute([$blockId, $userId, $action, $oldStatus, $newStatus, $remark]);
}
/**
* 获取拉黑记录ID
*/
private function getBlockId($userId, $blockedUserId) {
$sql = "SELECT id FROM user_blocks
WHERE user_id = ? AND blocked_user_id = ?
ORDER BY id DESC LIMIT 1";
$stmt = $this->pdo->prepare($sql);
$stmt->execute([$userId, $blockedUserId]);
return $stmt->fetchColumn();
}
}
使用示例
// 数据库连接
$pdo = new PDO('mysql:host=localhost;dbname=test', 'username', 'password');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$block = new UserBlock($pdo);
// 拉黑用户（永久）
try {
$block->blockUser(1, 2, 2, '骚扰用户');
echo "拉黑成功";
} catch (Exception $e) {
echo "错误：" . $e->getMessage();
}
// 临时拉黑7天
try {
$expiredAt = date('Y-m-d H:i:s', strtotime('+7 days'));
$block->blockUser(1, 3, 1, '临时警告', $expiredAt);
echo "临时拉黑成功";
} catch (Exception $e) {
echo "错误：" . $e->getMessage();
}
// 检查是否被拉黑
if ($block->isBlocked(1, 2)) {
echo "该用户已被拉黑";
} else {
echo "可以正常交互";
}
// 拉黑列表
$list = $block->getBlockedUsers(1, 10, 0);
print_r($list);
性能优化建议
添加redis缓存（可选）
// Redis缓存检查是否被拉黑
class BlockCheckWithCache {
private $redis;
private $pdo;
public function checkIsBlocked($userId, $targetUserId) {
// 先从Redis缓存检查
$cacheKey = "block:{$userId}:{$targetUserId}";
$cached = $this->redis->get($cacheKey);
if ($cached !== false) {
return $cached == '1';
}
// 缓存未命中，查询数据库
$result = $this->isBlockedInDb($userId, $targetUserId);
// 写入缓存（10分钟过期）
$this->redis->setex($cacheKey, 600, $result ? '1' : '0');
return $result;
}
}
注意事项
- 索引优化：确保user_id和blocked_user_id上有合适的联合索引
- 定期清理：建议设置定时任务清理过期的临时拉黑记录
- 数据一致性：拉黑/解除操作要确保事务性
- 敏感操作：拉黑操作建议记录日志，方便追溯
- 性能考虑：高频的拉黑查询可以考虑使用缓存
这个设计可以满足大多数场景的拉黑功能需求,你可以根据实际业务进行调整，需要扩展功能（如多级拉黑、分组拉黑等）可以在此基础上进行修改。
