快捷導(dǎo)航

實(shí)例測(cè)試MySQL的enum類型

更新時(shí)間：2017年11月29日 14:34:17 投稿：laozhang

小編給大家?guī)淼谋酒獌?nèi)容是關(guān)于MySQL的enum類型實(shí)例測(cè)試，通過不同的測(cè)試讓大家更好的理解enum類型的用法，一起學(xué)習(xí)下。

在開發(fā)項(xiàng)目時(shí)通常會(huì)遇到一些狀態(tài)字段，例如訂單的狀態(tài)有待支付、已支付、已關(guān)閉、已退款等，我以前做的項(xiàng)目都是把這些狀態(tài)用數(shù)字存在數(shù)據(jù)庫中，然后在 php 代碼中用常量來維護(hù)一份映射表，例如：

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

但是在實(shí)際使用過程中發(fā)現(xiàn)并不是那么好用，由于各種原因（追查 bug、臨時(shí)的統(tǒng)計(jì)需求等）我們常常需要登錄到 mysql 服務(wù)器里手動(dòng)執(zhí)行一些 sql 查詢，由于許多表都有狀態(tài)字段，寫 sql 時(shí)必須對(duì)照的 php 代碼里的映射關(guān)系來寫，一不小心還有可能將不同表的狀態(tài)數(shù)字弄混導(dǎo)致大問題。

于是我在新項(xiàng)目中準(zhǔn)備使用 mysql 的 enum 類型來存儲(chǔ)各種狀態(tài)，在使用過程中發(fā)現(xiàn)如果在 Laravel 的 migration 文件中對(duì)使用了 enum 類型的表做變更（即使是變更非 enum 類型的字段）都會(huì)報(bào)錯(cuò)

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

搜索了一下，發(fā)現(xiàn)是 doctrine 不支持 mysql 的 enum，該文中列舉了 enum 的 3 個(gè)缺點(diǎn)：

新增 enum 值的時(shí)候需要重建整個(gè)表，當(dāng)數(shù)據(jù)量大的時(shí)候可能需要耗費(fèi)數(shù)小時(shí)。

enum 值的排序規(guī)則是按創(chuàng)建表結(jié)構(gòu)時(shí)指定的順序，而非字面值的大小。

依賴 mysql 對(duì) enum 值的校驗(yàn)并不是非常必要，在默認(rèn)配置下插入非法值最終會(huì)變成空值。

根據(jù)新項(xiàng)目的實(shí)際情況，不太可能出現(xiàn)需要對(duì)狀態(tài)字段做排序的需求，即使有我們可以在設(shè)計(jì)表結(jié)構(gòu)的時(shí)候就定好順序，因此缺點(diǎn) 2 可以忽略；而缺點(diǎn) 3 則可以通過代碼規(guī)范、插入/更新前校驗(yàn)等方式來規(guī)避；至于缺點(diǎn) 1，我們需要做一些測(cè)試。

測(cè)試準(zhǔn)備#

首先創(chuàng)建一個(gè)表：

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

測(cè)試過程#

測(cè)試1#

在 enum 值列表最后添加一個(gè)值 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 值時(shí)幾乎沒有成本。

測(cè)試 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é)論：刪除一個(gè)沒有用過的 enum 值仍需全表掃描，成本較高，但還在可接受范圍內(nèi)。

測(cè)試 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 值列表中間新增值需要全表掃描并更新，成本較高。

測(cè)試 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é)論：需全表掃描，成本較高。

測(cè)試 5：#

給 status 字段添加索引后再執(zhí)行上述測(cè)試

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

發(fā)現(xiàn)測(cè)試 2-4 的耗時(shí)反而有所增加，應(yīng)該是同時(shí)需要更新索引導(dǎo)致的。

結(jié)語：#

對(duì)于我的新項(xiàng)目來說只會(huì)出現(xiàn)新增 enum 值的情況，即使將來有個(gè)別狀態(tài)廢棄不用也不需要去調(diào)整 enum 的值列表，因此決定在項(xiàng)目中引入 enum 類型作為存儲(chǔ)狀態(tài)的數(shù)據(jù)類型。

您可能感興趣的文章: