MYSQL - 一列引用多个表

Question

表中的单个列可以引用多个表吗？

Answer 1

一个很晚的答案，但对于任何想知道和谷歌的人来说。

是的，这是可以做到的，但这不是好的做法，即使它很简单，如果你不是很清楚自己在做什么，它可能会在你的脸上爆炸。不推荐。

但是，我可以看到用途。例如，您有一个包含数百万条记录的大型表，并且您希望在特殊情况下链接到未知或多个表（在这种情况下最好是多个表）。对于多个表，如果要为所有表创建一个外键，那将是数据库大小的巨大膨胀。例如，在技术支持系统中，您可能希望链接到可能存在问题的表中的记录，这可能是（几乎）数据库中的所有表，包括将来的表。

当然，您需要两个字段来链接：外键字段和它链接到的表的名称。让我们称他们为foreignId和linkedTable

linkedTable可以是枚举或字符串，最好是枚举（空间较小），但这只有在要链接到的不同表是固定的时才有可能。

让我们举一个非常愚蠢的例子。您有一个巨大的用户表users其中某些用户可以将一组个人数据集添加到他们的配置文件中。这可以是关于爱好、宠物、他们从事的运动或他们的职业。现在，此信息在所有四种情况下都不同。（4个可能的表格实际上不足以证明这种结构的合理性）

现在假设linkedTable是一个枚举，其可能的值为 pets、hobbies、sports 和 professions，它们是四个不同结构的表的名称。假设id是所有四个中的pkey。

例如，您按如下方式加入：

SELECT * FROM users 
    LEFT JOIN  pets        ON linkedTable = 'pets'        AND foreignId = pets.id
    LEFT JOIN  hobbies     ON linkedTable = 'hobbies'     AND foreignId = hobbies.id
    LEFT JOIN  sports      ON linkedTable = 'sports'      AND foreignId = sports.id
    LEFT JOIN  professions ON linkedTable = 'professions' AND foreignId = professions.id

这只是为了开一个基本的玩笑。由于您可能只在极少数情况下需要链接，因此当您遍历用户（无连接）时，您更有可能使用您的编程语言（如 PHP）进行查找。

想试试吗？您可以自己尝试构建此测试数据库（确保使用测试数据库）：

CREATE TABLE IF NOT EXISTS `users` (
    `id` INT NOT NULL AUTO_INCREMENT , 
    `name` VARCHAR(100) NOT NULL , 
    `linkedTable` ENUM('pets','hobbies','sports','professions') NULL DEFAULT NULL , 
    `foreignId` INT NULL DEFAULT NULL , 
  PRIMARY KEY (`id`), INDEX (`linkedTable`)
) ;
CREATE TABLE  IF NOT EXISTS `pets` ( 
    `id` INT NOT NULL AUTO_INCREMENT , 
    `animalTypeId` INT NOT NULL , 
    `name` VARCHAR(100) NOT NULL , 
    `colorId` INT NOT NULL , 
  PRIMARY KEY (`id`), INDEX (`animalTypeId`), INDEX (`colorId`)
) ;
CREATE TABLE  IF NOT EXISTS `hobbies` ( 
    `id` INT NOT NULL AUTO_INCREMENT , 
    `hobbyTypeId` INT NOT NULL , 
    `hoursPerWeekSpend` INT NOT NULL , 
    `websiteUrl` VARCHAR(300) NULL , 
  PRIMARY KEY (`id`), INDEX (`hobbyTypeId`)
) ;
CREATE TABLE  IF NOT EXISTS `sports` ( 
    `id` INT NOT NULL AUTO_INCREMENT , 
    `sportTypeId` INT NOT NULL , 
    `hoursPerWeekSpend` INT NOT NULL , 
    `nameClub` VARCHAR(100) NULL , 
    `professional` TINYINT NOT NULL DEFAULT 0, 
  PRIMARY KEY (`id`), INDEX (`sportTypeId`)
) ;
CREATE TABLE  IF NOT EXISTS `professions` ( 
    `id` INT NOT NULL AUTO_INCREMENT , 
    `professionId` INT NOT NULL , 
    `hoursPerWeek` INT NOT NULL , 
    `nameCompany` VARCHAR(100) NULL , 
    `jobDescription` VARCHAR(400) NULL, 
  PRIMARY KEY (`id`), INDEX (`professionId`)
) ;

INSERT INTO `users` (`id`, `name`, `linkedTable`, `foreignId`) 
   VALUES 
   (NULL, 'Hank', 'pets', '1'), 
   (NULL, 'Peter', 'hobbies', '2'), 
   (NULL, 'Muhammed', 'professions', '1'), 
   (NULL, 'Clarice', NULL, NULL), 
   (NULL, 'Miryam', 'professions', '2'), 
   (NULL, 'Ming-Lee', 'hobbies', '1'), 
   (NULL, 'Drakan', NULL, NULL), 
   (NULL, 'Gertrude', 'sports', '2'), 
   (NULL, 'Mbase', NULL, NULL);

