How to retrieve an enum value in MySQL?
If you retrieve an ENUM value in a numeric context, the column value’s index is returned. For example, you can retrieve numeric values from an ENUM column like this: Functions such as SUM () or AVG () that expect a numeric argument cast the argument to a number if necessary. For ENUM values, the index number is used in the calculation.
When to use an enum column instead of an index?
If the numeric value is quoted, it is still interpreted as an index if there is no matching string in the list of enumeration values. For these reasons, it is not advisable to define an ENUM column with enumeration values that look like numbers, because this can easily become confusing.
What is the index of the empty string in MySQL?
The index value of the empty string error value is 0. This means that you can use the following SELECT statement to find rows into which invalid ENUM values were assigned: The index of the NULL value is NULL . The term “index” here refers to a position within the list of enumeration values.
How are enumeration values interpreted in MySQL?
For example, the following column has enumeration members with string values of ‘0’, ‘1’, and ‘2’, but numeric index values of 1, 2, and 3 : If you store 2, it is interpreted as an index value, and becomes ‘1’ (the value with index 2). If you store ‘2’, it matches an enumeration value, so it is stored as ‘2’.
Why is MySQL’s enum data type is evil?
This is one of Chris Komlenic’s 8 Reasons Why MySQL’s ENUM Data Type Is Evil: 4. Getting a list of distinct ENUM members is a pain. A very common need is to populate a select-box or drop down list with possible values from the database.
How many distinct elements can an enum column have?
An ENUM column can have a maximum of 65,535 distinct elements. If you retrieve an ENUM value in a numeric context, the column value’s index is returned. For example, you can retrieve numeric values from an ENUM column like this: Functions such as SUM () or AVG () that expect a numeric argument cast the argument to a number if necessary.
What happens if you insert an invalid enum value?
In the non-strict SQL mode, if you insert an invalid value into an ENUM column, MySQL will use an empty string ” with the numeric index 0 for inserting. In case the strict SQL mode is enabled, trying to insert an invalid ENUM value will result in an error. Note that an ENUM column can accept NULL values if it is defined as a null-able column.