mysql如何使用mysql实现点赞功能

CREATE TABLE posts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255),
    like_count INT DEFAULT 0
);

CREATE TABLE post_likes (
    post_id INT,
    user_id INT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (post_id, user_id), -- 联合主键，防止重复点赞
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE
);

INSERT INTO post_likes (post_id, user_id)
VALUES (1001, 12345)
ON DUPLICATE KEY UPDATE created_at = CURRENT_TIMESTAMP;

DELETE FROM post_likes 
WHERE post_id = 1001 AND user_id = 12345;

DELIMITER ;;
CREATE TRIGGER after_like_insert 
AFTER INSERT ON post_likes
FOR EACH ROW 
BEGIN
    UPDATE posts SET like_count = like_count + 1 WHERE id = NEW.post_id;
END;;
<p>CREATE TRIGGER after_like_delete 
AFTER DELETE ON post_likes
FOR EACH ROW 
BEGIN
UPDATE posts SET like_count = like_count - 1 WHERE id = OLD.post_id;
END;;
DELIMITER ;</p>

-- 点赞后
UPDATE posts SET like_count = like_count + 1 WHERE id = 1001;
<p>-- 取消点赞后
UPDATE posts SET like_count = like_count - 1 WHERE id = 1001;</p>

SELECT like_count FROM posts WHERE id = 1001;

SELECT EXISTS(
    SELECT 1 FROM post_likes 
    WHERE post_id = 1001 AND user_id = 12345
) AS is_liked;

SELECT user_id, created_at FROM post_likes 
WHERE post_id = 1001 ORDER BY created_at DESC;