Retrieving MySQL Enumeration Values ​​Using Only SQL

Question

In a web application, I need to populate <select> all possible MySQL enumeration values.

When I execute any of:

  SHOW COLUMNS FROM mytable LIKE 'mycolumn';
 SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS 
 WHERE TABLE_NAME = 'mytable' AND COLUMN_NAME = 'mycolumn';

I always get enum('valueA','valueB','valueC') .

I know that I can parse it using PHP, but is it possible to do this only using SQL? I need something like this:

  + ----------------- +
 |  values ​​|
 + ----------------- +
 |  valueA |
 |  valueB |
 |  valueC |
 + ----------------- +

Answer 1

While I would agree not to use enumerations most of the time, this is possible in a single SQL statement: -

 SELECT DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING(COLUMN_TYPE, 7, LENGTH(COLUMN_TYPE) - 8), "','", 1 + units.i + tens.i * 10) , "','", -1) FROM INFORMATION_SCHEMA.COLUMNS CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) units CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) tens WHERE TABLE_NAME = 'mytable' AND COLUMN_NAME = 'mycolumn' 

This will work for listings with up to 100 possible values.

Answer 2

This is one of Chris Komlenic's 8 reasons MySQL Enum Data Type Is Evil :

4. Getting a list of individual ENUM members is a pain.

Very often it is necessary to fill in a selected or drop-down list with possible values ​​from the database. Like this:

Choose a color:

[ select box ]

If these values ​​are stored in a lookup table called "colors", all you need is: SELECT * FROM colors ..., which can then be parsed to dynamically create a drop-down list. You can add or change colors in the lookup table, and your sexual order forms will automatically update. Tall.

Now consider the evil ENUM: how do you retrieve a list of members? You can query the ENUM column in the table for DISTINCT values, but this will only return the values ​​that are actually used and present in the table, and not all possible values. You can query INFORMATION_SCHEMA and parse them from the query result in a scripting language, but this is unnecessarily complicated. In fact, I don’t know of any elegant, purely SQL method for retrieving the list of members of an ENUM column.

Answer 3

This is the simplest solution.

 $EnumColum = $mysqli->query( $link, "SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'mrb_shipping_carriers' AND COLUMN_NAME = 'uspsServiceType'" ) or die("Error in " . ": " . __FILE__ . ": " . __LINE__); $replace1 = preg_replace("/enum/", '',$EnumColum[0]['COLUMN_TYPE']); $replace2 = preg_replace("/\(/", '',$replace1); $replace3 = preg_replace("/\)/", '',$replace2); $replace4 = preg_replace("/\'/", '',$replace3); $newArray = explode(',',$replace4); foreach($newArray as $value){ echo $value . "\n<br>"; }