登录  /  注册

MySQL的enum类型实例测试

小云云
发布: 2017-11-30 11:05:37
原创
1360人浏览过

什么是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中文网其它相关文章!

智能AI问答
PHP中文网智能助手能迅速回答你的编程问题,提供实时的代码和解决方案,帮助你解决各种难题。不仅如此,它还能提供编程资源和学习指导,帮助你快速提升编程技能。无论你是初学者还是专业人士,AI智能助手都能成为你的可靠助手,助力你在编程领域取得更大的成就。
来源:php中文网
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn
作者最新文章
最新问题
热门教程
更多>
最新下载
更多>
网站特效
网站源码
网站素材
前端模板
关于我们 免责申明 意见反馈 讲师合作 广告合作 最新更新
php中文网:公益在线php培训,帮助PHP学习者快速成长!
关注服务号 技术交流群
PHP中文网订阅号
每天精选资源文章推送
PHP中文网APP
随时随地碎片化学习
PHP中文网抖音号
发现有趣的

Copyright 2014-2024 https://www.php.cn/ All Rights Reserved | php.cn | 湘ICP备2023035733号