整数类型

有两种类型的数字：整数和实数。如果存储整数，可以使用这几种整数类型：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 许可协议。转载请注明出处！