MariaDB ENUM – Helpful Guide

In this MariaDB tutorial, we will look at how to utilize the MariaDB ENUM datatype and look at several examples. There are lists of the topic that comes under discussion:

• MariaDB enum
• MariaDB enum type
• MariaDB enum vs set
• MariaDB enum size
• MariaDB enum vs tinyint
• MariaDB add enum value
• MariaDB change enum
• MariaDB enum empty string
• MariaDB order by enum
• MariaDB enum default

MariaDB ENUM

Here we will learn how to use the enumeration or enum data type in this section, which will be described using syntax and datatypes.

In MariaDB, the ENUM is a string object in MariaDB that has a value selected from a list of permissible values defined at the time of column creation. The following are its benefits:

• Compact data storage. The ENUM uses numeric indexes like (1,2,3,… ) to represent index string values.
• Readable queries and input.

The enumeration or enum data type has the following syntax:

SYNTAX:

CREATE TABLE TABLE_NAME(
...
COLUMN_NAME ENUM ('VALUE_1','VALUE_2','VALUE_3'),
...
);

In the syntax, we have kept three enumeration values and it is a good practice to keep several enumeration values under 20.

Let’s start by creating a USA_STATES table using the following query:

CREATE TABLE USA_STATES(
STATE_ID INT AUTO_INCREMENT PRIMARY KEY,
STATE_NAME VARCHAR(50),
STATE_CODE VARCHAR(5),
STATE_POLLUTION ENUM('HIGH','MEDIUM','LOW') NOT NULL);

INSERT INTO USA_STATES(STATE_NAME,STATE_CODE,STATE_POLLUTION)
VALUES('ALABAMA','AL','HIGH'),
('ALASKA','AK','MEDIUM'),
('ARIZONA','AZ','LOW'),
('ARKANSAS','AR','MEDIUM'),
('CALIFORNIA','CA','HIGH'),
('COLORADO','CO','HIGH'),
('CONNECTICUT','CT','MEDIUM'),
('DELAWARE','DE','HIGH'),
('FLORIDA','FL','MEDIUM'),
('GEORGIA','GA','LOW'),
('HAWAII','HI','HIGH'),
('IDAHO','ID','LOW'),
('ILLINOIS','IL','HIGH'),
('INDIANA','IN','MEDIUM'),      
('LOWA','IA','LOW'),
('KANSAS','KS','LOW'),
('KENTUCKY','KY','MEDIUM'),
('LOUISIANA','LA','LOW'),
('MAINE','ME','LOW'),
('MARYLAND','MD','HIGH'),
('MASSACHUSETTS','MA','HIGH'),
('MICHIGAN','MI','LOW'),
('MINNESOTA','MN','MEDIUM'),
('MISSISSIPPI','MS','LOW'),
('MISSOURI','MO','HIGH'),
('MONTANA','MT','LOW'),
('NEBRASKA','NE','MEDIUM'),
('NEVADA','NV','LOW'),
('NEW HAMPSHIRE','NH','HIGH'),
('NEW JERSEY','NJ','LOW'),
('NEW MEXICO','NM','HIGH'),
('NEW YORK','NY','LOW'),
('NORTH CAROLINA','NC','HIGH'),
('NORTH DAKOTA','ND','LOW'),
('OHIO','OH','MEDIUM'),
('OKLAHOMA','OK','LOW'),
('OREGON','OR','MEDIUM'),
('PENNSYLVANIA','PA','LOW'),
('PHODE ISLAND','RI','MEDIUM'),
('SOUTH CAROLINA','SC','LOW'),
('SOUTH DAKOTA','SD','MEDIUM'),
('TENNESSEE','TN','LOW'),
('TEXAS','TX','LOW'),
('UTAH','UT','HIGH'),
('VERMONT','VT','MEDIUM'),
('VIRGINIA','VA','LOW'),
('WASHINGTON','WA','HIGH'),
('WEST VIRGINIA','WV','MEDIUM'),
('WISCONSIN','WI','LOW'),
('WYCOMING','WY','LOW');

SELECT * FROM USA_STATES;

QUERY EXPLANATION:

In this query, we have created a new table as USA_STATES by using the CREATE TABLE statement and inserted new records from the table by using the INSERT INTO table. The MariaDB SELECT statement will retrieve all records from the USA_STATES table.

The following is an example of the MariaDB ENUM data type:

