【mysql】—— 数据类型详解

序言：

本期我将大家认识关于 mysql 数据库中的基本数据类型的学习。通过本篇文章，我相信大家对mysql 数据类型的理解都会更加深刻。

目录

（一）数据类型分类

（二）数值类型

1、tinyint类型

2、bit类型

3、小数类型

float类型

decimal

4、字符串类型

char

varchar

char和varchar比较

日期和时间类型

enum和set

总结

---

（一）数据类型分类

首先，在我们正式的学习数据类型之前，我们需要知道有哪些数据类型在mysql数据库中。因此，接下来我首先带大家认识具体有哪些数据类型。

💨 MySQL中的数据类型可以分为以下几个主要分类：

1️⃣数值类型：

• 整数类型：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT等。
• 浮点数类型：FLOAT、DOUBLE等。
• 定点数类型：DECIMAL。

2️⃣字符串类型：

• 固定长度字符串：CHAR。
• 可变长度字符串：VARCHAR。
• 大文本类型：TEXT。

3️⃣日期和时间类型：

• DATE：日期。
• TIME：时间。
• DATETIME：日期和时间。
• TIMESTAMP：时间戳。

4️⃣ 布尔类型：

• BOOL、BOOLEAN。

5️⃣枚举和集合类型：

• ENUM：从预定义值列表中选择一个值。
• SET：从预定义值集合中选择多个值。

6️⃣二进制类型：

• BLOB：用于存储二进制数据。

7️⃣ JSON类型：用于存储和处理JSON格式的数据。

 8️⃣空间数据类型：用于处理地理空间数据。

最后，我给大家整理总结了一张表，大家可以直观的去感受（对于其中红色的是需要大家掌握的，其余的等到需要用到的时候在学习不迟！！）

【小结】

这些数据类型可以根据需求在MySQL表的列定义中使用。在选择数据类型时，需要考虑存储需求、数据的精度要求、操作和计算的效率等因素。此外，MySQL还支持一些特殊的数据类型和存储引擎相关的数据类型，如GEOMETRY、POINT、XML等，可以根据具体需求选择使用。

---

（二）数值类型

下面是一些常用的数据类型值：

---

1、tinyint类型

MySQL中的 TINYINT 类型是一种整数类型，用于存储小整数值，占用1个字节的存储空间。

下面是关于TINYINT类型的详解

数据范围和存储空间：

• 有符号（默认）：范围为-128到127，占用1字节的存储空间
• 无符号：范围为0到255，占用1字节的存储空间

用途：

• TINYINT通常用于存储布尔值（0或1），表示真或假。
• 也可以用于存储较小的整数值。

有符号和无符号：

• 默认情况下，TINYINT是有符号的，范围为 [-128，127]
• 可以使用 UNSIGNED 关键字来声明TINYINT为无符号，范围变为【0,255】

存储方式：

• TINYINT类型的值以二进制格式存储。对于有符号类型，最高位（最左边的位）表示正负号，‘0’ 表示正数，‘1’表示负数
• 在使用时，MySQL会根据需要将二进制值转换为可读的整数形式。

💨 示例：

• 数值越界测试：

①创建一个TINYINT列：

 

②插入数据：

 

 ③查询数据：

【小结】 

•  上述，我们已经成功的进行了相关的测试。

但是接下来，我在插入这样的数据，看最终是否还能成功的插入呢？

 【说明】

1. 在MySQL中，整型可以指定是有符号的和无符号的，默认是有符号的。
2. 可以通过UNSIGNED来说明某个字段是无符号的

• 无符号案例：

①创建一个TINYINT列：

 ②插入数据（注意范围）：

 ③查询数据：

 

 

【注意】

• 尽量不使用unsigned，对于int类型可能存放不下的数据，int unsigned同样可能存放不下，与其如此，还不如设计时，将int类型提升为bigint类型。

【小结】

总之，TINYINT类型是MySQL中的一种整数类型，适用于存储布尔值或较小的整数。

它占用较少的存储空间，查询和计算效率高。可以根据需要选择是否使用有符号或无符号的TINYINT类型。

---

2、bit类型

MySQL中的BIT类型是一种用于存储位字段（bit fields）的数据类型。

下面是关于BIT类型的详解

• 基本语法：

