MySQL Enum's always contain '' (empty string) in possibilities

Question

Hi,

I'm trying to create a simple 'yes'/'maybe'/'no' Enum in MySQL with PhpMyAdmin I set NULL to No, and 'maybe' as the default value

I am expecting an error when executing something like "SET EnumCol=''", because '' (an empty string) should not be a valid value. But the query gets executed and the value gets set to '' - which means I'm forced to double check for this unwanted and illegal value whenever I read from the database!

Is this a bug in MySQL or PhpMyAdmin? Does anyone know a way of disabling this behavior?

Thanks.

Answer 1

Empty string is error indicator of invalid values in ENUM. From mysql ENUM type manual:

If you insert an invalid value into an ENUM (that is, a string not present in the list of allowed values), the empty string is inserted instead as a special error value. This string can be distinguished from a “normal” empty string by the fact that this string has the numerical value 0. More about this later.

To disable this behaviour:

If strict SQL mode is enabled, attempts to insert invalid ENUM values result in an error.

To enable strict mode see Server SQL Modes.

Answer 2

ENUM's are a pain in the butt. unless you also need to set the value by a number, i would stay away from them.

instead, use a varchar column with a foreign key to a lookup table to restrict the values. that will make it impossible to insert a bad value.