MYSQL数据类型-优快云博客

数值类型

tinyint

MYSQL中的tinyint很像c语言中的char

有符号的tinyint范围是-128~127
无符号的tinyint（类型为tinyint unsigned）范围是0~255

c语言中，你给char类型的变量赋值256，会发生截断，但不会报错。可是在mysql中，这种情况就会报错，这是为什么？

MYSQL非常严谨，他需要保证内部存的数据具有非常强的确定性和一致性
MYSQL想倒逼使用者，必须严格按MySQL的要求进行数据表中类型的设计与使用

MYSQL中的数据类型在本质上是一种约束，目的就是为了保证数据的确定性

bit类型

在 MySQL 中，BIT 类型用于存储位值，是一种紧凑的二进制数据类型，常用于存储开关状态、标志位等需要节省空间的场景。

bit类型存储范围：

可以指定长度 BIT(M)，其中 M 表示位数，范围是 1 ~ 64（默认 1）
存储大小根据 M 自动计算，例如 BIT(8) 占 1 字节，BIT(9) 占 2 字节

bit类型取值规则：

只能存储 0 或 1（单一位时）
多位时可存储二进制值，例如 BIT(3) 可存储 000 到 111（对应十进制 0~7）

注意：bit字段在显示时，是按照ASCII码对应的值显示的！
看下面这个例子，为什么a的值打印不出来呢？
在这里插入图片描述
主要原因就是因为，当bit(8)类型的变量a值为10时，select * from tt4会将ASCII码为10的对应字符作为变量a的值打印出来。但是其实ASCII码为10的对应字符是控制字符，它是不可显的，因此我们看不出来

如果这时候我们将变量a的值设置为65，那么select * from tt4会将ASCII码为65的对应字符作为变量a的值打印出来。ASCII码为65对应的是可显示字符A，我们就能看见了

bool类型

在 MySQL 中，BOOL（或 BOOLEAN）类型是一种用于存储布尔值的数据类型，常用来判断逻辑的真假

插入一个bool类型的变量时，我们可以使用 TRUE/FALSE、1/0 或其他非零值（非零值会被视为 1）作为bool类型的取值。

查询时如果返回值是bool类型，那么返回值只会是 1 或 0，不会返回 TRUE/FALSE

提问：mysql类型的bool类型是不是只有0和1两个值？
不是，虽然bool类型表面上看是只有0和1两个值，但实际上bool类型的底层就是tinyint(1)，也就是说bool类型本质上是 TINYINT(1) 的别名，并非真正意义上的布尔类型，其值也可以为2、3、4等，只是说0和1这俩值是最常用的

small int类型

SMALLINT：带符号短整型，范围是 -2¹⁵ 到 2¹⁵-1
SMALLINT UNSIGNED：无符号短整型，范围：0 ~ 2¹⁶-1

int 类型

这个我们太熟悉了，不再解释
带符号范围 0 ~ -2³¹到2³¹-1
无符号范围 0 ~ 2³²-1

bigint类型

顾名思义，更大范围的整型，相当于long long
带符号范围 -2⁶³到2⁶³-1
无符号范围 0 ~ 2⁶⁴-1

float类型

使用格式： float[(m, d)] [unsigned]
其中M指定显示长度，d指定小数位数，占用空间4个字节

举个粒子

float(4,2)表示的范围是多少？
-99.99 ~ 99.99
float(4,2) unsigned表示的范围是？
0 ~ 99.99

float的精度
float中的M 取值范围是 1 ≤ M ≤ 255
但实际上，FLOAT 类型的精度有限（约 7 位有效数字），当 M 超过 7 时，超出部分可能无法精确存储（会自动四舍五入）

float类型会在小数部分采用四舍五入（不包含5）的取整方案
举个例子，mysql中定义一个float(4,2)类型的变量，然后我插入一个数据value值为99.956，最后实际插入的是99.96

补充：常见的取整方案

0向取整
向上取整
四舍五入
向下取整

C语言的取整方案是什么？

0向取整

double

使用格式：DOUBLE[(m, d)] [unsigned]
其中M指定显示长度，d指定小数位数，占用空间8个字节
double类型的使用规则和float的规则一模一样，只是DOUBLE[(M,D)][UNSIGNED]：比 float 精度更高，占 8 字节

double的精度
double中M 取值范围是 1 ≤ M ≤ 255
但实际上double 类型的精度有限（大约只有 15 位有效数字），当 M 超过15 时，超出部分可能无法精确存储（会自动四舍五入）

decimal

在 MySQL 中，DECIMAL 是一种用于存储精确数值的数据类型，特别适合处理货币、金额等对精度要求极高的数据（与 FLOAT/DOUBLE 的不同之处在于——DECIMAL存储浮点型数据不会有精度损失问题）。

