MySQL中的Enum，Varchar和Int数据类型之间的区别是什么

Posted on by

在我的实践中，我经常看到人们使用可用选项数量很少且数量有限的字段。varchar

今天，让我们来看看哪种字段数据类型更好，并且将是您项目的最佳方法。

让我们想象一下，我们为CMS设计一个数据库。只要假装我们没有每个角落都有它们中的任何一个，我们需要创造最好的例子。

从我们的数据库开始，我们将从主表帖子开始。我现在省略了这个字段，因为这是我们的博客，除了我们之外，我们没有任何计划添加其他作者;)author

CREATE TABLE post (
    id         INT          NOT NULL AUTO_INCREMENT,
    title      VARCHAR(255) NOT NULL,
    status     ENUM ('archived', 'deleted', 'draft', 'published') DEFAULT 'draft',
    content    TEXT         NOT NULL,
    created_at DATETIME     NOT NULL                              DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME     NOT NULL                              DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY (status)
)
    ENGINE = InnoDB;

我可以想象三种方式来描述这个领域。status

第一个在上面的示例中。

status VARCHAR(255) NOT NULL default 'draft'

下一个将是Enum：

status ENUM ('archived', 'deleted', 'draft', 'published') DEFAULT 'draft',

最后一个是整数：

status TINYINT(1) UNSIGNED NOT NULL DEFAULT '0'

让我们在性能、易用性和对功能更改的支持方面比较这三种变体。为每个选项设置初始分数。

varchar_score = 0
enum_score = 0
integer_score = 0

开发人员/人类的可用性

对于人眼来说，Varchar和Enum方法看起来几乎相同。另一方面，整数不是人类可读的。然后你看到 status=1，你无法通过查看数据库来说明状态是什么，你必须在代码中保留数字和单词之间的映射。

更新分数：

varchar_score += 1
enum_score +=1

排序和查询。

在内部，MySQL使用数字和空来存储Enum的值。

内部值显示值
nullnull
0”
1‘archived’
2‘deleted’
3‘draft’
4‘published’

现在想象你的枚举是这样的：

enum('draft', 'published', 'deleted', 'archived')

数据的排序将基于内部表示 – 首先将变为值，然后是 – , ,  和 .在您意识到其原因之前，此行为第一次令人困惑。枚举中选项的正确顺序应与预期的排序行为匹配。draftdeleteddraftpublished

对于按字母顺序排序，它应该是这样的：

ENUM ('archived', 'deleted', 'draft', 'published')

考虑到这一点，看起来枚举可能会给初学者带来一些问题。但是你只需要记住，枚举只是引擎盖下的一个整数。

空间消耗

小号需要 1 个字节。枚举也是如此 – 它还需要1字节的存储。从技术上讲，它们都可能扩展到2个字节，但我无法想象现实生活中需要超过255个选项的情况。

对于Varchar来说，这有点复杂。这将取决于字符集。例如， 将每个字符占用一个字节，并且每个字符最多可以需要四个字节。加上一个字节来记录字符串的长度。latin1utf8mb4

状态集 、 、 和 将在字符集中占用 6 到 10 个字节。archiveddeleteddraftpublishedlatin1

最终的存储量将取决于值的分布，但无论如何，varchar 将占用更多的空间。

此外，您不仅应考虑数据的存储大小，还应考虑索引的存储大小。而 varchar 的索引也将占用更多空间。

enum_score += 1
integer_score +=1

数据库迁移

这个案例非常简单。对于 varchar 和整数，无需更改架构即可添加新的可能选项。另一方面，向枚举添加新选项将需要对架构进行更改。根据您将如何做到这一点 – 这可能会导致数据库停机，这是不可接受的。

varchar_score += 1
integer_score +=1

查询性能

为了进行一些实验，让我们在Docker容器内启动MySQL数据库并比较结果。

docker run --name blog_mysql -e MYSQL_ROOT_PASSWORD=secret -e MYSQL_DATABASE=blog -d mysql:8
docker exec -it blog_mysql mysql -u root -psecret blog

创建三个非常相似的表。唯一的区别将在于现场。status

CREATE TABLE post_enum (
    id         INT          NOT NULL AUTO_INCREMENT,
    title      VARCHAR(255) NOT NULL,
    status     ENUM ('archived', 'deleted', 'draft', 'published') DEFAULT 'draft',
    content    TEXT         NOT NULL,
    created_at DATETIME     NOT NULL                              DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME     NOT NULL                              DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY (status)
)
    ENGINE = InnoDB
    COLLATE utf8_bin;
    
    
CREATE TABLE post_varchar (
    id         INT          NOT NULL AUTO_INCREMENT,
    title      VARCHAR(255) NOT NULL,
    status     VARCHAR(10)           DEFAULT 'draft',
    content    TEXT         NOT NULL,
    created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY (status)
)
    ENGINE = InnoDB
    COLLATE utf8_bin;

CREATE TABLE post_int (
    id         INT          NOT NULL AUTO_INCREMENT,
    title      VARCHAR(255) NOT NULL,
    status     TINYINT(1)   NOT NULL DEFAULT 0,
    content    TEXT         NOT NULL,
    created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY (status)
)
    ENGINE = InnoDB
    COLLATE utf8_bin;

