标签系统的 MySQL 模式和 CRUD 查询

发布于 2024-12-14 03:00:40 字数 1325 浏览 4 评论 0原文

假设我有视频，每个视频可以有几个标签（每个视频最多 10 个标签）。

我规划了我的 SQL 架构，如下所示：

videos: id, title, path,
tag_rels：id、tag_id、item_id（它将指向videos。id），
标签： id，标签；

好吧，我觉得不错。

然后我编写了 SELECT 来获取视频及其标签。

SELECT `videos`.`id`, `videos`.`title`, `videos`.`path`, `tags`.`tag`
FROM `videos`
JOIN `tag_rels`
    ON `tag_rels`.`item_id` = `videos`.`id`
JOIN `tags`
    ON `tags`.`id` = `tag_rels`.`tag_id`

它没有经过测试，因为这都是在头脑层面上的。

巨大的问题是INSERT查询（我猜是查询）。

据我了解：

查询#1：在 videos 中插入视频。这将返回主键（videos.id），对吗？
查询#2：从数据库中选择标签#1并获取其主键，
查询#3：如果没有这样的记录（基于标签名称（tags.tag）），执行插入查询并插入它，目标是获取该标签的主键，
查询 #4：在带有视频的 pk 和标签的 的 tag_rels 中插入条目。 >pk；

因此，每个视频只有一个查询，再加上每个标签的 2 或 3 个查询，

这意味着如果视频有 10 个标签，并且（最坏的情况）这些标签中的任何一个都不会保存在其中。数据库，这将花费我 1 + 10 * 3... em.... 31 查询？！

非常感谢！

PS 我不想在数据库中出现重复的条目，我会喜欢有专栏used_in 到 tags 以及使用该标签的视频计数。

原文

Lets imagine that I have videos and each video can have few tags (maximal is 10 tags per video).

I planned my SQL schema and it looks like this:

videos: id, title, path,
tag_rels: id, tag_id, item_id (it will point to videos.id),
tags: id, tag;

Okay, seems fine to me.

Then I wrote SELECT that should get video, plus, tags for it.

SELECT `videos`.`id`, `videos`.`title`, `videos`.`path`, `tags`.`tag`
FROM `videos`
JOIN `tag_rels`
    ON `tag_rels`.`item_id` = `videos`.`id`
JOIN `tags`
    ON `tags`.`id` = `tag_rels`.`tag_id`

It's not tested or so because it's all in mind-level.

And the huge problem is INSERT query (queries, I guess).

As much as I understand:

Query #1: insert video in videos. This will return primary key (videos.id), right?
Query #2: select tag #1 from database and get it's primary key,
Query #3: if there aren't such record (based on tag name (tags.tag), do an insert query and insert it. The goal is to get primary key of that tag,
Query #4: insert entry in tag_rels with video's pk and tag's pk;

So, it's one query per video as it is, plus` 2 or 3 queries for each tag.

This means that if video has 10 tags and (worst situation) any of those tags aren't saved in database, it will cost me 1 + 10 * 3... em.... 31 query?!

There got to be a better way! Thanks a lot!

P.S. I don't want duplicate entries in the database and I would love to have column used_in to tags with video count that use the tag. In future...

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

神经暖 2024-12-21 03:00:40

这看起来类似于存储过程的情况。让商店生成视频数据和标签列表。然后在里面执行查询 #1 到 #4。由于您不必来回移动数据，因此速度会快得多。

这就是您所需要的。这只是一个起点，所以不要指望它能正确编译运行，而且您将需要所有 10 个标签（我不认为 mysql 可以接受数组，但有人可能更了解）。

