Mysql：多个表的一个枚举

Question

Let's say I have two tables:

mice
id (int) petname (varchar) gender (enum (male, female))
1        spot            male
2        rice           female

men
id (int) name (varchar) gender (enum (male, female))
1        bob            male
2        jane           female
3        steve          male

The gender enum exists twice. Once for each table. So if wanted to cater to say the sjw audience, I would need to expand the gender enum with new values, which would mean that I would have to manually edit the enums in several tables. This is suboptimal.

Is there any way to define the enum values once and use them in several tables?

Or perhaps to create a new table named genders and put the genders there, and then during inserts and updates into mice and men, it would only allow the gender column to contain values form the gender table. This check however needs to happen automatically (like with enum column type), so I don't have to execute additional queries first to check if the used gender exists in the gender table, before each update or insert query I want to run, which inserts a value into a gender column in mice or men.

Can this be done with mysql?

Answer 1

You can forget about SQL's enum. Instead, just use VARCHAR(50), etc, and use const in your PHP code to define the enum values.

I seldom use SQL's enum, in most cases, its benefit is less than its complexity。