decimal使用格式为：DECIMAL(M, D)，其中M表示十进制数据的总位数（精度，1 ≤ M ≤ 65），包括整数部分和小数部分，D表示数据的小数位数（标度，0 ≤ D ≤ M），默认值为 0

举个例子：DECIMAL(8, 2) 类型可以表示的数据范围是多少？

DECIMAL(8, 2) 表示总长度 8 位，其中小数部分占 2 位，整数部分最多 6 位（可存储范围：-999999.99 到 999999.99）。

为什么DECIMAL存储浮点型数据不会有精度损失问题？
DECIMAL类型实际上是采用字符串的形式存储数值，而非二进制浮点数，因此完全避免精度丢失（如 0.1 不会像 FLOAT 那样存在近似值问题）。

字符串类型

char

char类型使用格式：char(L)

L的含义是什么？L的取值范围是？

char(L)表示固定长度字符串
L指明固定长度字符串的长度，单位为字符（不是字节），最大长度是255个字符，最小值为0

char(2)类型的变量，长度一定是2字节吗？
char(2)中的2表示字符数，而非字节数。存储时，实际占用字节数由字符编码决定。比如在 UTF8 编码下，一个英文字符占 1 字节，一个汉字占 3 字节。存 2 个英文字符时，占用 2 字节；存 2 个汉字时，占用 6 字节。

char(2)类型的变量，长度能否大于2字节？
从字节数角度，如果字符编码下单个字符字节数较大（如 UTF8 下的汉字），则可能大于 2 字节。

char(2)类型的变量，长度能否小于2字节？
不可以。char是固定长度字符串类型，char(2)会预留存储 2 个字符的空间。若存入字符不足 2 个，会用空格在右侧填充至 2 个字符的长度，所以实际占用空间不会小于定义长度。

char(2)类型的变量能否只存储一个字符？
不可以。char(2)定义了长度为 2 个字符，即便存入空字符串，也会占用相应空间来存储填充的空格，实际占用空间不会为 0。

众所周知，一个中文汉字通常需要用两到三个字节的01字符串来表示。那问题来了，char(2)类型的变量可以记录两个汉字组成的字符串吗？为什么？
答案是可以，因为L的单位是字符，不是字节

varchar

语法：varchar(L)
请问其中L表示什么？L的取值范围是多少？
L表示最大字符长度，语法规定L个字符的大小最多不超过65535个字节，最小可以为0。
注意：这并不意味着L的最大值就是65535，比如在UTF-8编码中，一个字符需要用3个字节来表示，那么L的最大值就只能取到22000左右。这样才能确保varchar(L)的大小不超过65535个字节

关于varchar(L)，L最大可以取多少是，和表所采用的编码方式密切相关：

如果采用ASCII码编码，那你一个字符就是用1字节来存，varchar长度可以指定为0到65535之间的值，但是需要有1 - 3 个字节用于记录数据大小，所以说此时有效字节数最大值L是65532
当我们的表的编码是utf8时，因为utf中，一个字符需要用3个字节来存，所以varchar(L)中L最大值只能是65532/3=21844
如果编码是gbk，因为gbk中，一个字符占用2字节，所以varchar(L)中L最大值是65532/2=32766

varchar(2)类型的变量，长度一定是2字节吗？长度能否大于2字节？能否小于2字节？varchar(2)类型的变量能否只存储一个字符？

长度是否一定是 2 字节？
不一定。varchar(2) 中的 2 表示最多存储 2 个字符，而非 2 字节。实际占用的字节数取决于字符集中字符的大小
长度能否大于 2 字节？
能。当使用多字节字符集时，即使存储 1 个字符也可能超过 2 字节（如 utf8mb4 编码的汉字占 3~4 字节），因此 varchar(2) 的实际字节数可能大于 2。
长度能否小于 2 字节？
能。例如：
存储 1 个 latin1 字符（如 ‘a’），仅占用 1 字节。
存储 1 个 utf8mb4 单字节字符（如 ‘!’），仅占用 1 字节。
能否只存储一个字符？
能。
varchar(2) 表示 “最多 2 个字符”，允许存储 0~2 个字符（0 个字符即空字符串）。例如存储 ‘a’ 或 ’ 中 ’ 都是合法的，此时实际字符数为 1，字节数根据字符集而定。

varchar与char的区别是什么？
char是定长字符串类型。定义char(n)后，无论实际存储的字符串多短，都会占用n个字符的空间，若不足n，会用空格在右侧填充。比如char(5)存"abc"，实际存储为"abc “（右侧两个空格）。
varchar是变长字符串类型。定义varchar(n)，只会占用实际字符串长度再加上 1 到 2 个字节（用于存储长度信息，若字符串长度小于等于 255，用 1 字节来存长度信息；若大于 255，用 2 字节来存）。如varchar(5)存"abc”，实际占用 3 + 1 = 4 字节。

如何选择定长(char)或变长(varchar)字符串？

