mysql如何设计评论点赞表

CREATE TABLE comment_likes (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  comment_id BIGINT NOT NULL,
  status TINYINT DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uk_user_comment (user_id, comment_id),
  INDEX idx_comment_id (comment_id),
  INDEX idx_user_id (user_id)
);

INSERT INTO comment_likes (user_id, comment_id) 
VALUES (123, 456) 
ON DUPLICATE KEY UPDATE status = 1, updated_at = NOW();

UPDATE comment_likes SET status = 0, updated_at = NOW() 
WHERE user_id = 123 AND comment_id = 456;
-- 或直接删除
DELETE FROM comment_likes WHERE user_id = 123 AND comment_id = 456;

SELECT COUNT(*) FROM comment_likes 
WHERE comment_id = 456 AND status = 1;

SELECT 1 FROM comment_likes 
WHERE user_id = 123 AND comment_id = 456 AND status = 1;

ALTER TABLE comments ADD COLUMN like_count INT DEFAULT 0;