＜MySQL＞ 【数据类型】

---

一、数据类型一览

作为专门用来存储数据的 **数据库，MySQL **提供了丰富的数据类型，覆盖绝大多数使用场景
分类数据类型说明数值类型BIT(M)位类型，M指定位数，默认为 1，范围 [1, 64]TINYINT [UNSIGNED]有符号范围 [-128, 127]，无符号范围 [0, 255]，默认为有符号BOOL0 表示假，1 表示真，在许多数据库系统中，BOOL 与 TINYINT(1) 是同义的SMALLINT [UNSIGNED]有符号范围 [-2^15, 2^15-1]，无符号范围 [0, 2^16-1]INT [UNSIGNED]有符号范围 [-2^31, 2^31-1]，无符号范围 [0, 2^32-1]BIGINT [UNSIGNED]有符号范围 [-2^63, 2^63-1]，无符号范围 [0, 2^64-1]FLOAT [(M, D)] [UNSIGNED]M 指定显示长度，D 指定小数位数，大小依赖具体平台，通常占用 4 字节，但在某些情况下可能达到 8 字节DOUBLE [(M, D)] [UNSIGNED]比 FLOAT 精度更高，大小通常为 8 字节，在某些情况下可能更大DECIMAL(M, D) [UNSIGNED]比 DOUBLE 精度更高，能完全保证数据的精度，适用于需要高精度的金融计算字符、二进制类型CHAR(SIZE)固定长度字符串，最大支持 255 字符，用空格填充字符串到指定的长度VARCHAR(SIZE)可变长度字符串，最大支持 65535 字符，它不会用空格填充BLOB二进制数据TEXT大文本，不支持全文索引、默认值，需要注意有一些变体时间日期DATE格式为 yyyy-mm-ddDATETIME格式为 yyyy-mm-dd hh:mm:ssTIMESTAMP时间戳，通常表示一个时间点，包括日期和时间其他类型ENUM字符串对象，表示枚举类型，只能选取其中一个值，由于在某些数据库系统中的限制，ENUM 的使用有时会受到争议SET字符串对象，表示集合类型，可以选取零个或多个值

---

二、整型

（一）INT

首先看看最常用的 **

int

**类型，同

C/C++

中的一样，**MySQL **中的

int

占 **

4

** 字节，支持创建无符号类型

INT [UNSIGNED]

mysql> create table testINT
(
    整型 int,
    无符号整型 int unsigned
);

mysql> desc testINT;

注意：

1. MySQL 中创建无符号类型，是在类型之后加上 unsigned
2. 括号中的数字通常用于指定显示宽度，并不表示存储范围

向表中插入一组合法的数据

mysql> insert into testInt values (-1234, 1234), (1000000000, 3000000000);

整型的范围是 **[

-2147483648

,

2147483647

]**，无符号整型的范围是 **[

0

,

4294967295

]**，范围内的数据都可以正常插入，如果插入越界数据，则会插入失败：

mysql> insert into testInt (整型) values (2147483648);

mysql> insert into testInt (整型) values (-2147483649);

mysql> insert into testInt (无符号整型) values (4294967296);

mysql> insert into testInt (无符号整型) values (-1);

由此可见：MySQL 中对于数据范围具有严格约束，会直接拦截非法数据的插入。

也就是说：凡是成功插入 MySQL 中的数据，都是合法的。

**约束使用者，

MySQL

能保证数据插入的合法性（数据库中的数据是可预期、完整的）**

插入数据时，需要注意符合数据类型范围要求。

---

除了最常用的 int 外，MySQL 还提供了其他几种衍生类型：

• TINYINT 极短整型，占 **1 **字节，范围 [-128, 127]
• SMALLINT 短整型，占 **2 **字节，范围 [-32768, 32767]
• MEDIUMINT 中整型，占 **3 **字节，范围 [-8388608, 8388607]
• BIGINT 大整型，占 **8 **字节，范围 [-9223372036854775808, 9223372036854775807]

不同大小的整型适用于不同场景中，MySQL 提供了选择，把选择的权力交给了用户，由用户根据数据库的预期大小，自由选择整型。

注意： **这些衍生类型如同 INT 一样，可以声明为无符号类型，定义时在类型之后加上

unsigned

即可，创建无符号类型后，无法再存储负数，存储整正数的能力翻倍。**

尽量不使用 **

unsigned

**，对于 **

int

