MySQL从0到1全教程【3】MySQL数据库中常见的几种表字段数据类型

1 前言

MySQL的数据类型是一种约束，为了确保数据插入和存储的一致性，一旦我们插入的数据不合法，比如插入与字段类型不符的数据，MySQL会直接终止。在MySQL数据库中，数据类型有很多种，主要分为三大类：数值类型、字符串类型、时间日期类型。

2 数值类型（整数、小数）

在MySQL中，整型可以指定是有符号的和无符号的，默认是有符号的。可以通过UNSIGNED来说明某个字段是无符号的，注意使用无符号的取值范围变化。

2.1 tinyint / int / bigint

使用这些类型的时候，只需注意不要越界。

比如 tinyint的取值范围是 -128 ~ 127，如果插入 128，Mysql会直接终止。

mysql> insert into t1 values(128);     -- 越界插入，报错
ERROR 1264 (22003): Out of range value for column 'num' at row 1

注意：其实 int(M) 这种写法也是支持的，这里的M代表要显示字符个数，并不会限制int的取值，int(M) 和 zerofill 搭配使用才有意义。一般情况下都是直接使用 int

2.2 bit 类型

bit 是一个位字段类型，M的取值范围是 1~ 64，没有明确M时，默认分配1 bit

-- 语法格式: M表示位数，M 可以省略，如果不写 M，默认分配 1 bit
bit(M)
 
-- 案例: 
create table t2 ( 
    id int, 
    sex bit,         -- 默认分配 1 bit
    number bit(8)    -- 分配 8 bit
);

需要注意的是，bit字段在显示的时候，是按照ASCII码对应的值显示。比如插入 41，此时就会显示字母 ’ A '，但并非所有的插入的值转换成ASCII 码显示，有些字符Mysql 不支持显示。

2.3 float / double

float 和 double都可以用于存储小数，float 支持的最大精度是7位。有符号和无符号的取值范围不同，以float(4, 2) 为例：

• float(4, 2) 是有符号，取值范围 -99.99 ~ 99.99
• float(4, 2) unsigned 是无符号，取值范围 0 ~ 99.99

-- 语法格式: M 表示有效位数(整数+小数位数)，D 表示小数位数
float(M, D)
 
-- 案例: 
create table t5 ( 
    id int, 
    salary float(4,2),    -- 有效位数是4，小数位数是2，取值范围 -99.99 ~ 99.99
);

Mysql会检查插入的小数位数和总体长度：

• 小数部分：如果实际小数部分的长度超出预设的值 ( D )，会四舍五入
• 整数部分：如果四舍五入以后，总体长度超出预设的值 ( M ) 或者小数部分长度不满足预设的值 ( D )，此时如果即便补全也无法满足条件，Mysql会直接终止
  • float(4, 2)：插入 1.99 ，可以补全为 01.99
  • float(4, 2)：插入111.99，直接报错

总而言之，四舍五入以后，如果总体长度 ≠ M 且 小数位数 ≠ D，Mysql都会直接终止。

2.4 decimal

float 和 decimal 的使用方式完全一样，同样也需要注意四舍五入以后，总体长度 = M 且 小数位数 = D，他们之间的区别在于 decimal 和 float 支持的精度不同，float的支持的最大精度是7位，decimal的支持的最大精度是30。一般更推荐使用decimal。

-- 语法格式: M 表示有效位数(整数+小数位数)，D 表示小数位数
decimal(M, D)
 
-- 案例: 
create table t5 ( 
    id int, 
    salary decimal(4,2),  -- 有效位数是4，小数位数是2，取值范围 -99.99 ~ 99.99
);

3 字符串类型

3.1 char

char(L) 单位是字符，最多是255。（限制的是字符个数）

char(L) 类似于数组，实际占用的空间就是 L 个字符的空间。

-- 语法格式: L是可以存储的长度，单位是字符，最大长度值可以为255
char(L)
 
-- 案例: 
create table t5 ( 
    id int, 
    name char(10),  -- 分配10个字符的长度
);

注意：char(L) 的单位是字符，不是字节。字符可以是汉字或者字母，一个汉字算一个字符，一个字母也算一个字符。

3.2 varchar

varchar(L) 单位是字符，最大字节数是65535，其实是65532（uft8），因为需要有1 - 3 个字节用于记录“实际存储的数据个数”。

varchar(L) 类似于字符串容器，相当于给你预留了 L 个字符 ( L* 3 或者 L* 2 个字节 ) 的空间，实际占用多大空间根据你存入的数据决定。

