MySQL数据类型

数据类型的选取原则:

• 更小的通常更好:尽量使用可以正确存储数据的最小数据类型(它们占用更少的磁盘,内存,cpu缓存,处理时需要的cpu周期也更少)

• 简单:简单数据类型的操作通常所需cpu周期更少,例如整型就是比字符串类型的操作代价更低

• 尽量避免NULL:最好指定列为NOT NULL,如果查询中包含可为NULL的列对MySQL来说更难优化

  • 因为这使得索引,索引统计和值比较都更为复杂
  • 可为NULL的列会使用更多的存储空间,在MySQL里也需要特殊处理
  • 当可为NULL的列被索引时,每个索引记录需要一个额外的字节
  • 通常把可为NULL的列改为NOT NULL带来的性能提升比较小,所以调优时没有必要优先改掉这种情况
  • 如果计划在该列建立索引,就应该避免设计成可为NULL的列;InnoDB使用单独的位(bit)存储NULL值

• 选择具体类型:很多数据类型可以存储相同类型的数据,只是存储的长度和范围不一样,精度不同,所需空间不同

  • DATETIME和TIMESTAMP都可以存储相同类型的数据:时间和日期,精确到秒

    • TIMESTAMP只使用DATETIME一半的存储空间,并会根据时区变化,具有特殊的自动更新能力

    • TIMESTAMP允许的时间范围小得多,有时候它的特殊能力也会成为障碍

      MySQL的timestamp类型与时区有关。 timestamp类型存放的时间值是带时区信息的。当您在timestamp列中存储或检索值时,MySQL会将该值从客户端当前时区转换为UTC(世界标准时间)再存储。当您读取timestamp值时,MySQL会再将其从UTC转换为客户端当前时区的值再返回。所以,如果客户端与服务器的时区不同,在存储和读取timestamp值时会存在时区转换,可能导致值发生变化。

      所以,timestamp列的值在跨时区使用时需要注意这个时区转换的影响。如果要存储一个绝对时间,不需要转换,可以:

      1. 将所有客户端设置为使用UTC时区。
      2. 在创建表时为timestamp列指定zerofill选项和UTC时区:

整数类型

有两种类型的数字:整数(whole number)和实数(real number)

整数

• 值范围在-2的(N-1)次方到2(N-1)次方-1,其中N是存储空间的位数
• 整数有UNSIGNED属性,表示不允许负值,可将正数的上限提高一倍
• 类型的选择可以决定MySQL是怎样在内存和磁盘中保存数据的,但整数计算一般使用64位bigint整数(一些聚合函数例外,它们使用decimal或double进行计算)
• MySQL可为整数类型指定宽度,例如INT(11)大多数应用这是没有意义的,它不会限制值的合法范围,只是规定了一些交互工具用来显示字符的个数,对于存储和计算来说INT(1)和INT(20)是相同的

实数类型

• 实数是带有小数的数字,然而它们不只是为了存储小数部分,也可以使用DECIMAL存储比BIGINT还大的整数
• MySQL既支持精确类型,也支持不精确类型,FLOAT和DOUBLE类型支持使用标准的浮点运算进行近似计算,DECIMAL类型用于存储精确小数
• CPU不支持对DECIMAL的直接计算,相比之下CPU支持浮点计算所以浮点运算明显更快
• 浮点和DECIMAL类型都可指定精度,对于DECIMAL列可指定小数点前后所允许的最大位数,这会影响列的空间消耗
• 在MySQL5.0及以上版本中将数字打包到一个二进制字符串中(每4个字节存9个数字,小数点一个字节)
• MySQL5.0及更高版本中DECIMAL最多允许65个数字,DECIMAL只是一种存储格式,计算时仍会转换为DOUBLE类型
• 浮点类型在存储同样范围的值时通常比DECIMAL使用更少的空间,FLOAT是4个字节,DOUBLE是8个字节
• 尽量在需要对小数进行精确计算时才使用DECIMAL,例如财务数据
• 在数据量比较大时可考虑使用BIGINT代替DECIMAL将所需数值乘以相应的倍数即可,这样可以避免浮点计算不精确和DECIMAL计算代价太高的问题

字符串类型

VARCHAR和CHAR类型

这些值怎么存储的与存储引擎的具体实现有关,这里以InnoDB为例

存储引擎存储CHAR和VARCHAR值的方式在内存中和在磁盘上可能不一样,所以MySQL服务器从存储引擎读出的值可能需要转换为另一种存储格式

字符串长度定义不是字节数而是字符数,存储所需空间与字符集有关

• MySQL的utf8mb4字符集中,字母a只需要占用1个字节的存储空间。
• utf8mb4字符集是MySQL支持的最全的UTF-8编码字符集,它可以支持存储4字节的Unicode字符,因此最大字符长度是4字节。
• 但是基本的ASCII字符,包括大小写英文字母,数字和常用标点符号,只需要占用1个字节。

VARCHAR

• VARCHAR类型存储可变长字符串,比定长更节省空间;有一种情况例外(表使用ROW_FORMAT=FIXED)
• VARCHAR需要使用1或2个额外字节记录字符串长度,如果列最大长度小于等于255字节使用1字节,否则2字节
• VARCHAR节省了空间,由于可变长,在update时可能使得行比原来更长,如果页内没有更多空间则需要分裂页来使行放到页内
• 使用VARCHAR合适的场景:
  • 字符串列的最大长度比平均长度大得多
  • 列更新很少,所以碎片不是问题
  • 使用了UTF-8这种复杂字符集,每个字符都使用不同的字节数存储
  • InnoDB更灵活,它可以把过长的VARCHAR存储为BLOB

