MySQL的enum類型實例測試

小云云
發布: 2017-11-30 11:05:37
原創
1602 人瀏覽過

什麼是enum? enum是電話號碼映射(E.164 Number URI Mapping)的縮寫。這個縮寫的後面隱藏著一個偉大的創意:即透過最好和最廉價的路由途徑,可以在世界任何地點使用同一個電話號碼。您可以像註冊網域一樣註冊ENUM號碼。

在開發項目時通常會遇到一些狀態字段,例如訂單的狀態有待支付、已支付、已關閉、已退款等,我以前做的項目都是把這些狀態用數字存在數據庫中,然後在php 程式碼中用常數來維護一份映射表,例如:

const STATUS_PENDING = 0;
const STATUS_PAID = 1;
const STATUS_CLOSED = 2;
const STATUS_REFUNDED = 3;
登入後複製

但是在實際使用過程中發現並不是那麼好用,由於各種原因(追查bug、臨時的統計需求等)我們常常需要登入mysql 伺服器裡手動執行一些sql 查詢,由於許多表都有狀態字段,寫 sql 時必須對照的 php 程式碼裡的映射關係來寫,一不小心還有可能將不同表的狀態數字弄混導致大問題。

於是我在新專案中準備使用 mysql 的 enum 類型來儲存各種狀態,在使用過程中發現如果在 Laravel 的 migration 檔案中對使用了 enum 類型的表做變更(即使是變更非enum 類型的字段)都會報錯

[Doctrine\DBAL\DBALException]
Unknown database type enum requested, Doctrine\DBAL\Platforms\MySQL57Platform may not support it.
登入後複製

搜尋了一下,發現是doctrine 不支援mysql 的enum,該文中列舉了enum 的3 個缺點:

新增enum 值的時候需要重建整個表,當資料量大的時候可能需要耗費數小時。

enum 值的排序規則是依照建立表格結構時指定的順序,而非字面值的大小。

依賴 mysql 對 enum 值的校驗並不是非常必要,在預設配置下插入非法值最終會變成空值。

根據新項目的實際情況,不太可能出現需要對狀態欄位做排序的需求,即使有我們可以在設計表結構的時候就定好順序,因此缺點2 可以忽略;而缺點3 則可以透過程式碼規格、插入/更新前校驗等方式來規避;至於缺點 1,我們需要做一些測試。

測試準備

#先建立一個表格:

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 資料:

$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]);
登入後複製

測試流程

#測試1

#在enum 值清單最後加上一個值refunded

ALTER TABLE `enum_tests` CHANGE `status` `status` ENUM(&#39;pending&#39;,&#39;success&#39;,&#39;closed&#39;,&#39;refunded&#39;) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;
登入後複製


#輸出:

Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
登入後複製




結論:末尾追加enum 值時幾乎沒有成本。

測試2:

#刪除剛剛新增的值refunded

ALTER TABLE `enum_tests` CHANGE `status` `status` ENUM(&#39;pending&#39;,&#39;success&#39;,&#39;closed&#39;) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;
登入後複製
登入後複製

輸出:

Query OK, 1000000 rows affected (5.93 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
登入後複製


結論:刪除一個沒有使用過的enum 值仍需全表掃描,成本較高,但仍在可接受範圍內。

測試3:

#將refunded 插入值清單中間而非結尾

ALTER TABLE `enum_tests` CHANGE `status` `status` ENUM(&#39;pending&#39;,&#39;success&#39;,&#39;refunded&#39;, &#39;closed&#39;) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;
登入後複製

   


輸出:

Query OK, 1000000 rows affected (6.00 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
登入後複製

   

結論:在原enum 值清單中間新增值需要全表掃描並更新,成本較高。

測試4:

#刪除值清單中間的值

ALTER TABLE `enum_tests` CHANGE `status` `status` ENUM(&#39;pending&#39;,&#39;success&#39;,&#39;closed&#39;) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;
登入後複製
登入後複製

   

輸出:

Query OK, 1000000 rows affected (4.23 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
登入後複製

結論:需全表掃描,成本較高。

測試5:

#給status 欄位新增索引後再執行上述測試

ALTER TABLE `enum_tests` ADD INDEX(`status`);
登入後複製

發現測試2-4 的耗時反而增加,應該是同時需要更新索引所導致的。

###結語:########對於我的新項目來說只會出現新增enum 值的情況,即使將來有個別狀態廢棄不用也不需要去調整enum 的值列表,因此決定在專案中引入enum 類型作為儲存狀態的資料類型。 ######相關推薦:#########php 中Enum(枚舉)用法詳解############Enum擴充功能實例程式碼###### ######enum資料類型預設值的困惑解決思路#######

以上是MySQL的enum類型實例測試的詳細內容。更多資訊請關注PHP中文網其他相關文章!

相關標籤:
來源:php.cn
本網站聲明
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn
熱門教學
更多>
最新下載
更多>
網站特效
網站源碼
網站素材
前端模板