【MySQL】数据类型

一、常见数据类型汇总

分类数据类型说明数值类型BIT(M)二进制位。M指定位数，默认为1，范围1-64TINYINT [UNSIGNED]默认有符号，范围-128127，无符号范围0255BOOL1/0表示真/假SMALLINT [UNSIGNED]带符号范围-2^152^15-1，无符号范围02^16-1INT [UNSIGNED]带符号范围-2^312^31-1，无符号范围02^32-1BIGINT [UNSIGNED]带符号范围-2^632^63-1，无符号范围02^64-1FLOAT[(M, D)] [UNSIGNED]占4字节，M指定显示长度，D指定小数位数DOUBLE[(M, D)] [UNSIGNED]更大精度的小数，占8字节DECIMAL(M, D) [UNSIGNED]小数，精度更大文本、二进制类型CHAR(SIZE)固定长度字符串，最大255VARCHAR(SIZE)可变长度字符串，最大65535BLOB二进制数据TEXT大文本，不支持全文索引，不支持默认值日期和时间DATE/DATETIME/TIMESTAMP不同格式的时间日期String类型ENUM枚举，多选一SET集合，多选多

二、数值类型

2.1 tinyint

tinyint类型的数据在内存中只占一个字节，因此其能够表示的范围也比较小

有符号的tinyint能够表示的数据范围在**-128127之间，如果加上unsigned表示无符号，范围变为0255**

我们首先创建一个表，包含一列类型为有符号tinyint的字段

mysql> create table test1(
    -> num tinyint
    -> );

在范围内的值可以正常插入，如果插入一个超出范围的值呢？

128已经超出有符号的tinyint范围了，因此MySQL不允许我们进行插入

我们输入alter table test1 modify num tinyint unsigned 将num列的类型修改为无符号的tinyint，再尝试插入

此时就可以插入了

在编程语言中，向一个小空间存入大的数据可能不会报错，而是发生截断或隐式类型转换

但MySQL会对我们插入的数据进行约束，如果数据超出了规定的范围是不被允许插入的，这也是数据库的保护机制，能够保证数据库中的数据一定是合法的

更大范围的整数类型与tinyint没啥区别，这里就不作过多介绍，大家在使用时根据自己需求选择合适的类型即可。

2.2 bit

bit类型是一个表示二进制位的字段。在说明类型时，bit后面可以跟指定位数，如果不指定则默认位数为1

例如：

mysql> create table if not exists test2(
    -> id int,
    -> online bit(1) comment '用户是否在线'
    -> );

我们的online字段位数为1，所以只能插入0或1，表示用户是否在线

可以看到online的位数为1，而十进制数2已经超出了一位二进制能够表达的范围，因此不允许插入

将online的位数改大一点，就可以插入更大的数据了

但是我们在select的时候可以看到online列的数据显示不出来，这是因为bit类型的数据在显示时默认按照ASCII码的形式显示，例如：

a的ASCII码为97，因此我们插入的97在显示时也以字符a的形式显示了

2.3 float

float类型可以表示一个精度较低的小数，后面可以接指定的长度与小数位数，在内存中占4字节

例如：

mysql> create table if not exists test3(
    -> score float(4,2)
    -> );

对于类型为float(4,2)的小数，其长度为4，小数位数为2，能够表示的范围为-99.9999.99。如果定义为无符号，则只会把负数部分砍掉，变为099.99，正数部分的范围不会变大

MySQL在保存小数的时候会按照指定位数进行四舍五入

可以看到虽然99.994已经超过了float(4,2)的最大范围，但四舍五入后是符合条件的。而99.999四舍五入后超出范围，不允许插入

如果不指定长度和小数位数，则float使用默认的范围

修改类型为默认的float

mysql> alter table test3 modify score float;

可以看到float会有一定的精度损失，能够表示的精度大约是7位

2.4 decimal

decimal的语法跟float的差不多，二者的区别在于精度不同，decimal的精度更高

例如：

mysql> create table if not exists test4(
    -> t1 float(10,8),
    -> t2 decimal(10,8)
    -> );

我们分别向表中的两个字段插入对应数据

可以看到，类型为float(10,8)的字段t1在存放数据时已经发生了精度丢失，而类型为decimal(10,8)的字段t2能够完整的将我们的数据保存

decimal的长度M最大为65，小数位数D最大为30，默认长度和小数位数为(10,0)

三、字符串类型

3.1 char

char在C/C++中表示字符，而在MySQL中表示固定长度的字符串

