V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
samin
V2EX  ›  数据库

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

  •  
  •   samin · 2021-05-10 16:17:36 +08:00 · 1154 次点击
    这是一个创建于 1054 天前的主题,其中的信息可能已经有所发展或是发生改变。

    背景

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

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

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

    数字类型

    请记住所有的数字类型,底层存储都是固定长度的,比如 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 个字节存储长度的代价。

    总结

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

    7 条回复    2021-05-11 09:51:25 +08:00
    chionetw5
        1
    chionetw5  
       2021-05-10 16:45:16 +08:00
    我记得 varchar 是可以直接和 int 比较的 。若 a: varchar(10) = 12345asdfg 则 1234 > a 是 false 1234< a 是 true 。
    这样的话怎么说
    xuanbg
        2
    xuanbg  
       2021-05-10 17:02:16 +08:00
    比较结果和排序有关。简单滴说,数字 2 比 1 大,字符 32 比 123 大
    wpblank
        3
    wpblank  
       2021-05-10 17:42:21 +08:00
    楼上两位没看内容啊
    newtype0092
        4
    newtype0092  
       2021-05-10 17:49:36 +08:00
    所以不用 ENUM 的理由是什么?
    Rache1
        5
    Rache1  
       2021-05-10 19:50:39 +08:00
    @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
        6
    ychost  
       2021-05-10 20:52:45 +08:00
    不建议用数字没枚举,不看代码配置看数据根本很难知道是什么状态,多占用点空间用字符串可以省很多事
    cloudzhou
        7
    cloudzhou  
       2021-05-11 09:51:25 +08:00
    @ychost 和我理解一样,早先极致追求存储性能~现在看,其实很难达到瓶颈,而不好的设计,才是最主要原因
    使用字符串,最大好处是简单易懂,否则要回去看代码
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   2765 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 31ms · UTC 12:17 · PVG 20:17 · LAX 05:17 · JFK 08:17
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.