新闻单位建设网站的意义,工程公司网站建设,传统网站建设 成本,西安建设市场信息平台MySQL的数据类型 一、数据类型分类二、数值类型1、整数类型2、bit类型3、小数类型 三、字符串类型四、时间日期类型五、enum和set类型enum和set查找 数据类型的作用#xff1a;
决定了存储数据时应该开辟的空间大小和数据的取值范围。决定了如何识别一个特定的二进制序列。
… MySQL的数据类型 一、数据类型分类二、数值类型1、整数类型2、bit类型3、小数类型 三、字符串类型四、时间日期类型五、enum和set类型enum和set查找 数据类型的作用
决定了存储数据时应该开辟的空间大小和数据的取值范围。决定了如何识别一个特定的二进制序列。
一、数据类型分类 MySQL中的数据类型与编程语言中的数据类型相似在学习时我们不进行一 一演示通过讲述一个或两个特定的类型你也能够触类旁通。
二、数值类型
1、整数类型
MySQL为我们提供了不同占用不同字节大小的整数类型以便于我们能够更好的使用数据和节省磁盘的空间。 在MySQL中整型可以指定是有符号的和无符号的默认是有符号的可以通过 UNSIGNED 来说明某个字段是无符号的。
这里我们以tinyint类型为例介绍数值类型
在下面我们创建一张表并向表中插入一些合法数据一些非法数据。
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不会为我们的整形进行截断转换。 所以数据类型本身也是一种约束通过这种约束我们就能保证数据库中的数据是可预期完整的;
2、bit类型
基本语法
bit[(M)] : 位字段类型。M表示每个值的位数范围从1到64。如果M被忽略默认为1。bit类型的显示方式
创建一个表表当中包含一个int类型表示用户id和一个8位bit类型表示一些选项。如下
create table user(id int, op bit(8));
desc user;向user表中插入一些数据并显示表中的内容
insert into user values (124, 1);
insert into user values (127, 97);
select * from user;我们发现我们插入的1没有被显示而我们插入的97却显示成了字符a这因为bit类型在显示时是按照ASCII码对应的值进行显示的,1是不可显示字符97对应的就是a。
如果我们还是想要让bit类型能够像整数类型那样显示的话我们可以在显示时对op字段加上hex以16进制显示。
select id, hex(op) from user;可以看到结构能够正常显示了
bit类型的范围测试
创建一个表表当中包含用户id和用户性别gender其中gender的类型可以指定为1位bit类型因为性别只有男和女两种取值使用1个比特位来表示用户的性别就可以节省空间。
create table user_info(id int, gender bit);
desc user_info; 插入数据
insert into user_info values (12, 0);
insert into user_info values (13, 1);
insert into user_info values (13, 3);
insert into user_info values (14, 5);
select id, hex(gender) from user_info;可以看到如果插入gender列的数据不是0或1那么插入数据时就会产生报错。
建议
虽然MySQL提供了位类型bit但一般不建议将数据类型设置成位类型除非将来这个数据本身就只是给程序看的并且数据本身非常占用资源。因为查询位类型数据时默认会按照ASCII码对应的值进行显示这对于将来数据库管理员维护数据库或程序员调试程序都是不太方便的。
3、小数类型
float类型
语法
float[(m, d)] [unsigned] : M指定显示长度d指定小数位数占用空间4个字节特征
对于小数部分如果超出了精度MySQL在保存值时会进行四舍五入对于整数部分如果超出精度则拒绝插入。对于有符号的float的范围是去掉负数只留正数。float表示的精度大约是小数点后7位但是当float存储较高精度的数据时会出现误差。 案例1 我们创建一个float(4,2)类型则这个类型可以表示的范围是-99.99 ~ 99.99然后我们分别向这个表中插入 99.99, -99.9923.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;结果符合我们的预期 案例2 我们在创建一张无符号的float42表然后我们插入正数99100.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;查看表的内容后发现果然正数的范围没有改变负数不能被插入。 案例3 创建一张float类型的表然后我们向其中插入2202.31234567891.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;我们发现对于精度较高的数据无论是正数部分太大还是小数部分太多都会导致float类型存储时出现误差。
decimal类型
decimal和float类型的使用方式一样但decimal的精度比float更高。
语法
decimal(m, d) [unsigned] : 定点数m指定长度d表示小数点的位数特征
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;建议如果希望小数的精度高推荐使用decimal。
三、字符串类型
char类型
语法
char(L): 固定长度字符串L是可以存储的长度单位为字符最大长度值可以为255。特征
char类型的单位是字符而不是字节无论是英语a还是汉语我甚至感叹号!等都表示一个字符。
例如我们先创建一张表包含一个chart字段这个字段的类型我们设置为char(4)然后我们向其插入
abc你好啊~A你好A你好啊。
creat table ch (chart char(4));
desc ch;进行插入并显示
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限定字符的概念不是字节这样用户就不用关心复杂的编码细节了。
varchar类型
语法
varchar(L): 可变长度字符串L表示字符长度最大长度65535个字节说明
varchar类型是可变长字符串类型其使用与char并无区别但是varchar能够更加节省空间。
在上面char类似的案例演示中如果我们使用的是utf8编码对于char(4)MySQL其实在底层默认给我们分配了 4 ∗ 3 12 4 * 3 12 4∗312个字节 上面的演示案例中如果我们存放的是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没有差别差别在于使用utf8时底层varchar对于abc分配使用的空间相比于char要少。 案例2 由于varchar类型的字节数限制在定义编码格式为utf8的表时varchar(L)中的L如果超过了21844则会产生报错。如下
create table utf8_table (content varchar(21845)) charsetutf8;
create table utf8_table (content varchar(21844)) charsetutf8;由于varchar类型的字节数限制在定义编码格式为gbk的表时varchar(L)中的L如果超过了32766则会产生报错。如下
create table gbk_table (content varchar(32767)) charsetgbk;
create table gbk_table (content varchar(32766)) charsetgbk;char和varchar比较 char和varchar的区别如下
char类型可存储字符上限为255varchar类型可存储字符上限与表的编码格式有关。char(L)定义后无论存储的字符串长度是否到达L都会开辟用于存储L个字符的定长空间如果存储的字符串长度超过L则会报错。varchar(L)定义后会根据存储字符串的长度按需开辟空间并且需要使用1-3字节的空间用于表示存储字符串的长度以及其他控制信息如果存储的字符串长度超过L则会报错。
char和varchar的优缺点如下
char类型的数据是定长的因此磁盘空间比较浪费但是效率高直接访问定长的空间。varchar类型的数据是变长的因此磁盘空间比较节省但是效率低需要先读取存储字符串的长度再访问指定长度的空间。
如果要存储的数据是定长的那就使用char类型进行存储比如身份证号码、手机号、md5等。如果要存储的数据是变长的那就使用varchar类型进行存储比如名字、地址等。
四、时间日期类型
常用的三种时间日期类型如下
date日期格式为YYYY-MM-DD占用三字节。datetime时间日期格式为YYYY-MM-DD HH:MM:SS占用八字节。timestamp时间戳格式为YYYY-MM-DD HH:MM:SS占用四字节值得注意的是这个字段我们不主动修改的话此字段就会自动插入当前的时间戳。
创建一个表表当中包含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;如果评论人修改了评论内容那么就需要对评论表进行更新更新表的同时评论的发布时间也会更新为修改表的时间。如下
update comment_table set content加油;
select * from comment_table;五、enum和set类型
enum和set类型的区别如下
在定义enum字段时需要提供若干个选项的值在设置enum字段值时只允许选取其中的一个值。在定义set字段时需要提供若干个选项的值在设置set字段值时可以选取其中的一个或多个值。
语法
enum
enum(选项1, 选项2, 选项3,...);该设定只是提供了若干个选项的值最终一个单元格中实际只存储了其中一个值而且出于效率考虑这些值在MySQL内部实际存储的是“数字”因为这些选项的每个选项值依次对应如下数字1,2,3,…最多65535 个当我们添加枚举值时也可以添加对应的数字编号。
set
set(选项值1,选项值2,选项值3, ...);该设定只是提供了若干个选项的值最终一个单元格中设计可存储了其中任意多个值而且出于效率考虑这些值在MySQL内部实际存储的是“数字”因为这些选项的每个选项值依次对应如下数字1,2,4,8,16,32…最多64个。即每个比特位都不相等的「位图结构」 调查表案例 我们现在需要做一个爱好调查表包含名称性别爱好。
分析
名称我们可以定义为varchar类型以节省空间。人的性别只能从男和女中进行二选一因此可以定义成enum类型。人的爱好在提供的选项中可能存在多个因此可以定义成set类型。
创建一个调查表表当中包含被调查人的姓名、性别和爱好。如下
create table investigation( name varchar(20), gender enum(男,女), hobby set(下棋,打篮球,游泳,跑步,跳绳));desc investigation;向表中插入记录时被调查人的性别只能从男和女中进行二选一被调查人的爱好可以从提供的若干个选项中进行多选一或多选多多个爱好之间需要通过英文逗号隔开。如下
insert into investigation values (张飞, 男, 打篮球,游泳,跑步);
select * from investigation;在插入记录时除了通过指明男女来设置性别还可以通过插入数字1和2来设置性别。如下 在插入记录时除了通过指明多个选项来设置爱好还可以通过数字的方式来设置(对应的比特位进行映射例如31的比特位为全1对应的就是五个爱好全部插入)。如下 enum和set查找
where子句进行 「行匹配」
SELECT 选项 表名称 WHERE 字段想要匹配的信息如果想要筛选出调查表中所有女生的信息那么直接在select筛选时指明gender女即可。
select * from investigation where gender女;但是对于set类型我们使用where子句进行的匹配是严格匹配例如我们想要筛选出所有喜欢跳绳的人
select * from investigation where hobby跳绳;我们只是匹配到了刘禅而没有匹配到其他人要解决这个问题我们就要要借助MySQL给我们提供的find_in_set(str,strlist)函数了。
find_in_set(str,strlist) 集合查找
该函数的作用是查询strlist中是否包含str如果包含则返回str在strlist中的位置从1开始否则返回0。
通过select可以对find_in_set函数进行验证依次查找集合a,b,c中是否包含字符a、b、d这时在查找字符a和b时就会得到其在集合中的下标而在查找字符d时就会得到0值。如下 这时就可以通过select搭配find_in_set函数来筛选出爱好包含跳绳的人的信息了。如下
