Some Interesting features of ENUM data type in MySQL

Some Interesting features of ENUM data type in MySQL

n MySQL, ENUM is a datatype that allow us to define some list of permitted values during creation of table. This will help us to restrict values that are going to be entered against a column. This is something like white listing(list of permitted values) that we have learned in security section for web application. Around 65,535 value can be defined against a  ENUM type field. ENUM is a string object(only string values are allowed), if we want to include some numbers then that should be in quotation mark. For example: CREATE TABLE table_name( enum_col ENUM(‘value_1′, value_2’) ); If we will enter invalid value for this ENUM column during INSERT record, then it will display a warning message and set empty string for that record. -> Each ENUM value is having an index starting from 1(one) and every ENUM column is having an additional ENUM value, which is empty string with its index value 0(zero). So, we can search for all invalid entries against the ENUM column in a table using its index value: SELECT * FROM table_name WHERE enum_col = 0; ->Also, if we want to check the index value of ENUM column against each record in table then below mentioned SQL query will help to do the same: SELECT (enum_col + 0) AS enum_index FROM table_name; ->For sorting the ENUM type column based on its value rather than its index then we need to convert explicitly covert its value to CHARACTER as it implicitly taking its index value. For example: SELECT * FROM table_name ORDER BY CAST(enum_col AS CHAR); ->For calculate the average or summation of list of ENUM column values(numbers) then we have to first convert its type to CHARACTER then to INTEGER otherwise that will calculated its result based on its index value. For example: SELECT AVG(CAST(CAST(enum_col AS CHAR) AS UNSIGNED INTEGER)) AS enum_avg FROM table_name; I hope the above informations regarding ENUM will give you some brief idea.