Get ENUM Possible Values in MySQL Database

To improve query efficiency, MySQL has a specific data type called ENUM that stores string values as numeric indexes. By limiting values to particular possibilities, ENUM, in contrast to standard string types, ensures data consistency and streamlines comparisons. The SHOW COLUMNS statement is used to fetch all the possible values of ENUM, under the type column this is a straightforward way to understand, and validate data and also to check the constraints in the database schema.

Retrieving the possible values of ENUM

In the below example let us see how to create an ENUM column, fetch the data, and most importantly to retrieve the possible values of ENUM column. Following is the syntax to create a ENUM column in a table -

yourColumnName ENUM(value1,value2,........................N)

Example

In this example let us see how to get all possible values for an ENUM column in MySQL.

Let us create a table with ENUM column

CREATE TABLE EnumDemo (
    Light INT,
    IsONorOff ENUM('ON', 'OFF')
);

In the above table, 'EnumDemo' we have two columns Light and IsONorOff. IsONorOff is an ENUM data type restricted to 'ON' and 'OFF'.

With the help of INSERT statement we can insert values into EnumDemo.

INSERT INTO EnumDemo values(1,'ON'),(0,'OFF');

To display all the records in the table, use the SELECT query ?

SELECT * FROM EnumDemo;

Following is the output of the above query ?

This output shows the rows with Light values and their corresponding ENUM values.

To retrieve the possible values of ENUM column use SHOW COLUMNS statement.

SHOW COLUMNS FROM EnumDemo LIKE 'IsONorOff';

Following is the output of the above query ?

The Type column displays the possible values of ENUM column.

I would like to conclude that when we use the ENUM column in MySQL, it simplifies data management by allowing us to define limited values for a column. This column makes sure the data is consistent and reduces errors. The database can be handled more easily by retrieving the predefined values of the ENUM column using commands like SHOW COLUMNS.