bit[(M)] : 位字段类型。M表示每个值的位数，范围从1到64。如果M被忽略，默认为1。

存储空间：

• BIT数据类型占用的存储空间取决于定义时指定的位数，最大位数为64

位数限制：

• BIT类型可以定义的位数可以是1到64之间的任意整数

存储方式：

• BIT类型的数据以二进制形式进行存储

使用场景：

• BIT类型通常用于表示布尔值或位掩码。
• 可以用于存储二进制标志，每个位表示一种状态，如开/关、启用/禁用等。

查询和计算操作：

• 查询和计算BIT类型的数据需要使用相关的位运算操作符，如按位与（&）、按位或（|）、按位异或（^）等

默认值：

• BIT类型的默认值‘1’，也可以使用b'xxx'或0bxxx的形式指定具体的二进制值

💨 示例：

①创建一个 bit 列：

 

 ②插入数据（注意范围）：

  ③查询数据：

 【说明】

1. 此时我们会发现很怪异的现象，a的数据10没有出现；
2. 其实 bit 字段在显示时，是按照ASCII码对应的值显示，因此当我们插入 10之后没有任何显示

 

【注意】

如果我们有这样的值，只存放 0 或 1 ，这时可以定义 bit(1) 。这样可以节省空间

 

【小结】

总之，BIT类型是用于存储位字段的数据类型，可以指定位数范围，并以二进制形式进行存储。

它适用于存储布尔值或位掩码，并可以使用位运算操作符进行查询和计算操作。

需要注意的是：在操作BIT类型数据时，需要使用适当的位运算操作符来处理位级别的操作。

---

3、小数类型

float类型

MySQL中的FLOAT类型是一种浮点数类型，用于存储单精度浮点数。

下面是有关FLOAT类型的详解

• 语法：

float[(m, d)] [unsigned] : M指定显示长度，d指定小数位数，占用空间4个字节

存储范围和精度：

• FLOAT类型占用4字节的存储空间。
• 可以存储的范围为 [-3.402823466E+38，-1.175494351E-38]、[0]、[1.175494351E-38，3.402823466E+38] 之间的浮点数。
• 最多可存储7位小数。

精度和舍入错误：

• FLOAT类型是近似值类型，并且以二进制形式存储。
• 存在舍入错误的可能性，这意味着在进行计算时可能会出现轻微的精度损失。
• 比较FLOAT类型的值时，可能需要使用误差范围进行判断，而不是简单的等于比较。

默认值：

• FLOAT类型的默认值为 ‘0’

查询和操作：

• FLOAT类型可以使用算术运算符（如加法、减法、乘法、除法等）进行计算。
• 可以与其他数值类型进行混合运算，MySQL会自动进行类型转换。

存储方式：

• FLOAT类型的值以二进制格式存储，并使用IEEE 754标准表示。
• 在使用时，MySQL会根据需要将二进制值转换为可读的浮点数形式。

💨 示例1：

• 小数：float(4,2)表示的范围是-99.99 ~ 99.99，MySQL在保存值时会进行四舍五入。

 

💨 示例2：

• 如果定义的是float(4,2) unsigned 这时，因为把它指定为无符号的数，范围是 0 ~ 99.99

 

【小结】

1、总之，FLOAT类型是MySQL中的一种浮点数类型，适用于存储具有小数部分的数值。

2、它占用较少的存储空间，并提供了一定的范围和精度。

3、然而，在进行浮点数计算时，需要注意舍入误差和精度损失可能带来的影响。如果需要更高的精度，可以考虑使用DOUBLE类型。

---

decimal

MySQL中的DECIMAL类型是一种精确数值类型，用于存储固定精度的小数值

下面是关于DECIMAL类型的详解

• 语法：

decimal(m, d) [unsigned] : 定点数m指定长度，d表示小数点的位数

【说明】

1. decimal(5,2) 表示的范围是 -999.99 ~ 999.99
2. decimal(5,2) unsigned 表示的范围 0 ~ 999.99
3. decimal和float很像，但是有区别: float和decimal表示的精度不一样

存储空间：

• DECIMAL类型的存储空间取决于定义时指定的精度和小数位数

精度和小数位数：

• DECIMAL类型需要指定精度和小数位数，例如DECIMAL(10, 2)，其中10表示总位数，2表示小数位数；
• 精度范围为1到65，默认精度为10，默认小数位数为0

