Knowledgebase

Introduction

In MySQL, you can use the ENUM data type to specify a list of permitted values in a column. For instance, in a customers table, you can create a column named customer_type and explicitly tell MySQL to enumerate a list of values, for example INDIVIDUAL, BUSINESS and NGO, when creating the table. Although the ENUM datatype stores the list items as strings, it allows compact storage of data since the input values are automatically encoded to numbers during storage. Also, when you run a SELECT query against an ENUM column, data is translated back to human-readable formats without requiring usage of MySQL functions such as IF and CASE statements.

In addition to these advantages, MySQL columns defined with the ENUM data type have an inbuilt validation mechanism that throws an error/warning if a user tries to INSERT a value not defined in the list.

You'll create a test_db database and a sample customers table in an Ubuntu 20.04 server in this guide. You'll then use the ENUM datatype on a customer_type field to define a list of permitted values and see how MySQL handles this type of column. Later in the guide, you'll compare data stored on a VARCHAR column against the ENUM datatype to see how you can save disk space when designing your database.

Prerequisites

To complete this tutorial, you'll need the following:

• An Ubuntu 20.04 server.
• A sudo user.
• A LAMP Stack. This guide has been tested on the MySQL database, so you'll require it to complete the tutorial.

Create a Test test_db Database

SSH to your server and run the command below to log in to the MySQL server as root.

$ sudo mysql -u root -p

You'll be prompted to enter a password for your MySQL server. Type it and hit Enter to proceed. Once you get the mysql> prompt, run the command below to create a test_db.

mysql> CREATE DATABASE test_db;

Next, create a test_user user and remember to replace EXAMPLE_PASSWORD with a strong value. You'll later use these credentials to automatically populate a database table with a thousand records using PHP.

mysql> CREATE USER 'test_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'EXAMPLE_PASSWORD';
mysql> GRANT ALL PRIVILEGES ON test_db.* TO 'test_user'@'localhost';
mysql> FLUSH PRIVILEGES;

Switch to the test_db database.

mysql> USE test_db;

Next, create a customers table. Define the customer_type column with an ENUM datatype and supply a permitted list of comma-separated string values by running the command below.

mysql> CREATE TABLE customers
       (
           customer_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
           customer_type ENUM('INDIVIDUAL', 'BUSINESS', 'NGO'),
           first_name VARCHAR(50),
           last_name VARCHAR(50)
       ) ENGINE = InnoDB;

Populate the customers table with some records.

mysql> INSERT INTO customers(customer_type, first_name, last_name) VALUES ('BUSINESS', 'JOHN', 'DOE');
mysql> INSERT INTO customers(customer_type, first_name, last_name) VALUES ('INDIVIDUAL', 'MARY', 'SMITH');
mysql> INSERT INTO customers(customer_type, first_name, last_name) VALUES ('NGO', 'ROE', 'PETER');

Once you've defined the table and inserted some records, you'll now run some tests to review the advantages of the ENUM datatype.

Generate Readable Query Output with Enum DataType

The MySQL ENUM datatype allows you to query a table and get human-readable data without passing the values against any MySQL functions. For this guide, it was possible to define the customer_type column with an INT datatype and use the following values during an insert statement.

• 1 - INDIVIDUAL
• 2 - BUSINESS
• 3 - NGO

However, with the above approach, if you run a SELECT statement against the customers table, you would need to use the MySQL IF or CASE statements to translate those integer values(1, 2, 3) to their respective string values(INDIVIDUAL, BUSINESS, NGO).

When the customer_type column is defined with an ENUM data type, a simple query against the customers table should display the data in a human-readable format without requiring additional MySQL functions as shown below.

mysql> SELECT
       customer_id,
       customer_type,
       first_name,
       last_name
       FROM customers;

Ensure you get a list of all customers' names and the corresponding string values for the customer_type column.

+-------------+---------------+------------+-----------+
| customer_id | customer_type | first_name | last_name |
+-------------+---------------+------------+-----------+
|           1 | BUSINESS      | JOHN       | DOE       |
|           2 | INDIVIDUAL    | MARY       | SMITH     |
|           3 | NGO           | ROE        | PETER     |
+-------------+---------------+------------+-----------+
3 rows in set (0.00 sec)

