MySQL 中数字类型和字符类型字段大小比较

[samin]

背景

涉及到有状态变化的数据表会包含一个状态字段，有设计成用数字类型的，也有设计成字符串类型的，其中的利弊各执一词。

本文针对两种类型在存储大小的角度出发，论述两者的差异。

数字类型和字符类型的存储

数字类型

请记住所有的数字类型，底层存储都是固定长度的，比如 smallint 在硬盘据就是占 2 个字节（ 16 bit ）。

DCL 中，出现 smallint(2) 这样的声明，和字符类型代表的意义是不一样的，并不是代表最大可存储长度，仅仅影响展示效果。

假设有一个 smallint(2) 的字段，保存了数字为 1，硬盘中一样是占了 2 个字节，使用 select 查询这条数据会显示 “02”。这是数字类型括号数字声明的作用，在查看的时候，会用 0 去填充实际显示效果。

这种建表方式很鸡肋，一般显示数字效果，会让前端后者服务器端处理，不会通过数据库去实现，所一般的表结构声明不会出现数字类型加括号数字的情况。

字符类型

字符类型的存储有两个概念

• 存储长度

  影响最多可以存储几个字符，每个字符占 1 个字节。也就是说 char(30) 和 varchar(30) 在存储长度方面表达的意义是一样的，都是最多能存储 30 个字节，超过则报错。

• 是否可变

  影响硬盘存储长度。假设两个字段分别为 char(30) 和 varchar(30)，在都是存储 30 个字符的情况下，硬盘存储长度是一样的。假设是存储 20 个字符，则 char 字段用空格填充至 30 个字符，在硬盘是占用 30 个字节；而 varchar 则是采用字符 + 长度的方式，在硬盘中一共占用 21 个字节。

案例分析

有了以上的概念，通过一个实际案例比较来直观比较两者的差异。

业务场景

一个订单系统，有 “下单”、“待发货”、“配送中”、“已完成”、“已评价” 五个状态。

字段设计

用数字类型来存储

使用 tinyint 来存储状态，用 0~4 分别表示这 5 个状态。

用字符类型来存储

使用 varchar(1) 来存储状态，用 '0'、'1'、'2'、'3'、'4' 分别表示这 5 个状态。

比较

在底层存储中，以上两种方案，都是占用 1 个字节，两者在存储方面并无法体现各自的优势。

假设现在为了让状态有语义，把 varchar 长度改成 10，分别用 'order'、'ready'、'delivery'、'done'、'feedback' 来表示这 5 个状态，数字类型在节约空间方面确实体现了优势。

在拓展性方面，tinyint 可以在不修改表结构的前提下可以标识 128 种不同的状态，而 varchar 则需要把 1 个字符长度修改为 3 才能达到 tinyint 的效果，在这基础上还需要付出 3 个字节存储长度的代价。

总结

在状态变迁值不多的情况下，两者的在存储大小方面的优劣几乎无法体现。在状态量递增的情况下，数字类型的状态量记录方式，在硬盘持久化存储方面确实优于字符类型。

[chionetw5]

我记得 varchar 是可以直接和 int 比较的 。若 a: varchar(10) = 12345asdfg 则 1234 > a 是 false 1234< a 是 true 。
这样的话怎么说

[xuanbg]

比较结果和排序有关。简单滴说，数字 2 比 1 大，字符 32 比 123 大

[wpblank]

楼上两位没看内容啊

[newtype0092]

所以不用 ENUM 的理由是什么？

[Rache1]

@newtype0092 不太通用，而且在 MySQL 中，枚举的坑还有一些坑，比如 type enum('0','1','2','3') ，使用 type 的时候必须加单引号, where type='1' ，insert 、update 同理，否则会造成意外情况。

当要改这个 type 时，不能 append，比如新加一个 4，alter 时就要写 type enum('0','1','2','3','4')，而且最好不要在中间删除、添加枚举成员，灵活性相比 tinyint 要差一些

[ychost]

不建议用数字没枚举，不看代码配置看数据根本很难知道是什么状态，多占用点空间用字符串可以省很多事

[cloudzhou]

@ychost 和我理解一样，早先极致追求存储性能～现在看，其实很难达到瓶颈，而不好的设计，才是最主要原因
使用字符串，最大好处是简单易懂，否则要回去看代码