MySQL中的数据类型

小岛秀夫
59 阅读3分钟

这是我参与11月更文挑战的第4天，活动详情查看：11月更文挑战

在建表时我们需要认真考虑每个字段应该使用什么数据类型来保证数据库的性能。通常来讲，选择数据类型有以下三个原则

  1. 尽量选择能正确存储数据的最小数据类型
  2. 尽量选择能正确存储数据的简单数据类型
  3. 避免NULL值，如果允许为NULL则需要额外一个字节进行存储

整数类型

MySQL提供了以下整数类型，其存储为范围在 [−2(N−1),2(N−1)−1][-2^{(N-1)}, 2^{(N-1)}-1]，如果将其设定为unsigned无符号整数，则其存储整数的范围会增加一倍。

数据类型存储
TINYINT1Byte
SMALLINT2Byte
MEDIUMINT3Byte
INT4Byte
BIGINT8Byte

另外，INT(1)和INT(20)仅在显示上有区别，其存储和计算都是一样的。

实数类型

MySQL支持精确的实数类型DECIMAL和不精确的实数类型FLOAT、DOUBLE。FLOAT（4字节）和DOUBLE（8字节）类型支持使用标准的浮点运算进行近似计算，由CPU支持计算更加快速。DECIMAL的计算由MySQL服务器支持，定义方式为DECIMAL(M,N)，表示一共存储M个数字，小数点后保存N个数字。由于小数点占用一个字节，因此存储空间=F(log2(M−N))+F(log2(N))+1F(log_2(M-N))+F(log_2(N))+1个字节，，F函数为位数转换字节数，例如DECIMAL(18,9)使用4个字节存储前9个数字，4个字节存储后9个数字，1个字节存储小数点，共计9字节。

字符串类型

VARCHAR与CHAR类型

VARCHAR和CHAR是两种最主要的字符串类型。

VARCHAR是可变长度的，使用VARCHAR(N)定义最大长度，注意这个最大长度是优先的，由于行的最大长度是216−12^{16}-1个字节，减去用于存储长度的2个字节（根据需要用1个或2个字节存储长度），最大长度为65533个字节（如果是DEFAULT NULL，那么最大长度为65532个字节）。VARCHAR适用场景为

  1. 最大长度比平均长度大很多
  2. 对应列更新的频率低
  3. 使用UTF-8字符集，每个字符的字节数不同

CHAR是定长的，MySQL根据定义的长度分配足够的空间，末尾使用空格补齐，在查询时删除所有的末尾空格。CHAR适用的场景为

  1. 存储较短的字符串
  2. 字符串的值接近同一个长度
  3. 经常变更的数据（不容易产生碎片）

BLOB和TEXT类型

BLOB和TEXT被设计出来主要用于存储大字符串，InnoDB使用外部存储区域进行存储，每个值在行内需要1~4个字节存储对应指针。在排序时，仅对每列最前max_sort_length字节排序，二者的唯一区别是BLOB存储二进制，没有排序规则和字符集，而TEXT有排序规则和字符集。

日期和时间类型

注意MySQL支持的最小粒度为秒，如果要到微秒需要考虑BIGINT存储时间戳，MySQL支持两种日期类型：

DATETIME

  • 范围：从1001到9999年
  • 存储格式：封装到格式为YYYYMMDDHHMMSS的整数中，和时区无关，使用8个字节存储 TIMESTAMP
  • 范围：1970至2038年
  • 存储格式：4个字节存储的时间戳，显示依赖于时区
avatar
文章
阅读
粉丝