详解MySQL的常用数据类型

文章目录

• • 一、MySQL 数据类型
  • • 1.1、mysql中编码和字符
  • 二、数值类型
  • • 2.1、整数类型的长度
    • 2.2、浮点型
  • 三、字符串类型
  • • 3.1、字符串类型长度
  • 四、日期和时间类型
  • • 4.1、DATETIME
  • 五、二进制数据类型
  • 六、使用建议

一、MySQL 数据类型

MySQL支持很多数据类型，以便我们能在复杂的业务场景中支持各种各样的数据格式，存储适当的数据内容。我们在设计数据库时，正确的使用数据库类型对整个数据库的整洁和高效，会有很大的帮助。

MySQL 支持多种类型，大致可以分为三类：数值、日期/时间和字符串（字符）类型。

1.1、mysql中编码和字符

在 mysql 中，一个中文汉字所占的字节数与编码格式有关：

• 如果是GBK编码，则一个中文汉字占2个字节，英文占1个字节
• 如果是UTF8编码，则一个中文汉字占3个字节，而英文字母占1字节。

比如定义某个字段数据类型为：varchar(32)，表示这个可以存储 32 个字符，此时表示的是字符，所以跟中英文无关，也就是该字段可以存储 32 个中文，或者是 32 个英文，或者是 32 个中文和英文的混搭都行。但如果字符数超过 32 个的话就会报错。

二、数值类型

MySQL 支持所有标准 SQL 数值数据类型，mysql 的数值数据类型可以大致划分为两个类别，一个是整数，另一个是浮点数或小数。并且 MySQL 允许我们指定数值字段中的值是否有正负之分（UNSIGNED）或者用零填补（ZEROFILL）

在 MySQL 中支持的 5 个主要整数类型是 TINYINT，SMALLINT，MEDIUMINT，INT 和 BIGINT。这些类型在很大程度上是相同的，只有它们存储的值的大小是不相同的。浮点型有：FLOAT、DOUBLE、DECIMAL。

下面的表显示了需要的每个数值类型的存储所占用的字节和可表示的数值范围：

1 bit 即 1位　　1字节 = 8 bit　　1K = 1024字节　　1M = 1024K

比如 INT 类型，该类型所占字节为 4 个字节。比如数据库中某列的字段类型为 INT，则每条数据的该列的数据都会占服务器容量的 4 个字节的存储容量，并且该列所能显示的数值范围只能是 -128~127 ，或者如果该列是无符号的话，则范围是 0~255。如果你存储超出最大范围则会提示 out of range value …，比如往 INT 且有符号的数据类型里面存储 2147483648 的值，则会报错并且不会存储成功。

2.1、整数类型的长度

常用的整数数据类型有 tinyint ，smallint ，mediumint ， int ，bigint 共计5种。

在声明整数类型列时，我们可以跟上一个数值，例如 int(n) ，但实际上这里的 n 跟存储没有什么关系，无论 n 是什么，INT 数据类型都是 4 个字节，只能存储 -2 147 483 648~2 147 483 647（有符号时）范围的数据。

在mysql手册中这个 n 表示最大显示宽度，显示宽度与存储大小和数据类型所能包含的值的范围无关，最大有效的显示宽度是255，即 n 的值最大是 255。

比如我声明一个字段为 number INT(5) ，此时该 number 字段也是占 4 个字节，也只能存储 -2 147 483 648~2 147 483 647（有符号时）范围的数据。这个 n 的作用只有在我们给列加上 ZEROFILL（用 0 填充）属性时才会体现出来，并且只有在使用一些特定的客户端输出时才会有体现。比如：

`number` int(5) unsigned zerofill       -- 加上zerofill属性则必须同时加unsigned属性

此时，当该列某条数据的数字小于 5 位时，在某些特定的客户端检索输出时，会在数字前 “补0”，凑足5位数字。例如存储的数字是123，那么输出00123 。如果大于 5 位则原样显示原数字。

注意，不是在数据库中存储时会自动补 0 ，而是在某一些客户端查询输出时才有显示效果，目前仅发现使用在MySQL Shell才有显示效果，其他客户端连接时均无。

所以说，基本没有开发者会使用这个特性，因为基本没什么用。

2.2、浮点型

FLOAT 类型固定占用4个字节， DOUBLE 类型固定占用8个字节，逻辑和上述的整型类似。

DECIMAL 类型的定义方式是 DECIMAL(M,D) ，其中 M 表示最大位数，D 表示小数点右侧的位数。这里的“位”不是二进制的比特位，而是指十进制的数字的位数。

例如我们定义 DECIMAL(5,2) ，则表示最大位数为5位，小数点后2位，于是取值范围为 -999.99至999.99 。可以这样理解：M-D 的值为小数点前的位数，D 的值为小数点后的位数，要算取值范围则各个位置填充9，取正负范围。那么容易计算 DECIMAL(5,1) 的取值范围是 -9999.9至9999.9 ；DECIMAL(4,2) 的取值范围是 -99.99至99.99 。

DECIMAL(M,D) 的存储方式和其他数字类型都完全不同，它是以字符串形式进行存储的。这可能有点不好理解，以整型 tinyint 为例，它存储的值是直接为十进制到二进制的转换，以无符号型为例，当需要存入的值为100值，将100转化为二进制为1100100 ，使用1个字节即8位记录，实际存入的是 01100100 。但是用 DECIMAL 类型存储时，比如定义 DECIMAL(3,0) ，存入100时，实际存入的是由字符“1”，“0”，“0”拼接而成的字符串“100”的二进制值，存入时占用3个字节，分别是31，30，30（注意这是十六进制）。
1个数字字符占用1个字节，因此定义为 DECIMAL(M,D) 占用 M 个字节。（同上所述，M个字节为数据本身的占用空间，另外描述该数据的元数据还固定占用2个字节的空间）。
需要注意的是， DECIMAL 类型在存储时有补0操作。小数点前不足，向更高位补0，小数点后不足，向更低位补0。
以 DECIMAL(5,2) 为例，如果准备存入9.5，小数点前应为3位，缺2位，小数点后应为2位，缺1位，各补0后，实际存入 ‘009.50’ ，转化为十六进制为30 30 39 2E 35 30 。但是在检索输出时，小数点前的0一般会省略，而小数点后的0会保留，这一点也需要注意。

