【MySQL基础篇】：MySQL常用数据类型的选择逻辑与正确使用

✨感谢您阅读本篇文章，文章内容是个人学习笔记的整理，如果哪里有误的话还请您指正噢✨
✨ 个人主页：余辉zmh–CSDN博客
✨ 文章所属专栏：MySQL篇–CSDN博客

在这里插入图片描述

文章目录

  • 数据类型
    • 1.数据类型分类
    • 2.数值类型
      • int整形类型
      • bit位类型
      • float小数类型
    • 3.字符串类型
    • 4日期和时间类型
    • 5.enum和set
      • enum类型
      • set类型

数据类型

1.数据类型分类

分类数据类型说明
数值类型BIT(M)位类型。M指定位数，默认值1，范围1-64
TINYINT [UNSIGNED]带符号的范围-128~127，无符号范围0~255，默认有符号
BOOL使用0和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字节
DOUBLE[(M,D)] [UNSIGNED]表示比float精度更大的小数，占用空间8字节
DECIMAL(M,D) [UNSIGNED]定点数M指定长度，D表示小数点的位数
文本、二进制类型CHAR(size)固定长度字符串，最大255
VARCHAR(size)可变长度字符串，最大长度65535
BLOB二进制数据
TEXT大文本，不支持全文索引，不支持默认值
时间日期DATE/DATETIME/TIMESTAMP日期类型(yyyy-mm-dd)，(yyyy-mm-dd hh:mm:ss)，timestamp时间戳
String类型ENUM类型字符串对象，值来自表创建时在列规定中显示枚举的一个列值
SET类型字符串对象，可包含零或多个允许值，多个值用逗号分隔（值中不能包含逗号）

接下来会讲解几个常用类型的使用：

2.数值类型

int整形类型

1.TINYINT

TINYINT          -- 有符号：-128 到 127
TINYINT UNSIGNED -- 无符号：0 到 255
  • 存储空间大小：1字节；
  • 常见用途：布尔值，状态标志，年龄等小数值；
  • 示例：
 create table users(id int,age tinyint,status tinyint
);

不管是有符号还是无符号的，插入的数值必须在要求范围中才能插入成功，否则就会插入失败

在这里插入图片描述

后面的几个整型类型也是如此，就不演示了。

2.SMALLINT

SMALLINT          -- 有符号：-32,768 到 32,767
SMALLINT UNSIGNED -- 无符号：0 到 65,535
  • 存储空间大小：2字节；
  • 常见用途：年份，端口号，小计数值等；

3.INT

INT               -- 有符号：-2,147,483,648 到 2,147,483,647
INT UNSIGNED      -- 无符号：0 到 4,294,967,295
  • 存储空间大小：4字节；
  • 常见用途：主键，用户ID，计数器等；

4.BIGINT

BIGINT            -- 有符号：-9,223,372,036,854,775,808 到 9,223,372,036,854,775,807
BIGINT UNSIGNED   -- 无符号：0 到 18,446,744,073,709,551,615
  • 存储空间大小：8字节；
  • 常见用途：大数值，时间戳，大数据场景等；

注意：

  • 尽量不要使用unsigned无符号，对于int类型存不下的数据，换成int unsigned同样可能存不下，那还不如直接设置成一个更大的int类型；
  • 较小的整形索引更快，选择合适的类型可以节省空间，而且整形比字符串要更加高效；

bit位类型

1.语法格式

BIT(M) -- M表示位数， 范围1-64

2.基本用法

  • 单比特存储
create table flags(id int,is_active bit(1),    -- 存储0/1is_deleted bit(1)    -- 存储0/1
);
  • 多比特存储
create table permissions(id int,user_flag bit(8),   -- 8位用户权限system_flag bit(32) -- 32位系统标志
);

3.存储范围：

BIT(1)   -- 0 或 1
BIT(8)   -- 0 到 255
BIT(16)  -- 0 到 65535
BIT(32)  -- 0 到 4294967295
BIT(64)  -- 0 到 18446744073709551615

4.插入数据：