EXAMPLE:

SELECT * FROM USA_STATES WHERE USA_POLLUTION='HIGH';

The condition as WHERE clause in the USA_POLLUTION column is HIGH as enum datatype in the above query, which uses the SELECT statement to retrieve all records from the USA_STATES table.

Read: MariaDB JSON Function

MariaDB ENUM Type

We’ll learn about the ENUM data type in this part, which is explained with examples.

The MariaDB ENUM is a string entity with a value selected from a set of allowed values stated explicitly in the column specification during table formation.

The following is an example of the MariaDB ENUM datatype:

EXAMPLE:

SELECT * FROM USA_STATES WHERE STATE_POLLUTION = 'MEDIUM';

QUERY EXPLANATION:

• In this query, the MariaDB SELECT statement retrieves all records from the USA_STATES table based on the condition by the WHERE clause in which the STATE_POLLUTION column is MEDIUM.
• As a result of the SELECT operation in the USA_STATES table, all entries with the STATE_POLLUTION column set to MEDIUM will be returned.

Read: MariaDB vs Postgres – Detailed Comparison

MariaDB ENUM vs Set

in this sub-topic, we will know the difference between the ENUM datatype and the SET data type and which is explained with examples.

The MariaDB ENUM data type is a string object in MariaDB that has a value selected from a list of permissible values defined at the time of column creation.

The sample example of the ENUM datatype is given below:

ENUM DATA TYPE EXAMPLE:

SELECT * FROM USA_STATES 
WHERE STATE_POLLUTION='LOW';

ENUM data type explanation:

As we see in the above query, we have retrieved all records from the USA_STATES table based on the WHERE condition on the STATE_POLLUTION column as the LOW value by using the ENUM data type values.

The MariaDB SET data type is used to store in bitmap and where each member represents a single value. The syntax of the MariaDB SET is given below:

SYNTAX:

CREATE TABLE TABLE_NAME(
....,
COLUMN_NAME DATATYPE [SET] (VALUE_LIST),
...);

The sample example of the MariaDB SET data type is given below:

EXAMPLE:

CREATE TABLE PROGRAMMING(
CODE_ID INT AUTO_INCREMENT PRIMARY KEY,
SET_CODE SET('JAVA','PERL','C++','ANDROID'));

INSERT INTO PROGRAMMING (SET_CODE) VALUES
('JAVA,C++'),
('C++,ANDROID'),
('PERL,JAva');

SELECT * FROM PROGRAMMING;

SET data type explanation:

The CREATE TABLE statement was used to create the PROGRAMMING table, and the INSERT INTO statement was used to insert some records into the table. When retrieving all records from the PROGRAMMING table with the SELECT command, all of the set values appeared in the resultset in the synchronized order.

The main difference between the ENUM data type and SET data type:

DIFFERENCES:

• The values that are given inside the ENUM datatype must be that one and only that one. If we try to use a value other than an ENUM value, it will be considered illegal and denied.
• The values that are given inside the SET data type must be in sychronize order, not in unsychronize order, as you may have noticed in the SET query above. If we try to insert something other than the SET data type, it will be refused with a message like ‘DATA TRUNCATED for the SET data type’.

Read: MariaDB If Null + Examples

MariaDB ENUM Size

In this section, we will learn about the ENUM sizes in detail.

The MariaDB ENUM data type is a string object in MariaDB that has a value selected from a list of permissible values defined at the time of column creation.

In theory, an ENUM column can have up to 65,535 different values; however, the actual maximum is dependent on several factors. Internally, ENUM values are represented as integers.

When a table is created, all trailing spaces in ENUM values are immediately removed.

When compared to strings, ENUMs take only one or two bytes of storage space, depending on the number of enumeration variables.

The sample example of the MariaDB ENUM size with its data type in the USA_STATES table is given below:

EXAMPLE:

SELECT * FROM USA_STATES
WHERE STATE_POLLUTION IN ('MEDIUM','LOW');

QUERY EXPLANATION:

As we see in the above query, we have retrieved all records from the USA_STATES table by using the SELECT statement based on WHERE condition in the STATE_POLLUTION column on ENUM data type values as MEDIUM and LOW.

Read: MariaDB Logs – Helpful Guide

MariaDB ENUM vs TinyINT

In this sub-topic, we will differentiate between ENUM data type and TINYINT datatype and which is explained with the help of examples.

