實例測試MySQL的enum類型
在開發(fā)項目時通常會遇到一些狀態(tài)字段，例如訂單的狀態(tài)有 待支付、已支付、已關(guān)閉、已退款 等，我以前做的項目都是把這些狀態(tài)用數(shù)字存在數(shù)據(jù)庫中，然后在 php 代碼中用常量來維護一份映射表，例如：
const STATUS_PENDING = 0; const STATUS_PAID = 1; const STATUS_CLOSED = 2; const STATUS_REFUNDED = 3;
但是在實際使用過程中發(fā)現(xiàn)并不是那么好用，由于各種原因（追查 bug、臨時的統(tǒng)計需求等）我們常常需要登錄到 mysql 服務器里手動執(zhí)行一些 sql 查詢，由于許多表都有狀態(tài)字段，寫 sql 時必須對照的 php 代碼里的映射關(guān)系來寫，一不小心還有可能將不同表的狀態(tài)數(shù)字弄混導致大問題。
于是我在新項目中準備使用 mysql 的 enum 類型來存儲各種狀態(tài)，在使用過程中發(fā)現(xiàn)如果在 Laravel 的 migration 文件中對使用了 enum 類型的表做變更（即使是變更非 enum 類型的字段）都會報錯
[Doctrine\DBAL\DBALException] Unknown database type enum requested, Doctrine\DBAL\Platforms\MySQL57Platform may not support it.
搜索了一下，發(fā)現(xiàn)是 doctrine 不支持 mysql 的 enum，該文中列舉了 enum 的 3 個缺點：
新增 enum 值的時候需要重建整個表，當數(shù)據(jù)量大的時候可能需要耗費數(shù)小時。
enum 值的排序規(guī)則是按創(chuàng)建表結(jié)構(gòu)時指定的順序，而非字面值的大小。
依賴 mysql 對 enum 值的校驗并不是非常必要，在默認配置下插入非法值最終會變成空值。
根據(jù)新項目的實際情況，不太可能出現(xiàn)需要對狀態(tài)字段做排序的需求，即使有我們可以在設計表結(jié)構(gòu)的時候就定好順序，因此缺點 2 可以忽略；而缺點 3 則可以通過代碼規(guī)范、插入/更新前校驗等方式來規(guī)避；至于缺點 1，我們需要做一些測試。
測試準備#
首先創(chuàng)建一個表：
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;
然后插入 100W 條數(shù)據(jù)：
$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]);
}
測試過程#
測試1#
在 enum 值列表最后添加一個值 refunded
ALTER TABLE `enum_tests` CHANGE `status` `status` ENUM('pending','success','closed','refunded') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;
輸出：
Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0
結(jié)論：在末尾追加 enum 值時幾乎沒有成本。
測試 2：#
刪除剛剛添加的值 refunded
ALTER TABLE `enum_tests` CHANGE `status` `status` ENUM('pending','success','closed') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;
輸出：
Query OK, 1000000 rows affected (5.93 sec) Records: 1000000 Duplicates: 0 Warnings: 0
結(jié)論：刪除一個沒有用過的 enum 值仍需全表掃描，成本較高，但還在可接受范圍內(nèi)。
測試 3：#
將 refunded 插入到值列表中間而非末尾
ALTER TABLE `enum_tests` CHANGE `status` `status` ENUM('pending','success','refunded', 'closed') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;
輸出：
Query OK, 1000000 rows affected (6.00 sec) Records: 1000000 Duplicates: 0 Warnings: 0
結(jié)論：在原 enum 值列表中間新增值需要全表掃描并更新，成本較高。
測試 4：#
刪除值列表中間的值
ALTER TABLE `enum_tests` CHANGE `status` `status` ENUM('pending','success','closed') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;
輸出：
Query OK, 1000000 rows affected (4.23 sec) Records: 1000000 Duplicates: 0 Warnings: 0
結(jié)論：需全表掃描，成本較高。
測試 5：#
給 status 字段添加索引后再執(zhí)行上述測試
ALTER TABLE `enum_tests` ADD INDEX(`status`);
發(fā)現(xiàn)測試 2-4 的耗時反而有所增加，應該是同時需要更新索引導致的。
結(jié)語：#
對于我的新項目來說只會出現(xiàn)新增 enum 值的情況，即使將來有個別狀態(tài)廢棄不用也不需要去調(diào)整 enum 的值列表，因此決定在項目中引入 enum 類型作為存儲狀態(tài)的數(shù)據(jù)類型。
相關(guān)文章
mysql實現(xiàn)查詢結(jié)果導出csv文件及導入csv文件到數(shù)據(jù)庫操作
這篇文章主要介紹了mysql實現(xiàn)查詢結(jié)果導出csv文件及導入csv文件到數(shù)據(jù)庫操作,結(jié)合實例形式分析了mysql相關(guān)數(shù)據(jù)庫導出、導入語句使用方法及操作注意事項,需要的朋友可以參考下2018-07-07
mysql8.0.0 winx64.zip解壓版安裝配置教程
這篇文章主要為大家詳細介紹了mysql8.0.0 winx64.zip解壓版安裝配置教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下2018-05-05
這篇文章主要為大家詳細介紹了Linux下mysql 5.6.17安裝圖文教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下2016-09-09
這篇文章主要介紹了Mysql存儲二進制對象數(shù)據(jù)問題，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2023-03-03
這篇文章主要介紹了MySQL中修改庫名的操作教程,是MySQL學習當中的基礎(chǔ)知識,需要的朋友可以參考下2015-05-05
win10下mysql 8.0.12 安裝及環(huán)境變量配置教程
這篇文章主要為大家詳細介紹了MySQL8.0的安裝、配置、啟動服務和登錄及配置環(huán)境變量，具有一定的參考價值，感興趣的小伙伴們可以參考一下2019-03-03
MySQL 8.0.18使用clone plugin重建MGR的實現(xiàn)
這篇文章主要介紹了MySQL 8.0.18使用clone plugin重建MGR的實現(xiàn),文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2019-12-12
mysql是一種關(guān)聯(lián)數(shù)據(jù)庫管理系統(tǒng)，在程序開發(fā)中應用非常廣泛，下面通過本文給大家介紹mysql安裝與使用教程，感興趣的朋友一起學習吧2016-05-05
文章詳細介紹了如何在Linux系統(tǒng)上安裝和配置MySQL,包括下載、解壓、安裝依賴、啟動服務、獲取默認密碼、設置密碼、支持遠程登錄以及創(chuàng)建表,感興趣的朋友一起看看吧2024-12-12
MySQL 提供了子查詢這一強大工具,幫助我們實現(xiàn)嵌套查詢,本文主要介紹了MySQL中子查詢的具體實現(xiàn),文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧2024-09-09