MYSQL-引用多个表的一列 [英] MYSQL - One Column Referenced to Multiple Table
问题描述
一个表中的单个列可以引用多个表吗?
答案很晚，但是对于想知道&的人来说，糊涂.
是是可以做到的，但这是否的好习惯，尽管它很简单，但它可能会炸毁你的脸如果您不太了解自己在做什么.不推荐.
但是，我可以看到用途.例如，您有一个包含数百万条记录的大表，并且在特殊情况下，您希望链接到未知或多个表(在这种情况下，最好是很多).对于多个表，如果要为所有表创建外键，那么数据库大小将是巨大的负担.例如，在技术支持系统中，可能有一个未知的表，您想链接到该表以在可能存在问题的表中进行记录，并且该表可能(几乎)是数据库中的所有表，包括将来的表.
当然，您需要两个字段进行链接:外键字段和要链接到的表的名称.我们称它们为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');
然后运行第一个查询.
有趣的讨论笔记:您如何对此进行索引?
Can a single column in a table can be referenced to multiple tables?
A very late answer, but for whoever is wondering & googeling.
YES this can be done, but it is NOT good practice and even though it is quite simple, it will probably blow up in your face if you're not very aware of what you are doing. Not recommended.
However, I can see uses. For instance, you have a large table of millions of records, and you want in exceptional cases link to unknown or multiple tables (in which case it better be many). With multiple tables, if you would make a foreign key for all of them, that would be a huge bloat in your database size. An unknown table would be possible for instance in a technical support system, where you want to link to record in a table where there might be a problem, and this could be (almost) all tables in the database, including future ones.
Of course you will need two fields to link with: a foreign key field and the name of the table it is linking to. Lets call them foreignId and linkedTable
linkedTable could be an enum or a string, preferrably enum (less space), but that's only possible if the different tables you want to link to, are fixed.
Let's give an extremely silly example. You have an enormous user table users of which some user can add exactly one personal set of data to their profile. This can be about a hobby, a pet, a sport they practice or their profession. Now this info is different in all four cases. (4 possible tables is in reality not enough to justify this structure)
Now let's say linkedTable is an enum with possible values pets, hobbies, sports and professions, which are the names of four differently structured tables. Let's say id is the pkey in all four of them.
You join for instance as follows:
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
This is just to give a basic jest. Since you probably only need the link in rare cases, you will more likely do the lookup in your programming language, like PHP, when you loop through the users (without join).
Want to try out? You can try it yourself with building this test database (make sure you use a test database):
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');
Then run the first query.
Fun note for discussion: How would you index this?
这篇关于MYSQL-引用多个表的一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
