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

This article mainly gives you a brief description of the detailed steps of testing the enum type of MySQL. You can check the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here. Let's go straight to the topic. I hope this article on the detailed steps of testing the enum type of MySQL can bring you some practical help.

When developing a project, you usually encounter some status fields, such as the status of an order that is to be paid, paid, closed, refunded, and so on. In previous projects, I stored these status in a database with numbers, and then used constants in php code to maintain a mapping table, such as:

Const STATUS_PENDING = 0There Const STATUS_PAID = 1Traconst STATUS_CLOSED = 2There Const STATUS_REFUNDED = 3

However, it is not so easy to use in practice. Due to various reasons (tracking bug, temporary statistical requirements, etc.), we often need to log in to mysql CVM to execute some sql queries manually. Since many tables have status fields, we must write them against the mapping relationship in the php code when writing sql. Carelessly, it is possible to confuse the status numbers of different tables and cause big problems.

So I am going to use the enum type of mysql to store various states in my new project. In the course of using it, I found that if I made changes to tables that used the enum type in the migration file of Laravel (even if I changed fields that were not of enum type), I would report an error.

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

After a search, it is found that doctrine does not support mysql's enum. This article lists three shortcomings of enum:

When you add a new 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 the table structure was created, not the size of the literal value.

It is not necessary to rely on mysql to check the enum value, and inserting an illegal value in the default configuration will eventually become null.

According to the actual situation of the new project, it is unlikely that there is a need to sort the status fields, even if we can set the order when designing the table structure, so disadvantage 2 can be ignored; disadvantage 3 can be avoided by code specification, pre-insert / 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:

Foreach (['pending',' success', 'closed'] as $status) {$data [$status] = []; for ($I = 0x $I)

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

Testing process #

Test tool

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

Test 2RV #

Delete the value refunded you just added

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 expensive, but still within an acceptable range.

Test 3RV #

Insert refunded in the middle of the values 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: the new increment in the middle of the original enum value list needs to be scanned and updated, and the cost is high.

Test 4RV #

Delete the value in the middle of the values 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 needed and the cost is high.

Test 5RV #

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

ALTER TABLE `enum_ tests` ADD INDEX (`status`)

It is found that the time-consuming of test 2-4 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 be added, and there is no need to adjust the enum value list even if individual states are abandoned in the future, so I decided to introduce the enum type as the data type for storing the state in the project.

The detailed steps of testing the enum type of MySQL will stop here. If you want to know about other related issues, you can continue to follow our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.