mysql - 如何以编程方式确定 MySQL 关系类型 (1 :1, 1 :n, n :m) between tables?

Question

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

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

我在 stackoverflow 中发现了这个问题，但它无法解决问题: how-to-determine-cardinality-of-foreign-key-using-mysql

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

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

非识别 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 中具有此引用，但我想在我的应用程序上复制此行为。

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

Answer 1

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

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

“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**
}

希望这能帮助其他人解决问题，请随时添加任何建议或替代方法，谢谢大家。