用户浏览过得其他用户后排在列表最后-数据表设计

 

 

方案一：主表+行为关联表（推荐）
1. 用户表 (users)
sql
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    -- 其他用户信息字段
    INDEX idx_username(username)
);

2. 用户查看记录表 (user_views)
sql
CREATE TABLE user_views (
    id INT PRIMARY KEY AUTO_INCREMENT,
    viewer_id INT NOT NULL,  -- 谁在看（当前用户）
    viewed_id INT NOT NULL,  -- 被看的用户
    view_count INT DEFAULT 1,
    first_viewed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_viewed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_viewer_viewed (viewer_id, viewed_id),
    INDEX idx_viewer_lastviewed (viewer_id, last_viewed_at),
    FOREIGN KEY (viewer_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (viewed_id) REFERENCES users(id) ON DELETE CASCADE
);

查询示例
sql
-- 获取用户列表，未看过的排在前面，看过的按最后查看时间倒序排在后边
SELECT 
    u.*,
    uv.last_viewed_at,
    CASE WHEN uv.viewer_id IS NULL THEN 0 ELSE 1 END as has_viewed
FROM users u
LEFT JOIN user_views uv ON u.id = uv.viewed_id AND uv.viewer_id = ?  -- 当前用户ID
ORDER BY 
    has_viewed ASC,  -- 先排未看过的(0)，再看过的(1)
    uv.last_viewed_at DESC,  -- 看过的按最后查看时间倒序
    u.created_at DESC;  -- 其他排序条件