Instance test the enum type of MySQL, instance mysqlenum

Instance test the enum type of MySQL, instance mysqlenum

Some status fields are often encountered during the development project, such as the order status: Pending payment, paid, closed, and refunded, my previous projects used numbers to store these statuses in the database, and then maintained a ing table using constants in the php Code. For example:

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

However, in actual use, it is not so easy to use. due to various reasons (tracing bugs and temporary statistical requirements), we often need to log on to the mysql server to manually execute some SQL queries, because many tables have status fields, you must compare them with the ing relationships in the php code when writing SQL statements. If you are not careful, mixing the status numbers of different tables may lead to a big problem.

So I am going to use the enum type of mysql to store various statuses in the new project, during usage, it is found that if the enum type table is changed in the migration file of Laravel (even if it is changed to a non-enum type field), an error is reported.

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

After searching, we found that doctrine does not support mysql enum. This article lists the three disadvantages of enum:

When the enum value is added, the entire table needs to be rebuilt. It may take several hours to increase the data volume.

The enum value sorting rule is based on the sequence specified when the table structure is created, rather than the size of the literal value.

It is not necessary to rely on mysql to verify the enum value. inserting an invalid value in the default configuration will eventually become a null value.

According to the actual situation of the new project, it is unlikely that the Status field needs to be sorted. Even if there is a need, we can set the order when designing the table structure. Therefore, disadvantage 2 can be ignored; disadvantage 3 can be avoided through code specification, pre-insertion/update verification, etc. As for disadvantage 1, we need to perform some tests.

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;

Insert 100 million data records:

$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]);}

Test process #

Test 1 #

Add a value refunded at 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 to append the enum value at the end.

Test 2 :#

Delete the added value refunded

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 the acceptable range.

Test 3 :#

Insert refunded into the middle of the value list rather than 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: The new value-added value in the original enum Value List requires full table scan and update, which is costly.

Test 4 :#

Delete the value in the middle of the Value List

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: full table scan is required at a high cost.

Test 5 :#

Add an index to the status field before performing the preceding test.

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

It is found that the time consumed for testing 2-4 has increased, and the index must be updated at the same time.

Conclusion :#

For my new project, only the enum value is added. You do not need to adjust the enum Value List even if some statuses are discarded in the future, therefore, it is decided to introduce the enum type in the project as the Data Type of the storage status.