类型可能存放不下的数据，

int unsigned

**同样可能存放不下，与其如此，还不如设计时，将

int

类型提升为 **

bigint

**类型。

（二）BIT

MySQL ** 中提供了一个很有意思的类型：

bit(m)

**，表示 **位字段类型，

M

的范围

1~64

，这里的位指 比特位，

64

**比特位即 **

8

**字节，接下来分别创建几个

bit

类型：

mysql> create table testBIT
(
    比特位_1 bit(1),
    比特位_2 bit(2),
    比特位_8 bit(8)
);

mysql> desc testBit;

再分别向其中插入一些数据，查询时却看到了一些奇怪字符

mysql> insert into testBit values (1, 3, 100), (0, 2, 255);

mysql> select * from testBit;

这是因为 **查表时，位字段类型默认按

ASCII

的形式显示，查表可以发现，

100

**正好对应字符 **

d

**

如果我们想让其正常显示，查询时可以使用 **

hex()

** 这个内置函数将查询结果转为十六进制显示：

mysql> select hex(比特位_1), hex(比特位_2), hex(比特位_8) from testBit;

注：**

64

表示

6 * 16 + 4 = 100

，

FF

则表示

15 * 16 + 15 = 255

**

值得注意的是，对于

BIT

类型，

MySQL

也做出了相应的 约束，对于超出范围的数据，拒绝插入

mysql> insert into testBit (位字段类型_1) values (3);

这里的

3

明明只是一个整数，为什么会失败？

注意： **位字段类型限制的是比特位，而非位数，

3

的二进制表示为

0011

，已经使用了两个比特位，自然也就超出范围了，所以

bit(1)

只能插入

0

或

1。

**

三、浮点数

（一）FLOAT

**MySQL ** 支持我们熟悉的浮点数类型 **

float

、

double

**，所占大小分别为 **

4

字节和

8

**字节

FLOAT [(M, D)] [UNSIGNED]

DOUBLE [(M, D)] [UNSIGNED]

mysql> create table testFloat
(
    浮点数 float(4, 2)
);

mysql> desc testFloat;

其中，

float(4, 2)

中的 **

(4, 2)

** 表示当前浮点数支持显示 **

4

**位，且小数精度为 **

2

**位，数据范围为 **[

-99.99

,

99.99

]**

**

float

**和 **

double

**在定义时允许不指明显示位数和小数精度

---

插入一些正常数据，可以看到插入成功

mysql> insert into testFloat values (50.05), (99.99), (-99.99);

插入一些超出范围的数据，直接被拦截了

mysql> insert into testFloat values (500.05);

mysql> insert into testFloat values (-500.05);
 
mysql> insert into testFloat values (-100.00);

mysql> insert into testFloat values (-100.0);

为什么插入 **

-100.0

** 也被拦截了？

这是因为 **当小数部分不足时，

MySQL

自动补齐**，补齐后，实际插入的值为 **

-100.00

**，有五位数，超出范围，自然就被拦截了。

---

MySQL

也并非是铁面无私，当我们插入的数据小数部分超过指定精度时，

MySQL

会遵循四舍五入的规则，将多余的小数部分省略，五入 向前进一位后，如果超出了范围，也会被拦截：

mysql> insert into testFloat values (50.005), (50.000001), (99.994);

mysql> select * from testFloat;

// 下面这种情况会被拦截，因为四舍五入后，数据会变成 100.00
// mysql> insert into testFloat values (99. 995);

总之就是 **

MySQL

必须确保数据库中数据的合法性。**

---

如果将 **

float

**类型定义为无符号类型，会导致它的 负数 部分直接丢弃

mysql> create table testFloatU
(
    无符号浮点数 float(4, 2) unsigned
);

mysql> desc testFloatU;

mysql> insert into testFloatU values (99.991), (50.00), (0);

// 插入失败
mysql> insert into testFloatU values (-99.991), (-50.00), (-1);

至于为什么这么奇怪，这是因为浮点数的存储规则不同于其他类型，采用 **

IEEE 754

** 标准，不是简单的直接使用二进制位，而是将二进制位划分为几个区间，各司其职，如果将其设为无符号类型，等价于将符号表示的二进制位直接禁用。

---

**

float

**存在精度损失，当插入的小数部分过长时，会导致精度丢失

比如这里先创建一张含有 **

float(10, 8)

** 类型的表

