to get numeric values from an ENUM column, can be using
mysql> SELECT enum_col+0 FROM tbl_name;
But in my mysql5.5 console,this query return:
1.0000000000000000000000000000000
I want to get the a integer number 1
I know I can use:
SELECT CONVERT(enum_col,UNSIGNED) FROM tbl_name;
or
SELECT CAST(enum_col AS UNSIGNED) FROM tbl_name;
I want to know why enum_col+0 return a float,
and any other way to get numeric values from an ENUM column?
Have a look at this article - Type Conversion in Expression Evaluation; it describes how MySQL does conversion between different types; there are some rules, in your case you get result as a floating-point value.
...and try another variant:
SELECT TRIM(TRAILING ')' FROM TRIM(LEADING '(' FROM TRIM(LEADING 'enum' FROM column_type))) INTO @enum
FROM information_schema.`COLUMNS` WHERE TABLE_SCHEMA = 'db_name' AND TABLE_NAME = 'tbl_name' AND COLUMN_NAME = 'enum_col';
SELECT FIND_IN_SET(CONCAT('''', enum_col, ''''), @enum) FROM tbl_name
or this one:
SELECT REPLACE(TRIM(TRAILING ')' FROM TRIM(LEADING '(' FROM TRIM(LEADING 'enum' FROM column_type))), '''', '') INTO @enum
FROM information_schema.`COLUMNS` WHERE TABLE_SCHEMA = 'db_name' AND TABLE_NAME = 'tbl_name' AND COLUMN_NAME = 'enum_col';
SELECT FIND_IN_SET(enum_col, @enum) FROM tbl_name
