MySQL基础『数据类型』
1.数据类型一览
作为专门用来存储数据的 数据库，MySQL 提供了丰富的数据类型，覆盖绝大多数使用场景
分类 | 数据类型 | 说明 |
|---|---|---|
数值类型 | BIT(M) | 位类型，M指定位数，默认为 1，范围 [1, 64] |
TINYINT [UNSIGNED] | 有符号范围 [-128, 127]，无符号范围 [0, 255]，默认为有符号 | |
BOOL | 0 表示假，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-dd |
DATETIME | 格式为 yyyy-mm-dd hh:mm:ss | |
TIMESTAMP | 时间戳，通常表示一个时间点，包括日期和时间 | |
其他类型 | ENUM | 字符串对象，表示枚举类型，只能选取其中一个值，由于在某些数据库系统中的限制，ENUM 的使用有时会受到争议 |
SET | 字符串对象，表示集合类型，可以选取零个或多个值 |
创建一个数据库 dataType
mysql> create database if not exists dataType;
选择数据库
mysql> use dataType;
2.整型
2.1.INT
首先看看最常用的 int 类型，同 C/C++ 中的一样，MySQL 中的 int 占 4 字节，支持创建无符号类型
INT [UNSIGNED]mysql> create table if not exists testINT
(
整型 int,
无符号整型 int unsigned
);
mysql> desc testINT;
注意：
MySQL中创建无符号类型，是在类型之后加上unsigned- 括号中的数字通常用于指定显示宽度，并不表示存储范围
向表中插入一组合法的数据
mysql> insert into testINT values (-1234, 4321), (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提供了选择，把选择的权力交给了用户，由用户根据数据库的预期大小，自由选择整型
都可以正常建表并使用
mysql> create table if not exists testIntFamily
(
极短整型 tinyint,
短整型 smallint,
中整型 mediumint,
整型 int,
大整型 bigint
);
mysql> desc testIntFamily;
mysql> insert into testIntFamily values (127, 32767, 8388607, 2147483647, 9223372036854775807);
mysql> select * from testIntFamily;
注意： 这些衍生类型如同 int 一样，可以声明为无符号类型，定义时在类型之后加上 unsigned 即可，创建无符号类型后，无法再存储负数，存储整正数的能力翻倍
尽量不使用
unsigned，对于int类型可能存放不下的数据，int unsigned同样可能存放不下，与其如此，还不如设计时，将int类型提升为bigint类型
2.2.BIT
MySQL 中提供了一个很有意思的类型：bit(m)，表示 位字段类型，M 的范围 1~64，这里的位指 比特位，64 比特位即 8 字节，接下来分别创建几个 bit 类型
BIT(M)mysql> create table if not exists 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
3.浮点数
3.1.FLOAT
MySQL 支持我们熟悉的浮点数类型 float、double，所占大小分别为 4 字节和 8 字节
FLOAT [(M, D)] [UNSIGNED]
DOUBLE [(M, D)] [UNSIGNED]mysql> create table if not exists 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 if not exists 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 if not exists testFloatL
(
浮点数 float(10, 8)
);
mysql> desc testFloatL;
现在向 testFloatL 表中插入一个较长的数据，并查询结果
mysql> insert into testFloatL values (1.23456789);
mysql> select * from testFloatL;
可以看到第八位小数精度丢失了，证明 float 在使用过程中存在 精度损失 问题
float至多保证7位小数精度准确
至于 double，除了比 float 大一些之外，与 float 的使用方法一模一样，同样存在 精度损失问题，如果想要追求超高精度，可以改用 decimal 类型
3.2.DECIMAL
decimal 是 MySQL 针对高精度小数推出的浮点数类型，decimal 使用方法和 float / double 一样，在支持浮点数存储的同时，decimal 还可以保证小数部分的精度
DECIMAL(M, D) [UNSIGNED]创建一张表，其中分别包含两个高精度浮点数类型
mysql> create table if not exists 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 则是指哪打哪，插入的数据非常准确，精度保持的非常稳定
3.3.如何选择
float 比 decimal 占用空间小，decimal 比 float 精度高
如果对精度要求不是很高（精度少于等于七位），可以选择 float；如果从事金融相关工作，对精度的要求极其苛刻，选择 decimal
float能保证的精度最多是7位decimal能保证的精度最多是30位，不同版本的MySQL略有差异
4.字符
4.1.CHAR
char 在 C/C++ 中称为字符类型，大小为 1 字节，在 MySQL 中则是叫做 固定长度字符串，也就是说 char 可以存储字符串（前提是长度足够）
CHAR(L)创建一张表，其中包含了长度为 2 的 char
mysql> create table if not exists 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 ('ccc');
在
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);
4.2.VARCHAR
varchar 表示 变长字符串，不同于 char 至多支持插入长度为 255 的字符串，varchar 至多支持插入总大小为 65535 字节的字符串
VARCHAR(L)
varchar为什么被称为 变长字符串 ？ 这是因为给varchar分配空间后（假设分配了10字符），如果实际插入的字符串长度为5，则只会使用varchar中的5字符空间，如果实际插入的长度为8，就会使用8字符空间，在长度范围之内，varchar会根据实际插入的字符串长度，动态分配空间，达到 变长 的效果
mysql> create table if not exists 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，这是一个专门用来存储文本数据的类型，比如我当前编写的博客，就是通过该类型存储的
4.3.如何使用
char 与 varchar 的区别如下
实际存储 | char(4) | varchar(4) | char 占用字符 | varchar 占用字符 |
|---|---|---|---|---|
abcd | abcd | abcd | 4*3=12 | 4*3+1=13 |
好 | 好 | 好 | 4*3=12 | 1*3+1=4 |
Abcde | X | X | 数据超过长度 | 数据超过长度 |
varchar 会额外使用 1~3 字节的空间，用于记录数据大小
如何选择定长或变成字符？
- 如果数据确定长度都一样，就使用定长
char，比如身份证、手机号 - 如果数据长度有变化，就使用变长
varchar，比如名字、地址 - 定长的磁盘空间比较浪费，但是效率高
- 变长的磁盘空间比较节省，但是效率低
- 定长的意思是：直接开辟对应好的空间
- 变长的意思是：在不超过自定义范围的情况下，用多少开辟多少
5.时间和日期
5.1.DATE
在 MySQL 中，日期表示 年月日，通过 date 类型表示，占用 3 字节空间
// yyyy-mm-dd
DATEmysql> create table if not exists testDate
(
日期 date
);
mysql> desc testDate;
可以向其中插入日期信息
注意： 插入的日期信息需要使用 ' ' 或者 " " 引起来
mysql> insert into testDate values ('2018-4-10'), ('2019-9-11'), ('2021-7-15');
mysql> select * from testDate;
5.2.DATETIME
如果想插入更为详细的日期信息，可以改用 datetime，格式为 年月日 时分秒，称为 日期时间类型，占用 8 字节空间
范围从 1000 到 9999
// yyyy-mm-dd hh:mm:ss
DATETIMEmysql> alter table testDate add (日期时间 datetime);
mysql> desc testDate;
同样的，插入数据时需要使用 ' ' 或者 " " 将日期时间括起来
mysql> insert into testDate values ('1970-1-1', '1970-1-1 0:0:0'), ('2023-12-4', '2023-12-4 14:21:42');
mysql> select * from testDate;
诸如评论时间、博客发布时间等都是通过 datetime 类型存储的
除了 日期 和 日期时间，当然还有单纯 时间 类型，只能用来存储具体时间
// hh:mm:ss
TIME5.3.TIMESTAMP
MySQL 中提供了 时间戳 类型 timestamp，占用 8 字节，时间戳 最大的特点是随着对表的更新操作，更新时间，并且 时间戳 在查询时，显示的格式与 datetime 一样
时间戳是指从
1970-1-1 0:0:0开始计时的秒数
// yyyy-mm-dd hh:mm:ss
TIMESTAMPmysql> alter table testDate add (时间戳 timestamp);
mysql> desc testDate;
mysql> select * from testDate;
注意： 时间戳是一个非空字段，并且不允许用户手动插入时间
使用 update 指令更新字段信息，可以触发 时间戳 的更新
mysql> select * from testDate;
mysql> update testDate set 日期='2023-12-5' where 日期='2023-12-04';
mysql> select * from testDate;
利用这几个日期类型，编写一个简易版的 留言墙
mysql> create table if not exists 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
('曹操', '2018-1-1', '宁叫我负天下人，休叫天下人负我'),
('孙武', '2021-6-7', '其疾如风，其徐如林，侵掠如火，不动如山，难知如阴，动如雷震'),
('王勃', '2023-4-10', '落霞与孤鹜齐飞 秋水共长天一色');
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;
6.枚举和集合
6.1.ENUM
enum 称为 枚举 类型，用于提供一批元素，可以选择其中一个
ENUMmysql> create table if not exists 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);
6.2.SET
如果想多选，需要使用 集合 类型
SETmysql> alter table votes add (hobby set('唱', '跳', 'rap', '篮球', '写代码'));
mysql> desc votes;
可以看到，枚举 和 集合 类型默认都是可以为空的，现在继续向表中插入数据
注意： 在多选集合中的元素时，以 , 分隔，并且中间不能有空格
mysql> insert into votes values ('ruoli', '男', '唱,写代码'), ('小蔡', '女', '跳'), ('小徐', '男', 'rap'), ('小坤', '女', '篮球');
mysql> select * from votes;
集合 类型支持通过数字多选元素
mysql> insert into votes values ('赵六', 1, 1), ('田七', 2, 2), ('老八', 1, 3);
mysql> select * from votes;
结果显得有些诡异，这是因为 集合中的数字并非下标，而是一个数值，其中的比特位对应着集合中的选项
- 当值为
1时，二进制表示为0001，即 集合 中的第一个元素 唱 - 当值为
2时，二进制表示为0010，即 集合 中的第二个元素 跳 - 当值为
3时，二进制表示为0011，即 集合 中的第一、二个元素 唱，跳
所以如果想通过数字全选当前 集合 中的所有元素（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('f', '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 中数据类型的讲解到此结束，总之：数据类型本质上就是一种约束，确保插入数据库中数据的合法性