mysql> create table testFloatL
(
    浮点数 float(10, 8)
);

mysql> desc testFloatL;

在表中插入一个较长的数据，并查询结果

mysql> insert into testFloatL values (1.23456789);

mysql> select * from testFloatL;

可以看到第八位小数精度丢失了，证明 **

float

**在使用过程中存在 精度损失 问题。

**

float

**至多保证 **

7

** 位小数精度准确。

至于 **

double

**，除了比

float

大一些之外，与

float

的使用方法一模一样，同样存在 精度损失问题，如果想要追求超高精度，可以改用

decimal

类型。

（二）DECIMAL

**

decimal

**是 **MySQL ** 针对高精度小数推出的浮点数类型，

decimal

使用方法和

float / double

一样，在支持浮点数存储的同时，

decimal

还可以保证小数部分的精度：

DECIMAL(M, D) [UNSIGNED]

创建一张表，其中分别包含两个高精度浮点数类型

mysql> create table testDecimal
(
    f float(10, 8),
    d decimal(10, 8)
);

mysql> desc testDecimal;

分别插入普通数据和高精度数据，查看两者的差异

mysql> insert into testDecimal values (99.99, 99.99), (1.23456789, 1.23456789);

mysql> select * from testDecimal;

可以看到，**

float

不仅出现了精度丢失，还出现了只插入两位小数的情况下，后面出现了一堆小数；而

decimal

则是指哪打哪，插入的数据非常准确，精度保持的非常稳定。**

（三）如何选择

**float **比 **decimal **占用空间小，decimal 比 float 精度高。

• 如果对精度要求不是很高（精度少于等于七位），可以选择 float；
• 如果从事金融相关工作，对精度的要求极其苛刻，选择 decimal。

float 能保证的精度最多是 7 位。
decimal 能保证的精度最多是 30 位，不同版本的 MySQL 略有差异。

四、字符

（一）CHAR

**char **在

C/C++

中称为字符类型，大小为 **

1

** 字节，在

MySQL

中则是叫做 固定长度字符串，也就是说

char

可以存储字符串（前提是长度足够）。

CHAR(L)

创建一张表，其中包含了长度为 **

2

** 的 **char **

mysql> create table testChar
(
    固定字符串 char(2)
);

mysql> desc testChar;

向表中插入字符串

注：**在

MySQL

中使用 ' ' 或者 " " 括起来的为字符串：**

mysql> insert into testChar values ('a'), ('bb'), ('你'), ('你好');

mysql> select * from testChar;

mysql> insert into testChar values ('你好么');

mysql> insert into testChar values ('hhh');

**在

utf8

编码中，一个英文字符占

1

字节，一个中文字符占

3

字节。**

成功插入 **2 **个英文字符可以理解，但为什么也可以成功插入 **2 **个中文字符？

这是因为 MySQL 中的 char 类型大小并非 1 字节，而是 1 字符，无论是什么语言，都可以按个数插入，而非实际占用空间，这与 C/C++ 中的 char 有根本上的区别。

这也就是解释了为什么 char 被称为 固定长度字符串，不过这个字符串也是有 **约束 **的，当实际插入的字符串长度超过 char 的固定长度时，插入操作会被拦截，并且 char 支持的最大长度为 255，大于 255 的字符串注定不能使用 char 类型存储。

mysql> alter table testChar modify 固定字符串 char(255);

mysql> desc testChar;

// 修改失败
mysql> alter table testChar modify 固定字符串 char(256);

（二）VARCHAR

**varchar **表示 变长字符串，不同于

char

至多支持插入长度为

255

的字符串，

varchar

至多支持插入总大小为 **

65535

**字节的字符串：

VARCHAR(L)

varchar 为什么被称为 **变长字符串 **？

这是因为给 varchar 分配空间后（假设分配了 10 字符），如果实际插入的字符串长度为 5，则只会使用 varchar 中的 5 字符空间，如果实际插入的长度为 8，就会使用 8 字符空间，在长度范围之内，varchar 会根据实际插入的字符串长度，动态分配空间，达到 变长 的效果。

mysql> create table testVarchar
(
    变长字符串 varchar(6)
);

mysqL> desc testVarchar;

mysql> insert into testVarchar values ('你'), ('你好'), ('你好么'), ('你好吃了么');

mysql> select * from testVarchar;

尝试将

变成字符串

的大小改为 **

65535

**，结果提示最大支持修改为 **