CREATE PROCEDURE insertvideo (IN videotitle CHAR(20),IN videopath CHAR(20), IN tag1 CHAR(20),IN tag2 CHAR(20), IN tag3 CHAR(20)  )
    BEGIN
      DECLARE myid INT;
      DECLARE tagid INT;
      INSERT INTO videos (title, path) VALUES ( videotitle,videopath);
      SELECT id INTO myid FROM videos WHERE title=videotitle AND path=videopath;
      SET tagid=NULL;
      SELECT id INTO tagid FROM tags WHERE tag=tag1;
      IF tagid IS NULL THEN
        INSERT INTO tags (tag) VALUES (tag1);
        SELECT id INTO tagid FROM tags WHERE tag=tag1;
      END IF;
      INSERT INTO tag_rels (tag_id, item_id) VALUES (tagid,myid);
      SET tagid=NULL;
      SELECT id INTO tagid FROM tags WHERE tag=tag2;
      IF tagid IS NULL THEN
        INSERT INTO tags (tag) VALUES (tag1);
        SELECT id INTO tagid FROM tags WHERE tag=tag2;
      END IF;
      INSERT INTO tag_rels (tag_id, item_id) VALUES (tagid,myid);
      SET tagid=NULL;
      SELECT id INTO tagid FROM tags WHERE tag=tag3;
      IF tagid IS NULL THEN
        INSERT INTO tags (tag) VALUES (tag1);
        SELECT id INTO tagid FROM tags WHERE tag=tag3;
      END IF;
      INSERT INTO tag_rels (tag_id, item_id) VALUES (tagid,myid);
    END

This looks a like case for stored procedure. Have the store produce take the video data, and the list of tags. Then do Queries #1 to #4 inside. It will be much faster since you don't have to move data back and forth.

Here's kind of what you would need. It's only a starting point so don't expect it to compile much less run correctly, and you would need all 10 tags (I don't think mysql can take an array, but someone might know better).

CREATE PROCEDURE insertvideo (IN videotitle CHAR(20),IN videopath CHAR(20), IN tag1 CHAR(20),IN tag2 CHAR(20), IN tag3 CHAR(20)  )
    BEGIN
      DECLARE myid INT;
      DECLARE tagid INT;
      INSERT INTO videos (title, path) VALUES ( videotitle,videopath);
      SELECT id INTO myid FROM videos WHERE title=videotitle AND path=videopath;
      SET tagid=NULL;
      SELECT id INTO tagid FROM tags WHERE tag=tag1;
      IF tagid IS NULL THEN
        INSERT INTO tags (tag) VALUES (tag1);
        SELECT id INTO tagid FROM tags WHERE tag=tag1;
      END IF;
      INSERT INTO tag_rels (tag_id, item_id) VALUES (tagid,myid);
      SET tagid=NULL;
      SELECT id INTO tagid FROM tags WHERE tag=tag2;
      IF tagid IS NULL THEN
        INSERT INTO tags (tag) VALUES (tag1);
        SELECT id INTO tagid FROM tags WHERE tag=tag2;
      END IF;
      INSERT INTO tag_rels (tag_id, item_id) VALUES (tagid,myid);
      SET tagid=NULL;
      SELECT id INTO tagid FROM tags WHERE tag=tag3;
      IF tagid IS NULL THEN
        INSERT INTO tags (tag) VALUES (tag1);
        SELECT id INTO tagid FROM tags WHERE tag=tag3;
      END IF;
      INSERT INTO tag_rels (tag_id, item_id) VALUES (tagid,myid);
    END

回复收藏 0 原文

完美的未来在梦里 2024-12-21 03:00:40

我认为你的计算是错误的。无论标签数量如何，您都需要执行 3 个查询。
1. 插入视频...
2. 插入标签(tag) 从中选择* (SELECT 'tag_1' as tag UNION SELECT 'tag_2' ...)a 不存在的地方（从标签 b 中选择 1，其中 b.tag = a.tag）
3.假设您在 (tag_id, item_id) 上有唯一索引，INSERT IGNORE INTO tag_rels( tag_id, item_id) SELECT tag_id, new_video_id FROM Tags WHERE Tags.tag IN ([new tag_list]) (new_video_id - 是你在视频中插入记录后得到的Video.id）