如何以编程方式确定表之间的 MySQL 关系类型 (1:1, 1:n, n:m)? [英] How to determine programmatically MySQL relationship type (1:1, 1:n, n:m) between tables?

问题描述

我正在尝试查询 MySQL 服务器以确定有关数据库的信息，以便构建一些代码.

I'm trying to query a MySQL server to determine information about a database in order to scaffold some code.

我已经非常成功地使用 Java JDBC 和 INFORMATION_SCHEMA 表，但问题是我需要确定表关系是否是 OneToOne、OneToMany 或 ManyToMany.我找不到实现这一目标的好方法，如果有人能帮我一点，如果可能的解决方案不是 MySQL 特定且可靠的解决方案，我会很高兴，这样它就可以帮助其他人.

I've been quite successful using Java JDBC and INFORMATION_SCHEMA table for this but the problem is that I need to determine if a table relation is OneToOne, OneToMany or ManyToMany. I can't find a good way to achieve this and I would love if someone could help me a bit and if its possible with a solution that is not MySQL specific and solid so it could help others.

我在stackoverflow中发现了这个问题，但它不能解决问题:how-to-determine-cardinality-of-foreign-key-使用-mysql

I found this question in stackoverflow but it wont solve the problem: how-to-determine-cardinality-of-foreign-key-using-mysql

编辑(更多)为了进一步解释我的问题，我将添加更多信息.目前我正在使用 MySQL 与 InnoDB 和 MySQL Workbench 来创建 EER 图表并生成 SQL 以创建数据库.

EDIT (More) To further explain my problem I will add a bit more information. Currently I'm using MySQL with InnoDB and MySQL Workbench to create the EER Diagram and generate the SQL to create the database.

我试图在我的 Java 应用程序中对两个现有表之间的关系进行逆向工程，以确定一个表是 OneToOne、OneToMany 还是 ManyToMany.问题是，当我在 MySQL Workbench 中设计模型并在两个表之间创建关系时，即使它们的 SQL 输出相同，我也看不到非识别 1:1 和非识别 1:N 之间的任何区别.

I'm trying to reverse engineer in my Java application the relation between two existing tables to determine if a table is OneToOne, OneToMany or ManyToMany. The problem is that when I design the model in MySQL Workbench and I create a relationship between two tables I don't see any difference between Non-Identifying 1:1 and Non-Identifying 1:N even their SQL output are the same.

非识别 1:1

CREATE TABLE IF NOT EXISTS `TestDB`.`table1` (
  `var1` BIT(1) NOT NULL,
  `var2` BIT(8) NOT NULL,
  `var3` VARCHAR(45) NULL DEFAULT NULL,
  `var4` INT(11) NOT NULL,
  `table2_var1` INT(11) NOT NULL,
  PRIMARY KEY (`var1`, `var2`),
  INDEX `fk_table1_table2_idx` (`table2_var1` ASC),
  CONSTRAINT `fk_table1_table2`
    FOREIGN KEY (`table2_var1`)
    REFERENCES `TestDB`.`table2` (`var1`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci

非识别 1:n

CREATE TABLE IF NOT EXISTS `TestDB`.`table1` (
  `var1` BIT(1) NOT NULL,
  `var2` BIT(8) NOT NULL,
  `var3` VARCHAR(45) NULL DEFAULT NULL,
  `var4` INT(11) NOT NULL,
  `table2_var1` INT(11) NOT NULL,
  PRIMARY KEY (`var1`, `var2`),
  INDEX `fk_table1_table2_idx` (`table2_var1` ASC),
  CONSTRAINT `fk_table1_table2`
    FOREIGN KEY (`table2_var1`)
    REFERENCES `TestDB`.`table2` (`var1`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci

当我使用 MySQL Workbench 反转数据库以查看它是否可以猜测它是 1:1 还是 1:n 时，令人惊奇的部分出现了，它是居然能猜到，图中有正确的关系箭头！！！也许它将引用存储为唯一的某个地方，或者 InnoDB 在它自己的供应商特定 INFORMATION_SCHEMA 上有这个，但我想在我的应用程序上复制这种行为.

The amazing part comes when I do reverse the database using MySQL Workbench to see if it can guess if it was a 1:1 or a 1:n, it's actually able to guess it, the diagram has the correct relationship arrows!!! Perhaps it stores the reference as unique somewhere or InnoDB has this on its own vendor specific INFORMATION_SCHEMA but I would like to replicate this behaviour on my application.

关于如何实现这一目标的任何想法?

Any ideas of how could I achieve this?

推荐答案

经过进一步研究，我发现尽管以某种方式 MySQL Workbench 能够对 1:1 和 1:n 进行逆向工程 关系即使是引用外键的属性不是 PK 或 UQ 的非标识关系，这也可能对供应商特定 (InnoDB) 属性执行.

After further research I found that although somehow MySQL Workbench is able to reverse engineer the 1:1 and 1:n relation even when is a non-identifying relationship where the attribute that references the foreign key isn't PK or UQ, this might be do to vendor specific (InnoDB) properties.

ALL 其他测试的 SQL 逆向工程工具显示非识别关系为 OneToMany，即使它们在 MySQL WorkBench 中设计为 OneToOne 非识别.假设我执行了一个 JOIN 查询来检索必要的信息来区分 1:1 和 1:n所以 SQL 如下所示:

ALL other SQL reverse engineer tools tested showed non-identifying relationships as OneToMany even thou they where designed in MySQL WorkBench as OneToOne non-identifying. Assuming this I preformed a JOIN query to retrieve the necessary information to distinguish 1:1 from 1:n So the SQL goes like the following:

table1"的示例

select INFORMATION_SCHEMA.COLUMNS.COLUMN_KEY, INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, INFORMATION_SCHEMA.COLUMNS.TABLE_NAME from INFORMATION_SCHEMA.COLUMNS
join INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
on INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME=INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME
where INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME='table1' 
and referenced_table_name is not null

终于……

伪代码

if (COLUMN_KEY == "PRI" || COLUMN_KEY == "UNI") { 
    //then you can assume is **OneToOne**
} else {
    //then you can assume is **OneToMany**
}

希望这对其他人有帮助，请随时添加任何建议或替代方法，谢谢.

Hope this helps others with the struggle, feel free to add any suggestions or alternative ways of doing this, thanks all.

这篇关于如何以编程方式确定表之间的 MySQL 关系类型 (1:1, 1:n, n:m)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！