MySQL ENUM column type

Question

Can i get all possibilities of enum column type by one query?

For example, i have column colors with possibilities 'red', 'green', 'blue'.

I want to get something like this:

colors
------
red
green
blue

or

colors
------
red, green, blue

possible duplicate of [How can I get enum possible values in a MySQL database?](http://stackoverflow.com/questions/2350052/how-can-i-get-enum-possible-values-in-a-mysql-database) — ApplePie, Jun 30 '13 at 19:25

sailingthoms · Accepted Answer · 2013-06-30T20:37:14.440

If you would like to get the actual values present in this column, try this:

SELECT DISTINCT color FROM colorenum

If you are going to get all values that are allowed for this column, try:

SHOW COLUMNS FROM colorenum WHERE Field = 'color'

see example here: http://www.sqlfiddle.com/#!2/d27350/1

or

SELECT COLUMN_TYPE 
FROM information_schema.COLUMNS 
WHERE TABLE_NAME='colorenum' AND COLUMN_NAME = 'COLOR';

This one will return enum('red','green','blue','white','yellow','black') for example.

or

SELECT REPLACE(
  (REPLACE(
    (REPLACE(
      (REPLACE(
        (SELECT COLUMN_TYPE 
         FROM information_schema.COLUMNS 
         WHERE TABLE_NAME='colorenum' AND COLUMN_NAME = 'COLOR')
      , 'enum(', '')),
    ')', '')),
  '\'', '')),
',', ', ');

This one will return red, green, blue, white, yellow, black for example.

The last one is best for me to use. Thank you. – Vlado Jul 01 '13 at 06:07 — Vlado, Jul 01 '13 at 06:07

MySQL ENUM column type

1 Answers1