MySQL:通过迭代并与另一行连接来更新表中的行
我有一个表 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 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
这对您来说效果如何?
How does this perform for you?
使用:
...或者:
当 AUTH2PAPERLOC 表中没有 PAPERS.id 的任何实例时,需要使用
COALESCE
将 NULL 转换为零。Use either:
...or:
The
COALESCE
is necessary to convert the NULL to a zero when there aren't any instances of PAPERS.id in the AUTH2PAPERLOC table.