数据库状态字段int vs varchar

在开发中，经常遇到需要在数据库中存储状态类型的字段，例如：性别、订单状态等。常用的存储方式是使用int类型（当然，由于状态数量有限，使用tinyint存储足够了，占用空间也会更少）建立映射关系，如：0-男，1-女，2-未知。这样的做法比起直接存储字符串考虑的更多是性能更好、空间占用更小，然而却会牺牲可读性，那么int跟varchar相比，优势真的大到值得牺牲可读性、便利性吗？还是说这只是人云亦云的谣传？下面做一些测试。

一、数据准备

建表

-- 性别字段使用数字映射存储的用户表
create table user_int
(
    id       int auto_increment comment '主键'
        primary key,
    username varchar(32)            not null comment '用户名',
    passwd   varchar(32)            not null comment '密码',
    phone    varchar(16) default '' not null comment '手机号',
    id_card  varchar(32)            not null comment '身份证号',
    gender   int                    not null comment '性别（0-男，1-女，2-未知）',
    constraint uidx_user_username
        unique (username)
)
    comment '用户表';

-- 性别字段使用英文字符串存储的用户表
create table user_varchar
(
    id       int auto_increment comment '主键'
        primary key,
    username varchar(32)            not null comment '用户名',
    passwd   varchar(32)            not null comment '密码',
    phone    varchar(16) default '' not null comment '手机号',
    id_card  varchar(32)            not null comment '身份证号',
    gender   varchar(16)            not null comment '性别（male-男，female-女，unknown-未知）',
    constraint uidx_user_username
        unique (username)
)
    comment '用户表';

模拟数据

-- 信任存储函数创建者不会创建写入二进制日志引起不安全事件的存储函数，打开后才能创建存储函数
set global log_bin_trust_function_creators=TRUE;

-- 指定mysql解释器命令行的结束符为$$
DELIMITER $$
-- 创建函数，模拟100万条性别字段为int的用户表数据
CREATE FUNCTION `mock_int_data`()
    RETURNS INT
BEGIN
    DECLARE num INT DEFAULT 1000000;
    DECLARE i INT DEFAULT 0;
    WHILE i < num
        DO
            INSERT INTO user_int(username, passwd, phone, id_card, gender)
            VALUES (CONCAT('用户', i), '123456', CONCAT('18800000000'), '387532198302132381',
                    floor(rand() * 3));
            SET i = i + 1;
        END WHILE;
    RETURN i;
END $$
DELIMITER ;
-- 执行模拟函数
select mock_int_data();

DELIMITER $$
-- 创建获取随机性别（male,female,unknown）的函数
-- 将每个选项用空格补充到等长，则每个选项的开头坐标依次为1、8、15，
-- 接着取随机数1、8、15，用substring函数截取选项，并去除空格，返回后即是随机的性别
create function gender() 
    returns varchar(7)
begin
    declare gender varchar(100) default "   male femaleunknown"; -- 1,8,15
    declare idx int;
    declare ret varchar(7);
    set idx = 1+floor(rand()*3)*7;
    set ret = trim(substring(gender,idx,7));
    return ret;
end $$

-- 创建函数，模拟100万条性别字段为varchar的用户数据
CREATE FUNCTION `mock_varchar_data`()
    RETURNS INT
BEGIN
    DECLARE num INT DEFAULT 1000000;
    DECLARE i INT DEFAULT 0;
    WHILE i < num
        DO
            INSERT INTO user_varchar(username, passwd, phone, id_card, gender)
            VALUES (CONCAT('用户', i), '123456', CONCAT('18800000000'), '387532198302132381',gender());
            SET i = i + 1;
        END WHILE;
    RETURN i;
END $$
DELIMITER ;
-- 执行模拟函数
select mock_varchar_data();

二、开启sql执行时间记录

-- 查看当前会话中SQL语句执行的资源消耗情况
show profiles;
-- profiles为空，查看profiling选项是否打开
show variables like 'profiling';
-- 打开profiling
set profiling=1;
-- 查看是否打开成功
show variables like 'profiling';
-- 再次查看profiles，此时出现记录
show profiles;

show profiles

三、测试

空间占用
- 100万数据，varchar比int多4MB，2.78%
全表查询
- user_int、user_varchar分别执行10次全表查询，取平均值，int比varchar快0.0299%
```
select * from user_int;
select * from user_varchar;
show profiles;
```

类型	第1次	第2次	第3次	第4次	第5次	第6次	第7次	第8次	第9次	第10次	平均值
int	5.4063	5.3312	5.2880	5.3517	5.3138	5.4558	5.3152	5.3192	5.3173	5.3594	5.3458
varchar	5.3063	5.4175	5.3064	5.3649	5.3347	5.3200	5.3425	5.3827	5.3405	5.3582	5.3474

条件查询
- user_int、user_varchar每种性别查询3次，取平均值，varchar比int快1.08%
```
select * from user_int where gender = 0;
select * from user_varchar where gender = 'male';
```

类型	第1次	第2次	第3次	第4次	第5次	第6次	第7次	第8次	第9次	第10次	平均值
int	3.6277	3.4822	3.5490	3.5368	3.6479	3.5561	3.5794	3.6387	3.4996	3.4862	3.5604
varchar	3.8676	3.4183	3.4375	3.4480	3.5101	3.4837	3.6666	3.4817	3.4619	3.4437	3.5219

四、500万数据

空间占用
- 500万数据，varchar比int多16MB，2.53%
- 500万数据，vachar比tinyint多32MB，5.19%
全表查询，int比varchar快1.62%

类型	第1次	第2次	第3次	第4次	第5次	平均值
int	35.7578	27.0466	27.3853	28.3764	26.9878	29.1108
varchar	37.4828	27.6771	27.6627	27.4923	27.6368	29.5903

条件查询，int比varchar快2.03%

类型	第1次	第2次	第3次	平均值
int	16.9604	16.7249	16.7816	16.8223
varchar	17.0741	17.0718	17.3679	17.1713

五、总结

100万级别的数据下，int和varchar性能差距几乎可以忽略不计，空间varchar占用多2.78%；
500万级别的数据下，全表查询int比varchar快1.62%，条件查询int比varchar快2.03%，空间占用varchar比int多2.53%，varchar比tinyint多5.19%；
可以看出，100万级别的数据不需要考虑int跟varchar的性能差距，500万级别的数据下int有一点点优势，然而优势并不明显。因此，个人认为在数据量不是特别大（千万级）的情况下完全可以使用varchar存储状态类型的字段，提高可读性、便利性。

参考文章：MySQL中int、char、varchar的性能浅谈、MySQL简单三步模拟创建百万数据、如何快速在mysql中生成大量Mock数据、「MySQL系列」分析SQL执行时间及查询执行计划

目录CONTENT