CHAR

• 定长的,总是根据定义的长度分配空间
• 存储char时MySQL会删除所以末尾空格,char值会根据需要采用空格进行填充以方便比较
• 适合存储很短的字符串,或者所有值都接近一个长度,例如MD5
• 经常变更的数据char不容易产生碎片
• 非常短的列char也更有效率,例如char(1)只有Y和N值,如果采用单字节字符集只需要1个字节,而VARCHAR却需要2字节,因为还有一个字节存储额外长度
• char值如果末尾存在空格,查询时会被MySQL服务器层截取,而varchar类型则不会

使用VARCHAR(5)和VARCHAR(1000)存储字符串'hello'的空间开销是一样的,那么使用更短的列有什么优势?

优势很大,更长的列会消耗更多的内存,MySQL通常会分配固定大小的内存,尤其是使用内存临时表进行排序或操作时,在利用磁盘临时表进行排序也同样很糟糕

最好的策略是只分配真正需要的空间

BINARY和VARBINARY

• 它们存储二进制字符串,它们存储的是字节码而不是字符,填充也不一致,BINARY采用的是\0(零字节)而不是空格,在检索时也会去掉填充值
• 当需要存储二进制数据并希望MySQL使用字节码而不是字符进行比较时这些类型很有用
• 二进制比较的优势不仅在大小写敏感上,MySQL比较BINARY字符串时每次按一个字节并且根据该数值比较,简单且更快

BLOB和TEXT类型

• BOLB和TEXT都是存储很大的数据而设计的,分别采用二进制和字符方式存储
• 与其他类型不同,MySQL把这两类当作一个独立的对象处理,存储引擎也会特殊处理,当值特别大时会有专门的外部区域来进行存储,此时每个值在行内需要1~4个字节存储一个指针,然后在外部区域存储实际值;
• 二者仅有的不同是BLOB存储的是二进制数据,没有排序规制和字符集
• MySQL对其排序处理也与其他类型不同,它只针对每个列最前max_sort_length字节而非整个字符串做排序,可通过max_sort_length配置或者使用ORDER BY SUSTRING(column, length)来确定排序的长度
• MySQL不能将BLOB和TEXT列全部长度的字符串进行索引,也不能使用这些索引消除排序

枚举ENUM

• 有时可以使用枚举代替常用的字符串类型,枚举列可以把一些不重复的字符串存储成一个预定义的集合
• MySQL存储枚举时非常紧凑,会根据列表值的数量压缩到一两个字节中
• MySQL在内部会将每个值在列表中的位置保存为整数,并在表的.frm文件中保存数字-字符串的映射表
• 枚举字段是按照内部存储的整数排序的而不是字符串
  • 按照所需顺序来定义枚举列
  • 使用field函数显示指定排序顺序,但这会导致MySQL无法利用索引
• 枚举字符串列表是固定的,如果需要新增,则必须使用alter table

日期和时间类型

MySQL可以使用许多类型来保存日期和时间值,MySQL能存储的最小时间粒度是秒,(MariaDB支持微秒级别的时间类型),但是MySQL也可以使用微秒级的粒度进行临时计算

DATETIME

• 可以保存大范围值: 1001~9999年,8字节存储,时区无关,把日期和时间封装到YYYYMMDDHHMMSS的整数中
• 默认情况下MySQL以一种可排序的,无歧义的格式显示DATETIME值,例如:"2023-05-27 11:21:28",这是ANSI标准定义的时间表示法

TIMESTAMP

• 时间戳,保存了1970年1月1日(格林尼治标准时间)以来的秒数,与UNIX时间戳相同
• 使用4字节存储空间,范围较小,只能表示1970~2038年
• 显示值依赖时区
• 插入时如果未指定默认为当前时间,默认也是NOT NULL,更新时默认会更新第一个TIMESTAMP列的值除非update语句明确指定了该列的值
• 可使用BIGINT存储微秒级别的时间戳,或者使用DOUBLE存储秒之后的小数部分

位数据类型

位存储类型技术上还是字符串类型

BIT

• 5.0版本之前BIT是TINYINT的同义词,之后是完全不同的两种类型了
• BIT存储一个或多个true/false值,最大长度BIT(64)
• MySQL把BIT当作字符串类型,而非数字类型,但在数字上下文场景下位字符会转为数字,尽量避免使用BIT类型
• 如果想使用bit空间存储false/true值可使用char(0),可保存NULL或者长度为0的字符串(空字符串)

SET

• 如果需要保存很多true/false可以考虑合并这些列到SET数据类型,它在MySQL内部是以一系列打包的位的集合来表示,可有效利用存储空间
• 太小众了,用到再说吧,暂时不想了解这个了....

特殊类型数据

• 低于秒级精度的时间戳(参见上文)
• IPv4地址:通常使用VARCHAR(15)存储,它实际是32位无符号整数,不是字符串,小数点分4段仅是方便阅读,MySQL提供了INET_ATON()和INET_NTOA()函数来转换两种表示方法