在另一个表中引用MySQL ENUM [英] Referring MySQL ENUM in another table

问题描述

我有一个表SkillLevel创建为

I have a table SkillLevel created as

CREATE TABLE `sklllevel` (
  `Name` varchar(20) NOT NULL,
  `level` enum('No Experience','Beginner','Expert','Advisor') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

带有值

INSERT INTO test.sklllevel (name,level) values ('No Experience','No Experience'),('Beginner','Beginner'),('Expert','Expert'),('Advisor','Advisor');

我想在另一个表中以testSkill.tkSkill引用SkillLevel.Level:

I want to refer SkillLevel.Level with testSkill.tkSkill in another table created as:

CREATE TABLE `testskill` (
  `pkid` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `tkSkill` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`pkid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

我是否需要tkSkill作为ENUM并具有相同的一组值才能设置外键?最佳做法是什么?

DO I need to have tkSkill as ENUM with same set of Values to set a foreign key? What is the best practise here?

推荐答案

简短答案:枚举以数字形式存储，因此从技术上讲，您可以将它们与tkSkill一起作为tinyint加入.

Short answer: enums are stored as a number, so technically, you could join them with tkSkill as a tinyint.

要将其用作外键，您确实确实需要将tkSkill和level都设为相同的枚举-但是您需要将level用作unique列才能成为外键，因此，在其上添加unique(准确地说:对于InnoDB，如果您手动创建索引，则可以具有非唯一的外键，但是非唯一的外键通常是个坏主意).但是您应该考虑sklllevel中的密钥应该是什么，因为现在看起来好像您希望Name作为密钥.

To use it as a foreign key, you indeed need to have both tkSkill and level to be the same enum - but you need level to be a unique column to qualify as a foreign key, so add unique to it (to be really precise: for InnoDB, you can have non-unique foreign keys if you manually create the index, but non-unique foreign keys are generally a bad idea). But you should think about what your key in sklllevel should be, since now it looks as if you want Name to be the key.

并且独立于将其用作键，您应该将tkSkill定义为(相同)枚举，以确保如果您想更改枚举(这是一个坏主意！)，并且它们都具有相同的含义.例如增加另一个技能水平；或者，当您直接从表testskill中进行选择而无需加入sklllevel时，如果要读取"(直接理解)该值；或者并且如果您想使用它们的枚举值将值插入tkSkill中(例如，使用专家"而不是3，但您可以同时使用两者)，而无需在sklllevel中查找它们.

And independently from having it as key you should define tkSkill as (the same) enum to make sure they both mean the same if you at one point would like to change the enums (what is a bad idea!) and e.g. add another skilllevel; or if you want to "read" (directly understand) the value when you select directly from the table testskill without the need to join sklllevel; and if you want to insert values into tkSkill by using their enum-namoe (e.g. 'Expert' instead of 3, but you can use both) without looking them up in sklllevel.

更长的答案:最佳实践是:不要使用枚举.根据信仰"，在少数情况下，枚举可能会稍微有用(如果有的话).一种可能是您不想使用引用表来跳过联接以显示整数ID的文本值/说明.在您的设置中，您实际上是在使用参考表，但仍想使用枚举.

Longer answer: Best practice is: don't use enums. Depending on "belief", there is none to only a handful of cases when enums might be slightly useful, if at all. One could be that you don't want to use a reference table to skip a join to display the textvalue/description of an integer-id. In your setup, you are actually using a reference table and still want to use enums.

最接近使用枚举的最佳实践的方法是将tkSkill定义为testskill中的枚举，并且根本没有sklllevel-表(参考表).

The closest you'll get to best practice using enums would be to define tkSkill as enum in testskill and don't have the sklllevel-table (the reference table) at all.

但是，我再次敦促您不要使用枚举.您可以将表定义为

But again, I would urge you not to use enums. You can define your table as

CREATE TABLE `sklllevel` (
  `Id` tinyint(4) primary key,
  `Name` varchar(20) NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

，然后将该ID用作tkSkill的外键.甚至

and then use that id as a foreign key for tkSkill. Or even

CREATE TABLE `sklllevel` (
  `Name` varchar(20) primary key
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

，然后将tkSkill定义为varchar(20)并将其用作外键-尽管它将使用更多空间，但是如果这是您使用枚举的原因，则表中将具有可读"值首先.

and then define tkSkill as varchar(20) and use this as a foreign key - it will use more space, though, but you will have "readable" values in the table if that was the reason for you to use enums in the first place.

在这里您可以找到枚举的一些背景:

Here you can find some background to enums: 8 Reasons Why MySQL's ENUM Data Type Is Evil

这篇关于在另一个表中引用MySQL ENUM的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！