MySQL数据类型

ChicagoTypewriter

已于 2025-07-02 22:28:32 修改

阅读量1.3k

点赞数 30

CC 4.0 BY-SA版权

分类专栏：数据库文章标签： mysql 数据库

于 2025-07-02 07:00:00 首次发布

本文链接：https://blog.youkuaiyun.com/niceHack/article/details/149057736

数据库专栏收录该内容

5 篇文章

订阅专栏

选择合适的数据类型

整数类型

整数类型	TINYINT	SMALLINT	MEDIUMINT	INT	BIGINT
字节Byte	1	2	3	4	8
位bit	8	16	24	32	64

可以存储的值的范围为：-2(n-1) ~ 2(n-1)-1

整数类型可以选择UNSIGNED属性，表示不允许负值，这大致可以使正数的上限提高一倍，比如tinyint，正常存储范围是-128 – 127，使用无符号定义后范围是0 – 255。

需要特别注意的是，我们经常在ddl语句中看到类似INT(11)这样的定义，这种定义实际上并不影响MySQL的实际空间分配和存储，他只是规定了一些客户端工具用来显示字符的个数，对于计算和存储来说，INT(1)和INT(20)是相同的！！！

实数类型

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

整数类型

FLOAT

DOUBLE

DECIMAL(10,0)

默认10位

DECIMAL(65,30)

最大精度支持65位

字节Byte

4+1=5

3*4+4+3*4+2=30

位bit

240

DECIMAL(M,D)

M表示该数据类型的精度

D表示该数据类型小数部分的长度，M-D则表示整数部分的长度

Decimal数据类型的空间占用计算规则：

整数部分和小数部分的单独计算
每9位占用4个字节
剩下的1-2位占用1字节，3-4占用2字节，5-6占用3字节，7-9占用4字节
符号和小数点不占用额外空间

Decimal空间占用计算方式举例如下：

类型定义	存储空间 (字节)	计算说明
DECIMAL(5,2)	3	3位整数(2字节) + 2位小数(1字节)
DECIMAL(9,2)	5	7位整数(4字节) + 2位小数(1字节)
DECIMAL(10,2)	5	8位整数(4字节) + 2位小数(1字节)
DECIMAL(18,9)	9	9位整数(4字节) + 9位小数(4字节) + 1字节(剩余0位)
DECIMAL(20,10)	10	10位整数(5字节) + 10位小数(5字节)

MySQL会使用double进行浮点数类型的内部计算。

不推荐使用以上三种数据类型，比如在业务中，常用的实践方式是使用bitint代替实数类型，避免浮点数存储和计算不精确以及decimal类型精确计算但是代价高的问题。

字符串类型

说到了字符串就少不了字符集，以及字符集的排序规则。每个字符串列支持独立的字符集和该字符集的排序规则集。

VARCHAR

这是在开发中最常用的字符串类型。它比固定长度的类型更节省空间，因为它仅使用必要的空间（用更少的空间存储更短的值）。

Varchar需要用额外1或2个字节记录字符串的长度，如果列的最大长度小于或等于255则使用1字节，否则使用2字节。所以我们一般在考量ddl语句时，在业务允许的情况下，都使用varchar(255)来定义字符串数据类型。那么如果是在latin1字符集的情况下，该列最多占用255+1字节的空间，如果是utf8的编码方式则最多占用255*3+1字节。因为latin1是每一个字符占用1个字节。MySQL中的utf8实际上是阉割版的，即utf8mb3每个字符最多占用3个字节，汉字和拉丁字母占用3个字节，emoji表情需要占用4个字节，所以无法正常保存，需要在定义的时候修改为utf8mb4。

如果列的最大容量远大于存储值的平均长度；更新较少；使用了utf8这类复杂字符集，每个字符都使用不同的字节数进行存储，则推荐使用varchar字符串类型。

CHAR

固定长度，总是分配固定的长度出来。当存储char值时，如果字符串末尾有空格，MySQL会自动删除所有尾随空格，如果需要比较，值会用空格填充。Char的好处是因为固定长度，所以不容易出现碎片。

TEXT

TINYTEXT

SMALLTEXT

TEXT

MEDIUMTEXT

LONGTEXT

BLOB（二进制形式存储字符串，没有排序规则或字符集）

TINYBLOB

SMALLBLOB

BLOB

MEDIUMBLOB

LONGBLOB

当text和blob值太大时，innoDB会使用独立的“外部”存储区域，此时每个值在行内占用1-4个字节的存储空间，剩余的值完全存储在外部存储区域中。

Text和blob类型的排序方式和其他类型不同：只对这些列的最前max_sort_length字节而不是整个字符串做排序，仔细想想也可以理解，因为在某些情况下这两种数据类型下的值可能会非常大，性能压力大效率低下，当然这个配置是可以根据实际情况进行修改的。

MySQL不能将blob和text数据类型的完整字符串放入索引，只能使用前缀索引，所以在使用order by语句排序时仍需要全表扫描和filesort。

字符串家族中还有一种特殊的类型，枚举类型，个人不建议使用，这部分定义应该由业务代码来完成，就像不推荐使用外键和联表查询一样。

思考：varchar(5)和varchar(200)存储“hello”的空间开销是一样的，那么使用更短的列有什么优势呢？

因为MySQL通常会在内存中分配固定大小的内存块来保存值，这对于使用内存临时表的排序或其他操作来说尤其糟糕，在利用磁盘临时表进行文件排序时也同样糟糕。

日期和时间

YEAR

DATE

DATETIME

从1000年到9999年，精度为1微秒。使用YYYYMMDDHHMMSS格式存储压缩成整数的日期和时间，与时区无关，因为它记录的是日期和时间的文本表示，需要8个字节。

TIMESTAMP

存储自1970年1月1日格林尼治标准时间以来经过的秒数—与unix时间戳相同。只使用4字节的存储空间，它的范围比datetime小得多，只能表示从1970-1-1到2038-1-19范围的日期和时间。MySQL提供了unix时间戳和日期互相转换的函数：FROM_UNIXTIME()和UNIX_TIMESTAMP()。值得注意的是unix时间戳是基于时区的，因为它记录的是一个相对值，那么它的实际时间会随着参考的时区的变化而发生变化。

当然还可以使用毫秒数，它和timestamp的区别是：一个是毫秒数一个是秒钟数；当然他们都和时区有关系，显示的实际时间与随着时区的不同而不同。

结论，优先使用datetime类型存储时间，如果需要记录时区信息则使用timestamp。

位压缩数据类型

BIT

最大可以存储64位的，在检索的时候，默认返回该值对应的ASCII码值对应的字符，但是在数字上下文，则得到的是数字。下图中二进制b’00111001’对应的十进制数字为57，在检索的时候，返回这个ASCII码值对应的字符9，而数字上下文则返回ASCII码值，这会让人非常困惑（这实际上并不重要，因为不推荐使用该类型），建议谨慎使用，最好避免。