我正处于数据库设计的早期阶段,所以还没有最终的结果,并且我正在为具有可选标签的线程使用“TOXI”3表设计,但我忍不住觉得加入是并不是真的必要,也许我只需要依赖我的简单标签列posts
我可以在其中存储类似 varchar 的表<tag>, <secondTag>
.
回顾一下:
- 是否值得在 2 个标签表上进行额外的左连接,而不是在我的表中只包含一个标签列?
posts
table.
- 有没有办法可以优化我的查询?
Schema
CREATE TABLE `posts` (
`post_id` INT UNSIGNED PRIMARY AUTO_INCREMENT,
`post_name` VARCHAR(255)
) Engine=InnoDB;
CREATE TABLE `post_tags` (
`tag_id` INT UNSIGNED PRIMARY AUTO_INCREMENT,
`tag_name` VARCHAR(255)
) Engine=InnoDB;
CREATE TABLE `post_tags_map` (
`map_id` INT PRIMARY AUTO_INCREMENT,
`post_id` INT NOT NULL,
`tags_id` INT NOT NULL,
FOREIGN KEY `post_id` REFERENCES `posts` (`post_id`),
FOREIGN KEY `post_id` REFERENCES `post_tags` (`tag_id`)
) Engine=InnoDB;
样本数据
INSERT INTO `posts` (`post_id`, `post_name`)
VALUES
(1, 'test');
INSERT INTO `post_tags` (`tag_id`, `tag_name`)
VALUES
(1, 'mma'),
(2, 'ufc');
INSERT INTO `posts_tags_map` (`map_id`, `post_id`, `tags_id`)
VALUES
(1, 1, 1),
(2, 1, 2);
当前查询
SELECT
posts.*,
GROUP_CONCAT( post_tags.tag_name order by post_tags.tag_name ) AS tags
FROM posts
LEFT JOIN posts_tags_map
ON posts_tags_map.post_id = posts.post_id
LEFT JOIN post_tags
ON posts_tags_map.tags_id = posts_tags.tag_id
WHERE posts.post_id = 1
GROUP BY post_id
Result
IF有标签:
post_id post_name tags
1 test mma, ufc