三、字符串类型

MySQL 提供了 8 个基本的字符串类型，包括 CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 和 SET，可以存储的范围从简单的一个字符到巨大的文本块或二进制字符串数据。

3.1、字符串类型长度

常用的字符串类型的数据类型有 CHAR 和 VARCHAR 两种，两者后面都必须要跟上一个数字表示长度，例如 CHAR(10)、VARCHAR(10)。

char(n) 和 varchar(n) 括号中的 n 代表最大可容纳的字符的个数，并不代表字节个数。注意，一个中文和一个英文都是 1 个字符，只不过 mysql 的编码格式不同时，1 个中文和 1 个英文所占用的存储字节不同而已。（虽然在早期的版本中，n 指的是字节数，但已经是非常旧的版本了，估计一般人也用不到）

CHAR(n) 和 VARCHAR(n) 都是表示可存储 n 个字符，但是 char 类型在少于 n 个字符时，会在字符串的右边使用空格来填充以达到 n 个字符。

比如：CHAR(4) 和 VARCHAR(4) ，对于 CHAR(4) 表示固定容纳4个字符，当少于4个字符时，会使用空格填充空缺的部分，如果超过4个字符，会自动截断超出部分。例如你存入数据为 ‘ab’ ，实际会存入 'ab ’ （ab后有2个空格）。但是如果我们使用 select 语句来查询 char 类型的字段时，会发现根本就没有自动补空格，这是因为 CHAR 字段在检索输出时，会自动省略右侧的空格。

VARCHAR 是 CHAR 类型的一个变体，它是一种可变长度的字符串类型，并且在声明时也必须要指定字符长度。VARCHAR 类型对于未达到 n 字符的情况不会补空。

CHAR 和 VARCHGAR 不同之处在于 MYSQL 数据库处理这个指示器（即 n）的方式：CHAR 把这个大小视为值的大小，在长度不足的情况下就用空格补足。而 VARCHAR 类型只会把 n 作为限制字符串的最大长度，短于指示器长度的 VARCHAR 类型不会被空格填补，但长于指示器的值仍然会被截短。

关于计算 VARCHAR 类型字符串的占用空间，有一点需要说明的是， VARCHAR 类型字符串的占用空间实际上包含2部分，一是存储数据本身占用的空间，二是描述数据的元数据占用的空间，例如 VARCHAR 类型会使用1个字节记录存入数据实际的字符数。

比如下面示例说明 varchar 字段的字符长度和占用存储字节数：

（1） ‘a啊b’ —— 字符数为3，不补空，实际存入为 ‘a啊b’ ，字符数为3，字节数为 1+3+1=5 。

（2）‘a啊b哈ccccccccc’ —— 字符数超出4，仅保留前4个字符，因此实际存入 ‘a啊b哈’ ，字符数：4，字节数：1+3+1+3=8 。这种情况和 CHAR 类型处理一致。

（3）‘a啊和哈’ —— 字符数刚好为4，不需要截断和补齐，因此实际存入 ‘a啊和哈’ ，字符数：4，字节数：1+3+3+3=10

两者的使用场景：因为 VARCHAR 类型可以根据实际内容动态改变存储值的长度，所以在不能确定字段需要多少字符时使用 VARCHAR 类型可以大大地节约磁盘空间、提高存储效率。但如果确切知道字符串长度，比如就在50~55之间，那就用 CHAR，因为 CHAR 类型由于本身定长的特性使其性能要高于 VARCHAR。

四、日期和时间类型

在处理日期和时间类型的值时，MySQL 带有 5 个不同的数据类型可供选择。它们可以被分成简单的日期（DATE）、时间类型（TIME）、年份（YEAR）、混合日期（DATETIME）、时间类型（TIMESTAMP）。

每一个时间、日期类型都有合法的取值范围，当指定确定不合法的值时，系统将“零”值插入数据库中。

这个时间的对应的Java类、只要格式正确、都是可以取的，主要看需求是什么。不管你后端是String、Date 类型、只要格式是对应的就是可以的。

4.1、DATETIME

DATETIME类型后面可以接后缀，当然也可以没有，如datetime、datetime(3)、datetime(6)，这个后缀是用来定义毫秒的精度的。这个后缀最大是6，超过就会报错。

假设建表语句为：

则存储结果分别为：

五、二进制数据类型

主要包含下列几种数据类型：tinyblob，blob，mediumblob，longblob。

mysql类型名	mysql类型名
tinyblob	不超过 255 个字符的二进制字符串
blob	二进制形式的长文本数据
mediumblob	二进制形式的中等长度文本数据
longblob	二进制形式的极大文本数据

六、使用建议

不建议选择较大的数值范围，尽量选择合适的数据范围，越小的数据类型会更快，占用磁盘、内存和CPU缓存也更小。

越简单的类型执行的成本越低，比如整型比字符类型操作代价要小得多，很多固定范围的文本内容字段可以用整型表示。

尽量制定列为NOT NULL，有NULL的列值会使得索引、索引统计和值比较更加复杂。

浮点类型的建议统一选择decimal，精度会好很多。

记录时间的建议使用时间戳格式，存储方便，索引高效。