如何计算引用MySql中的外键的行数? [英] How to count amount of rows referring to a foreign key in MySql?

问题描述

比方说一张桌子

CREATE TABLE `testdb`.`test` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

还有其他表可能具有引用test.id列的外键.有趣的是，我不知道哪个表具有这样的外键以及该表具有多少行.

there are other tables may have foreign key referring to test.id column. The interesting thing is I don't know what table has such a foreign key and how many rows the table has.

现在，我想计算散布在具有test.id外键的表中的行数.有可能吗?

now I want to calculate amount of rows dispersing in tables that have foreign key to test.id. Is it possible?

我认为这在理论上是可能的，否则MySql无法执行ON DELETE CASCADE，DELETE SET NULL ...

I think it's theoretically possible, otherwise MySql cannot do operations like ON DELETE CASCADE, DELETE SET NULL ...

推荐答案

显示具有行计数的所有引用表

SELECT rc.table_name, t.TABLE_ROWS
FROM `REFERENTIAL_CONSTRAINTS` rc
INNER JOIN `TABLES` t ON t.TABLE_NAME = rc.TABLE_NAME
WHERE rc.REFERENCED_TABLE_NAME = "test"

显示所有引用表的行数之和

SELECT SUM(t.TABLE_ROWS) AS allReferencedTablesRowCount
FROM `REFERENTIAL_CONSTRAINTS` rc
INNER JOIN `TABLES` t ON t.TABLE_NAME = rc.TABLE_NAME
WHERE rc.REFERENCED_TABLE_NAME = "test"

这篇关于如何计算引用MySql中的外键的行数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！