云栖号资讯：【点击查看更多行业资讯】
在这里您可以找到不同行业的第一手的上云资讯，还在等什么，快来！
最近需要对表加一个字段，同时觉得前期建立表的时候有点粗暴，没有加很对限制，比如有些字符串长度是有限制的，在创建表时字段也没有对其进行限制。所以想借着这次加字段对表字段也进行一个优化，在优化之前先看了点理论知识，理论指导实践。
写在前面
选择合适的字段类型既可以节省空间，又可以在查询上提高效率，因此字段类型选择是很重要的。本篇文章将介绍常用字段类型：
- 整数类型
- 实数类型
- 字符串类型
- 日期和时间
- 枚举类型
整数类型
整数类型有TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT，存储空间及数值范围如下表
数值范围为-2^(N-1) ~ 2^N， 其中N为存储空间大小
整数类型有可选的UNSIGNED属性，不允许出现负值。设置UNSIGNED属性可以使正数的上限提高一倍，数值范围大小为 0 ~ 2^(N-1) + 2^N
一般选择最小的能够满足存储的类型就行，更小的数据类型通常更快，占用更少的磁盘、内存和CPU缓存。处理时需要的CPU周期也更少。
实数类型
FLOAT
单精度浮点型，使用8位
DOUBLE
双精度浮点型，使用16位存储
DECIMAL
float和double进行计算时会发生精度损失，需要精度计算的时候可以使用DECIMAL，使用DECIMAL需要额外的空间和计算开销，因此当且仅当需要精度计算时才使用。
字符串类型
1. VARCHAR和CHAR
varchar和char是非常非常常用的字符串类型
VARCHAR
VARCHAR用于存储变长字符串，使用该类型存储字符串时需要额外使用1或2个额外字节记录字符串的长度：
- 列的最大长度小于或等于255 => 使用1字节
- 列的长度大于255 => 使用2字节
适用VARCHAR作为存储类型的场景：
- 列更新很少 => 列经常更新容易产生页分裂
- 列长度非固定 => VARCHAR存储时只使用必要空间，因此会省空间
CHAR
CHAR用于存储定长字符串，在存储CHAR类型时，会删除所有的末尾空格
使用CHAR最为存储类型的场景
- 列几乎定长
- 列长度很短 => VARCHAR需要额外字节存储长度
- 列经常更新
2. BLOB和TEXT类型
BLOB和TEXT类型都是用来存储很大的数据，比如文章内容这些
BLOB
采用二进制方式存储， BLOB细分又可以分为TINYBLOB，SMALLBLOB，BLOB，MEDIUMBLOB， LONGBLOB
TEXT
采用字符方式存储，TEXT细分又可以分为TINYTEXT，SMALLTEXT，TEXT，MEDIUMTEXT， LONGTEXT
当BLOB和TEXT值太大时，InnoDB存储会使用外部存储区域来存储值，然后保存一个1~4字节的指针指向外部存储
日期和时间类型
常用的日期类型有DATETIME和TIMESTAMP
DATETIME
使用8字节存储，可以保存大范围的值，从1001~9999年
TIMESTAMP
使用4字节存储，保存范围比DATETIME小，从1970~2038年
对于需要存储更小粒度的日期和时间可以使用DOUBLE或BIGINT，当然不是存储小粒度也可以使用BIGINT
DATETIME和TIMESTAMP如何选择
之前曾因为时间类型搞出过线上慢查询，这篇文章记录了慢查询原因：很高兴！终于踩到了慢查询的坑， 对于需要对时间进行范围查找、排序、分组等操作之类的建议使用BIGINT，如果对时间类型字段没有任何操作，建议使用TIMESTAMP。
在stackoverflow下找到如下：
枚举类型
可以使用枚举列代替常用的字符串类型，通过枚举可以限制值的取值范围
枚举使用
创建表语句：
插入语句：
对于status字段底层存储的是整数而不是字符串，在底层会维护一个 数字 - 字符串的映射关系
查询语句并根据status字段进行排序：
查询结果：
说明：
- 排序的结果是根据内部存储的整数来的而不是定义的字符串进行排序的
- 底层存储的是整数，根据映射关系转化为字符串，因此会有一定的开销
为什么使用TINYINT而不用ENUM
之前创建表的时候对于常用字符串的代替选择的都是TINYINT类型，应用层在做转换。当看到ENUM类型时有点困惑，为什么没选择使用ENUM而是TINY，网上查找了一下原因，如下图：
总结原因如下：
- 不方便迁移，可扩展性弱，如比较熟悉的PostgreSQL数据库就不支持ENUM类型
- ENUM字段添加或删除字符串时会进行表重构，这个操作非常耗时和耗性能
- 有坑 以之前的表dataset_enum为例插入数据：
成功插入了数据 查询数据：
数值类型做转化以后也可以插入
- 无法与其他表做关联
【云栖号在线课堂】每天都有产品技术专家分享！
课程地址：https://yqh.aliyun.com/live立即加入社群，与专家面对面，及时了解课程最新动态！
【云栖号在线课堂 社群】https://c.tb.cn/F3.Z8gvnK
原文发布时间：2020-05-08
本文作者：何甜甜在吗
本文来自：“掘金”，了解相关信息可以关注“掘金”