Validate Data On an Enum Column

Once you've defined a column name with the MySQL ENUM datatype, it internally validates entries to the table. For instance, try to run the INSERT command below and use a non-enumerated value such as MICROFINANCE on the customer_type column and see whether the operation will succeed.

mysql> INSERT INTO customers(customer_type, first_name, last_name) VALUES ('MICROFINANCE', 'JANE', 'MIKE');

Since MICROFINANCE is not defined in the list of allowed input values, you'll get the following error.

ERROR 1265 (01000): Data truncated for column 'customer_type' at row 1

In case the MySQL STRICT_TRANS_TABLES is disabled, the INSERT command above will succeed, and a warning will be displayed as shown below.

Query OK, 1 row affected, 1 warning (0.00 sec)

You can examine the source of the warning in detail by running the command below.

mysql> SHOW WARNINGS;

You should get the warning in detail, as shown below.

+---------+------+----------------------------------------------------+
| Level   | Code | Message                                            |
+---------+------+----------------------------------------------------+
| Warning | 1265 | Data truncated for column 'customer_type' at row 1 |
+---------+------+----------------------------------------------------+
1 row in set (0.00 sec)

Filter Data with an Enum Column

When filtering data on a table defined with the ENUM datatype, you can either use the string or the index value in the WHERE clause.

For instance, to get all customers defined with the INDIVIDUAL customer type, run the command below.

mysql> SELECT
       customer_id,
       customer_type,
       first_name,
       last_name
       FROM customers
       WHERE customer_type = 'INDIVIDUAL';

You should see the output below.

+-------------+---------------+------------+-----------+
| customer_id | customer_type | first_name | last_name |
+-------------+---------------+------------+-----------+
|           2 | INDIVIDUAL    | MARY       | SMITH     |
+-------------+---------------+------------+-----------+
1 row in set (0.00 sec)

Next, run the same filter command, but this time around, replace the string value with the index value for the INDIVIDUAL customer type(1).

mysql> SELECT
       customer_id,
       customer_type,
       first_name,
       last_name
       FROM customers
       WHERE customer_type = 1;

You should get the output shown below.

+-------------+---------------+------------+-----------+
| customer_id | customer_type | first_name | last_name |
+-------------+---------------+------------+-----------+
|           2 | INDIVIDUAL    | MARY       | SMITH     |
+-------------+---------------+------------+-----------+
1 row in set (0.00 sec)

Sort Data with an Enum Column

You can use the MySQL ORDER BY clause against an ENUM column to sort records. Run the command below.

mysql> SELECT
       customer_id,
       customer_type,
       first_name,
       last_name
       FROM customers
       ORDER BY customer_type ASC;

As you can confirm from the list below, ENUM columns are sorted by their acceptable index values and not chronologically.

+-------------+---------------+------------+-----------+
| customer_id | customer_type | first_name | last_name |
+-------------+---------------+------------+-----------+
|           2 | INDIVIDUAL    | MARY       | SMITH     |
|           1 | BUSINESS      | JOHN       | DOE       |
|           3 | NGO           | ROE        | PETER     |
+-------------+---------------+------------+-----------+
3 rows in set (0.00 sec)

If you really want to sort the values based on the string values, cast the ENUM field to a string value when running the ORDER BY command as shown below.

mysql> SELECT
       customer_id,
       customer_type,
       first_name,
       last_name
       FROM customers
       ORDER BY CAST(customer_type AS CHAR);

Your customers' list should now be ordered by the string values in alphabetical order.

+-------------+---------------+------------+-----------+
| customer_id | customer_type | first_name | last_name |
+-------------+---------------+------------+-----------+
|           1 | BUSINESS      | JOHN       | DOE       |
|           2 | INDIVIDUAL    | MARY       | SMITH     |
|           3 | NGO           | ROE        | PETER     |
+-------------+---------------+------------+-----------+
3 rows in set (0.00 sec)

Once you've run the above tests, delete all records from the customers table. You'll require an empty set to run further tests.

mysql> TRUNCATE TABLE customers;

Make sure the command is successful.

Query OK, 0 rows affected (0.03 sec)

Exit from the MySQL command-line interface.

mysql> QUIT;

Compact Data with Enum