The MariaDB ENUM data type is a string object in MariaDB that has a value selected from a list of permissible values defined at the time of column creation. The sample example of the MariaDB ENUM data type in the USA_STATES table is given below:

EXAMPLE:

SELECT * FROM USA_STATES
WHERE STATE_POLLUTION IN ('HIGH','LOW');

As we see in the above query, we have retrieved all records from the USA_STATES table by using the SELECT statement based on the WHERE condition in the STATE_POLLUTION column on ENUM data type values as HIGH and LOW.

DEFINE TINYINT data type:

The MariaDB TinyINT data type is a very small integer and it carries the signed integer from -128 to 127. The unsigned integer range from 0 to 255.

The sample example of the MariaDB TinyINT data type is given below:

EXAMPLE:

CREATE TABLE MASK_VALUE (
MARKS_X TINYINT,
MARKS_Y TINYINT UNSIGNED,
MArks_Z TINYINT ZEROFILL);

INSERT INTO MASK_VALUE VALUES (-10,10,10);

SELECT * FROM MASK_VALUE;

TINYINT data type explanation:

As we see in the above query, with the CREATE TABLE statement we have created the MASK_VALUE table. And inserted different values by different column names such as MARK_X, MARK_Y, and MARK_Z columns with different data types as TINYINT, TINYINT UNSIGNED, and TINYINT ZEROFILL data types.

Then we have inserted different values like -10,10,10 for all different columns by using the INSERT INTO statement for the MASK_VALUE table. And retrieving all records from the MASK_VALUE table by using the SELECT statement.

The main difference between the MariaDB ENUM data type and MariaDB TinyINT data type is given below:

• When we declare a column as an enum, it becomes extremely difficult to change or add more enum values to that column as the data in the table grows.
• In that situation, it’s just a great suffering; we’re helpless, and we’ll have to temporarily halt table activities. Which could end up costing you money. If it were a little int, though, it would just be a new value in the SQL query.

Read: MariaDB Drop Table + Examples

MariaDB Add ENUM Value

In this part, we’ll look at how to add the ENUM value to a MariaDB table using the ALTER TABLE statement. And we will discuss syntax and an example for this implementation.

In MariaDB, the ALTER TABLE statement is used to modify, drop, or delete data from a table’s columns. You may also use the MariaDB ALTER TABLE statement to rename columns in a table.

The MariaDB ALTER TABLE statement with the ENUM value appended has the following syntax:

SYNTAX:

ALTER TABLE TABLE_NAME 
ADD NEW_COLUMN DATATYPE
[FIRST | AFTER COLUMN_NAME];

The syntax explanation:

• TABLE_NAME: the name of the table that we want to modify
• NEW_COLUMN: the name of the column that we want to add to the table.
• DATATYPE: the datatype and definition of the column i.e:( not or not null, etc).
• FIRST | AFTER COLUMN_NAME: It is an optional field that specifies where a column should be created in the table. The argument will be appended to the end of the table if it is not given.

The following is an example of an ALTER TABLE statement to add the ENUM value:

EXAMPLE:

ALTER TABLE USA_STATES
ADD STATE_POLLUTION ENUM('HIGH','VERY HIGH','LOW','VERY LOW','MEDIUM','VERY MEDIUM');

ADD ENUM data type by ALTER TABLE statement EXPLANATION:

In this query, we have added the STATE_POLLUTION column by the ENUM data type with different values like ‘HIGH’, ‘VERY HIGH’, ‘LOW’, ‘VERY LOW’, ‘MEDIUM’, ‘VERY MEDIUM’ in the USA_STATES table by using the ALTER TABLE statement.

Read: MariaDB Foreign Key + Examples

MariaDB Change ENUM

In this topic, we will discuss the use of ALTER TABLE statement to change the column name of the ENUM value in MariaDB. And we will explain it by using an example in MariaDB.

The following is the syntax for changing MariaDB ENUM value with the ALTER TABLE statement:

SYNTAX:

ALTER TABLE TABLE_NAME 
CHANGE OLD_COLUMN_NAME NEW_COLUMN_NAME ENUM();

The following is an example of how to change the ENUM value using the ALTER TABLE statement:

EXAMPLE:

