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时间戳存储为整数值，但这并不会带来任何收益。用整数保存时间戳的格式通常不方便处理，所以不推荐这样做。

总结

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

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

本文作者： 荒古
本文链接： https://haxianhe.com/2019/08/...
版权声明： 本博客所有文章除特别声明外，均采用 CC BY-NC-SA 3.0 许可协议。转载请注明出处！