Menu
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.
905
In the above syntax, if you compare empty string( ' ') to empty string( ' '), the result will always be NULL.
ENUM values are sorted based on their index numbers, which depend on the order in which the enumeration members were listed in the column specification. For example, 'b' sorts before 'a' for ENUM('b', 'a') . The empty string sorts before nonempty strings, and NULL values sort before all other enumeration values.
DEFAULT: When a value is not specified in the column, the ENUM data type inserts the default value. In other words, if the INSERT statement does not provide a value for this column, then the default value will be inserted.
By default, when an enum is a part of an entity, JPA maps its values into numbers using the ordinal() method. What it means is that without customizations JPA stores enum value as numbers. These numbers are associated with the order in which you define values in the enum.
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.
183
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
