jayd_fez1701 wrote:
> When I do this, does MySql create two different,
> though identical, behind-the-scenes lookup tables
> with the values "black" and "beige"? Or does it
> recognize that the two enums are identical and
> create only one table for the enum?
Neither. It doesn't create a table at all. MySQL stores an enumerated list of those values in memory for each of the fields, but actually stores to disk the value as an integer; the actual size of the integer depends on the number of elements in the ENUM or SET. So, there is no "table" actually created. Instead, the ENUM is a structure attached in memory to the definition of the table, and when values are inserted into and retrieved from the ENUM field, MySQL uses the enum structure to "map" the integer values to the string values and vice versa.
So, if you're worried about efficiency, don't be. MySQL handles ENUM and SET types quite capably. There are other reasons, most noticeably that of flexibility, to choose to buid your own tables, but efficiency really isn't one of them...
To verify that the value of an ENUM field is actually numeric, simply do the following:
SELECT enum_field+0 FROM my_table;
Jay Pipes
Community Relations Manager, North America, MySQL Inc.
Got Cluster?
http://www.mysql.com/cluster
Personal:
http://jpipes.com