21845

**

mysql> alter table testVarchar modify 变长字符串 varchar(65535);

为什么这里的

varchar

最大只支持

21845

？

**因为此时存入的是中文字符，前面提到过，一个中文字符在

utf8

编码中占

3

字节，

21845 * 3 = 65535

，而这正是

varchar

支持字符串的最大字节数。**

---

需要注意的是

varchar

的最大长度与编码密切相关，在英文字符状态下，

varchar

至多支持插入长度为 **

65535

的字符串，但是其中需要使用

1~3

** 字节的空间用来记录数据大小，所以实际至多支持插入长度为 **

65532

**的英文字符串。

对于中文字符串来说，除去 **

3

**字节的记录空间，可用空间剩余 **

65532

**字节，每个中文字符占

3

字节，实际至多插入长度 **

65532 / 3 = 21844

** 的中文字符串。

将之前的 **

testVarchar

**大小修改为 **

21844

**

mysql> desc testVarchar;

mysql> alter table testVarchar 变长字符串 varchar(21488);

mysql> desc testVarchar;

注意： **我这里能修改成功是因为当前的

testVarchar

表中只有一个字符，数据表中的字段也是需要占用空间的，当字段过多时，是无法修改

varchar

为最大容量的。**

如果实际使用场景中多次涉及

varchar

的边界，可以改用 **

text

**，这是一个专门用来存储文本数据的类型，比如当前编写的博客，就是通过该类型存储的。

（三）如何使用

**

char

** 与 **

varchar

** 的区别如下
实际存储char(4)varchar(4)char 占用字符varchar 占用字符abcdabcdabcd43=1243+1=13好好好43=1213+1=4AbcdeXX数据超过长度数据超过长度

varchar

会额外使用 **

1~3

** 字节的空间，用于记录数据大小。

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

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

五、时间和日期

（一）DATE

在 **MySQL **中，日期表示 年月日，通过 **

date

**类型表示，占用 **

3

** 字节空间

// yyyy-mm-dd
DATE

mysql> create table testDate( 日期 date );

mysql> desc testDate;

可以向其中插入日期信息

注意： 插入的日期信息需要使用 ' ' 或者 " " 引起来。

mysql> insert into testDate values ('2024-5-20'), ('2024-5-21'), ('2024-1-1');

mysql> select * from testDate;

（二）DATETIME

如果想插入更为详细的日期信息，可以改用 **

datetime

**，格式为 年月日 时分秒，称为 日期时间类型，占用 **

8

** 字节空间。

范围从 **

1000

**到 **

9999

**

// yyyy-mm-dd hh:mm:ss
DATETIME

mysql> alter table testDate add (日期时间 datetime);

mysql> desc testDate;

同样的，插入数据时需要使用 ' ' 或者 " " 将日期时间括起来：

mysql> insert into testDate values 
('2024-5-20', '2024-5-20 0:0:0'), ('2024-5-21', '2024-5-21 12:0:0');

mysql> select * from testDate;

诸如评论时间、博客发布时间等都是通过 **

datetime

**类型存储的：

除了 日期 和 日期时间，当然还有单纯 时间 类型，只能用来存储具体时间

// hh:mm:ss
TIME

（三）TIMESTAMP

MySQL中提供了 时间戳 类型 **

timestamp

**，占用 **

8

** 字节，时间戳 最大的特点是随着对表的更新操作，更新时间，并且 时间戳 在查询时，显示的格式与 **

datetime

**一样

时间戳是指从 **

1970-1-1 0:0:0

** 开始计时的秒数

// yyyy-mm-dd hh:mm:ss
TIMESTAMP

注意：时间戳是一个非空字段，并且不允许用户手动插入时间。

使用

update

指令更新字段信息，可以触发 时间戳 的更新。

mysql> select * from testDate;

mysql> update testDate set 日期='2000-1-1' where 日期='2024-5-20';

mysql> select * from testDate;

---

（四）简易留言板

利用这几个日期类型，编写一个简易版的 留言墙

mysql> create table commentWall
(
    name varchar(32) comment '用户名',
    regisTime date comment '注册时间',
    msg varchar(256) comment '留言信息',
    modifyTime timestamp comment '发布/修改时间'
);

mysql> desc commentWall;

向其中插入一些留言信息，并在片刻之后修改其中的留言信息。

