MySQL数据类型

entner 发布于2019-07-01 12:31 / 791人阅读

摘要：数据类型优化作者的故事原本觉得数据类型是非常简单并十分基础的知识，认为自己掌握的差不多了。数据类型存储空间时间范围个字节年年个字节年年通常情况下应该尽量使用，相比于空间效率更高。

MySQL数据类型优化 作者的故事

原本觉得mysql数据类型是非常简单并十分基础的知识，认为自己掌握的差不多了。但经过上一次的面试，才发现自己掌握的并不牢固，很多细节和原理并不知道。后来翻阅了《高性能mysql》这本书，仔细阅读了第四章Schema与数据类型优化。因此，写这篇文章记录和总结下，并加深理解。

选择优化的数据类型

不管存储哪几种类型，以下几个简单的原则都有助于做出更好的选择

更小的通常更好

简单就好

尽量避免null

整数类型

数据类型	存储空间
TINYINT	8位
SMALLINT	16位
MEDINUMINT	24位
INT	32位
BIGINT	64位

取值范围：-2^(N-1) ~ 2^(N-1)-1，N位存储空间的位数。
整数类型有可选的UNSIGNED类型，表示不允许负值，这大致可以使正数的上限提高一倍。例如 TINYINT UNSIGNED可以存储的范围是0~255，而TINYINT的存储范围是-128~127。
有符号和无符号类型使用相同的存储空间，并且具有相同的性能，因此可根据实际情况选择合适的类型。

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

实数类型

浮点类型和DECIMAL类型都可以指定精度。
对于DECIMAL列，可以指定小数点前后所允许的最大位数。这会影响列的空间消耗。MySQL5.0和更高版本将数字打包保存到一个二进制字符串中（每4个字节存9个数字）。例如DECIMAL(18,9)小数点两边各存储9个数字，一共使用9个字节，小数点前后各占4个字节，小数点占1个字节。

浮点类型在存储同样的值时，通常比DECIMAL使用更少的空间。FLOAT使用4个字节，DOUBLE使用8个字节，相比FLOAT有更高的精度和更大的范围。

因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用DECIMAL，例如存储财务数据。但是在数据量比较大的时候，可以考虑使用BIGINT代替DECIMAL，将要存储的值根据小数的位数乘以相应的倍数即可。

字符串类型

VARCHAR和CHAR是最主要的字符串类型

VARCHAR

VARCHAR主要用于存储可变长字符串，他比定长更节省空间。有一种情况例外，如果MySQL表使用ROW_FORMAT=FIXED创建的话，每一行都会定长存储。
VARCHAR需要使用1或2个额外字节存储字符串的长度，如果列的最大长度<=255则使用1个字节，否则使用2个字节。
VARCHAR节省了存储空间，对性能也有好处。但是由于行是变长的，在update时可能使行变得比原来更长，这就需要额外的工作。
适合用VARCHAR的场景：字符串列的最大长度比平均长度大很多；列的更新少。

CHAR

CHAR类型是定长的，适合存储很短的字符串或者所有的值都接近同一个长度。例如非常适合存储密码的MD5值。对于经常变更的列，CHAR比VARCHAR更适合。

备注：使用VARCHAR(5)和VARCHAR(200)存储hello的空间开销是一样的，但是更长的列会消耗更多的内存，因为MySQL通常会分配固定大小的内存块来保存内部值。最好的策略就是只分配真正需要的空间。

日期和时间类型

MySQL可以使用很多类型来保存时间和日期，如YEAR和DATE，MySQL能存储最小时间粒度为秒。
这里主要介绍2种相似的日期类型DATETIME和TIMESTAMP。

数据类型	存储空间	时间范围
DATETIME	8个字节	1001年~9999年
TIMESTAMP	4个字节	1970年~2038年

通常情况下应该尽量使用TIMESTAMP，相比于DATETIME空间效率更高。有的人会将unix时间戳存储为整数值，但这不会带来任何收益（除了特殊情况，如下），数据处理起来也不方便，因此不推荐这样做。

对于需要存储比秒更小粒度的日期和时间值情况，建议可以使用BIGINT类型存储微秒级别的时间戳，或者使用DOUBLE存储秒之后的小数部分。

总结

本篇文章主要是介绍MySQL常用的数据类型，如有错误或者不准确的地方，欢迎交流。

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/30881.html

深入Mysql - 谈谈我对数据类型的认识

摘要：要确保没有低估需要存储的值的范围，更小是相对与数据类型的最大值范围来讲的。如果无法确定哪个数据类型是最好的，就选择你认为不会超过范围的最小类型。整数类型有两个类型的数字整数和实数。列默认为，这与其他的数据类型不一样。简述良好的逻辑设计和物理设计是高性能系统的基石，比如反范式设计可以加快某些类型的查询同时也会影响另外一些类型的查询效率，所以我们必须重视Mysql对于数据库的设计（本...

SillyMonkey 2019-07-01 12:13 评论0 收藏0
深入Mysql - 谈谈我对数据类型的认识

摘要：要确保没有低估需要存储的值的范围，更小是相对与数据类型的最大值范围来讲的。如果无法确定哪个数据类型是最好的，就选择你认为不会超过范围的最小类型。整数类型有两个类型的数字整数和实数。列默认为，这与其他的数据类型不一样。简述良好的逻辑设计和物理设计是高性能系统的基石，比如反范式设计可以加快某些类型的查询同时也会影响另外一些类型的查询效率，所以我们必须重视Mysql对于数据库的设计（本...

junbaor 2019-07-24 10:29 评论0 收藏0