【MySQL】MySQL的数据类型
目录
5.枚举与集合类型（Enumeration and Set Types）
5.2.1.find_in_set(str,strlist)
1.数据类型分类
数据类型的作用：
- 决定了存储数据时应该开辟的空间大小和数据的取值范围。
- 决定了如何识别一个特定的二进制序列。
MySQL中的数据类型与编程语言中的数据类型相似，在学习时我们不进行一 一演示，通过讲述一个或两个特定的类型，你也能够触类旁通。
2.数值类型
2.1.整数类型
MySQL为我们提供了不同占用不同字节大小的整数类型，以便于我们能够更好的使用数据和节省磁盘的空间。
在MySQL中，整型可以指定是有符号的和无符号的，默认是有符号的，可以通过 UNSIGNED 来说明某个字段是无符号的。
这里我们以tinyint类型为例介绍数值类型：
- 创建有符号数据类型
在MySQL中，整型可以指定是有符号的和无符号的，默认是有符号的。我们直接创建即可。
在下面我们在db1库里面创建一张新表，名为tiny，并向表中插入一些合法数据，一些非法数据（用于数值越界测试）。
create table tiny (num tinyint); desc tiny; insert into tiny values (0); insert into tiny values (-128); insert into tiny values (127); insert into tiny values (-129);
查看表中的内容，发现合法的数据被成功的插入了，非法的数据直接被拒绝插入，说明MySQL不会为我们的整形进行截断转换。
所以数据类型本身也是一种：约束，通过这种约束我们就能保证数据库中的数据是可预期，完整的;
我们看看我们创建表时的信息
注意：创建一个表就是在/var/lib/mysql/db1里面创建了一个表文件
- 创建无符号数据类型
在MySQL中，整型可以指定是有符号的和无符号的，默认是有符号的，可以通过 UNSIGNED 来说明某个字段是无符号的。
我们重复上面的步骤
create table tiny2 (num tinyint unsigned); desc tiny2; insert into tiny2 values (0); insert into tiny2 values (256); insert into tiny2 values (255); insert into tiny2 values (-129); insert into tiny2 values (229);
我们看看我们插进去的数据
查看表中的内容，发现合法的数据被成功的插入了，非法的数据直接被拒绝插入，说明MySQL不会为我们的整形进行截断转换。
2.2.bit类型
基本语法：
bit[(M)] : 位字段类型。M表示每个值的位数，范围从1到64。如果M被忽略，默认为1。
- bit类型的显示方式
我们接着在db1库里面，创建一个新表user，表当中包含一个int类型表示用户id，和一个8位bit类型表示一些选项。如下：
create table user(id int, op bit(1));
desc user; 
向user表中插入一些数据并显示表中的内容：
insert into user values (121, 1);
insert into user values (122, 0);
insert into user values (123, 2);
insert into user values (124, 8);
insert into user values (125, -1);
select * from user; 
也很容易看回来，这个表是有限制的。可以看到如果插入op列的数据不是0或1，那么插入数据时就会产生报错。
我们看到这个bit类型是使用十六进制来显示的。
注意：bit类型最多设置64位，不能超过64
create table user1(id int, op bit(64));
2.3.浮点类型
2.3.1.float类型
语法：
float[(m, d)] [unsigned]M指定显示长度，d指定小数位数，占用空间4个字节，注意M=d+整数部分的长度
特征：
- 对于小数部分如果超出了精度MySQL在保存值时会进行四舍五入，对于整数部分如果超出精度则拒绝插入。
- 整数也可以被浮点数存储
- 对于有符号的float的范围是去掉负数只留正数。
- float表示的精度大约是小数点后7位，但是当float存储较高精度的数据时会出现误差。
案例
1：
我们创建一个float(4,2)类型，则这个类型可以表示的范围是-99.99 ~ 99.99，然后我们分别向这个表中插入 99.99, -99.99，23.655, -36.232， 100, -100。
create table ftable ( price float(4,2) );
desc ftable;
insert into ftable values (99.99);
insert into ftable values (-99.99);
insert into ftable values (23.655);
insert into ftable values (-36.232);
insert into ftable values (100);
insert into ftable values (-100);
查看表中的内容：
select * from ftable; 
我们发现
- 23.655变成了23.66，这个是四舍五入
- -36.232变成了-36.23，这个是四舍五入
- +-100超过了我们设置的浮点数的范围大小，所以不能存进去
结果符合我们的预期！
案例2
我们在创建一张无符号的float（4，2）表，然后我们插入正数99，100.00,和负数-22.01。
create table futable( price float(4,2) unsigned );
desc futable;
insert into futable values (99);
insert into futable values (100.00);
insert into futable values (-22.01);
select * from futable; 
我们发现这个正数的上限还是99.99，负数部分却不能直接插入了
查看表的内容后，发现果然正数的范围没有改变，负数不能被插入。
同时，我们发现这个整数99也能被当作浮点数存储
案例3
创建一张float类型的表，然后我们向其中插入2202.3，123456789，1.23456789，然后观察表中的结果。
create table fltable( num float );
insert into fltable values (2202.3);
insert into fltable values (2202.3);
insert into fltable values (123456789);
insert into fltable values (1.23456789);
select * from fltable;
我们看到123456789被存储为了12345700，1.23456789被存储为1.23457
我们发现对于精度较高的数据，无论是正数部分太大，还是小数部分太多，都会导致float类型存储时出现误差。
2.3.2.decimal类型
decimal和float类型的使用方式一样，但decimal的精度比float更高。
语法：
decimal(m, d) [unsigned] : 定点数m指定长度，d表示小数点的位数特征
- decimal(5,2)表示的范围是-999.99到999.99
- decimal(5,2) unsigned表示的范围是0到999.99
- decimal整数最大位数m为65。支持小数最大位数d是30。如果d被省略，默认为0，如果m被省略，默认是10。
创建一个新表，表当中分别包含一个float(10,8)的列和一个decimal(10,8)的列。如下：
create table dectable( num float(10,8), num2 decimal(10,8) );
desc dectable; 
向表当中插入一条记录，指定float和decimal的值均为23.12345612，但最终查表时会发现decimal保持了数据的原貌，而float则会存在一定的精度损失。如下：
insert into dectable values (23.12345612, 23.12345612);
select * from dectable;
说明：float表示的精度大约是7位。 decimal整数最大位数m为65。支持小数最大位数d是30。如果d被省略，默认为0.如果m被省略，默认是10。
建议：如果希望小数的精度高，推荐使用decimal。
3.字符串类型
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。
注意：char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。
CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。
BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。
有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。
3.1.char类型
语法：
char(L): 固定长度字符串，L是可以存储的长度，单位为字符，最大长度值可以为255。特征
char类型的单位是字符，而不是字节，无论是英语a，还是汉语我，甚至感叹号!等都表示一个字符。- L最大不能超过255.
- char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。
例如我们先创建一张表，包含一个chart字段，这个字段的类型我们设置为char(4)，
create table ch(chart char(4));
desc ch;
然后我们向其插入：
abc，你好啊~，A，你好，A，你好啊。
insert into chart values ('abc');
insert into ch values ('你好啊~');
insert into ch values ('A，你好');
insert into ch values ('A,你好啊');
select * from ch;
结果符合我们的预期，对于A，你好啊，这个数据的字符长度是大于4的所以不能够被正常插入。
优点：
在不同编码中，一个字符所占的字节个数是不同的，比如utf8中一个字符占3个字节，而gbk中一个字符占2个字节。MySQL限定字符的概念不是字节，这样用户就不用关心复杂的编码细节了。
3.2.varchar类型
语法：
varchar(L): 可变长度字符串，L表示字符长度，最大长度65535个字节说明：
- varchar类型是可变长字符串类型，其使用与char并无区别，但是varchar能够更加节省空间。
- VARCHAR 是一个可变长度的字符类型，它只占用存储实际字符所需的字节数加上一个额外的长度前缀（通常是1或2个字节，取决于最大长度）。
- 在上面char类似的案例演示中，如果我们使用的是utf8编码，对于char(4)，MySQL其实在底层默认给我们分配了：4 ∗ 3 = 12个字节， 上面的演示案例中，如果我们存放的是abc三个字符，其实我们只需要是使用了3个utf8字符，即9个字节，另外的3个字节被浪费了，而varchar出现就能改变这种状况。
特征
- varchar类型最多占用65535字节，其中有1~2字节用来表示实际数据长度，还有1字节来存储其他控制信息，因此varchar类型的有效字节数最多是65532字节。
因此varchar类型可指定的字符个数上限，与表的编码格式有关：
- 对于utf8编码来说，一个字符占用三个字节，因此varchar(L)中的L最大可指定为 65532 ÷ 3 = 21844 。
- 对于gbk编码来说，一个字符占用两个字节，因此varchar(L)中的L最大可指定为 65532 ÷ 2 = 32766 。
案例1
和char一样，我们先创建一张表，包含一个varchar字段，这个字段的类型我们设置为varchar(4)，然后我们向其插入：
abc，你好啊~，A，你好，A，你好啊。
create table varch (varch varchar(4));
desc varch;
insert into varch values ('abc');
insert into varch values ('你好啊~');
insert into varch values ('A，你好');
insert into varch values ('A,你好啊');
select * from varch; 
我们发现和char是一模一样的。结果与使用char没有差别，差别在于使用utf8时底层varchar对于abc分配使用的空间相比于char要少。
关于 VARCHAR 的情况：
- 可变长度：VARCHAR 是一个可变长度的字符类型，它只占用存储实际字符所需的字节数加上一个额外的长度前缀（通常是1或2个字节，取决于最大长度）。
- 存储效率：如果您在 VARCHAR(4) 中存储了 'abc' 三个字符，并且这些字符都是ASCII字符，那么它只会占用3个字节（字符本身）加上1个字节的长度前缀，总共4个字节。
- 案例
2
由于varchar类型的字节数限制，在定义编码格式为utf8的表时，varchar(L)中的L如果超过了21844，则会产生报错。如下：
create table utf8_table (content varchar(21845)) charset=utf8;
create table utf8_table (content varchar(21844)) charset=utf8;
由于varchar类型的字节数限制，在定义编码格式为gbk的表时，varchar(L)中的L如果超过了32766，则会产生报错。如下：
create table gbk_table (content varchar(32767)) charset=gbk;
create table gbk_table (content varchar(32766)) charset=gbk;
3.3.char和varchar比较
char和varchar的区别如下：
- char类型可存储字符上限为255，varchar类型可存储字符上限与表的编码格式有关。
- char(L)定义后，无论存储的字符串长度是否到达L，都会开辟用于存储L个字符的定长空间，如果存储的字符串长度超过L则会报错。
- varchar(L)定义后，会根据存储字符串的长度按需动态开辟空间，并且需要使用1-3字节的空间用于表示存储字符串的长度以及其他控制信息，如果存储的字符串长度超过L则会报错。
char和varchar的优缺点如下：：
- char类型的数据是定长的，因此磁盘空间比较浪费，但是效率高（直接访问定长的空间）。
- varchar类型的数据是变长的，因此磁盘空间比较节省，但是效率低（需要先读取存储字符串的长度，再访问指定长度的空间）。
如果要存储的数据是定长的，那就使用char类型进行存储，比如身份证号码、手机号、md5等。如果要存储的数据是变长的，那就使用varchar类型进行存储，比如名字、地址等。
如何选择定长或变长字符串？
- 如果数据确定长度都一样，就使用定长（char），比如：身份证，手机号，md5
- 如果数据长度有变化,就使用变长(varchar), 比如：名字，地址，但是你要保证最长的能存的进去。 定长的磁盘空间比较浪费，但是效率高。
- 变长的磁盘空间比较节省，但是效率低。
- 定长的意义是，直接开辟好对应的空间
- 变长的意义是，在不超过自定义范围的情况下，用多少，开辟多少。
4.日期和时间类型
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。
TIMESTAMP类型有专有的自动更新特性，将在后面描述。
常用的三种时间日期类型如下：
- date：日期格式为YYYY-MM-DD，占用三字节。
- datetime：时间日期格式为YYYY-MM-DD HH:MM:SS，占用八字节。
- timestamp：时间戳，格式为YYYY-MM-DD HH:MM:SS，占用四字节，值得注意的是这个字段，我们不主动修改的话，此字段就会自动插入当前的时间戳。
date、datetime使用示例
创建一个表，表当中包含date、datetime两种时间日期类型的列。如下：
create table time( t1 date, t2 datetime );
desc time;
insert into time values ('2023-11-01', '2023-11-01 22:53:40');
select * from time;
timestamp类型使用案例
利用timestamp会自动更新时间的特性，我们可以创建一个评论表，表当中包含评论人的昵称、评论的内容和评论的发布时间。如下：
create table comment_table ( name varchar(20), content text(100), publish_time timestamp);
desc comment_table;
我们插入数据
insert into comment_table (name,content) values ('A','你好');
select * from comment_table;
我们发现它这个还是空的。？？？？？？
没关系，我们创建一个可以自动更新的
假设你有一个表 example_table，并且你想让 created_at 字段在插入时自动设置时间戳，updated_at 字段在每次更新记录时自动更新时间戳。
CREATE TABLE example_table (
id INT AUTO_INCREMENT PRIMARY KEY,
data VARCHAR(255),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);注意这个不能直接运行，需要去掉空格之后才可以去运行。
在这个例子中：
- created_at 字段在插入新记录时会自动设置为当前时间戳，并且在之后不会更改。
- updated_at 字段在插入新记录时也会自动设置为当前时间戳，并且在每次更新记录时都会更新为当前时间戳。
以下是相关属性的说明：
-
DEFAULT CURRENT_TIMESTAMP：当插入新记录时，如果未为该字段提供值，它将自动设置为当前时间戳。 -
ON UPDATE CURRENT_TIMESTAMP：当记录更新时，该字段将自动更新为当前时间戳。
你可以同时设置这两个属性来确保 TIMESTAMP 字段在插入和更新时都能自动处理时间戳。
注意：
- 一个表中只能有一个 TIMESTAMP 字段使用 ON UPDATE CURRENT_TIMESTAMP 属性，并且没有 DEFAULT 子句（或者有一个 DEFAULT CURRENT_TIMESTAMP 子句），除非你在 MySQL 5.6.5 或更高版本中启用了多个 TIMESTAMP 列的自动更新功能（使用 explicit_defaults_for_timestamp 系统变量）。
- 在 MySQL 8.0 中，explicit_defaults_for_timestamp 变量默认是启用的（值为 ON），这允许你有更多的灵活性来配置多个 TIMESTAMP 字段。
你可以通过以下命令检查 explicit_defaults_for_timestamp 的值：
SHOW VARIABLES LIKE 'explicit_defaults_for_timestamp';
如果希望更改这个变量的值，可以在 MySQL 配置文件（如 my.cnf 或 my.ini）中进行设置，或者通过运行 SET GLOBAL 命令（这将在当前会话和之后的新会话中生效，但不会持久化到服务器重启之后）：
SET GLOBAL explicit_defaults_for_timestamp = OFF;但是，请注意在生产环境中更改全局变量时潜在的兼容性和性能影响。
desc example_table; 
我们此时插入数据
insert into example_table (id,number) values(1,'abc');
select * from example_table;
我们后续再修改一下数据
update example_table set data='加油！';
select * from example_table;
很神奇吧！
5.枚举与集合类型（Enumeration and Set Types）
- ENUM: 枚举类型，用于存储单一值，可以选择一个预定义的集合。
- SET: 集合类型，用于存储多个值，可以选择多个预定义的集合。
enum和set类型的区别如下：
- 在定义enum字段时需要提供若干个选项的值，在设置enum字段值时只允许选取其中的一个值。
- 在定义set字段时需要提供若干个选项的值，在设置set字段值时可以选取其中的一个或多个值。
ENUM 和 SET 的内部存储：
- ENUM 类型在内部存储为整数，这些整数从1开始递增。
- SET 类型在内部存储为位图结构，每个选项对应一个唯一的位，所以每个选项的存储是基于2的幂次方（1, 2, 4, 8, 16, ...）。
语法：
- enum
enum('选项1', '选项2', '选项3',...);该设定只是提供了若干个选项的值，最终一个单元格中，实际只存储了其中一个值；而且出于效率考虑，这些值在MySQL内部实际存储的是“数字”，因为这些选项的每个选项值依次对应如下数字：1,2,3,…最多65535个；当我们添加枚举值时，也可以添加对应的数字编号。
- set
set('选项值1','选项值2','选项值3', ...);该设定只是提供了若干个选项的值，最终一个单元格中，设计可存储了其中任意多个值；而且出于效率考虑，这些值在MySQL内部实际存储的是“数字”，因为这些选项的每个选项值依次对应如下数字：1,2,4,8,16,32，…最多64个。（即每个比特位都不相等的「位图结构」）
5.1.调查表案例
我们现在需要做一个爱好调查表，包含名称，性别，爱好。
分析：
- 名称我们可以定义为varchar类型以节省空间。
- 人的性别只能从男和女中进行二选一，因此可以定义成enum类型。
- 人的爱好在提供的选项中可能存在多个，因此可以定义成set类型。
创建一个调查表，表当中包含被调查人的姓名、性别和爱好。如下：
create table investigation( name varchar(20), gender enum('男','女'), hobby set('下棋','打篮球','游泳','跑步','跳绳'));
desc investigation;
向表中插入记录时，被调查人的性别只能从男和女中进行二选一，被调查人的爱好可以从提供的若干个选项中进行多选一或多选多，多个爱好之间需要通过英文逗号隔开。如下：
insert into investigation values ('张飞', '男', '打篮球,游泳,跑步');
select * from investigation;
在插入记录时，除了通过指明男女来设置性别，还可以通过插入数字1和2来设置性别。
这是因为性别是enum存储的，ENUM 类型在内部存储为整数，这些整数从1开始递增。
如下：
insert into investigation values ('李四', 1, '打篮球,游泳,跑步');
insert into investigation values ('王五', 2, '游泳');
select * from investigation; 
在插入记录时，除了通过指明多个选项来设置爱好，还可以通过数字的方式来设置，(对应的比特位进行映射，例如31的比特位为全1（即11111），对应的就是五个爱好全部插入)。
这是因为SET 类型在内部存储为位图结构，每个选项对应一个唯一的位，所以每个选项的存储是基于2的幂次方（1, 2, 4, 8, 16, ...）。
如下：
insert into investigation values ('A', 1, 1);
insert into investigation values ('B', 2, 3);
insert into investigation values ('C', 1, 7);
insert into investigation values ('D', 2, 15);
select * from investigation; 
5.2.enum和set查找
- where子句进行 「行匹配」
SELECT 选项 表名称 WHERE 字段=想要匹配的信息如果想要筛选出调查表中所有女生的信息，那么直接在select筛选时指明gender='女'即可。
select * from investigation where gender='女';
但是对于set类型我们使用where子句，进行的匹配是严格匹配，例如我们想要筛选出所有喜欢跳绳的人：
select * from investigation where hobby='游泳'; 
我们只是匹配到了王五，而没有匹配到其他人， 这个意味着我们只找到了hobby只有游泳的，那如果我想要找hobby里包含游泳的呢？
要解决这个问题我们就要要借助MySQL给我们提供的find_in_set(str,strlist)函数了。
5.2.1.find_in_set(str,strlist)
FIND_IN_SET(str,strlist) 是 MySQL 中的一个字符串函数，用于在一个以逗号分隔的字符串列表中查找一个字符串的位置。这个函数通常与 SET 数据类型一起使用，但也可以用于任何包含逗号分隔值的字符串。
语法
FIND_IN_SET(str,strlist) - str 是要查找的字符串。
- strlist 是包含用逗号分隔的多个字符串的列表。
返回值
- 如果 str 在 strlist 中，则返回 str 在 strlist 中的位置（从1开始计数）。
- 如果 str 不在 strlist 中，则返回0。
- 如果 strlist 为空字符串，则返回0。
- 如果 str 或 strlist 为 NULL，则函数返回 NULL。
‘我们可以来看看它是怎么使用的
通过select可以对find_in_set函数进行验证，依次查找集合a,b,c中是否包含字符a、b、d，这时在查找字符a和b时就会得到其在集合中的下标，而在查找字符d时就会得到0值。
如下：
select find_in_set ('a','a,b,c'); select find_in_set ('b','a,b,c'); select find_in_set ('c','a,b,c'); select find_in_set ('d','a,b,c');
这时就可以通过select搭配find_in_set函数，来筛选出爱好包含跳绳的人的信息了。如下：
select * from investigation;
select * from investigation where find_in_set('游泳',hobby); 
如果说，我们想要同时查询跑步和游泳的
select * from investigation where find_in_set('游泳',hobby) and find_in_set('跑步',hobby); 
注意：千万不要像下面这样子写
这样子是错误的
原文地址：https://blog.csdn.net/2301_80224556/article/details/142818528
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！