MySQL常见字段类型长度汇总

文章目录

一、整数

TINYINT

长度：固定为1个字节。
范围：-128 到 127（有符号）或 0 到 255（无符号）。

SMALLINT

长度：固定为2个字节。
范围：-32,768 到 32,767（有符号）或 0 到 65,535（无符号）。

MEDIUMINT

长度：固定为3个字节。
范围：-8,388,608 到 8,388,607（有符号）或 0 到 16,777,215（无符号）。

INT

长度：固定为4个字节。
范围：-2,147,483,648 到 2,147,483,647（有符号）或 0 到 4,294,967,295（无符号）。

BIGINT

长度：固定为8个字节。
范围：-9,223,372,036,854,775,808 到 9,223,372,036,854,775,807（有符号）或 0 到 18,446,744,073,709,551,615（无符号）。

二、字符串

CHAR

长度：0到255个字符。
存储：定长，总是使用指定的长度。如果存储的字符串长度小于定义的长度，它将使用空格填充。

VARCHAR

长度：0到65535个字符，但实际最大长度取决于最大行大小（65535字节）和使用的字符集。
存储：变长，只使用必要的空间加上一个或两个额外的字节来记录字符串的长度。

BINARY

长度：0到65535个字节，取决于字符集和校对规则。
存储：定长，总是使用指定的长度。BINARY类型类似于CHAR类型，但存储二进制字节字符串。

VARBINARY

长度：0到65535个字节，取决于字符集和校对规则。
存储：变长，只使用必要的空间加上一个额外的字节来记录字符串的长度。VARBINARY类型类似于VARCHAR类型，但存储二进制字节字符串。

TINYTEXT

长度：最大255个字符。2^8 − 1
存储：存储长度需要1个字节，字符串本身存储在单独的表中。

TEXT

长度：最大65,535个字符。2^16 − 1
存储：存储长度需要2个字节，字符串本身存储在单独的表中。

MEDIUMTEXT

长度：最大16,777,215个字符。2^24 − 1
存储：存储长度需要3个字节，字符串本身存储在单独的表中。

LONGTEXT

长度：最大4,294,967,295个字符。2^32 − 1
存储：存储长度需要4个字节，字符串本身存储在单独的表中。

TINYBLOB

长度：最大255个字节。2^8 − 1
存储：存储长度需要1个字节，二进制数据存储在单独的表中。

BLOB

长度：最大65,535个字节。2^16 − 1
存储：存储长度需要2个字节，二进制数据存储在单独的表中。

MEDIUMBLOB

长度：最大16,777,215个字节。2^24 − 1
存储：存储长度需要3个字节，二进制数据存储在单独的表中。

LONGBLOB

长度：最大4,294,967,295个字节。2^32 − 1
存储：存储长度需要4个字节，二进制数据存储在单独的表中。

TEXT和BLOB字段类型之间的主要区别？

• 存储内容： TEXT：通常用于存储长文本内容，如文章、评论等。 BLOB：通常用于存储二进制数据，如图片、音频、视频等。
• 最大长度： TEXT：TINYTEXT 最多可存储 255 个字符，TEXT 最多可存储 65,535 个字符，MEDIUMTEXT 最多可存储 16,777,215 个字符，LONGTEXT 最多可存储 4,294,967,295 个字符。 BLOB：TINYBLOB 最多可存储 255 个字节，BLOB 最多可存储 65,535 个字节，MEDIUMBLOB 最多可存储 16,777,215 个字节，LONGBLOB 最多可存储 4,294,967,295 个字节。
• 字符集和排序规则： TEXT：与字符集和排序规则相关，因为它存储的是文本数据。 BLOB：与字符集和排序规则无关，因为它存储的是二进制数据。
• 存储和检索： TEXT：当你存储或检索TEXT字段时，MySQL会考虑字符集和排序规则。 BLOB：对于BLOB字段，MySQL将其视为纯粹的二进制数据，不会考虑字符集或排序规则。
• 用途： TEXT：通常用于需要存储和检索大量文本数据的场景，如文章、评论等。 BLOB：通常用于存储大型二进制对象，如图片、音频、视频等，或者存储与字符集和排序规则无关的数据。

选择TEXT还是BLOB主要取决于你的数据需求和场景。如果你正在存储大量的文本数据，并且需要考虑到字符集和排序规则，那么TEXT可能是更好的选择。如果你正在存储大型二进制对象或与字符集和排序规则无关的数据，那么BLOB可能更适合你的需求。

三、浮点数

NUMERIC(M, D), DECIMAL(M, D)

精度和标度：M是精度（总位数）从1到65，D是标度（小数点后的位数）从0到30。
存储：取决于精度和标度。根据指定的精度和标度使用固定数量的字节。

FLOAT

FLOAT 是单精度浮点数数据类型，用于存储近似值。
FLOAT 类型的数值可能会有舍入误差，因为浮点数使用二进制表示，无法精确表示某些十进制小数。
存储：32位（4字节）来存储数值，并且可以存储大约7位有效数字。

DOUBLE

DOUBLE 是双精度浮点数数据类型，也用于存储近似值。
与 FLOAT 类似，DOUBLE 类型的数值也可能会有舍入误
存储：使用64位（8字节）来存储数值，并且可以存储大约15位有效数字。

浮点数的区别？

DECIMAL 和 NUMERIC 是精确数值类型，用于需要精确计算的场景，如货币值。它们以字符串形式存储，不会引入舍入误差。
FLOAT 和 DOUBLE 是浮点数类型，用于存储近似值。它们使用二进制表示，可能会有舍入误差。
DECIMAL 和 NUMERIC 需要指定精度和标度，而 FLOAT 和 DOUBLE 则不需要。
存储大小方面，DECIMAL 和 NUMERIC 的存储大小根据指定的精度和标度而定，而 FLOAT 固定为4字节，DOUBLE 固定为8字节。

四、总结

选择字段的原则

• 通常更小的更好 > 应该尽量使用可以正确存储数据的最小数据类型，更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU更少，但是要确保没有低估需要存储的值的范围，如果无法确认哪个数据类型，就选择你认为不会超过范围的最小类型
• 简单就好 > 简单数据类型的操作，通常需要更少的CPU> 整型比字符操作代价更低，因为字符的字符集和校对规更复杂> 使用datetime类型，使用MySQL自建类型而不是字符串来存储日期和时间> 用整型存储IP地址，可以用函数把字符串转成整型存，空间小，INET_ATON、INET_NTOA
• 尽量避免null > 如果查询中包含可为NULL的列，对MySQL来说很难优化，因为可为null的列使得索引、索引统计和值比较都更加复杂，坦白来说，通常情况下null的列改为not null带来的性能提升比较小，所以没有必要将所有的表的schema进行修改，但是应该尽量避免设计成可为null的列

int(1)定义的长度影响写入的长度？

这里的int(1)插入123可以成功的，只要不超过int类型的表示范围-2^31 - 2^31-1，所以说字段括号的长度是没有意义的，不影响写入长度。

varchar(5)与varchar(255)保存同样的内容，有区别？

我们知道，varchar(n) ，n<=255使用额外一个字节保存长度，n>255使用额外两个字节保存长度。实际上，两者的硬盘存储空间相同，但内存空间占用不同，是括号的指定大小，因为将行从存储引擎层复制到SQL层时，varchar将转换为字段char以获得利用固定宽度行的优势，因此，内存中的字符串将填充到声明的varchar列的最大长度。

具体可以查阅，MySQL官方文档：https://dev.mysql.com/doc/refman/5.7/en/data-types.html