MySQL数据类型

1 字段类型

1.1 整型

• MySQL中可以表示整型数据的类型有5种：TINYINT， SMALLINT，MEDIUMINT，INT和BIGINT，分别占用1、2、3、4、8个字节。
• 所有类型都可以设置成unsigned。
• 需要注意的是INT(11)这类描述中的“11”指的是交互工具中显示宽度为11，不会对数据占用空间和表示范围造成任何影响。

1.2 浮点数

FLOAT 和 DOUBLE 为浮点类型，DECIMAL 为高精度小数类型。CPU 原生支持浮点运算，但是不支持 DECIMAl 类型的计算，因此 DECIMAL 的计算比浮点类型需要更高的代价。

• float(M, D)和double(M, D)，分别占用4个和8个字节

  • M表示总位数，如果插入数据的位数超过M，则报错。
  • D表示小数点后的精度，如果插入数据的精度超过D，则四舍五入插入。

• decimal(M, D)，占用空间取决于M和D，如果M>D，为M+2否则为D+2

  • float、double类型存在精度丢失问题，即写入数据库的数据未必是插入数据库的数据，而decimal无论写入数据中的数据是多少，都不会存在精度丢失问题。因为decimal是以字符串形式进行保存的；

  为什么float/double可能存在精度丢失问题？

  因为计算机存储数据都是要先转化成二进制再存储，而将小数转成二进制有时候可能得到一个无穷数，这时候计算机就会对其进行截断保存（mysql中float截取前32位，double截取前64位）。

  • decimal(M,D)的规则和float/double相同，但区别在float/double在不指定M、D时默认按照实际精度 （由计算机硬件和操作系统决定） 来处理而decimal在不指定M、D时默认为decimal(10, 0)。

1.3 字符串

• char：固定长度字符串， 其长度范围为0 ~ 255个字符且与编码方式无关，无论字符实际长度是多少，都会按照指定长度存储，不够的用空格补足；

• varchar(n)：为可变长度字符串，在utf8编码的数据库中其长度范围为0 ~ 21844个字符，在gbk编码下长度范围为0~32765。

  不同编码下长度范围是咋算的？

  • MySQL要求一个行的定义长度不能超过65535即64K；
  • 对于未指定varchar字段not null的表，会有1个字节专门表示该字段是否为null；
  • varchar(M)，当M范围为0<=M<=255时会专门有一个字节记录varchar型字符串长度，当M>255时会专门有两个字节记录varchar型字符串的长度，把这一点和上一点结合，那么65535个字节实际可用的为65535-3=65532个字节；
  • 所有英文无论其编码方式，都占用1个字节，但对于gbk编码，一个汉字占两个字节，因此最大M=65532/2=32766；对于utf8编码，一个汉字占3个字节，因此最大M=65532/3=21844，上面的结论都成立；
  • 举一反三，对于utfmb4编码方式，1个字符最大可能占4个字节，那么varchar(M)，M最大为65532/4=16383，可以自己验证一下

  注： 上面是表中只有varchar型数据的情况，如果表中同时存在int、double、char这些数据，需要把这些数据所占据的空间减去，才能计算varchar(M)型数据M最大等于多少。

• BLOB和TEXT

  • BLOB和TEXT都是为存储很大的数据而设计的数据类型，分别采用二进制和字符方式存储，前者主要用于存储图片、音频等二进制文件。
  • 与其他类型不同，MySQL把每个BLOB和TEXT值当做一个独立的对象去处理。当BLOB和TEXT值太大时，InnoDB会使用专门的“外部”存储区域来进行存储，此时每个值在行内需要1~4个字节存储一个指针，然后在外部存储区域存储实际的值。
  • 它只对每个列的最前max_sort_length个字节而不是整个字符串做排序。同样的，MySQL也不能将BLOB或TEXT列全部长度的字符串进行索引。

