用户关注粉丝表设计

use dormitory_management;

CREATE TABLE user (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE follow (
    follow_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    following_id  INT NOT NULL,
     status int DEFAULT '0' comment '0,关注，1已关注，2，回关，3，互关，4.取关'
);

-- 向 user 表插入中文测试数据
INSERT INTO user (username, password, email) VALUES
('张三', 'password1', 'zhangsan@example.com'),
('李四', 'password2', 'lisi@example.com'),
('王五', 'password3', 'wangwu@example.com');

-- 向 follow 表插入中文测试数据
INSERT INTO follow (user_id, following_id, status) VALUES
(1, 2, 0),
(2, 1, 1),
(1, 3, 2),
(3, 1, 3),
(2, 3, 4);



# 用户2 已关注的人
select * from follow where user_id = 2 and status = 1