MySQL:通过迭代并与另一行连接来更新表中的行

发布于 2024-10-29 23:54:45 字数 1132 浏览 1 评论 0原文

我有一个表 paper

CREATE TABLE `papers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(1000) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `my_count` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `title_fulltext` (`title`),
) ENGINE=MyISAM AUTO_INCREMENT=1617432 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

和另一个表 link_table

CREATE TABLE `auth2paper2loc` (
  `auth_id` int(11) NOT NULL,
  `paper_id` int(11) NOT NULL,
  `loc_id` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

上表中的 id paper.id 与第二个表中的 link_table.paper_id 相同。我想迭代上表中的每一行,并计算其 id 在第二个表中出现的次数,并将“计数”存储到上表中的“my_count”列中。

示例:如果 tid = 1 = paper_id 的论文在表 link_table 中出现 5 次,则 my_count = 5。

我可以通过 Python 脚本做到这一点,但它会导致太多查询,而且我有数百万个条目,因此速度非常慢。我无法找出正确的语法来在 MySQL 中实现这一点。

这就是我在 Python 的 for 循环中迭代的内容(太慢了):

SELECT count(link_table.auth_id) FROM link_table
WHERE link_table.paper_id = %s

UPDATE papers SET auth_count = %s WHERE id = %s

有人可以告诉我如何创建这个吗?必须有一种方法可以将其嵌套并直接放入 MySQL 中,这样速度更快,不是吗?

I have a table papers

CREATE TABLE `papers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(1000) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `my_count` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `title_fulltext` (`title`),
) ENGINE=MyISAM AUTO_INCREMENT=1617432 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

and another table link_table

CREATE TABLE `auth2paper2loc` (
  `auth_id` int(11) NOT NULL,
  `paper_id` int(11) NOT NULL,
  `loc_id` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

The id papers.id from the upper table is the same one like the link_table.paper_id in the second table. I want to iterate through every row in the upper table and count how many times this its id appears in the second table and store the "count" into the column "my_count" in the upper table.

Example: If The paper with tid = 1 = paper_id appears 5 times in the table link_table, then my_count = 5.

I can do that by a Python script but it results in too many querys and I have millions of entrys so it is really slow. And I can't figure out the right syntax to make this right inside of MySQL.

This is what I am iterating about in a for-loop in Python (too slow):

SELECT count(link_table.auth_id) FROM link_table
WHERE link_table.paper_id = %s

UPDATE papers SET auth_count = %s WHERE id = %s

Could someone please tell me how to create this one? There must be a way to nest this and put it directly in MySQL so it is faster, isn't there?

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

扫码二维码加入Web技术交流群

发布评论

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

评论(3

楠木可依 2024-11-05 23:54:45

这对您来说效果如何?

update papers a
   set my_count = (select count(*) 
                     from auth2paper2loc b
                    where b.paper_id = a.id);

How does this perform for you?

update papers a
   set my_count = (select count(*) 
                     from auth2paper2loc b
                    where b.paper_id = a.id);
帅哥哥的热头脑 2024-11-05 23:54:45

使用:

UPDATE PAPERS
   SET my_count = (SELECT COUNT(b.paper_id)
                     FROM AUTH2PAPERLOC b
                    WHERE b.paper_id = PAPERS.id)

...或者:

   UPDATE PAPERS
LEFT JOIN (SELECT b.paper_id,
                  COUNT(b.paper_id) AS numCount
             FROM AUTH2PAPERLOC b
         GROUP BY b.paper_id) x ON x.paper_id = PAPERS.id
      SET my_count = COALESCE(x.numCount, 0)

当 AUTH2PAPERLOC 表中没有 PAPERS.id 的任何实例时,需要使用 COALESCE 将 NULL 转换为零。

Use either:

UPDATE PAPERS
   SET my_count = (SELECT COUNT(b.paper_id)
                     FROM AUTH2PAPERLOC b
                    WHERE b.paper_id = PAPERS.id)

...or:

   UPDATE PAPERS
LEFT JOIN (SELECT b.paper_id,
                  COUNT(b.paper_id) AS numCount
             FROM AUTH2PAPERLOC b
         GROUP BY b.paper_id) x ON x.paper_id = PAPERS.id
      SET my_count = COALESCE(x.numCount, 0)

The COALESCE is necessary to convert the NULL to a zero when there aren't any instances of PAPERS.id in the AUTH2PAPERLOC table.

煮茶煮酒煮时光 2024-11-05 23:54:45
update papers left join 
 (select paper_id, count(*) total from auth2paper2loc group by paper_id) X
 on papers.id = X.paper_id
set papers.my_count = IFNULL(X.total, 0)
update papers left join 
 (select paper_id, count(*) total from auth2paper2loc group by paper_id) X
 on papers.id = X.paper_id
set papers.my_count = IFNULL(X.total, 0)
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文