-- 方式1：使用十进制
insert into permissions values(1001, 21, 252);-- 方式2：使用b前缀（b表示二进制字面量） 比较推荐这种写法
insert into permissions values(1002, b'10101', b'11111100');-- 方式3：使用十六进制
insert into permissions values(1003, 0x15, 0xFC);

5.查询和显示：

-- 默认显示
select *from permissions;

在较新版本的MySQL8.0中，BIT类型默认以十六进制格式显示（在老版本中会显示看不见任何数据）：

在这里插入图片描述

除了默认格式显示，也可以指定形式显示：

 select id,bin(user_flag) as user_flag_binary,  -- 二进制格式显示hex(system_flag) as system_flag_hex  -- 十六进制格式显示from permissions;

在这里插入图片描述

float小数类型

float是单精度浮点数类型，用于存储小数数据；

1.基本语法：

float[(M,D)] [unsigned] [zerofill]
  • M：总位数（精度）；
  • D：小数点后的位数（标度）；
  • unsigned：无符号（非负数）；
  • zerofill：用零填充；

存储范围和精度：

-- FLOAT的存储特性
-- 范围：-3.402823466E+38 到 -1.175494351E-38, 0, 1.175494351E-38 到 3.402823466E+38
-- 精度：约7位十进制数字

2.常用示例：

  • 创建表时使用float：
create table products(name varchar(30),price float(8,2),weight float(8,3),rating float,discount float(3,2) unsigned
);
  • 插入数据：
insert into products values('苹果', 5.99, 0.250, 4.5, 0.10),('香蕉', 4.88, 0.200, 4.0, 0.15),('橙子', 3.99, 0.300, 3.9, 0.20);
  • 查询和计算：
-- 基本查询
select * from products;-- 数学计算select name,price,price*(1-discount) as final_price -- 计算折扣后的价格from products;

在这里插入图片描述

3.精度问题

以float(4,2)为例，表示的范围就是-99.99-99.99，MySQL在保存数据时会进行四舍五入；如果是float(8.2)，表示的范围就是-99999999.99-99999999.99，同样也是四舍五入；

insert into products values('葡萄', 2.991, 0.150, 4.1, 0.15),('桃子', 2.947, 0.350, 3.7, 0.20);

在这里插入图片描述

如果是一个float(4,2) unsigned 无符号的，这时表示范围就是0-99.99，直接把负数舍弃掉；

除了float之外，还有一个浮点数类型decimal，它的用法和float一模一样，float怎么用，decimal就怎么用（这里就不展示示例了），唯一的区别就是表示的精度不同；

create table test(id int,f1 float(10,8),f2 decimal(10,8)
);

在这里插入图片描述

对于插入相同的一个浮点数，float和decimal因为精度不同，导致存储的数据不同；

  • float的精度大约为7位；
  • decimal整数最大位数m为65位，如果m省略，默认位10；支持小数最大位数d是30，如果d被省略，默认为0；
  • 如果希望小数的精度高，推荐使用decimal；

3.字符串类型

CHAR 类型

  • 固定长度：无论存储多少字符，都占用相同的存储空间；

  • 语法：char(M) 其中 M 是字符数（1-255），char(2)表示可以存放两个字符，可以是字母或汉字，但是不能超过2个；

  • 存储方式：总是占用 M 个字符的存储空间，不足的部分用空格填充；

VARCHAR 类型

  • 可变长度：根据实际存储的字符数占用存储空间；

  • 语法：varchar(M) 其中 M 是最大字符数（不能超过65535字节）；

  • 存储方式：只占用实际字符数 + 1-3字节的长度前缀（用来记录数据大小）；

示例：

create table student(name varchar(30),gender char(2)
);insert into student values('张三', '男'),('李四', '女');select * from student;
+--------+--------+
| name   | gender |
+--------+--------+
| 张三   | 男     |
| 李四   | 女     |
+--------+--------+

注意点：

varchar(M)中，M的大小和表的编码密切相关；

在编码utf8中，一个字符占用3个字节，假设长度前缀占用一个字符，就是三个字节，所以实际上有效字节数就是65535-3=65532；有效字符数就是65532/3=21844；

所以在创建表时，使用varchar定义的字符大小一定不能超过21844，这是单个varchar列的限制：

