您现在的位置：程式碼之家 >> 編程語言 > >> 更多程式語言 >> 程式設計疑難解答

MySQL 關注功能數據庫設計：從逗號分隔到範式化建模

編輯：程式設計疑難解答

反模式識別與關聯表設計最佳實踐

將關注關係存儲為逗號分隔字符串（1,3,5,7,9）是典型的 第一範式（1NF）違規，會導致查詢、維護、擴展極其困難。標準關係型建模應使用 關聯表（關係表）。

❌ 反模式危害分析

操作	逗號字符串方案	關聯表方案
新增關注	讀取→拼接→寫回（并發需鎖）	`INSERT INTO follows ...`（原子操作）
取消關注	字符串替換/正則（易出錯）	`DELETE FROM follows WHERE ...`
查詢關注列表	`FIND_IN_SET` 全表掃描，無法用索引	索引查詢 `O(log n)`
統計關注數	需解析字符串長度	`COUNT(*)` 瞬間完成
共同關注/推薦	極其複雜，幾乎不可行	SQL JOIN 簡單高效
事務一致性	難以保證	原生支持

✅ 標準三表設計

-- 用戶表
CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    -- 其他字段...
);

-- 關注關係表（核心）
CREATE TABLE follows (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    follower_id BIGINT NOT NULL,      -- 關注者
    followee_id BIGINT NOT NULL,      -- 被關注者
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    UNIQUE KEY uk_follow (follower_id, followee_id), -- 防止重復關注
    INDEX idx_follower (follower_id),  -- 查詢我關注了誰
    INDEX idx_followee (followee_id),  -- 查詢誰關注了我
    FOREIGN KEY (follower_id) REFERENCES users(id),
    FOREIGN KEY (followee_id) REFERENCES users(id)
);

-- 可選：用戶統計緩存表（高併發優化）
CREATE TABLE user_stats (
    user_id BIGINT PRIMARY KEY,
    followees_count INT DEFAULT 0,  -- 我關注的人數
    followers_count INT DEFAULT 0,  -- 關注我的人數
    FOREIGN KEY (user_id) REFERENCES users(id)
);

核心查詢示例

-- 我關注了誰（分頁）
SELECT u.* FROM users u
JOIN follows f ON u.id = f.followee_id
WHERE f.follower_id = ?
ORDER BY f.created_at DESC
LIMIT 20 OFFSET 0;

-- 誰關注了我（分頁）
SELECT u.* FROM users u
JOIN follows f ON u.id = f.follower_id
WHERE f.followee_id = ?
ORDER BY f.created_at DESC
LIMIT 20 OFFSET 0;

-- 關注/取消關注（事務保證一致性）
START TRANSACTION;
INSERT IGNORE INTO follows (follower_id, followee_id) VALUES (?, ?);
UPDATE user_stats SET followees_count = followees_count + 1 WHERE user_id = ?;
UPDATE user_stats SET followers_count = followers_count + 1 WHERE user_id = ?;
COMMIT;

PHP (ThinkPHP) 實現片段


// 模型定義
class Follow extends Model {
    protected $table = 'follows';
    
    // 關注
    public function follow($followerId, $followeeId) {
        return $this->transaction(function() use ($followerId, $followeeId) {
            $this->insert(['follower_id' => $followerId, 'followee_id' => $followeeId]);
            Db::name('user_stats')->where('user_id', $followerId)->inc('followees_count');
            Db::name('user_stats')->where('user_id', $followeeId)->inc('followers_count');
        });
    }
    
    // 取消關注
    public function unfollow($followerId, $followeeId) { ... }
    
    // 獲取關注列表
    public function getFollowees($followerId, $page, $size) { ... }
}

遷移建議

若已有逗號字符串數據，編寫一次性腳本遷移：

-- 臨時表解析
CREATE TEMPORARY TABLE tmp_follows AS
SELECT 
    user_id AS follower_id,
    CAST(TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(concat(follow_ids, ','), ',', n), ',', -1)) AS UNSIGNED) AS followee_id
FROM users
JOIN (SELECT 1 n UNION SELECT 2 UNION SELECT 3 ... UNION SELECT 100) numbers
  ON CHAR_LENGTH(follow_ids) - CHAR_LENGTH(REPLACE(follow_ids, ',', '')) >= n - 1
WHERE follow_ids IS NOT NULL AND follow_ids != '';

-- 導入正式表
INSERT IGNORE INTO follows (follower_id, followee_id)
SELECT follower_id, followee_id FROM tmp_follows;

遵循關係型數據庫設計原則，用關聯表替代字符串存儲，是系統可維護性、擴展性與性能的基石。