ALTER TABLE `enum_tests` CHANGE `status` `status` ENUM('pending','success','closed','refunded') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;
输出:
1
2
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
结论:在末尾追加 enum 值时几乎没有成本。
测试 2:#
删除刚刚添加的值 refunded
1
ALTER TABLE `enum_tests` CHANGE `status` `status` ENUM('pending','success','closed') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;
输出:
1
2
Query OK, 1000000 rows affected (5.93 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
结论:删除一个没有用过的 enum 值仍需全表扫描,成本较高,但还在可接受范围内。
测试 3:#
将 refunded 插入到值列表中间而非末尾
1
ALTER TABLE `enum_tests` CHANGE `status` `status` ENUM('pending','success','refunded', 'closed') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;
输出:
1
2
Query OK, 1000000 rows affected (6.00 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
结论:在原 enum 值列表中间新增值需要全表扫描并更新,成本较高。
测试 4:#
删除值列表中间的值
1
ALTER TABLE `enum_tests` CHANGE `status` `status` ENUM('pending','success','closed') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;