MySQL enum type instance test

What is enum? enum is the abbreviation of E.164 Number URI Mapping. Behind this abbreviation lies a great idea: the ability to use the same phone number anywhere in the world via the best and cheapest routing. You can register an ENUM number just like you would a domain name.

When developing projects, we usually encounter some status fields, such as the status of an order pending payment, paid, closed, refunded, etc. In the projects I worked on before, these statuses were stored in the database as numbers. in php Constants are used in the code to maintain a mapping table, for example:

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

However, in actual use, it is found that it is not so easy to use. Due to various reasons (tracking bugs, temporary statistical requirements, etc.) we often need Log in to the mysql server and manually execute some sql Query, since many tables have status fields, you must write SQL according to the mapping relationship in the PHP code. If you are not careful, you may confuse the status numbers of different tables, causing big problems.

So I planned to use the enum type of mysql to store various states in the new project. During the use, I found that if I use the Any changes to the enum type table (even changes to non-enum type fields) will result in an error

[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's enum. The article lists 3 shortcomings of enum:

When adding an enum value, you need to rebuild the entire table, which may take several hours when the amount of data is large.

enum values ​​are sorted in the order specified when creating the table structure, not by the size of the literal value.

It is not necessary to rely on mysql to verify the enum value. Inserting illegal values ​​under the default configuration 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 fields. Even if there is, we can set the order when designing the table structure, so Disadvantage 2 can be ignored; and Disadvantage 3 It can be avoided through code specifications, verification before inserting/updating, 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 100W pieces of data:

$count = 1000000;
$bulk = 1000;
$data = [];
foreach (['pending', 'success', 'closed'] as $status) {
  $data[$status] = [];
  for ($i = 0; $i < $bulk; $i++) {
    $data[$status][] = [&#39;status&#39; => $status];
  }
}
  
for ($i = 0; $i < $count; $i += $bulk) {
  $status = array_random([&#39;pending&#39;, &#39;success&#39;, &#39;closed&#39;]);
  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 when appending enum values ​​at the end.

Test 2:

#Delete the value just added 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 more expensive, but still within an acceptable range.

Test 3:

#Insert refunded into the middle of the value list 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 scanning and updating the entire table, 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: A full table scan is required and the cost is high.

Test 5:

#Add an index to the status field and then execute the above test

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

Discover the time consumption of tests 2-4 On the contrary, it has 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, so Decided to introduce enum into the project Type serves as a data type for storing state.

Related recommendations:

Detailed explanation of Enum (enumeration) usage in php

Enum extended feature example code

How to solve the confusion about the default value of enum data type

The above is the detailed content of MySQL enum type instance test. For more information, please follow other related articles on the PHP Chinese website!