• Text V.S varchar

  两者之间的联系在于： 当varchar(M)的M大于某些数值时，varchar会自动转为text。当然两个也存在区别：

  • 单行64K空间，varchar只能利用63352个字节，但是text可以利用全部65535个；
  • text可以指定text(M)，但是没有任何作用；
  • text不允许有默认值，varchar可以。

1.4 时间和日期

MySQL中表示时间的类型有date，time，year，datetime和timestamp，分别占用3，3，1，8，4个字节。其中date、time和year不带具体小时分钟等，因此datetime和timestamp最常使用。下面对比下这两种类型：

• datetime和timestamp存储日期的格式都是“yyyy-MM-dd HH:mm:ss”，前者8个字节，后者4个字节。
• datetime与timestamp能存储的时间范围也不同，datetime的存储范围为1000-01-01 00:00:00——9999-12-31 23:59:59，timestamp存储的时间范围为19700101080001——20380119111407。
• datetime默认值为空，当插入的值为null时，该列的值就是null；timestamp默认值不为空，当插入的值为null的时候，mysql会取当前时间。
• datetime存储的时间与时区无关，timestamp存储的时间及显示的时间都依赖于当前时区。

1.5 类型的选择

1. char和varchar

   VARCHAR是最常见的字符串类型。VARCHAR节省了存储空间，所以对性能也有帮助。但是，由于行是可变的，在UPDATE时可能使行变得比原来更长，这就导致需要做额外的工作。如果一个行占用的空间增长，并且在页内没有更多的空间可以存储，MyISAM会将行拆成不同的片段存储；InnoDB则需要分裂页来使行可以放进页内。

   什么时候使用varchar？

   • 字符串的最大长度比平均长度大很多；
   • 列的更新很少，所以碎片不是问题 ；
   • 使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储。

   当存储CHAR值时，MySQL会删除所有的末尾空格。CHAR值会根据需要采用空格进行填充以方便比较。

   什么时候使用char？

   • CHAR适合存储很短的字符串，或者所有值都接近同一个长度，如密码的MD5值。
   • 对于经常变更的数据，CHAR也比VARCHAR更好，因为CHAR不容易产生碎片。

2. varchar(5)和varchar(200)

   使用VARCHAR(5)和VARCHAR(200)存储"hello"的空间开销是一样的。 但是更长的列会消耗更多的内存，因为MySQL通常会分配固定大小的内存块来保存内部值。尤其是使用内存临时表进行排序或其他操作时会特别糟糕。在利用磁盘临时表进行排序时也同样糟糕。所以最好的策略是只分配真正需要的空间。

3. 选择标识符

   • 整数类型通常是标识列的最佳选择，因为它们很快并且可以使用AUTO_INCREMENT。
   • 如果可能，应该避免使用字符串类型作为标识列，因为它们很耗空间，并且比数字类型慢。
   • 对于完全随机的字符串也需要多加注意，例如MD5(),SHA1()或者UUID()产生的字符串。这些函数生成的新值会任意分布在很大的空间内，这会导致INSERT以及一些SELECT语句变得很慢：
   • 因为插入值会随机的写入到索引的不同位置，所以使得INSERT语句更慢。这会导致页分裂、磁盘随机访问。
   • SELECT语句会变的更慢，因为逻辑上相邻的行会分布在磁盘和内存的不同地方。
   • 随机值导致缓存对所有类型的查询语句效果都很差，因为会使得缓存赖以工作的局部性原理失效。

4. varchar长度怎么选?

   varchar需要多长就设置多长，不必考虑2的多少次幂的问题，但是不能想分配多少就分配多少，设置过大，会浪费内存。因为读取数据时候，需要预分配内存，如果n设置的很大，需要分配很大的内存空间。另外，如果遇到了varchar(256)这种情况，可以设置为varchar(255)呢，明显节省了一个存储长度的字节呀。

参考资料

1. https://pdai.tech/md/db/sql-mysql/sql-mysql-theory.html
2. https://www.cnblogs.com/xrq730/p/8446246.html