MySQL的各种数据类型

1、MySQL 数据类型

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

 

1.2、mysql中编码和字符

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

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

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

 

2、数值类型

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

类型	大小	范围（有符号，默认）	范围（无符号）	用途	对应java类
TINYINT	1 Bytes	(-128，127)	(0，255)	小整数值 一个很小很小的整数	Integer
SMALLINT	2 Bytes	(-32 768，32 767)	(0，65 535)	大整数值 一个小整数	Integer
MEDIUMINT	3 Bytes	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值 一个中等大小的整数	Integer
INT或INTEGER	4 Bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值 一个int大小的整数 （大都用来做id）	Integer
BIGINT	8 Bytes	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值 一个蛮大的整数（也常用来做id）	Long
FLOAT	4 Bytes	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度，浮点数值。 学生成绩、允许有误差的、单精度浮点数	Float
DOUBLE	8 Bytes	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度，浮点数值。 学生成绩、允许有误差的、双精度浮点数	Double
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值 例如定义 DECIMAL(5,2)  ，则表示最大位数为5位，小数点后2位，于是取值范围为 -999.99至999.99	依赖于M和D的值	小数值。 用来计算工资、盈利、金融方面	Java.math.BigDecimal

比如 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会保留，这一点也需要注意。

可参考：https://zhuanlan.zhihu.com/p/111028232

 

3、字符串类型

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

类型	大小	用途	对应java类
CHAR	0-255 bytes（字节）	定长字符串	String
VARCHAR	0-65535 bytes	变长字符串	String
TINYTEXT	0-255 bytes	短文本字符串	String
TEXT	0-65 535 bytes	长文本数据	String
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据	String
LONGTEXT	0-4 294 967 295 bytes	极大文本数据	String

 

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。

 

4、日期和时间类型

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

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

类型	大小 ( 固定长度、单位为字节)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD（年月日）	日期值
TIME	3	'-838:59:59'/'838:59:59'	HH:MM:SS（时分秒）	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS（年月日时分秒）	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

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

 

4.1、DATETIME

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

假设建表语句为：

 

则存储结果分别为：

 

5、二进制数据类型

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

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