范围：

• DECIMAL类型可以存储从-10^（精度-小数位数）+1到10^（精度-小数位数）-1的数值

存储方式：

• DECIMAL类型使用定点表示法进行存储，以确保精确性

精确计算：

• DECIMAL类型的计算是精确的，不会有舍入误差

使用场景：

• DECIMAL类型适用于需要对小数进行精确计算的场景，如货币金额、精确统计数据等。
• 由于DECIMAL类型的存储和计算是精确的，它比FLOAT和DOUBLE类型更适合处理需要高精度的小数值。

💨 示例：

【说明】

1. float表示的精度大约是7位；
2. decimal整数最大位数m为65。支持小数最大位数d是30。如果d被省略，默认为0.如果m被省略， 默认是10。

💨 建议：如果希望小数的精度高，推荐使用decimal。

【小结】

1、总之，DECIMAL类型是MySQL中的一种精确数值类型，用于存储固定精度的小数值。

2、它提供了精确的计算和存储，并适用于需要高精度和准确性的场景。

3、在定义DECIMAL列时，需要指定精度和小数位数，以满足具体的业务需求。

---

4、字符串类型

char

MySQL中的CHAR类型是一种固定长度的字符串类型，用于存储定长的字符数据。

下面是关于CHAR类型的详解

• 语法：

char(L): 固定长度字符串，L是可以存储的长度，单位为字符，最大长度值可以为255

存储空间：

• CHAR类型占用固定长度的存储空间，占用的字节数取决于定义时指定的长度

长度限制：

• CHAR类型需要在定义时指定长度，长度范围为1到255个字符，默认长度为1

填充空格：

• 如果存储的字符串长度小于定义的长度，将使用空格填充剩余的位置

查询效率：

• 由于CHAR类型是固定长度的，查询效率比可变长度的字符串类型（如VARCHAR）更高。因为在检索和比较时，不需要计算实际存储的字符串长度

存储方式：

• CHAR类型的字符串以定长的方式进行存储，不受实际存储内容的影响

使用场景：

• 适用于存储长度固定的字符串，如国家代码、性别、状态等。
• CHAR类型通常用于存储较短的字符串，而不适用于存储可变长度的大文本。

💨 示例：

 

【说明】

• char(2) 表示可以存放两个字符，可以是字母或汉字，但是不能超过2个， 最多只能是255

 

【小结】

1、CHAR类型是MySQL中的一种固定长度字符串类型，适用于存储定长的字符数据。

2、它占用固定的存储空间，效率较高，并且适合存储长度不变的字符串。

3、在定义CHAR列时，需要指定长度以满足具体的业务需求。

---

varchar

MySQL中的VARCHAR类型是一种可变长度的字符串类型，用于存储可变长度的字符数据。

下面是关于VARCHAR类型的详解

• 语法：

varchar(L): 可变长度字符串，L表示字符长度，最大长度65535个字节

存储空间：

• VARCHAR类型占用实际存储的字节数，根据存储的字符串长度而变化

长度限制：

• VARCHAR类型需要在定义时指定最大长度，长度范围为1到65535个字符，默认长度为1

存储效率：

• 由于VARCHAR类型是可变长度的，存储的实际字节数取决于存储的内容长度，对于较短的字符串，占用的存储空间较小

查询效率：

1. 由于VARCHAR类型是可变长度的，查询效率相对于CHAR类型可能稍低；
2. 因为在检索和比较时，需要计算实际存储的字符串长度。

存储方式：

• VARCHAR类型的字符串以变长方式进行存储，只占用实际存储内容所需的空间

使用场景：

1. 适用于存储长度可变的字符串，如用户名、电子邮件地址、文章内容等。
2. VARCHAR类型通常用于存储较长的字符串，尤其是长度不确定的情况。

💨 示例：

 

【说明】

1. 关于varchar(len),len到底是多大，这个len值，和表的编码密切相关：
2. varchar长度可以指定为 [0，65535] 之间的值，但是有1 - 3 个字节用于记录数据大小，所以说有效字节数是 65532。
3. 当我们的表的编码是utf8时，varchar(n)的参数n最大值是65532/3=21844[因为utf中，一个字符占 用3个字节]；
4. 如果编码是gbk，varchar(n)的参数n最大是65532/2=32766（因为gbk中，一个字符 占用2字节）

 

