Getting the values of a MySQL enum using only SQL

Question

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

When I execute either of:

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

I always end up with enum('valueA','valueB','valueC').

I know I can parse it with PHP but can it be done using SQL only? I need something like this:

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

possible duplicate of displaying mysql enum values in php

Jim
– Jim

2013-06-13 14:43:32 +00:00
Commented Jun 13, 2013 at 14:43 — Jim
– Jim, Commented Jun 13, 2013 at 14:43

eggyal · Accepted Answer · 2013-06-13 14:43:07Z

7

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. Like this:

Select color:

[ select box ]

If these values are stored in a reference table named 'colors', all you need is: SELECT * FROM colors ...which can then be parsed out to dynamically generate the drop down list. You can add or change the colors in the reference table, and your sexy order forms will automatically be updated. Awesome.

Now consider the evil ENUM: how do you extract the member list? You could query the ENUM column in your table for DISTINCT values but that will only return values that are actually used and present in the table, not necessarily all possible values. You can query INFORMATION_SCHEMA and parse them out of the query result with a scripting language, but that's unnecessarily complicated. In fact, I don't know of any elegant, purely SQL way to extract the member list of an ENUM column.

answered Jun 13, 2013 at 14:43

eggyal

127k18 gold badges220 silver badges244 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

gumgl Over a year ago

Ugh, this is what I was afraid of.

Kickstart · Accepted Answer · 2013-06-13 16:00:31Z

4

While I would agree about not using enums most of the time, it 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 enums with up to 100 possible values

answered Jun 13, 2013 at 16:00

Kickstart

21.5k2 gold badges26 silver badges33 bronze badges

6 Comments

eggyal Over a year ago

What if one or more values contain ','? See sqlfiddle.

Kickstart Over a year ago

It would cause problems, although can be dealt with by replacing the embeded ',' (stored as '','') and a few other faffs. Either way it is possible, even if a good reason to avoid using enums.

gumgl Over a year ago

Thank you so much! I knew there had to be a way, although my SQL skills are not developed enough to find it myself. And yes, I totally agree with not using enums, but sometimes you can't change how the current system is built.

Kickstart Over a year ago

It is possible, but I would suggest that if you can you amend your tables to avoid the use of enums. My code above is not likely to be quick and not something I would want to use on any generally used page. Possibly better to use it to extract the values to populate table(s) of the possible values which the system can be modified to use instead.

eggyal Over a year ago

@gumgl: I suppose the more general observation is that, if the possible values are embedded in the database schema (as they are in the definition of an ENUM column), then they may as well be embedded in one's code too. A change of schema invariably requires a change of codebase. If one is reluctant to embed the options into one's code, it indicates that they are expected to change / intended to be dynamic - in which case ENUM is entirely the wrong datatype to use in the first place.

|

uthark · Accepted Answer · 2014-04-15 22:58:24Z

0

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>";
}

edited Apr 15, 2014 at 22:58

uthark

5,3632 gold badges47 silver badges60 bronze badges

answered Apr 15, 2014 at 22:36

thewordsmith

11 bronze badge

Collectives™ on Stack Overflow

Getting the values of a MySQL enum using only SQL

3 Answers 3

1 Comment

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related