likes
comments
collection
share

MySQL：创建表时如何选择合适的字段类型

作者站长头像
站长
· 阅读数 10

最近需要对表加一个字段，同时觉得前期建立表的时候有点粗暴，没有加很对限制，比如有些字符串长度是有限制的，在创建表时字段也没有对其进行限制。所以想借着这次加字段对表字段也进行一个优化，在优化之前先看了点理论知识，理论指导实践

写在前面

选择合适的字段类型既可以节省空间，又可以在查询上提高效率，因此字段类型选择是很重要的。本篇文章将介绍常用字段类型：

  • 整数类型
  • 实数类型
  • 字符串类型
  • 日期和时间
  • 枚举类型

整数类型

整数类型有TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT，存储空间及数值范围如下表

类型 存储空间(单位为位) 数值范围
TINYINT 8 -128 ~ 127
SMALLINT 16 -32768 ~ 32767
MEDIUMINT 24 -8388608 ~ 8388607
INT 32 -2147483648 ~ 2147483647
BIGINT 64 太大了

数值范围为-2^(N-1) ~ 2^N， 其中N为存储空间大小

整数类型有可选的UNSIGNED属性，不允许出现负值。设置UNSIGNED属性可以使正数的上限提高一倍，数值范围大小为 0 ~ 2^(N-1) + 2^N

一般选择最小的能够满足存储的类型就行，更小的数据类型通常更快，占用更少的磁盘、内存和CPU缓存。处理时需要的CPU周期也更少

实数类型

FLOAT

单精度浮点型，使用8位

DOUBLE

双精度浮点型，使用16位存储

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如何选择

MySQL：创建表时如何选择合适的字段类型

枚举类型

可以使用枚举列代替常用的字符串类型，通过枚举可以限制值的取值范围

枚举使用

创建表语句：

 CREATE TABLE `dataset_enum` (
  `name` varchar(48) DEFAULT NULL,
  `status` enum('NEW','UPLOADING','USING','DELETING') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

插入语句：

insert into dataset_enum(name, status) values("t4", "DELETING")

对于status字段底层存储的是整数而不是字符串，在底层会维护一个 数字 - 字符串的映射关系

查询语句并根据status字段进行排序：

select * from dataset_enum order by status;

查询结果：

+------+-----------+
| name | status    |
+------+-----------+
| t1   | NEW       |
| t2   | UPLOADING |
| t4   | DELETING  |
+------+-----------+

说明：

  • 排序的结果是根据内部存储的整数来的而不是定义的字符串进行排序的
  • 底层存储的是整数，根据映射关系转化为字符串，因此会有一定的开销

为什么使用TINYINT而不用ENUM

之前创建表的时候对于常用字符串的代替选择的都是TINYINT类型，应用层在做转换。当看到ENUM类型时有点困惑，为什么没选择使用ENUM而是TINY，网上查找了一下原因，如下图：

MySQL：创建表时如何选择合适的字段类型

总结原因如下：

  • 不方便迁移，可扩展性弱，如比较熟悉的PostgreSQL数据库就不支持ENUM类型
  • ENUM字段添加或删除字符串时会进行表重构，这个操作非常耗时和耗性能
  • 有坑 以之前的表dataset_enum为例插入数据：
    mysql> insert into dataset_enum values("t1", "NEW"), ("t2", 2);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0
    成功插入了数据 查询数据：
    mysql> select * from dataset_enum;
+------+-----------+
| name | status    |
+------+-----------+
| t1   | NEW       |
| t2   | UPLOADING |
+------+-----------+
    数值类型做转化以后也可以插入
  • 无法与其他表做关联

参考文章： Should I use the datetime or timestamp data type in MySQL? 8 Reasons Why MySQL's ENUM Data Type Is Evil 为什么辣么多人喜欢用 tinyint而不用 enum? 《高性能MySQL》