Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using a table to provide enum values in MySQL?

Is there a way to map one of the the columns contents of a MySQL table to an enum on another table in MySQL? I thought this would be a no brainer, but there doesn't seem to be any info that I can find on the subject.

Any advice or help on this matter would be cool and if it's not possible, does anyone know of an internal reason why it wouldn't be possible?

Best regards everyone :)

Gary

like image 755
Gary Paluk Avatar asked Jan 21 '10 04:01

Gary Paluk


People also ask

How can I get ENUM possible values in a MySQL database?

If you want to determine all possible values for an ENUM column, use SHOW COLUMNS FROM tbl_name LIKE enum_col and parse the ENUM definition in the Type column of the output.

How do I query ENUM in SQL?

In MySQL one can create an enum as such: USE WorldofWarcraft; CREATE TABLE [users] ( ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, username varchar(255), password varchar(255), mail varchar (255), rank ENUM ('Fresh meat', 'Intern','Janitor','Lieutenant','Supreme being')DEFAULT 'Fresh meat', );

Does MySQL support ENUM?

In MySQL, an ENUM is a string object whose value is chosen from a list of permitted values defined at the time of column creation. The ENUM data type provides the following advantages: Compact data storage. MySQL ENUM uses numeric indexes (1, 2, 3, …) to represents string values.

What is correct usage of ENUM in MySQL syntax?

The ENUM data type in MySQL is a string object. It allows us to limit the value chosen from a list of permitted values in the column specification at the time of table creation. It is short for enumeration, which means that each column may have one of the specified possible values.


1 Answers

The enum type is handy as a one-off, but it doesn't scale well to multiple tables and isn't standard SQL either. Best thing to do here is to use normal tables and relations:

  1. Define a new table to hold the list of possible values; let's call it Master1
  2. In the other two tables (let's call them Table1 and Table2), don't make the field an enum; just make it a normal field with a foreign key relation to Master1.

The foreign key relation will do the job of restricting to a list of possible values; and because foreign keys and relations are absolutely standard SQL, this approach will have other benefits - for example reporting tools can recognise the foreign key and understand how to use the related data.

like image 59
Vince Bowdren Avatar answered Oct 29 '22 05:10

Vince Bowdren