SHOW TABLES;

+----------------+
| Tables_in_blog |
+----------------+
| post_enum      |
| post_int       |
| post_varchar   |
+----------------+

让我们玩得开心，好吗？没有多少人在做MySQL过程，但让我们尝试一些。

DROP PROCEDURE IF EXISTS add_post_varchar;
DROP PROCEDURE IF EXISTS add_post_enum;
DROP PROCEDURE IF EXISTS add_post_int;

DELIMITER $$

CREATE PROCEDURE add_post_varchar(number_of_posts INT)
BEGIN
    DECLARE loop_counter INT UNSIGNED DEFAULT 0;
    DECLARE post_content TEXT DEFAULT 'Lorem Ipsum is simply dummy text of the printing and typesetting industry.';
    DECLARE post_title VARCHAR(255) DEFAULT 'Lorem Ipsum';

    WHILE loop_counter < number_of_posts
        DO
            INSERT
            INTO
                post_varchar (title, status, content)
            SELECT
                post_title,
                ELT(0.5 + RAND() * 4, 'archived', 'deleted', 'draft', 'published'),
                post_content;

            SET loop_counter = loop_counter + 1;
        END WHILE;
END $$

CREATE PROCEDURE add_post_enum(number_of_posts INT)
BEGIN
    DECLARE loop_counter INT UNSIGNED DEFAULT 0;
    DECLARE post_content TEXT DEFAULT 'Lorem Ipsum is simply dummy text of the printing and typesetting industry.';
    DECLARE post_title VARCHAR(255) DEFAULT 'Lorem Ipsum';

    WHILE loop_counter < number_of_posts
        DO
            INSERT
            INTO
                post_enum (title, status, content)
            SELECT
                post_title,
                ELT(0.5 + RAND() * 4, 'archived', 'deleted', 'draft', 'published'),
                post_content;

            SET loop_counter = loop_counter + 1;
        END WHILE;
END $$
CREATE PROCEDURE add_post_int(number_of_posts INT)
BEGIN
    DECLARE loop_counter INT UNSIGNED DEFAULT 0;
    DECLARE post_content TEXT DEFAULT 'Lorem Ipsum is simply dummy text of the printing and typesetting industry.';
    DECLARE post_title VARCHAR(255) DEFAULT 'Lorem Ipsum';

    WHILE loop_counter < number_of_posts
        DO
            INSERT
            INTO
                post_int (title, status, content)
            SELECT
                post_title,
                ELT(0.5 + RAND() * 4, '1', '2', '3', '4'),
                post_content;

            SET loop_counter = loop_counter + 1;
        END WHILE;
END $$

DELIMITER ;

并在每个表中创建 200 000 条记录。

call add_post_int(200000);
call add_post_enum(200000);
call add_post_varchar(200000);

SELECT COUNT(*), status FROM post_enum GROUP BY status
UNION
SELECT COUNT(*), status FROM post_int GROUP BY status
UNION
SELECT count(*), status FROM post_varchar GROUP BY status;

+----------+-----------+
| COUNT(*) | status    |
+----------+-----------+
|    50058 | archived  |
|    50229 | deleted   |
|    50273 | draft     |
|    49440 | published |
|    50123 | 1         |
|    49848 | 2         |
|    49642 | 3         |
|    50387 | 4         |
|    49885 | archived  |
|    49974 | deleted   |
|    50060 | draft     |
|    50081 | published |
+----------+-----------+

现在我们的分布几乎均匀。

让我们运行一些查询。首先，启用分析：

SET profiling=1;

接下来运行查询，该查询将仅使用索引来获取数据，而不会转到堆。

SELECT SQL_NO_CACHE COUNT(*), status FROM post_enum GROUP BY status;
SELECT SQL_NO_CACHE COUNT(*), status FROM post_varchar GROUP BY status;
SELECT SQL_NO_CACHE COUNT(*), status FROM post_int GROUP BY status;

最后，让我们检查一下我们的表现。

Query_IDDuration查询
10.05163150SELECT SQL_NO_CACHE COUNT(*), status FROM post_enum GROUP BY status
20.05172150SELECT SQL_NO_CACHE COUNT(*), status FROM post_varchar GROUP BY status
30.05893025SELECT SQL_NO_CACHE COUNT(*), status FROM post_int GROUP BY status

每个场景都以相同的速度工作。更新分数：

varchar_score += 1
enum_score += 1
integer_score += 1

结论

我们的最终得分是：

选项得分
integer_score3
varchar_score3
enum_score3

事实证明，我们所有的分数都是一样的。我没想到一开始会这样。老实说，我希望枚举能赢得这场战斗。但是枚举在排序中有一些特定的行为，如果你知道你的工具，这没什么大不了的，但它可能会让初学者感到困惑。

枚举在人性化和占用更少空间方面具有优势。考虑到所有这些，我建议对这样的选项使用枚举。

愿快速查询与您同在;)

推荐阅读：