Example test MySQL enum type

When developing a project, you will often encounter some status fields, such as the order status to be paid, paid, closed, refunded, etc. In my previous projects, these statuses were stored in the database as numbers, and then a mapping table was maintained in the PHP code using constants, for example:

const STATUS_PENDING = 0;
const STATUS_PAID = 1;
const STATUS_CLOSED = 2;
const STATUS_REFUNDED = 3;

However, in actual use, we found that it is not so easy to use. Due to various reasons (tracking bugs, temporary statistical needs, etc.), we often need to log in to the MySQL server to manually execute some SQL queries. Since many tables have status fields, when writing SQL, we must refer to the mapping relationship in the PHP code. If we are not careful, we may confuse the status numbers of different tables and cause big problems.

So I am going to use MySQL's enum type to store various states in my new project. During use, I found that if I make changes to the table using the enum type in the Laravel migration file (even if I change a non-enum type field), an error will be reported.

[Doctrine\DBAL\DBALException]
Unknown database type enum requested, Doctrine\DBAL\Platforms\MySQL57Platform may not support it.

After searching, I found that doctrine does not support MySQL enum. The article lists three disadvantages of enum:

When adding a new enum value, the entire table needs to be rebuilt, which may take several hours when the amount of data is large.

The sorting rule of enum values ​​is based on the order specified when the table structure is created, not the size of the literal value.

It is not necessary to rely on MySQL to validate enum values. In the default configuration, inserting an invalid value will eventually become a null value.

According to the actual situation of the new project, it is unlikely that there will be a need to sort the status field. Even if there is, we can set the order when designing the table structure, so disadvantage 2 can be ignored; disadvantage 3 can be avoided through code standards, pre-insert/pre-update verification, etc.; as for disadvantage 1, we need to do some testing.

Test Preparation

First create a table:

CREATE TABLE `enum_tests` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `status` enum('pending','success','closed') COLLATE utf8mb4_unicode_ci NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Then insert 1 million pieces of data:

$count = 1000000;
$bulk = 1000;
$data = [];
foreach (['pending', 'success', 'closed'] as $status) {
  $data[$status] = [];
  for ($i = 0; $i < $bulk; $i++) {
    $data[$status][] = ['status' => $status];
  }
}

for ($i = 0; $i < $count; $i += $bulk) {
  $status = array_random(['pending', 'success', 'closed']);
  EnumTest::insert($data[$status]);
}

Testing Process

Test 1#

Add a value of refund to the end of the enum value list

ALTER TABLE `enum_tests` CHANGE `status` `status` ENUM('pending','success','closed','refunded') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

Output:

Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0

Conclusion: There is almost no cost when appending enum value at the end.

Test 2:#

Delete the value just added refund

ALTER TABLE `enum_tests` CHANGE `status` `status` ENUM('pending','success','closed') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

Output:

Query OK, 1000000 rows affected (5.93 sec)
Records: 1000000 Duplicates: 0 Warnings: 0

Conclusion: Deleting an unused enum value still requires a full table scan, which is costly but still within an acceptable range.

Test 3:#

Insert refund in the middle of the list of values ​​instead of at the end

ALTER TABLE `enum_tests` CHANGE `status` `status` ENUM('pending','success','refunded', 'closed') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

Output:

Query OK, 1000000 rows affected (6.00 sec)
Records: 1000000 Duplicates: 0 Warnings: 0

Conclusion: Adding a new value in the middle of the original enum value list requires a full table scan and update, which is costly.

Test 4:#

Remove the value in the middle of a list of values

ALTER TABLE `enum_tests` CHANGE `status` `status` ENUM('pending','success','closed') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

Output:

Query OK, 1000000 rows affected (4.23 sec)
Records: 1000000 Duplicates: 0 Warnings: 0

Conclusion: The entire table needs to be scanned, which is very costly.

Test 5:#

Add an index to the status field and then run the above test

ALTER TABLE `enum_tests` ADD INDEX(`status`);

It was found that the time consumption of tests 2-4 actually increased, which should be caused by the need to update the index at the same time.

Conclusion

For my new project, only new enum values ​​will appear. Even if some states are abandoned in the future, there is no need to adjust the enum value list. Therefore, I decided to introduce the enum type as the data type for storing states in the project.