注意： 因为时间戳不允许用户手动插入，所以在插入数据时，不能再使用全列插入的方式，需要指明往哪些列中插入数据。

mysql> insert into commentWall (name, regisTime, msg) values 
('小明', '2021-1-1', '新年快乐!'),
('小红', '2022-5-1', '五一快乐!'),
('小黑', '2023-10-1', '国庆快乐!');

mysql> select name as 用户名, regisTime as 注册时间, msg as 留言内容, modifyTime as 修改时间 from commentWall;

mysql> update commentWall set msg='五一出来玩么' where name='小红';

mysql> select name as 用户名, regisTime as 注册时间, msg as 留言内容, modifyTime as 修改时间 from commentWall;

六、枚举和集合

（一）ENUM

enum称为 枚举 类型，用于提供一批元素，可以选择其中一个

ENUM

mysql> create table votes
(
    name varchar(16),
    gender enum('男', '女')
);

mysql> desc votes;

向其中插入值时，只能选择枚举中已经存在的元素，并且只能选择一个

mysql> insert into votes values ('张三', '男'), ('小红', '女');

mysql> select * from votes;

// 非法操作
mysql> insert into votes values  ('李四', '男, 女');

mysq> insert into votes values  ('王五', '第三人称');

---

除了直接选择枚举中的元素值外，还可以通过下标的方式进行选择，当然超过下标索引范围的选择也是非法的：

注：**这里的下标从

1

开始**

mysql> insert into votes values ('李四', 1), ('小婷', 2);

mysql> select * from votes;

// 非法操作
mysql> insert into votes values ('赵六', 0);

mysql> insert into votes values ('田七', 3);

（二）SET

如果想多选，需要使用 集合 类型

SET

mysql> alter table votes add (hobby set('唱', '跳', 'rap', '篮球', 'music'));

mysql> desc votes;

可以看到，枚举 和 集合 类型默认都是可以为空的，现在继续向表中插入数据。

注意： **在多选集合中的元素时，以

,

分隔，并且中间不能有空格。**

mysql> insert into votes values ('蔡徐坤', '男', '唱,跳,rap,篮球,music'), ('肖红', '女', '唱');

mysql> select * from votes;

---

集合 类型支持通过数字多选元素

mysql> insert into votes values ('陈立农', 1, 3), ('范丞丞', 1, 4);

mysql> select * from votes;

结果显得有些诡异，这是因为 集合中的数字并非下标，而是一个数值，其中的比特位对应着集合中的选项：

• 当值为 **1 **时，二进制表示为 0001，即 集合 中的第一个元素 唱
• 当值为 **3 **时，二进制表示为 0011，即 集合 中的第二个元素 唱，跳
• 当值为 **4 **时，二进制表示为 0100，即 集合 中的第三个元素 rap

所以如果想通过数字全选当前 集合 中的所有元素（**

11111

**），转化成十进制，表示为 **

31

**，实际插入时结果符合预期：

mysql> insert into votes values ('唯一真神', 1, 31);

mysql> select * from votes;

如果把数值设为 **

0

**，会发现最终选择的元素为 **

空。

**

注意： **

NULL

与

空

不等价。**

**

NULL

**表示什么都没有，

空

则表示某个类型存在，但它现在为空。

mysql> insert into votes values ('故人', 1, 0);

mysql> select * from votes;

---

（三）集合 中元素的查询

如果想查询包含其中一个元素的行信息，需要借助函数 **

find_in_set(val, set)

**

这个函数可以在 **

set

**中查找是否存在 **

val

**，如果存在就返回其下标，不存在则返回 **

0

**，在

MySQL

中可以直接通过 **

select

**指令执行函数：

mysql> select find_in_set('a', 'a,b,c,d,e');

mysql> select find_in_set('c', 'a,b,c,d,e');

mysql> select find_in_set('e', 'a,b,c,d,e');

mysql> select find_in_set('ee', 'a,b,c,d,e');

将 **

find_in_set

**函数加入 **

select

**查询子句中，即可筛选出包含 集合 中任意元素的行信息：

mysql> select * from votes where find_in_set('唱', hobby);

如果想同时筛选出包含多个元素的行信息，可以使用 **

and

**级联两个条件：

mysql> select * from votes where find_in_set('唱', hobby) and find_in_set('跳', hobby);

关于

MySQL

中数据类型的讲解到此结束，总之：数据类型本质上就是一种约束，确保插入数据库中数据的合法性。