php - 列出数据库表关系及其类型

Question

我想知道是否有一种方法可以根据外键关系以编程方式列出数据库中的表关系及其类型？

<小时/>

以这些表为例:

CREATE TABLE `a` (
    `id` int NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `b` (
    `id` int NOT NULL,
    `a_id` int NOT NULL,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk.b.a.b` FOREIGN KEY (`a_id`) REFERENCES `a` (`id`)
) ENGINE=InnoDB;

CREATE TABLE `c` (
    `id` int NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `b_c` (
    `b_id` int NOT NULL,
    `c_id` int NOT NULL,
    PRIMARY KEY (`b_id`,`c_id`),
    CONSTRAINT `fk.b_c.b.c` FOREIGN KEY (`c_id`) REFERENCES `c` (`id`),
    CONSTRAINT `fk.b_c.c.b` FOREIGN KEY (`b_id`) REFERENCES `b` (`id`)
) ENGINE=InnoDB;

我们可以通过此查询获取外键关系:

SELECT 
    table_name 'table',
    column_name 'column',
    referenced_table_name 'referenced_table',
    referenced_column_name 'referenced_column'
    FROM
        information_schema.key_column_usage
    WHERE
        referenced_table_name IS NOT NULL
        AND table_schema = 'test';

table   column   referenced_table   referenced_column
b       a_id     a                  id
b_c     c_id     c                  id
b_c     b_id     b                  id

现在...我认为上面的关系信息应该足以推断出存在哪些关系及其类型，但我无法将其转化为算法...回答我原来的问题:我知道有一种方法，但一直没能找到它。 😕

所以，我希望比我更聪明的人要么知道如何做到这一点，要么准备通过脑筋急转弯(在我的例子中是脑力 killer )来解决？ 🤔

<小时/>

基本上，在这种情况下，“答案”应该是有 4 种关系:

• A → B，一对多
• B → A，多对一
• B → C，多对多，通过 b_c
• C → B，多对多，通过 b_c

我需要在 PHP 中执行此操作，但任何类型的可理解的算法/伪代码也希望有很大帮助。 👍🏼

Answer 1

我认为一对多很简单，你已经明白了。迭代此表

row table   column   referenced_table   referenced_column
1   b       a_id     a                  id
2   b_c     c_id     c                  id
3   b_c     b_id     b                  id

你就拥有了所有一对多

• 第 1 行:A → B，一对多
• 第 2 行:C → B_C，一对多
• 第 3 行:B → B_C，一对多

所有多对一都是这些的反转

• 第 1 行:B → A，多对一(与上一个相反)
• 第 2 行:B_C → C，多对一(与上一个相反)
• 第 3 行:B_C → B，多对一(与上一个相反)

困难在于找到多对多，理论上我认为你应该使用图表并导航它来查找所有多对多关系，但我可以想到一个可以使用您拥有的表进行的技巧:按列表进行分组，如下所示

SELECT
    table as through,
    GROUP_CONCAT(referenced_table SEPARATOR ',') as tables
FROM (
    SELECT 
        table_name 'table',
        referenced_table_name 'referenced_table',
    FROM
        information_schema.key_column_usage
    WHERE
        referenced_table_name IS NOT NULL
        AND table_schema = 'test';
    ) as yourQuery
GROUP BY table
HAVING count(referenced_table) > 1;

你应该以这样的方式结束

row   through   tables
1     b_c       c,b

这将为您提供第一列中的直通表的名称，以及“表”行上的多个多对多表的名称(可以是 2 个或更多)