ALTER TABLE USA_STATES MODIFY COLUMN STATE_POLLUTION
ENUM('HIGH','MEDIUM',LOW');

DESC USA_STATES;

ENUM data type by ALTER TABLE statement EXPLANATION:

In the USA_STATES table, we have used the ALTER TABLE command to change the ENUM data type values from ‘HIGH’, ‘VERY HIGH’ to HIGH; ‘MEDIUM’, ‘VERY MEDIUM‘ to MEDIUM and ‘LOW’, ‘VERY LOW’ to LOW values in the STATE_POLLUTION column.

Use the DESC USA_STATES statement to see if new ENUM values for the STATE_POLLUTION column have been added to the USA_STATES table.

Read: MariaDB Temporary Table + Examples

MariaDB Enum Empty String

We’ll learn how to keep an enum data type as an empty string in this section, which is explained with examples.

The following query is an example of how to retain the ENUM data type as an empty string:

EXAMPLE:

SELECT * FROM STATES_OF_USA 
WHERE STATE_POPULATION=' ';

With the WHERE criteria on the STATE_POPULATION column as an empty string, we obtain all data from the STATES_OF_USA table, as seen in the following query. We know that we retained the enum data types as ‘HIGH,’ ‘MEDIUM,’ and ‘LOW,‘ yet the query will return an empty result.

Read: MariaDB Backup Database

MariaDB Order By ENUM

This section illustrates how to utilize the ORDER BY clause on the ENUM data type column in MariaDB. Moreover, we will also discuss the syntax and an example for this implementation.

The ORDER BY in MariaDB is utilized to sort the columns in ascending or descending order and display the results in a table. The ASC keyword is rarely used by programmers to bring the order in ascending order. The ASC or DESC keyword is used in the query to receive the result set in ascending or descending order.

The syntax of the MariaDB ORDER BY clause on the ENUM data type is given below:

SYNTAX:

SELECT exp FROM TABLE_NAME 
[WHERE some_condition]
ORDER BY COLUMN_NAME [DESC | ASC];

The syntax explanation:

• exp: The column or calculation that we want to retreive.
• TABLE_NAME: The table we wish to retreive data from. There should be atleast one table specified with the FROM clause.
• WHERE some_condition: The conditions that should be met from which rows to be selected.
• ASC: It is an optional option that arranges the result in ascending order (it is also the default value if no option is specified).
• DESC: It also an optional option that arranges the results of a table in the descending order.

The sample example of MariaDB ORDER BY clause on the ENUM data type by the following query:

EXAMPLE:

SELECT * FROM STATES_OF_USA
ORDER BY STATE_POPULATION DESC;

As can be seen in the preceding query, we utilized the ORDER BY clause on the STATE_POPULATION column with the DESC keyword on the STATES_OF_USA database with the SELECT statement to acquire the data structure in descending order.

As a result, the query’s output will be based on the ORDER BY clause on the STATE_POPULATION column of the STATES_OF_USA table.

Read: MariaDB Truncate Table

MariaDB ENUM Default

In this part, we’ll learn how to set the default value for the ENUM data type using an example.

The NULL and empty values can also be found in an ENUM. The NULL becomes a permitted value, as well as the default value if the ENUM column is declared to allow NULL values. If strict SQL Mode is not enabled and an invalid value is placed into an ENUM, a specific empty string is added with an index value of zero and a warning.

This may be perplexing because the empty string is also a valid value, with the only difference being that its index is not 0. If strict mode is enabled, inserting will fail with an error.

If there is a DEFAULT value is missing then the default value will be:

• Put as NULL, if the column is nullable.
• Otherwise th efirst value in the enumeration.

The sample example of the ENUM data type as DEFAULT value by the following query:

EXAMPLE:

DESC STATES_OF_USA;

The STATES_OF_USA table was mentioned in the preceding query to identify the NULL or DEFAULT value on the STATE_POPULATION column, which is shown in the resultset.

Also, take a look at some more MariaDB tutorials.

• MariaDB Rename Column
• MariaDB Union Operator
• MariaDB Case Statement
• MariaDB Window functions
• MariaDB Queries – Detailed Guide

In this MariaDB tutorial, we have looked at the MariaDB ENUM data type and also discussed some examples. There are lists of the topic that comes under discussion:

• MariaDB enum
• MariaDB enum type
• MariaDB enum vs set
• MariaDB enum size
• MariaDB enum vs tinyint
• MariaDB add enum value
• MariaDB change enum
• MariaDB enum empty string
• MariaDB order by enum
• MariaDB enum default