如果某个变量的任意取值长度都一样，就使用定长（char），比如：身份证，手机号
如果某个变量的数据长度会随着取值的变化而变化,就使用变长(varchar), 比如：名字，地址，你要保证最长的能存的进去。

定长字符串与变长字符串，谁的效率高？

定长的磁盘空间比较浪费，但是效率高（用空间换时间）
变长的磁盘空间比较节省，但是效率低（用时间换空间）
定长字符串是直接开辟好对应的空间，让你往里面填数据
设计变长字符串的的目的是，想让你在不超过自定义范围的情况下，用多少，开辟多少

日期和时间类型

date

格式 'year-month-day'
例如：‘1997-01-01’
一个date类型数据占用的空间为三字节

datetime

时间日期格式'year-month-day Hour : minute : second'
表示范围从 1000 到 9999
例如：‘2008-08-08 12:01:01’
一个datetime 类型数据占用的空间为8字节
如何把datetime类型的变量设置成当前时间？ 直接调now()

timestamp

时间戳，从1970年开始的 yyyy-mm-dd HH:ii:ss
格式和 datetime 完全一致，占用四字节
这个类型的特点是，会随着数据的更新而自动更新
添加数据时，你不需要手动输入时间戳，默认缺省状态下，时间戳会自动补上当前时间
更新数据时，该记录的时间戳也会自动更新成当前时间

下面我们就举一个时间变量的使用示例
首先创建一个包含三种时间类型的表，模拟不同的时间记录需求：

CREATE TABLE time_demo (
  id INT PRIMARY KEY AUTO_INCREMENT,
  event_date DATE,                -- 仅记录日期（如生日、节日）
  event_datetime DATETIME,        -- 记录具体日期时间（如本地活动时间）
  event_timestamp TIMESTAMP       -- 记录带时区的时间（如全球事件时间）
);

然后插入测试数据

-- 插入数据（使用标准格式）
INSERT INTO time_demo (event_date, event_datetime, event_timestamp)
VALUES 
  -- 示例1：普通日期时间
  ('2023-10-05', '2023-10-05 14:30:00', '2023-10-05 14:30:00'),
  -- 示例2：跨天时间（DATETIME不受时区影响）
  ('2023-12-31', '2023-12-31 23:59:59', '2023-12-31 23:59:59'),
  -- 示例3：空值（表示未记录）
  (NULL, NULL, NULL);

枚举与集合

enum：枚举

格式：enum('选项1','选项2','选项3',...);
该设定只是提供了若干个选项的值，最终一个单元格中，实际只存储了其中一个值；而且出于效率考虑，这些值实际存储的是“数字”，因为这些选项的每个选项值依次对应如下数字：1,2,3,…最多65535个；当我们添加枚举值时，也可以添加对应的数字编号。

set：集合

使用格式：set('选项值1','选项值2','选项值3', ...);

set与enum的区别是什么？
enum是单选题，set是多选题。我们用下面这个例子来进一步理解
案例：请创建一个调查表votes，用于调查人的喜好，比如从登山，游泳，篮球，武术中去选择(可以多选)，性别（男，女）[单选]

   mysql> create table votes(
   -> username varchar(30),
   -> hobby set('登山','游泳','篮球','武术'),
   -> gender enum('男','女'));

定义好表结构之后，我们如何插入具体的数据呢？
举例：我们可以直接列举，也可以传数字来指代集合/枚举类型中的元素
insert into votes values ('赵六3', '游泳,登山', '男');
insert into votes values ('赵六3', '3', '1');

请问：insert into votes values ('赵六3', 3, 1);对应插入的记录中，赵六3的hobby=3为什么代表登山＋游泳，gender=1为什么代表男？

    gender=1对应的是男，gender=2对应的是女，所以赵六3的性别是男
    
    hobby=1对应0001，是登山
    hobby=2对应0010，是游泳
    hobby=3对应0011，是登山＋游泳
    hobby=4对应0100，是篮球
    hobby=5对应0101，是登山+篮球
    ...
    hobby=8对应1000，是武术
    ...
    hobby=15对应1111，是登山＋游泳 + 篮球 + 武术

集合类型使用数字标识每个爱好的时候，为什么要采用比特位位置来和set中的爱好对应起来？
因为set是多选题，假如说32个选项中，我们就可以用000…011来表示最后两个选项都被选了的情况

枚举类型的数字标识规则需要和集合类型一样吗？为什么？
不需要，枚举使用数字标识的时候，就是正常的数组下标，因为枚举是单选题

enum和set的特点
对数据的约束性更强，既可以避免很多错误信息，检索起来又快

find_in_set函数
我们通过下面的例子就很容易明白这个函数的作用
select * from votes where find_in_set('登山', hobby); 与select * from votes where hobby='登山'; 的区别
后者只能检索出爱好仅为登山的人，不能检索出爱好除了登山还有其他项目的人，但是前者可以