create table s1(name varchar(21844),  -- 21844 * 3 = 65532字节
)charset utf8;

在这里插入图片描述

但是还要考虑整行大小限制，一行除了varchar大小之外，还有其他字段的大小，比如下面这种情况：

create table s1(name varchar(21844),  -- 21844 * 3 = 65532字节gender char(2)        -- 2 * 3 = 6字节
)charset utf8;

实际计算：

  • name 列：21844字符 × 3字节 = 65532字节

  • gender 列：2字符 × 3字节 = 6字节

  • VARCHAR长度前缀：3字节（存储实际长度）

  • 总计：65532 + 6 + 3 = 65541字节 > 65535字节

正确的最大长度应该是：

-- 计算：65535 - 其他列开销 - VARCHAR长度前缀
-- 65535 - 6 - 3 = 65526字节
-- 65526 ÷ 3 = 21842字符

在这里插入图片描述

char和varchar比较：

实际存储char(4)varchar(4)char占用字节varchar占用字节
abcdabcdabcd4*3=124*3+1=13
AAA4*3=121*3+1=4
Abcde错错数据超过长度数据超过长度

如何选择定长或变长字符串？

  • 如果数据确定长度都一样，就使用定长（char），比如：身份证，手机号，md5

  • 如果数据长度有变化,就使用变长(varchar), 比如：名字，地址，但是你要保证最长的能存的进去。

  • 定长的磁盘空间比较浪费，但是效率高。

  • 变长的磁盘空间比较节省，但是效率低。

  • 定长的意义是，直接开辟好对应的空间

  • 变长的意义是，在不超过自定义范围的情况下，用多少，开辟多少。

4日期和时间类型

常用的日期有以下三个：

  • date：日期yyyy-mm-dd，占用三个字节；
  • datetime：时间日期格式yyy-mm-dd hh:ii:ss表示范围从1000到9999，占用八个字节；
  • timestap：时间戳，从1970年开始的yyyy-mm-dd hh-ii-ss格式和datetime完全一致，占用四个字节；

示例：

create table birthday(-> t1 date,-> t2 datetime,-> t3 timestamp default current_timestamp-> );

default current_timestamp：在插入数据时不用输入，时间戳会自动补上当前时间；通常用来更新数据的插入时间；

在这里插入图片描述

5.enum和set

enum类型

1.基本语法

enum('值1', '值2', '值3', ...);

2.存储机制-数字存储

  • 内部存储：用整数存储（1, 2, 3…），可以理解为从1开始的下标；

  • 值1 = 1，值2 = 2，值3 = 3…以此类推；

  • 存储空间：1-2字节（取决于选项数量）；

3.常用示例

-- 创建表
create table student (name varchar(50),gender enum('男', '女'),status enum('active', 'inactive', 'pending')
);-- 插入数据
insert into users values 
('张三', '男', '1'),
('李四', '女', '2'),
('赵六', '1', '3');-- 查询（可以用数字代替字符串）
select * from student where gender = 1;  -- 等同于 where gender = '男'
select * from student where status = 2;  -- 等同于 where status = 'inactive'

在这里插入图片描述

4.数字存储验证：

select gender,gender + 0 as gender_number,status,status + 0 as status_numberfrom student;

在这里插入图片描述

set类型

1.基本语法

set('值1', '值2', '值3', ...)

2.存储机制-位掩码存储

  • 内部存储：用位掩码（bitmask）存储；

  • 值1 = 1 (001)，值2 = 2 (010)，值3 = 4 (100)；

  • 组合存储：多个值用位运算组合；

  • 存储空间：1-8字节（取决于选项数量）；

3.位掩码示例

-- 假设 set('A', 'B', 'C', 'D');
-- A = 1(0001)
-- B = 2(0010)
-- C = 4(0100)
-- D = 8(1000)-- 组合存储
-- 'A, B' = 1 + 2 = 3(0011)
-- 'A, C' = 1 + 4 = 5(0101)
-- 'B, C, D' = 2 + 4 + 8 = 14(1110)

4.常用示例

