MySQL数据类型

目录

数据类型分类

数值类型

tinyint类型

bit类型

float类型

decimal类型

字符串类型

char类型

varchar类型

时间日期类型

enum和set类型

---

数据类型分类

数据类型有什么用：

决定数据存储时应该开辟的空间大小

决定如何识别一个特定的二进制序列

决定数据的取值范围

 

 或者也可以如此分类：

MySQL中常用的数据类型：

1. 整数类型：

   • TINYINT：1字节，范围为-128到127（有符号）或0到255（无符号）。
   • SMALLINT：2字节，范围为-32768到32767（有符号）或0到65535（无符号）。
   • MEDIUMINT：3字节，范围为-8388608到8388607（有符号）或0到16777215（无符号）。
   • INT/INTEGER：4字节，范围为-2147483648到2147483647（有符号）或0到4294967295（无符号）。
   • BIGINT：8字节，范围为-9223372036854775808到9223372036854775807（有符号）或0到18446744073709551615（无符号）。

2. 浮点数类型：

   • FLOAT：单精度浮点数，4字节。
   • DOUBLE：双精度浮点数，8字节。
   • DECIMAL：定点数，用于存储精确的小数值。

3. 字符串类型：

   • CHAR：固定长度字符串，最多255个字符。
   • VARCHAR：可变长度字符串，最多65535个字符。
   • TEXT：可变长度文本，最多65535个字符。

4. 日期和时间类型：

   • DATE：日期，格式为'YYYY-MM-DD'。
   • TIME：时间，格式为'HH:MM:SS'。
   • DATETIME：日期时间，格式为'YYYY-MM-DD HH:MM:SS'。
   • TIMESTAMP：时间戳，记录时间戳的整数值。

5. 其他类型：

   • ENUM：枚举类型，从给定的值列表中选择一个值。
   • SET：集合类型，从给定的值列表中选择多个值。
   • BLOB：二进制大对象，用于存储二进制数据。

数值类型

tinyint类型

TINYINT：1字节，范围为-128到127（有符号）或0到255（无符号）

倘若插入超出这个范围的数字就会报错，插入失败，例如：

创建一个表，有两个数据类型的数据，一个为有符号的tinyint，一个为无符号的tinyint，使用insert插入数据查看效果：

说明：MySQL本身是不支持bool类型的，当把一个数据设置成bool类型时，数据库会自动将其转换成tinyint(1)的数据类型，其实这个就是变相的bool类型，因为tinyint(1)只有1和0两种取值，可以分别对应bool类型的true和false。

比如用tinyint(1) 和CHECK约束取值为0或1，就可以用tinyint(1)模拟出bool类型了。

 

注意：括号中数字1表示字段的显示宽度，当使用SELECT语句查询并显示tinyint字段时，MySQL会根据指定的显示宽度来对齐输出结果。如果字段的实际值宽度小于指定的显示宽度，MySQL会在左侧填充空格以满足指定的显示宽度。显示宽度仅影响查询结果的显示，不会限制字段的实际存储范围或大小。因此，尽管tinyint(1)指定了显示宽度为1，但仍然可以存储和处理更大或更小的整数值。 

bit类型

bit类型后括号中数字代表比特位的位数，影响它的取值范围。

有的情况下bit类型在显示时，是按照ASCII码对应值的字符进行显示的，这却决于编码方式。

例如：n1的取值为0-1

 n2为两个比特位，取值为0-3，而n3则为0-7

float类型

float(M,D)中M表示总位数，D表示小数位数。

例如：float(4,2)的取值范围为-99.99~99.99

由于MySQL在保存值时会进行四舍五入，因此实际可插入float(4,2)的范围为-99.994~99.994，如果插入的数据不在该范围内，那么插入数据时就会产生报错。

而float(4,2) unsigned的取值范围为0~99.99，实际可插入的范围是0~99.994，实际就是把对应有符号float类型中的负数去掉，含有负号都将插入失败（即使四舍五入为0）。

例如：

 

 

decimal类型

decimal和float类型类似，但decimal的精度比float更高，float大概为7位小数。

测试如下：

可见float有精度损失，decimal精度更高。 

字符串类型

char类型

char类型表示定长的字符串，可存储字符上限为255，char(L)定义后，无论存储的字符串长度是否到达L，都会开辟用于存储L个字符的定长空间，如果存储的字符串长度超过L则会报错。

注意这里的字符不是C语言中的字符，并不只是指一个英文字母，一个汉字也算是一个字符。

这样的好处是用户就不用关心复杂的编码细节，MySQL限定字符的概念不是字节，一个字符所占的字节个数是不同的，比如utf8中一个字符占3个字节，而gbk中一个字符占2个字节。

例如：

  

注：插入字符串时要加单引号，纯数字则不用。 

varchar类型

varchar类型最多占用65535字节，其中有1~2字节用来表示实际数据长度，还有1字节来存储其他控制信息，因此varchar类型的有效字节数最多是65532字节。

varchar(L)定义后，会根据存储字符串的长度按需开辟空间，并且需要使用1-3字节的空间用于表示存储字符串的长度以及其他控制信息，如果存储的字符串长度超过L则会报错。

而varchar类型可指定的字符个数上限L，与表的编码格式有关：

对于utf8编码来说，一个字符占用三个字节，因此varchar(L)中的L最大可指定为 65532 ÷ 3 = 21844。
对于gbk编码来说，一个字符占用两个字节，因此varchar(L)中的L最大可指定为 65532 ÷ 2 = 32766。

对于utf8mb4编码来说，一个字符占用四个字节，因此varchar(L)中的L最大可指定为 65532 ÷ 4 = 16383。

因此在定义编码格式为utf8mb4的表时，varchar(L)中的L如果超过了16383，则会产生报错。

例如：

 char和varchar比较

• char类型的数据是定长的，因此磁盘空间比较浪费，但是效率高（直接访问定长的空间）。
• varchar类型的数据是变长的，因此磁盘空间比较节省，但是效率低（需要先读取存储字符串的长度，再访问指定长度的空间）

时间日期类型

常用的三种时间日期类型如下：

• date：表示日期，日期格式为YYYY-MM-DD，占用三字节。
• datetime：表示日期和时间，日期时间格式为YYYY-MM-DD HH:MM:SS，占用八字节。
• timestamp：表示日期和时间（时间戳），格式为YYYY-MM-DD HH:MM:SS，占用四字节。

创建一个表，表当中包含date、datetime和timestamp三种时间日期类型的列。

查看表结构，timestamp类型的列不允许为空，具有默认值CURRENT_TIMESTAMP。例如：

enum和set类型

enum和set类型的区别如下：

• 在定义enum字段时需要提供若干个选项的值，在设置enum字段值时只允许选取其中的一个值。
• 在定义set字段时需要提供若干个选项的值，在设置set字段值时可以选取其中的一个或多个值。

简单来说enum是多选一的属性，而set则没有限定，好比人只有一个年龄一个性别，却有多个爱好。比如：

 

 MySQL出于效率考虑，在存储enum值时实际存储的都是数字，enum中提供的选项值依次对应数字1、2、3、…，最多65535个，因此在设置enum值时可以通过数字的方式进行设置。

因为MySQL存储set值时实际存储的也是数字，set中提供的选项值依次对应数字1、2、4、8、…，最多64个，因此在设置set值时可以通过数字的方式进行设置。（之所以是1，2，4这样的数字，转成2进制再结合set的特点就懂了）。

比如：(注3=1+2，31=1+2+4+8+16）

 查找时也可以利用数字这一特性：

 

还可以利用find_in_set函数，比如筛选出有跑步爱好的人：