SQL中处理集合实现帖子点赞与取消点赞功能的最优方案咨询

当前用INT[]类型存储点赞用户ID的方案适合用户量小、并发低的场景，不是大规模业务的最优解，优缺点如下：

• 优势：无需关联查询，可直接读取likes字段或通过array_length(like_user_id, 1)快速获取点赞总数
• 劣势：数组长度过大时读写性能会大幅下降，无法为数组内的单个用户ID加索引，查询特定用户是否点赞的效率随数组长度增长线性降低，并发更新帖子数据时容易出现行锁冲突，也不支持点赞用户列表的分页查询

如果当前业务规模小，坚持使用数组方案，PostgreSQL本身提供了完善的数组操作函数，你之前写的更新语句逻辑完全错误，正确实现如下：

点赞操作 #

逻辑：仅当用户ID未存在于点赞数组中时，追加用户ID并将点赞数+1，避免重复点赞

UPDATE posts 
SET 
  like_user_id = array_append(like_user_id, $2),
  likes = likes + 1
WHERE 
  pid = $1 
  AND NOT ($2 = ANY(like_user_id));

取消点赞操作 #

逻辑：仅当用户ID存在于点赞数组中时，移除用户ID并将点赞数-1

UPDATE posts 
SET 
  like_user_id = array_remove(like_user_id, $2),
  likes = likes - 1
WHERE 
  pid = $1 
  AND $2 = ANY(like_user_id);

修正后的接口代码示例 #

// 点赞接口
const likePost = (req, res, next) => {
  const values = [req.body.id, req.body.user_id];
  const query = `
    UPDATE posts 
    SET like_user_id = array_append(like_user_id, $2), likes = likes + 1
    WHERE pid = $1 AND NOT ($2 = ANY(like_user_id))
  `;
  pool.query(query, values, (q_err, q_res) => {
    if (q_err) return next(q_err);
    q_res.rowCount === 0 
      ? res.json(`您已经点赞过帖子 ${req.body.id} 啦`) 
      : res.json(`帖子 ${req.body.id} 点赞成功 👍`);
  });
};

// 取消点赞接口
const unlikePost = (req, res, next) => {
  const values = [req.body.id, req.body.user_id];
  const query = `
    UPDATE posts 
    SET like_user_id = array_remove(like_user_id, $2), likes = likes - 1
    WHERE pid = $1 AND $2 = ANY(like_user_id)
  `;
  pool.query(query, values, (q_err, q_res) => {
    if (q_err) return next(q_err);
    q_res.rowCount === 0 
      ? res.json(`您还未点赞过帖子 ${req.body.id}`) 
      : res.json(`帖子 ${req.body.id} 取消点赞成功`);
  });
};

如果业务后续有增长预期，使用单独的点赞关联表是更稳妥的选择，优势如下：

• 支持为pid和user_id加索引，查询用户是否点赞、用户点赞列表、帖子点赞列表的性能远高于数组方案
• 并发冲突少，每个点赞是独立行记录，更新不会锁定整行帖子数据
• 扩展性强，后续可灵活增加点赞时间、点赞设备等扩展字段

关联表建表语句 #

CREATE TABLE post_likes (
  id SERIAL PRIMARY KEY,
  pid INT REFERENCES posts(pid) ON DELETE CASCADE,
  user_id INT REFERENCES users(uid) ON DELETE CASCADE,
  create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(pid, user_id) -- 唯一约束天然避免重复点赞
);

自动维护点赞数触发器 #

无需手动更新posts表的likes字段，通过数据库触发器自动同步：

CREATE OR REPLACE FUNCTION update_post_likes_count()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    UPDATE posts SET likes = likes + 1 WHERE pid = NEW.pid;
  ELSIF TG_OP = 'DELETE' THEN
    UPDATE posts SET likes = likes - 1 WHERE pid = OLD.pid;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_post_likes_change
AFTER INSERT OR DELETE ON post_likes
FOR EACH ROW EXECUTE FUNCTION update_post_likes_count();

点赞/取消点赞操作 #

点赞：INSERT INTO post_likes(pid, user_id) VALUES ($1, $2) ON CONFLICT DO NOTHING;
取消点赞：DELETE FROM post_likes WHERE pid = $1 AND user_id = $2;

内容的提问来源于stack exchange，提问作者user9958772