网站建设 绵阳,浦东新区专业网站建设,网页设计软件下载网站,网站有效内容的宣传及推广✨个人主页#xff1a; 北 海 #x1f389;所属专栏#xff1a; MySQL 学习 #x1f383;操作环境#xff1a; CentOS 7.6 阿里云远程服务器 #x1f381;软件版本#xff1a; MySQL 5.7.44 文章目录 1.数据类型一览2.整型2.1.INT2.2.BIT 3.浮点数3.1.FLOAT3.2.DECIMAL3… ✨个人主页 北 海 所属专栏 MySQL 学习 操作环境 CentOS 7.6 阿里云远程服务器 软件版本 MySQL 5.7.44 文章目录 1.数据类型一览2.整型2.1.INT2.2.BIT 3.浮点数3.1.FLOAT3.2.DECIMAL3.3.如何选择 4.字符4.1.CHAR4.2.VARCHAR4.3.如何使用 5.时间和日期5.1.DATE5.2.DATETIME5.3.TIMESTAMP 6.枚举和集合6.1.ENUM6.2.SET 1.数据类型一览
作为专门用来存储数据的 数据库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字符串对象表示集合类型可以选取零个或多个值 创建一个数据库 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 占用字符abcdabcdabcd4*3124*3113好好好4*3121*314AbcdeXX数据超过长度数据超过长度
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 中数据类型的讲解到此结束总之数据类型本质上就是一种约束确保插入数据库中数据的合法性
