MySQL 数据类型总结

MySQL 支持多种类型的 SQL 数据类型：数字类型，日期和时间类型，字符串（字符和字节）类型，空间类型和 JSON 数据类型。

1. 数字类型

MySQL 的数字类型包括：Integer Types（整型）、Fixed-Point Types（定点类型）、Floating-Point Types （浮点类型）、Bit-Value(比特值型)等。

1.1 整型

在建表时，经常有类似的操作 int(10),这里我用 int(M)来表示，请注意：

• (1) 不管 M 多大，int 型永远占 4 字节的存储空间
• (2) M 表示显示宽度，不足的用 0 补齐，但是必须设置 UNSIGNED ZEROFILL 属性才能生效，超出指定长度时直接显示整个数字

对于上面的第二点，举例说明

-- 创建学生表
drop table if exists student;
create table student(
		stu_id int(6) unsigned zerofill primary key comment '学号',
		stu_name char(20) comment '学生姓名',
		stu_age tinyint unsigned  comment '学生年龄',
		stu_sex tinyint unsigned  comment '学生性别：0-女，1-男',
		address varchar(64) comment '家庭住址',
		create_date datetime comment '创建时间',
		status_date datetime comment '修改时间'
)comment '学生表';
-- 插入两条记录
insert into student(stu_name,stu_age,stu_sex,address,create_date,status_date) values('tom',20,1,'shanghai',now(),now());
insert into student(stu_id,stu_name,stu_age,stu_sex,address,create_date,status_date) values(12345678,'jerry',21,1,'shanghai',now(),now());

上面创建了一张 student 表，并插入一条记录，我们执行查询语句看下这条记录，如下所示；

可以看到，第一条结果 stu_id 显示为 000001，前面 5 位用 0 补齐；第二条 stu_id 显示为 12345678

1.2 浮点类型 FLOAT 和 DOUBLE

对于浮点类型，声明形式为 float(M,D)和 double(M,D)，其中 M 表示总位数，D 表示小数位数，如果小数部分超出 D 位，则进行四舍五入处理。例如 float(5,2)，表示整数部分占三位，小数部分占两位。

1.3 定点类型 Decimal

浮点类型 FLOAT 和 DOUBLE 会出现丢失精度的问题，而 Decimal 不会。Decimal 声明形式为 decimal(M,N),其中 M 表示总位数，N 表示小数位数（和浮点类型类似）。

2. 日期和时间类型

注意 fsp 是可选的，表示微秒位数，取值范围是 0~6，不指定则默认为 0（不显示微秒）。

3. 字符串类型 char 和 varchar

char 和 varchar 使用频率很高，总结如下

（1）char 表示固定长度的字符串，如果存入的字符长度不足 n 位，前面使用空格补齐，char 类型取值范围是 0~255；

（2）varchar 表示可变长度的字符串，取值范围是 0~65535，注意：实际最大长度取决于编码格式；

（3）char 类型占用的存储空间是保存字符所需要的字节数，而 varchar 类型占用的存储空间除了保存字符需要的字节数外，还要额外占用 1~2 个字节的空间（因为对于 varchar 类型，除了存储要保存的字符外，还需要存储字符长度，当字符长度不超过 255 时，则使用 1 个字节，当字符长度超过 255 时，则使用 2 个字节）；

（4）char 类型检索速度快于 varchar 类型；

（5）对于 char 类型，MySQL 会过滤尾部的空格，而 varchar 类型不会。

在实际使用中，如何确定使用 char 还是 varchar 呢，这里有一个参考标准：当你能大概确定保存的字符长度时，使用 char 是一个不错的选择；当你不能确定要保存的字符串长度时，使用 varchar，来达到节省空间的目的。

4. 对 varchar 类型保存的最大字符长度探究

在探讨这个问题之前，先说明几条 mysql 中的规范：

• （1）MySQL 表的内部表示的最大行大小限制为 65,535 字节
• （2）对于 varchar 类型，除了存储要保存的字符外，还需要存储字符长度，当字符长度不超过 255 时，则使用 1 个字节，当字符长度超过 255 时，则使用 2 个字节
• （3）对于 varchar 类型，需要使用一个字节来表示该字段是否为 null

（1）看下面一段 sql

create table test_varchar(
	 int_value int,
	 varchar_value varchar(65535)
)engine=innodb charset=utf8;

执行这段 sql，会看到这样的错误提示

1074 - Column length too big for column 'varchar_value' (max = 21845); use BLOB or TEXT instead

那么我们来修改一下 sql

create table test_varchar(
	 int_value int,
	 varchar_value varchar(21845)
)engine=innodb charset=utf8;

很不幸，执行这段 sql，仍然会提示我们错误：

1118 - Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

最后，我们把 sql 修改成这样：

create table test_varchar(
	 varchar_value varchar(21844)
)engine=innodb charset=utf8;

就可以成功执行了。

那么 21844 是怎么来的呢？首先，根据上面说的（2）、（3）两条规范，我们需要减去 2 个字节用于保存字符串长度，还需要减去一个字节，用来表示该字段是否为空；然后，对于 utf-8 编码需要 3 个字节来表示一个汉字；最后(65535-2-1)/3=21844

如果你把编码格式修改成 gbk，那么最大字符长度=(65535-2-1)/2=32766，除 2 是因为 gbk 编码使用 2 个字节来表示一个汉字。

但是，别忘了我们的第（1）条规范，单行长度不能超过 65535 字节，所以如果我们增加一个 int 字段，sql 如下：

create table test_varchar(
	 int_value int,
	 varchar_value varchar(21844)
)engine=innodb charset=utf8;

执行这段 sql，你仍然会得到以 1118 开头的错误码，原因就是超出了 MySQL 的单行长度限制。