在JavaScript路由中为SQL查询添加点赞/取消点赞判断条件

你可以通过先查询用户是否已点赞，再根据结果执行插入或删除操作，同时同步更新post表的点赞计数。以下是具体实现：

核心逻辑实现 #

router.post("/like", (req, res) => {
    const userLiking = req.body.userLiking;
    const postid = req.body.postid;

    // 第一步：检查用户是否已点赞该帖子
    db.query(
        "SELECT 1 FROM likes WHERE userLiking = ? AND postid = ?",
        [userLiking, postid],
        (err, results) => {
            if (err) {
                console.error(err);
                return res.status(500).json({ error: "数据库查询失败" });
            }

            const hasLiked = results.length > 0;

            if (hasLiked) {
                // 已点赞：执行取消点赞逻辑
                // 1. 删除点赞记录
                db.query(
                    "DELETE FROM likes WHERE userLiking = ? AND postid = ?",
                    [userLiking, postid],
                    (deleteErr) => {
                        if (deleteErr) {
                            console.error(deleteErr);
                            return res.status(500).json({ error: "取消点赞失败" });
                        }
                        // 2. 帖子点赞数减1
                        db.query(
                            "UPDATE post SET likes = likes - 1 WHERE id = ?",
                            postid,
                            (updateErr) => {
                                if (updateErr) {
                                    console.error(updateErr);
                                    return res.status(500).json({ error: "更新点赞数失败" });
                                }
                                res.json({ success: true, action: "unliked" });
                            }
                        );
                    }
                );
            } else {
                // 未点赞：执行添加点赞逻辑
                // 1. 插入点赞记录
                db.query(
                    "INSERT INTO likes (userLiking, postid) VALUES(?,?)",
                    [userLiking, postid],
                    (insertErr) => {
                        if (insertErr) {
                            console.error(insertErr);
                            return res.status(500).json({ error: "添加点赞失败" });
                        }
                        // 2. 帖子点赞数加1
                        db.query(
                            "UPDATE post SET likes = likes + 1 WHERE id = ?",
                            postid,
                            (updateErr) => {
                                if (updateErr) {
                                    console.error(updateErr);
                                    return res.status(500).json({ error: "更新点赞数失败" });
                                }
                                res.json({ success: true, action: "liked" });
                            }
                        );
                    }
                );
            }
        }
    );
});

关键优化建议 #

• 添加数据库层面约束：在likes表上创建userLiking和postid的联合唯一索引，避免同一用户重复点赞：

  CREATE UNIQUE INDEX idx_user_post ON likes(userLiking, postid);
  

• 使用事务保证原子性：如果需要确保点赞记录和帖子点赞数的操作要么都成功、要么都失败，可以开启数据库事务（以MySQL为例）：

  db.beginTransaction((err) => {
      if (err) {
          console.error(err);
          return res.status(500).json({ error: "事务开启失败" });
      }
      // 执行插入/删除 + 更新操作
      // 操作成功调用db.commit()，失败调用db.rollback()
  });
  

• 用async/await简化代码：避免回调嵌套，提升代码可读性：

  router.post("/like", async (req, res) => {
      try {
          const userLiking = req.body.userLiking;
          const postid = req.body.postid;
  
          const [likeExists] = await db.query(
              "SELECT 1 FROM likes WHERE userLiking = ? AND postid = ?",
              [userLiking, postid]
          );
  
          if (likeExists.length > 0) {
              await db.query("DELETE FROM likes WHERE userLiking = ? AND postid = ?", [userLiking, postid]);
              await db.query("UPDATE post SET likes = likes - 1 WHERE id = ?", postid);
              return res.json({ success: true, action: "unliked" });
          } else {
              await db.query("INSERT INTO likes (userLiking, postid) VALUES(?,?)", [userLiking, postid]);
              await db.query("UPDATE post SET likes = likes + 1 WHERE id = ?", postid);
              return res.json({ success: true, action: "liked" });
          }
      } catch (err) {
          console.error(err);
          res.status(500).json({ error: "操作失败" });
      }
  });
  

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