tag标签 mysql表结构

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE tags (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE article_tags (
    article_id INT,
    tag_id INT,
    PRIMARY KEY (article_id, tag_id),
    FOREIGN KEY (article_id) REFERENCES articles(id),
    FOREIGN KEY (tag_id) REFERENCES tags(id)
);

SELECT a.*
FROM articles a
JOIN article_tags at ON a.id = at.article_id
WHERE at.tag_id = ?;

-- 首先检查标签是否存在，如果不存在则插入新标签
INSERT INTO tags (name) SELECT ? FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM tags WHERE name = ?);

-- 获取新插入标签的 ID
SET @tag_id = LAST_INSERT_ID();

-- 将标签与文章关联
INSERT INTO article_tags (article_id, tag_id) VALUES (?, @tag_id);

DELETE FROM article_tags WHERE article_id = ? AND tag_id = ?;