INSERT INTO `pets` (`id`, `animalTypeId`, `name`, `colorId`) 
VALUES (NULL, '1', 'Mimi', '3'), (NULL, '2', 'Tiger', '8');
INSERT INTO `hobbies` (`id`, `hobbyTypeId`, `hoursPerWeekSpend`, `websiteUrl`) 
VALUES (NULL, '123', '21', NULL), (NULL, '2', '1', 'http://www.freesoup.org');
INSERT INTO `sports` (`id`, `sportTypeId`, `hoursPerWeekSpend`, `nameClub`, `professional`) 
VALUES (NULL, '2', '3', 'Racket to Racket', '0'), (NULL, '12', '34', NULL, '1');
INSERT INTO `professions` (`id`, `professionId`, `hoursPerWeek`, `nameCompany`, `jobDescription`) 
VALUES (NULL, '275', '40', 'Ben & Jerry's', 'Ice cream designer'), (NULL, '21', '24', 'City of Dublin', 'Garbage collector');

然后运行第一个查询。

有趣的讨论笔记：您将如何对此进行索引？

Answer 2

如果您的意思是"一个表中的列是否可以用作多个表中的外键"，那么答案是肯定的。这就是关系数据库的重点

Answer 3

是的，你可以这样做。 以下是有关如何执行此操作的示例：

下面是具有将由多个表引用的列（国家/地区 ID）的表：

CREATE TABLE DLAccountingSystem.tblCountry
(
    CountryID       INT             AUTO_INCREMENT NOT NULL PRIMARY KEY,
    CountryName     VARCHAR(128)    NOT NULL,
    LastEditUser    VARCHAR(128)    NOT NULL,
    LastEditDate    DATETIME        NOT NULL
) ENGINE=INNODB;

以下是将引用列（国家/地区 ID）的表：

CREATE TABLE DLAccountingSystem.tblCity
(
    CityID          INT             AUTO_INCREMENT NOT NULL PRIMARY KEY,
    CountryID       INT             NOT NULL,
    CityName        VARCHAR(128)    NOT NULL,
    LastEditUser    VARCHAR(128)    NOT NULL,
    LastEditDate    DATETIME        NOT NULL
) ENGINE=INNODB;
CREATE TABLE DLAccountingSystem.tblProvince
(
    ProvinceID      INT             AUTO_INCREMENT NOT NULL PRIMARY KEY,
    CountryID       INT             NOT NULL,
    ProvinceName    VARCHAR(128)    NOT NULL,
    LastEditUser    VARCHAR(128)    NOT NULL,
    LastEditDate    DATETIME        NOT NULL
) ENGINE=INNODB;

下面介绍如何创建对列的引用：

 ALTER TABLE DLAccountingSystem.tblCity
 ADD CONSTRAINT fk_tblcitycountryid FOREIGN KEY CountryID (CountryID)
 REFERENCES DLAccountingSystem.tblCountry (CountryID)
 ON DELETE NO ACTION
 ON UPDATE NO ACTION
 ALTER TABLE DLAccountingSystem.tblProvince
 ADD CONSTRAINT fk_tblprovincecountryid FOREIGN KEY CountryID (CountryID)
 REFERENCES DLAccountingSystem.tblCountry (CountryID)
 ON DELETE NO ACTION
 ON UPDATE NO ACTION

这是一个表，

其中包含引用来自（国家/地区ID，省ID，城市ID）多个表的不同列的列（我个人不建议这种表结构方式。只是我的意见没有冒犯;)）

CREATE TABLE DLAccountingSystem.tblPersons
(
    PersonID       INT          AUTO_INCREMENT NOT NULL PRIMARY KEY,
    PlaceID        INT          NOT NULL,
    PlaceTypeID    INT          NOT NULL, -- this property refers to what table are you referencing.
 //Other properties here.....
) ENGINE=INNODB;

您还应该有一个包含 PlaceType 的查找表：

CREATE TABLE DLAccountingSystem.tblPlaceType
(
    PlaceTypeID       INT          AUTO_INCREMENT NOT NULL PRIMARY KEY,
    PlaceTypeName        INT          NOT NULL
 //Other properties here.....
) ENGINE=INNODB;

以下是获取它的方法：

SELECT p1.PersonID,
       tcity.CityName,
       tprov.ProvinceName,
       tcoun.CountryName
FROM DLAccountingSystem.tblPersons p1 
LEFT JOIN (SELECT p2.PersonID, p2.PlaceTypeID, c.CityName FROM DLAccountingSystem.tblPersons p2 INNER JOIN DLAccountingSystem.tblCity c ON p2.ObjectID = c.CityID WHERE PlaceTypeID = @CityTypeID) tcity ON p1.PersonID = tcity.PersonID
LEFT JOIN (SELECT p2.PersonID, p2.PlaceTypeID, c.ProvinceName FROM DLAccountingSystem.tblPersons p2 INNER JOIN DLAccountingSystem.tblProvince c ON p2.ObjectID = c.ProvinceID WHERE PlaceTypeID = @ProvinceTypeID) tprov ON p1.PersonID = tprov.PersonID
LEFT JOIN (SELECT p2.PersonID, p2.PlaceTypeID, c.CountryName FROM DLAccountingSystem.tblPersons p2 INNER JOIN DLAccountingSystem.tblCountry c ON p2.ObjectID = c.CountryID WHERE PlaceTypeID = @CountryTypeID) tcoun ON p1.PersonID = tcoun.PersonID

您可以从其他表中进行选择，例如

Answer 4

同一列或一组列可以充当一个或多个外键的父和/或子终结点。