MySQL学习笔记之数据类型

整数类型

有两种类型的数字:整数和实数。如果存储整数,可以使用这几种整数类型:TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT。分别使用8,16,24,32,64位存储空间。它们可以存储的值的范围从-2^(N-1)到2(N-1),其中N是存储空间的位数。

整数类型有可选的UNSIGNED属性,表示不允许负值,这大致可以使正数的上限提高一倍。例如TINYINT UNSIGNED可以存储的范围是-~255,而TINYINT的存储范围是-128~127。

有符号和无符号类型使用相同的存储空间,并具有相同的性能,因此可以根据实际情况选择合适的类型。

MySQL 可以为整数类型指定宽度,例如INT(11),对大多数应用这是没有意义的:它不会限制值的合法范围,只是规定了MySQL的一些交互工具(例如MySQL命令行客户端)用来显示字符的个数。对于存储和计算来说,INT(1)和INT(20)是相同的。

实数类型

实数是带有小数部分的数字。

(实际工作中还没有使用过实数类型,因为,小数都可以单位换算成整数,显然在服务端使用整数比使用小数靠谱的多)。

字符串类型

VARCHAR 和 CHAR 类型

VARCHAR 和 CHAR 是两种最主要的字符串类型。很难精确地解释这些值是怎么存储在存储磁盘和内存中的,因为这跟存储引擎的具体实现有关。下面的描述假设使用的存储引擎是InnoDB 或者 MyISAM。如果使用的不是这两种存储引擎,请参考所使用的存储引擎的文档。

先看看VARCHAR和CHAR值通常在磁盘上怎么存储。请注意,存储引擎存储CHAR或者VARCHAR值的方式在内存中和在磁盘上可能不一样,所以MySQL服务器从存储引擎读出的值可能需要转换为另一种存储格式。

VARCHAR

VARCHAR 类型用于存储可变长字符串,是最常见的字符串数据类型。它比定长类型更节省空间,因为它仅使用必要的空间。有一种情况例外,如果MySQL表使用ROW_FORMAT = FIXED创建的话,每一行都会使用定长存储,这会很浪费空间。

VARCHAR需要使用1或2个额外字节记录字符串的长度:如果列的最大长度小于或等于255字节,则只使用1个字节表示,否则使用2个字节。

VARCHAR节省了存储空间,所以对性能也有帮助。但是,由于行是变长的,在UPDATE时可能使行变得比原来更长,这就导致需要做额外的工作。如果一个行占用的空间增长,并且在页内没有更多的空间可以存储,在这种情况下,不同的存储引擎的处理方式是不一样的。例如,MyISAM会将行拆成不同的片段存储,InnoDB则需要分裂页来使行可以放进页内。其他一些存储引擎也许从不在原数据位置更新数据。

CHAR

CHAR 类型是定长的:MySQL 总是根据定义的字符串长度分配足够的空间。当存储CHAR值时,MySQL会删除所有的末尾空格。CHAR值会根据需要采用空格进行填充以方便比较。

CHAR 适合存储值很短的字符串,或者所有值都接近同一个长度。例如,CHAR非常适合存储密码的MD5值,因为这是一个定长的值。对于经常变更的数据,CHAR也比VARCHAR更好,因为定长的CHAR类型不容易产生碎片。对于非常短的列,CHAR比VARCHAR在存储空间上也更有效率。例如用CHAR(1)来存储只有Y和N的值,如果采用单字节字符集只需要一个字节。但是VARCHAR(1)却需要两个字节,因为还有一个记录长度的额外字节。

慷慨是不明智的

使用VARCHAR(5)和VARCHAR(200)存储’hello’的空间开销是一样的。那么使用更短的列有什么优势吗?

事实证明有很大的优势。更长的列会消耗更多的内存,因为MySQL通常会分配固定大小的内存块来保存内部值。尤其是使用内存临时表进行排序或操作时会特别糟糕。在利用磁盘临时表进行排序时也同样糟糕。

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

BLOG 和 TEXT 类型

BLOG 和TEXT 都是为了存储很大的数据而设计的字符串数据类型,分别采用二进制和字符方式存储。

实际上,它们分别属于两组不同数据类型家族:字符类型是TINIYTEXT,SMALLTEXT,MEDIUMTEXT,LONGTEXT;对应的二进制类型是TINYBLOG,SMALLBLOG,BLOG,MEDIUMBLOG,LONGBLOG。BLOG是SMALLBLOG的同义词。TEXT是SMALLTEXT的同义词。

与其他类型不同,MySQL 把每个BLOG 和 TEXT 值当作一个独立的对象处理。存储引擎在存储时通常会做特殊处理。当BLOG和TEXT值太大时,InnoDB会使用专门的“外部”存储区域来进行存储,此时每个值在行内需要1~4个字节存储一个指针,然后在外部存储区域存储实际的值。

BLOG和TEXT家族之间仅有的不同是BLOG类型存储的是二进制数据,没有排序规则或字符集,而TEXT类型有字符集和排序规则。

日期和时间类型

MySQL 提供两种相似的日期类型:DATETIME和TIMESTAMP。

DATETIME

这个类型能保存大范围的值,从1001年到9999年,精度为秒。它把是日期和时间封装到格式为YYYYMMDDHHMMSS的整数中,与时区无关。使用8个字节的存储空间。

默认情况下,MySQL 以一种可排序的、无歧义的格式显示DATETIME值,例如“2018-01-16 22:23:00”。这是ANSI标准定义的日期和时间表示方法。

TIMESTAMP

就像它的名字一样,TIMESTAMP类型保存了从1970年1月1日午夜(格林尼治标准时间)以来的秒数,它和UNIX时间戳相同。TIMESTAMP 只使用4个字节的存储空间,因此它的范围比DATETIME小得多:只能表示从1970年到2038年。

MySQL 4.1以及更新的版本按照DATEYTIME的方式格式化TIMESTAMP的值,但是MySQL 4.0 以及更老的版本不会在各个部分之间显示任何标点符号。这仅仅是显示格式上的区别,TIMESTAMP的存储格式在各个版本都是一样的。

除特殊行为外,通常也应该尽量使用TIMESTAMP,因为它比DATETIME空间效率更高。有时候人们会将Unix时间戳存储为整数值,但这并不会带来任何收益。用整数保存时间戳的格式通常不方便处理,所以不推荐这样做。

总结

实际工作中常用的数据类型就那几种,除了了解各个数据类型之间的差异,更多的是在工作中多多积累什么情况下用什么数据类型更合适,这是一个需要衡量的问题。

欢迎关注我的公众号:荒古传说