-- 创建测试表create table student(name varchar(30),gender char(2),hobby set('篮球', '足球', '羽毛球', '乒乓球'));-- 插入数据
insert into student values('张三', '男', '篮球'),('李四', '女', '羽毛球, 乒乓球'),('王五', '男', '2'),('赵六', '女', '7');-- 查询
select * from student;
select * from student where hobby = 1;       -- 1对应0001，篮球
select * from student where hobby = '羽毛球'; -- 查找羽毛球的

在这里插入图片描述

这里就有一个问题了，在查找羽毛球时，显示为空，但是表中明明有人包含了羽毛球，为什么会没有显示出来呢？

这是因为set类型的精确匹配特性：

  • where hobby = ‘羽毛球’ 是精确匹配；

  • 它只查找 hobby 字段完全等于 ‘羽毛球’ 的记录；

  • 而在上面的数据中，羽毛球都是和其他爱好组合在一起的；

但是我们实际生活中每个人都有好几个爱好，总不能必须只有一个爱好才能查找出来吧？当然不是，这时候就要借助其他查找方式了

这里推荐两种方式：

  • 使用find_in_set函数：
select * from student where find_in_set('羽毛球', hobby);
  • 使用位运算：
-- SET('篮球', '足球', '羽毛球', '乒乓球') 的位掩码
-- 篮球=1, 足球=2, 羽毛球=4, 乒乓球=8
select * from student where hobby & 4;

在这里插入图片描述

5.位掩码存储验证

select name,hobby,hobby + 0 as hobby_numberfrom student;

在这里插入图片描述

总结

性能优势：

enum优势：

  • 存储效率高：1-2字节 vs VARCHAR的变长存储

  • 查询速度快：整数比较比字符串比较快

  • 索引效率高：整数索引更紧凑

set优势：

  • 多选存储：一个字段存储多个值

  • 位运算查询：支持高效的位运算查询

  • 存储紧凑：多个选项组合存储

注意事项：

enum注意事项：

  • 数字转换：可以用数字代替字符串，但不推荐
  • 修改选项：修改ENUM选项会影响现有数据
  • 空值：NULL和空字符串是不同的

set注意事项：

  • 顺序无关：‘A,B’ 和 ‘B,A’ 等价
  • 重复值：自动去重
  • 位运算：理解位掩码有助于高级查询
    位掩码存储验证**

以上就是关于MySQL数据类型中常用的几个类型的使用讲解，如果哪里有错的话，可以在评论区指正，也欢迎大家一起讨论学习，如果对你的学习有帮助的话，点点赞关注支持一下吧！！！

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如若转载，请注明出处：http://www.pswp.cn/diannao/93308.shtml
繁体地址，请注明出处：http://hk.pswp.cn/diannao/93308.shtml

如若内容造成侵权/违法违规/事实不符，请联系多彩编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

三、搭建springCloudAlibaba2021.1版本分布式微服务-springcloud loadbalancer负载均衡

三、搭建springCloudAlibaba2021.1版本分布式微服务-springcloud loadbalancer负载均衡

什么是负责均衡 Spring Cloud LoadBalancer是一个客户端负载均衡器&#xff0c;类似于Ribbon&#xff0c;但是由于Ribbon已经进入维护模式&#xff0c;并且Ribbon 2并不与Ribbon 1相互兼容&#xff0c;所以Spring Cloud全家桶在Spring Cloud Commons项目中&#xff0c;添加了Sp…
阅读更多...
Oracle不完全恢复实战指南：从原理到操作详解

Oracle不完全恢复实战指南：从原理到操作详解

核心提示&#xff1a;当误删表、日志损坏或控制文件丢失时&#xff0c;Oracle的不完全恢复是DBA最后的救命稻草。掌握关键恢复技术&#xff0c;可在数据灾难中力挽狂澜。一、不完全恢复核心概念 1. 核心特点 必须关闭数据库&#xff1a;在MOUNT状态下执行重做日志恢复权限要求&…
阅读更多...
Linux之shell脚本篇（二）

Linux之shell脚本篇（二）

