＜MySQL＞ 【数据类型】

目录

一、数据类型一览

二、整型

（一）INT

（二）BIT

三、浮点数

（一）FLOAT

（二）DECIMAL

（三）如何选择

四、字符

（一）CHAR

（二）VARCHAR

（三）如何使用

五、时间和日期

（一）DATE

（二）DATETIME

（三）TIMESTAMP

（四）简易留言板

六、枚举和集合

（一）ENUM

（二）SET

（三）集合 中元素的查询

一、数据类型一览

作为专门用来存储数据的 数据库，MySQL 提供了丰富的数据类型，覆盖绝大多数使用场景

二、整型

（一）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 的区别如下

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