How to get available values for SET field?

Question

Hello,

Is there any way to get available values for SET field in table?

Thank you.

Answer 1

You can retrieve the possible values for a SET field using DESCRIBE myTableName mySetColumn or SHOW COLUMNS FROM myTableName LIKE mySetColumn:

  mysql> DESCRIBE myTableName mySetColumn;
  +-------+-------------------------------------------+------+-----+---------+-------+
  | Field | Type                                      | Null | Key | Default | Extra |
  +-------+-------------------------------------------+------+-----+---------+-------+
  | myset | set('Travel','Sports','Dancing','Dining') | YES  |     | NULL    |       |
  +-------+-------------------------------------------+------+-----+---------+-------+

Informative article here, manual here.

Answer 2

SELECT `COLUMN_TYPE` FROM `information_schema`.`COLUMNS`
 WHERE `TABLE_SCHEMA` = 'my_database_name'
   AND `TABLE_NAME`   = 'my_table_name'
   AND `COLUMN_NAME`  = 'my_set_column';

gives you only the type, e.g.

set('Travel','Sports','Dancing','Dining')

you'll still have to extract the set value on a textual base, but there's less clutter around it this way.