In this step, you will run some tests against the customers table to see the disk usage both when the customer_type field is defined with a VARCHAR and an ENUM datatype.

Use nano to open a new /var/www/html/populate.php file in the root directory of your webserver.

$ sudo nano /var/www/html/populate.php

Next, populate the file with the following content.

<?php
        try {
            define('DB_NAME', 'test_db');
            define('DB_USER', 'test_user');
            define('DB_PASSWORD', 'EXAMPLE_PASSWORD');
            define('DB_HOST', 'localhost');

            $pdo = new PDO("mysql:host=" . DB_HOST . ";dbname=" . DB_NAME, DB_USER , DB_PASSWORD );
            $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);


            for ($i = 1; $i <= 1000; $i++) {

                $data = [];

                $sql = "INSERT INTO customers
                       (
                       customer_type,
                       first_name,
                       last_name
                       )
                       VALUES
                       (
                       :customer_type,
                       :first_name,
                       :last_name
                      )
                      ";

                $data = [
                        "customer_type" =>  "INDIVIDUAL",
                        "first_name"    =>  "FIRST NAME # " . $i ,
                        "last_name"     =>  "LAST NAME #  " . $i
                        ];

                $stmt = $pdo->prepare($sql);
                $stmt->execute($data);

            }

            echo "Inserted 1000 rows.\n";

            } catch(PDOException $e) {
                echo $e->getMessage();
            }

Save and close the file. In the above file, you're inserting a thousand records to the customers table using PHP. Next, execute the URL below using Linux curl command to populate the customers table.

$ curl http://localhost/populate.php

After a few seconds, ensure you get the below output confirming that the PHP script has indeed populated the table with a thousand records.

Inserted 1000 rows.

Log back to the MySQL server.

$ sudo mysql -u root -p

Enter your root password for your MySQL server and press Enter to proceed. Then, run the command below to check the disk usage for the customers table when the customer_type column is defined with the ENUM datatype.

mysql> SELECT
       table_name,
       ROUND(((data_length + index_length)), 2) AS "Size in Bytes"
       FROM information_schema.TABLES
       WHERE table_schema = "test_db"
       ORDER BY (data_length + index_length) DESC;

The output below confirms that the customers table's disk usage is around 98KB(98304 bytes).

+------------+---------------+
| TABLE_NAME | Size in Bytes |
+------------+---------------+
| customers  |      98304.00 |
+------------+---------------+
1 row in set (0.01 sec)

Switch to the test_db database and drop the customers table.

mysql> USE test_db;
mysql> DROP TABLE customers;

Next, recreate the customers table. This time around, define the customer_type column with a VARCHAR datatype.

mysql> CREATE TABLE customers
       (
           customer_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
           customer_type VARCHAR(10),
           first_name VARCHAR(50),
           last_name VARCHAR(50)
       ) ENGINE = InnoDB;

Log out from the MySQL command-line interface.

mysql> QUIT;

Populate the customers table again using your automated PHP script.

$ curl http://localhost/populate.php

Ensure you get the following output to confirm that the one thousand records were inserted successfully.

Inserted 1000 rows.

Log in back to the MySQL server.

$ sudo mysql -u root -p

Enter your password and press Enter to continue. Then, run the command below again to check the size of the customers table.

mysql> SELECT
       table_name,
       ROUND(((data_length + index_length)), 2) AS "Size in Bytes"
       FROM information_schema.TABLES
       WHERE table_schema = "test_db"
       ORDER BY (data_length + index_length) DESC;

As you can see from the output below, the size is now around 115KB(114688 bytes).

+------------+---------------+
| TABLE_NAME | Size in Bytes |
+------------+---------------+
| customers  |     114688.00 |
+------------+---------------+
1 row in set (0.00 sec)

If you compare 115KB to the previous value of 98KB (when the customer_type column was defined with an ENUM datatype), you will see that the ENUM datatype is better for saving disk usage on your server.

You can calculate the disk space that you've saved in this tutorial using the formula below.

= (115 - 98) / 98 * 100 = 17%

From the output above, you have saved up to 17% of disk space.

Conclusion

In this guide, you've learned how to use the MySQL ENUM datatype to generate readable query outputs. You've also seen how this datatype is great for validating data and saving disk on your Ubuntu 20.04 server.