【小结】

1、总之，VARCHAR类型是MySQL中的一种可变长度字符串类型，适用于存储可变长度的字符数据

2、它根据实际存储内容的长度来占用存储空间，效率较高，并适用于存储长度不确定的字符串。

 

---

char和varchar比较

我给大家整理了一张表格帮助大家理解记忆：

 

如何选择定长或变长字符串？

• 如果数据确定长度都一样，就使用定长（char），比如：身份证，手机号，md5
• 如果数据长度有变化,就使用变长(varchar), 比如：名字，地址，但是你要保证最长的能存的进去。
• 定长的磁盘空间比较浪费，但是效率高。
• 变长的磁盘空间比较节省，但是效率低。
• 定长的意义是，直接开辟好对应的空间
• 变长的意义是，在不超过自定义范围的情况下，用多少，开辟多少。

---

日期和时间类型

MySQL中提供了多种日期和时间类型，用于存储日期、时间和日期时间数据

下面是关于MySQL日期和时间类型的详解

1. DATE类型：用于存储日期值（年、月、日），格式为'YYYY-MM-DD'。范围从'1000-01-01'到'9999-12-31'。

2. TIME类型：用于存储时间值（时、分、秒），格式为'HH:MM:SS'。范围从'-838:59:59'到'838:59:59'。

3. DATETIME类型：用于存储日期和时间值，格式为'YYYY-MM-DD HH:MM:SS'。范围从'1000-01-01 00:00:00'到'9999-12-31 23:59:59'。

4. TIMESTAMP类型：与DATETIME类型类似，但范围限制为'1970-01-01 00:00:01'到'2038-01-19 03:14:07'。通常用于记录某条数据的创建或修改时间。

5. YEAR类型：存储年份值，格式为'YYYY'。范围从1901到2155。

6. INTERVAL类型：用于表示时间间隔或时间差，可以与日期或时间类型相加或相减。

💨 示例：

 

【小结】

1、MySQL提供了多种日期和时间类型，可根据具体需求选择合适的类型来存储日期、时间和日期时间数据。

---

enum和set

MySQL中的ENUM和SET类型是用于存储具有固定取值的数据的列类型

下面是关于ENUM和SET类型的详解

• 语法：

enum：枚举，“单选”类型；enum('选项1','选项2','选项3',...);

【说明】

1. 该设定只是提供了若干个选项的值，最终一个单元格中，实际只存储了其中一个值；
2. 而且出于效率考 虑，这些值实际存储的是“数字”，因为这些选项的每个选项值依次对应如下数字：1,2,3,....最多65535 个；
3. 当我们添加枚举值时，也可以添加对应的数字编号。

• 语法：

set：集合，“多选”类型；set('选项值1','选项值2','选项值3', ...);

【说明】

1. 该设定只是提供了若干个选项的值，最终一个单元格中，设计可存储了其中任意多个值；
2. 而且出于效率 考虑，这些值实际存储的是“数字”，因为这些选项的每个选项值依次对应如下数字：1,2,4,8,16,32，.... 最多64个。

说明：不建议在添加枚举值，集合值的时候采用数字的方式，因为不利于阅读。

💨 示例：

有一个调查表 votes ，需要调查人的喜好， 比如（登山，游泳，篮球，武术）中去选择 ( 可以多选 ) ， （男，女）[ 单选 ]

•  插入数据：

•  有如下数据，想查找所有喜欢登山的人：

•  我们可以发现不能查询出所有，爱好为登山的人

集合查询使用 find_ in_ set 函数：

find_in_set(sub,str_list) ：

1. 如果 sub 在 str_list 中，则返回下标；如果不在，返回0；
2. str_list 用逗号分隔的字符串。

mysql> select find_in_set('a', 'a,b,c');
+---------------------------+
| find_in_set('a', 'a,b,c') |
+---------------------------+
| 1 |
+---------------------------+
mysql> select find_in_set('d', 'a,b,c');
+---------------------------+
| find_in_set('d', 'a,b,c') |
+---------------------------+
| 0 |
+---------------------------+

查询爱好登山的人：

 

---

总结

以上便是mysql数据类型的全部知识。感谢大家的支持与观看！！！