Diamond Notes

Been a bit behind on tip of the days if you haven’t noticed. Sorry about that . It’s getting crazy trying to get out the next issue of the magazine, training our new jr dba (who suggested this tip..thanks David!!) and trying to get things in line for both a short vacation and then the Users Conference next month.

Excuses over, here is the tip. Today’s tip is dealing with the ENUM (enumeration) data type. ENUM is very similar to the SET data type. It can hold a list of allowable values. The values in the list are called members. An ENUM list can hold up to 65,535 members. Try typing that many in!! As an example of a very simple table:

CREATE TABLE enums (

userid INT NOT NULL AUTO_INCREMENT

value ENUM( ‘FALSE’, ‘TRUE’ )

PRIMARY KEY (userid)

);

Here is what is important to understand. Internally to the MySQL database enums are stored as integers beginning with a value of one and going to N where N is the number of members in the list. In our example above ‘FALSE’ would be represented internally as a ‘1′ and ‘TRUE’ is represented as a ‘2′.

There can be subtle “problems” (errors) when you don’t take into account the string nature of what you are inserting into a ENUM type and its internal integer representation.

If, instead of the above table example, I used the following table:

CREATE TABLE enums (

userid INT NOT NULL AUTO_INCREMENT

value ENUM(’0′,’1′)

PRIMARY KEY (userid)

);

Here I stored the values as ‘0′ for false and  ‘1′ for true which is a fairly common programming practice. I would argue that you shouldn’t, but let us assume you did. Internally these strings of ‘0′ and ‘1′ are stored as integers of ‘1′ and ‘2′.  Confused yet?

Do you see the problem?

What if I do this:

INSERT INTO enums (value) VALUES (’1′);

I get the expected value returned when I SELECT:

userid = 1

value = 1

ie we just set userid of one to “true”.

Now we insert this way:

INSERT INTO enums (value) VALUES (1);

meaning to insert a value of TRUE into the value. However, that isn’t what happened. The 1 is an integer, so MySQL uses the first value of the list which is a ‘0′ .. ie false

While the example is a bit contrived, it goes to show you that you have to be careful how you insert when using enums.