Mysql数据库基础（四）—— 表的字段类型（Mysql数据类型）

Mysql的数据类型是一种约束，为了确保数据插入和存储的一致性，一旦我们插入的数据不合法，比如插入与字段类型不符的数据，Mysql会直接终止。这一点上和C语言就有区别，C语言存在隐式类型转换，即便类型不一致，编译器也不会报错。

除此之外，虽然Mysql中的数据类型和C语言具有相似性，但是表达的意义不一定相同，比如下面的 varchar，存储时是以字符为单位，而并非字节。

---

          目录

1、Mysql数据类型的分类

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

(1) tinyint / int / bigint

(2) bit 类型

(3) float / double

(4) decimal 

2、字符串类型

(1) char

(2) varchar

(3) char 和 varchar 的区别

3、日期类型

4、枚举类型 

(1) enum

(2) set

(3) set 集合的查询（find_in_set 函数）

---

1、Mysql数据类型的分类

Mysql和C语言一样，数据类型可以分为整型、浮点型、字符型，也可以带符号（有符号、无符号）。Mysql 也有自己独有的日期类型。

为了便于理解，Mysql 和 C语言的某些类型比较相似。

Mysql类型	C语言类型	取值范围
tinyint	char	带符号：-128~127
tinyint  unsigned（其他同理）	unsigned char	无符号：0~255
smallint	short	详见上表
int	int	详见上表
bigint	long	详见上表

注意：Mysql的 char —— C语言的数组、Mysql的varchar —— C语言的字符串。这两个例子比较特殊，后面会具体介绍。

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

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

(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) 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 不支持显示。

(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都会直接终止。

(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
);

2、字符串类型

(1) char

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

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

-- 语法格式: L是可以存储的长度，单位是字符，最大长度值可以为255
char(L)

-- 案例: 
create table t5 ( 
    id int, 
    name char(10),  -- 分配10个字符的长度
);

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

(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) 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、枚举类型 

(1) enum

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

-- 语法格式: 最多可以有65535个选项
enum('选项1','选项2','选项3',...);

-- 案例: 
create table t5 ( 
    sex enum('男','女','未知')
);

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

(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 对应 ' 游泳 '

(3) set 集合的查询（find_in_set 函数）

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

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