PHP 拉黑功能数据库设计

wen PHP项目 2026-08-10 164

本文目录导读：

PHP 拉黑功能数据库设计

基础表结构设计
功能扩展设计
PHP核心代码示例
性能优化建议
注意事项

我来为你设计一个完整的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;
    }
}