注意：一个字符占多少字节，会随当前系统的字符集变化。

• 如果字符集为 utf8，一个字符 = 3个字节，此时varchar最多可以保存的字符个数为 65532/3 = 21844
• 如果字符集为 gbk，一个字符 = 2个字节，此时varchar最多可以保存的字符个数为 65532/2 = 32766

-- 语法格式: L是可以存储的长度，单位是字符，最大字节数是65535
varchar(L)
 
-- 案例: 
create table t5 ( 
    id int, 
    name char(10),  -- 分配10个字符的长度
);

3.3 char 和 varchar 的区别

限制条件的角度：char限制的是字符数，varchar限制的是字节数

空间占用的角度：

• char(L)：实际占用的空间大小就是 L 个字符的大小
• varchar(L)：L个字符仅表示最多可以使用多大空间，实际占用的空间根据存入的内容大小决定

举个例子，我们创建的表中包含两个字段，类型分别是char(4) 和 varchar(4) ，两个字段都存入字母’A’ ，请问这两个字段分别占用了多大空间？（假设字符集是utf8）

create table t5 ( 
    name1 char(4),      -- 存入字母'A'，占用了多大空间？
    name2 varchar(4),   -- 存入字母'A'，占用了多大空间？
);

首先是字段name1 char(4)，实际占用的空间就是分配的字符个数。分配了4个字符，占用的字节数为 4 * 3 = 12 个字节

然后是字段 name2 varchar(4)，实际占用的空间取决于存入的内容大小。存入 1 个字符，占用的字节数为 1 * 3 + 1 = 4 个字节（最后加1是因为需要1个字节记录实际存储的数据个数）

3 日期类型

常用的日期类型有以下三个：

• date：日期格式 ‘yyyy-mm-dd’ ，占用3个字节（仅存储日期）
• datetime：时间日期格式 ’ yyyy-mm-dd HH:ii:ss '，占用8个字节（存储日期 + 时间）
• timestamp：时间戳，从1970年开始到当前日期时间经过的秒数， 转换成 yyyy-mm-dd HH:ii:ss 格式以后和datetime类型完全一致，占用 4 个字节（存储日期 + 时间）

其中有几点需要注意：

①timestamp 类型有默认值，默认就是当前时间。所以即便不插入内容也会自动补充，建议能手动插入的务必手动插入，不要把“生杀大权握在别人手里”。

②数据库会自动修正与格式不符的分隔符，或者补全不完整的日期。比如图中的10补全为2010，分隔符由’ / ’ 修正为 ’ - ’

4 枚举类型

4.1 enum

和C语言中的一致，在定义字段的时候就要指定枚举的内容。插入的值必须是枚举列表中的一个值。

-- 语法格式: 最多可以有65535个选项
enum('选项1','选项2','选项3',...);
 
-- 案例: 
create table t5 ( 
    sex enum('男','女','未知')
);

插入数据时，插入的内容必须是枚举范围内的内容或者下标，如果写入了枚举范围之外的内容，数据库就会报错。比如 sex enum(‘男’,‘女’,‘未知’) 字段可以写入的值只能是 ‘男’ 、‘女’、‘未知’ 中的一个（ 或者0 、1、2中的一个）

4.2 set

set 在这里的意思仅表示集合，与C++中的set无关。set 和 enum的使用方法一样，必须在定义字段的时候指定枚举的内容。与 enum 不同的是，set 可以选择枚举列表中的多个值来插入到数据库中，多个值需要用逗号分隔符隔开。

-- 语法格式: 最多可以有64个选项
set('选项1','选项2','选项3',...);
 
-- 案例: 
create table t5 ( 
    hobby set('游泳','踢足球','打羽毛球','打篮球')
);
 
-- 选择多个枚举值插入（注意分隔符后面不能出现空格）
insert into t5 (hobby) values ('游泳,打羽毛球'); 
 
-- 也可以插入数字（等价于上面的写法）
insert into t5 (hobby) values (5);

注意： set 也可以插入数字，但是表示的意义和enum不一样，是以位图的形式来识别的。比如插入 5，转换成二进制就是 0101 ，第二个 1 对应 ’ 打羽毛球 '，第四个 1 对应 ’ 游泳 ’

4.3 set 集合的查询（find_in_set 函数）

假设要查询字段 hobby 包含 ‘游泳’ 的记录

-- find_in_set函数:
--                第一个参数: 要查找的内容
--                第二个参数: 要查找的字段
select * from t5 where find_in_set('游泳',hobby);