Shulou(Shulou.com)06/01 Report--

When developing a project, you usually encounter some status fields, such as the status of the order: pending payment, paid, closed, refunded, etc. In my previous projects, I stored these statuses in the database with numbers, and then maintained a mapping table with constants in the php code, such as:

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

But in the actual use of the process is not found so easy to use, due to various reasons (bug tracking, temporary statistical requirements, etc.) we often need to log into the mysql server manually perform some sql queries, because many tables have status fields, write sql must contrast the php code mapping relationship to write, accidentally there may be different table status figures confused leading to big problems.

So I prepared to use mysql enum type to store various states in my new project. In the process of using it, I found that if I made changes to tables using enum type in Laravel migration file (even if I changed fields of non-enum type), errors would be reported.

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

Search, found that docrine does not support mysql enum, the article listed enum 3 shortcomings:

Adding enum values requires rebuilding the entire table, which can take hours when the data volume is large.

The sorting rule for enum values is in the order specified when the table structure was created, not by literal size.

Dependency on mysql for checking enum values is not necessary, and inserting illegal values in the default configuration will eventually result in null values.

Based on the reality 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; disadvantage 3 can be avoided by code specification, insertion/update check, 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

$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 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 little cost in appending enum values at the end.

Test 2:#

Delete the value you 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 full table scanning, which is costly but acceptable.

Test 3:#

Insert refunded 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 new values in the middle of the original enum value list needs to scan and update the whole table, and the cost is high.

Test 4:#

Delete the middle value of the 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: Full scan is needed and the cost is high.

Test 5:#

Add an index to the status field before performing the above test

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

It was found that tests 2-4 took longer, probably due to the need to update the index at the same time.

Conclusion: #

For my new project, there will only be new enum values, even if there are individual states obsolete in the future, there is no need to adjust the enum value list, so I decided to introduce enum type as the data type for storing state in the project.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.