一、shell编程之if语句引言Linux在shell编程中&#xff0c;通常都是以自上而下运行&#xff0c;但是为了提高其代码严谨性&#xff0c;我们即引入了多条件 控制语句例如&#xff1a;if、for、while、case等语句&#xff0c;有时候针对条件我们还会结合正则表达式去运用。将这些…
阅读更多...
如何在android framewrok dump camera data

如何在android framewrok dump camera data

实现dump 函数 实现1 void dumpBufferToFile(buffer_handle_t* buffer, int width, int height, int frameNum) {void* data NULL;GraphicBufferMapper::getInstance().lock(*buffer, GRALLOC_USAGE_SW_READ_OFTEN, Rect(width, height), &data);char filename[128];sprin…
阅读更多...
机器学习中的可解释性：深入理解SHAP值及其应用

机器学习中的可解释性：深入理解SHAP值及其应用

机器学习可解释性的重要性在人工智能技术快速发展的2025年&#xff0c;机器学习模型已经深度渗透到医疗诊断、金融风控、司法量刑等关键领域。然而&#xff0c;随着模型复杂度的不断提升&#xff0c;一个根本性矛盾日益凸显&#xff1a;模型预测性能的提升往往以牺牲可解释性为…
阅读更多...
.NET9 使用 OData 协议项目实战

.NET9 使用 OData 协议项目实战

.NET 中 ODate 协议介绍 OData(Open Data Protocol) 是一个开放的 Web 协议&#xff0c;用于查询和更新数据。在 .NET 生态系统中&#xff0c;OData 被广泛支持和使用。 主要特性 1. 统一的数据访问方式 提供标准化的查询语法支持 CRUD 操作支持元数据描述 2. 查询能力 标…
阅读更多...
Android 性能优化：提升应用启动速度（GC抑制）

Android 性能优化：提升应用启动速度（GC抑制）

前言 在移动应用开发领域&#xff0c;启动速度是用户体验的重要指标。对于Android应用而言&#xff0c;垃圾回收&#xff08;Garbage Collection, GC&#xff09;机制虽然是内存管理的核心&#xff0c;但在应用启动期间频繁触发GC会显著拖慢启动速度。本文将深入探讨如何通过GC…
阅读更多...
做了一款小而美的本地校验器

做了一款小而美的本地校验器

需求说明 前阵子收到一则读者留言&#xff0c;指出&#xff1a;市面上AI核稿工具&#xff08;ProWritingAid&#xff0c;WPS AI Spell Check&#xff0c;Writer&#xff0c;QuillBot&#xff0c;Grammarly&#xff09;要么收费太高&#xff0c;要么让人担心文章泄露。 如下图所…
阅读更多...
uniapp + uview-plus 微信小程序二维码生成和保存完整解决方案

uniapp + uview-plus 微信小程序二维码生成和保存完整解决方案

uniapp + uview-plus 微信小程序二维码生成和保存完整解决方案 📋 项目背景 在开发微信小程序时，经常需要实现二维码的生成和保存功能。本文档提供了一个基于 uniapp + uview-plus 框架的完整解决方案，彻底解决了以下常见问题： ✅ Canvas API 兼容性问题 ✅ 微信小程序权…
阅读更多...
Linux中应用程序的安装于管理

Linux中应用程序的安装于管理

Linux中应用程序的安装于管理 一 . rpm安装 1.挂载 光驱里面存放了很多rpm的软件包 光驱在系统中使用时&#xff0c;需要挂载 mount /dev/cdrom /mnt/ cd /mnt[rootstw mnt]# ls CentOS_BuildTag GPL LiveOS RPM-GPG-KEY-CentOS-7 EFI images Packag…
阅读更多...
mysql重置密码

mysql重置密码

要区分 MySQL 是通过 systemd 还是传统 service 管理&#xff0c;以及对应的密码重置方案&#xff0c;可按以下步骤操作&#xff1a; 一、如何区分管理方式&#xff08;systemd 还是传统 service&#xff09; 通过以下命令判断系统默认的服务管理方式&#xff1a;检查系统是否使…
阅读更多...
C++ TAP（基于任务的异步编程模式）

C++ TAP（基于任务的异步编程模式）

&#x1f680; C TAP&#xff08;基于任务的异步编程模式&#xff09;1. 引言&#xff1a;走进异步编程新时代&#xff08;&#x1f680;&#xff09; 在当今高性能计算领域&#xff0c;同步编程模型的局限性日益凸显。传统的回调地狱和线程管理复杂性促使微软提出了基于任务的…
阅读更多...
利用C++手撕栈与队列的基本功能（四）

利用C++手撕栈与队列的基本功能（四）

栈和队列详细教程可以观看 https://www.bilibili.com/video/BV1nJ411V7bd?spm_id_from333.788.videopod.episodes&vd_sourcedaed5b8a51d3ab7eb209efa9d0ff9a34&p48栈和队列概念 栈和队列是限定插入和删除只能在表的端点进行的线性表在装电池、装弹夹、拿放盘子时都会出…
阅读更多...
net8.0一键创建支持(Redis)

net8.0一键创建支持(Redis)

Necore项目生成器 - 在线创建Necore模板项目 | 一键下载 RedisController.cs using CSRedis; using Microsoft.AspNetCore.Http; using Microsoft.AspNetCore.Mvc; using UnT.Template.Application.Responses; using UnT.Template.Domain;namespace UnT.Template.Controllers {…
阅读更多...
Leetcode——42. 接雨水

Leetcode——42. 接雨水

还记得第一次见该题根本无从下手。其实&#xff0c;我们不妨把问题拆解&#xff0c;简单化。不要怕自己写的是暴力算法&#xff0c;有很多算法技巧其实就是在暴力算法的基础上优化得来。题目目的是求所有可接雨水数量&#xff0c;我们可以求出每一个位置可接雨水数量&#xff0…
阅读更多...
Go 语言--＞指针

Go 语言--＞指针

Go 语言–>指针 它允许你操作内存中的实际数据&#xff0c;而不仅仅是数据的副本。指针存储的是另一个变量的内存地址&#xff0c;而不是变量的实际值。 1. 什么是指针 指针是存储变量内存地址的变量&#xff0c;它指向另一个变量。通过指针&#xff0c;你可以间接地访问和修…
阅读更多...
软工八将：软件开发全流程核心角色体系解析

软工八将：软件开发全流程核心角色体系解析

软工八将&#xff1a;软件开发全流程核心角色体系解析 作者注&#xff1a;本概念是由大学生董翔提出&#xff0c;具有一些影响意义。 在现代软件开发领域&#xff0c;团队角色的专业化分工是产品成功的核心保障。“软工八将”作为一套系统梳理软件开发全流程核心角色的术语&…
阅读更多...
安全风险监测系统是什么？内容有哪些？

安全风险监测系统是什么？内容有哪些？

安全风险监测系统是基于物联网感知网络与智能分析技术的综合管理平台&#xff0c;通过实时采集、分析和评估各类安全风险指标&#xff0c;构建起覆盖识别、预警、处置全流程的主动防御体系。作为现代安全管理的中枢神经系统&#xff0c;该系统实现了从被动响应到主动预防的范式…
阅读更多...
车载诊断架构 ---面向售后的DTC应该怎么样填写？

车载诊断架构 ---面向售后的DTC应该怎么样填写？

我是穿拖鞋的汉子，魔都中坚持长期主义的汽车电子工程师。 老规矩，分享一段喜欢的文字，避免自己成为高知识低文化的工程师： 简单，单纯，喜欢独处，独来独往，不易合同频过着接地气的生活，除了生存温饱问题之外，没有什么过多的欲望，表面看起来很高冷，内心热情，如果你身…
阅读更多...
墨者：SQL注入漏洞测试(宽字节)

墨者：SQL注入漏洞测试(宽字节)

墨者学院&#xff1a;SQL注入漏洞测试(宽字节)&#x1f680; 1. 宽字节注入原理✨ 1.1. 与普通注入对比⭐ 特性普通注入宽字节注入适用场景无转义处理使用addslashes()等转义函数核心原理直接闭合引号利用GBK等编码吞掉转义符\关键字符 " -- #%df %5c防御难度易防御需调…
阅读更多...
最新文章

Copyright @ 2022~2023