char的后面可以跟长度L，指定字符串的最长长度，单位为字符，最大为255

例如：

mysql> create table if not exists test5(
    -> id int,
    -> name char(2)
    -> );

在表test5中，name的类型为最长为2的固定长度字符串

可以看到，'abc'的长度为3，超出了限制，因此不允许插入

可以看到，即便是中文字符，只要长度不超过限制，也能够插入

MySQL中的字符和我们编程语言意义上的字符是不同的，mysql中的字符只代表一种符号，一个汉字就是一个字符，以字符为单位存储

当我们想要将类型修改为char(256)时不被允许，这是因为char的长度最大只能设置为255

3.2 varchar

varchar类型表示一个可变长度的字符串，最大长度为65535字节

可以看到，varchar的单位也是以字符数为单位的，下面也提示我们varchar的最大长度为21845

在不同的编码格式下，一个字符所占的字节个数也不同，因此varchar可存储的字符数量也可能不同。在默认的utf8编码格式下，一个字符占3个字节，因此65535字节正好21845个字符

如果在gbk编码格式下，一个字符占2字节，65535字节就换算为32766个字符

那我们改成21845再试一下呢？

实际上也是行不通的，这是因为varchar类型是可变长度的字符串，什么是可变长度字符串？

char是固定长度的字符串，类似于C/C++中定义了一个字符数组，即使你的字符串中只有一个字符，也会按照char的长度给你分配固定的内存

而varchar则是字符串中有多少字符就分配多少空间，但最多不能超过定义的上限

因此，varchar类型的字段需要有1-3个字节来记录字符串中数据的长度，这就导致了虽然规定长度最大为21845（utf8编码下），但实际最多只能存21844个字符

四、日期和时间类型

常用的日期和时间类型有：

• date：格式为 'yyyy-mm-dd' ，占3字节
• datetime：格式为 'yyyy-mm-dd HH:ii:ss' ，占8字节
• timestamp：时间戳，格式与datetime相同，从1970年开始，占4字节

MySQL中的时间戳不是以秒为单位的，而是转换为了对应格式

date类型和datetime类型的数据需要我们自行设置格式插入，timestamp类型的属性则不需要。如果存在类型为timestamp的属性，当我们向行中插入或修改数据时，timestamp会自动更新

例如：

mysql> create table if not exists test7(
    -> t1 date,
    -> t2 datetime,
    -> t3 timestamp
    -> );

可以看到，虽然我们没有指定t3的数据，但当我们插入一行数据时t3自动被更新为当前时间戳

如果对行中的数据进行修改，也会触发时间戳的更新

五、枚举和集合

5.1 enum枚举

有时我们在建表时想要限制用户在某些字段中只能插入我们指定范围内的值，就可以使用枚举或集合。枚举类型中提供了若干个值，用户在插入时只能选择这些值中的一个

例如：

mysql> create table if not exists test8(
    -> id int,
    -> gender enum('男','女')
    -> );

其实就是单选题，应该不难理解

枚举中的值在存储时实际上是以数字的形式存储的，即枚举的每个选项都对应了一个数字

可以看到，枚举的第一个值对应1，第二个值就对应了2......

5.2 set集合

集合类型中提供了若干个值，用户在插入时可以选择这些值中的多个，即多选多

例如：

mysql> create table if not exists test9(
    -> id int,
    -> hobby set('篮球','足球','乒乓球','羽毛球','游泳')
    -> );

枚举中的选项有自己对应的数字，从1开始递增，集合也是这样吗？

0表示空，不选值

1和2对应篮球和足球，好像是一样的

3不应该对应乒乓球吗？怎么变成篮球和足球了？

我们可以把集合看作一个位图，每个选项对应一个二进制位，选择某个选项则对应位为1，否则为0

假设集合中有5个选项，则二进制串00000对应十进制的0，表示什么都不选，空串

二进制串00001对应十进制的1，对应选择第一个选项

二进制串00010对应十进制的2，对应选择第二个选项

二进制串00011对应十进制的3，对应选择第一个和第二个选项

现在明白了吗？

对集合类型的字段进行查找，会严格按照我们要查找的值进行筛选，例如假设我们想要筛选出所有兴趣包含足球的数据

可以看到按照这个方式查找，只会严格按照我们输入的内容进行筛选，也就是说其他包含该内容的数据不在筛选范围内

如果想要实现筛选出所有包含该内容的数据，可以用find_in_set函数

完.