[MySQL#3] 数据约束 | 数值类 | varchar | timestamp | enum vs set

接上篇文章的一个小提醒：数据库很底层了，定义了就尽量不要改啦，否则上层也要变动和调整

本文思维导图如下：

1. 引入

数据类型分类
在MySQL中，每种数据类型都有其特定的用途，类似于我们在学习C/C++等语言时遇到的情形。以下是一些MySQL中常见的数据类型：

2. 数值类型

数值类型可以分为以下几类：位类型、布尔类型、整数类型、浮点数类型。

以下主要以整型为例进行说明：

• 整数类型包括tinyint、smallint等，它们根据名称不同，所占的字节数也不同，这些都是MySQL预先定义好的。
• 默认情况下，如果我们只写tinyint、smallint等，它们是有符号类型的。其取值范围与C/C++语言中的对应整数范围相同。
• 如果后面加上unsigned，则表示是无符号类型。

以tinyint类型为例，其他整数类型的使用方法与此类似。

• 创建表示例：

create table if not exists t1(
    num tinyint
);

在这里，我们看到tinyint后面有一个数字4，这个含义我们将在讨论约束时详细说明，现在先不考虑。

• 插入数据时，tinyint默认是有符号的，其取值范围是-128~127。超出这个范围的数据将无法插入。
• 在MySQL中，整型可以指定为有符号或无符号，默认是有符号的。可以通过UNSIGNED关键字指定字段为无符号类型。

创建无符号tinyint类型的表：

create table t2(
    num tinyint unsigned
);

这样就创建了一个num字段为无符号类型的表。当插入超出取值范围的数据时，MySQL会拦截，不允许插入。

• 注意：尽量不使用unsigned，对于int类型可能存放不下的数据，int unsigned同样可能存放不下。
• 不如在设计时将int类型提升为bigint类型。

⭕ 约束

• 如果我们向mysql特定的类型中插入不合法的数据，MySQL一般都是直接拦截我们，不让我们做越界的操作!
• MySQL必须保证插入数据的完整性，一旦截断，那在MySQL中有些是成功插入的有些是截断后插入的，那作为用户来讲，他还能信任MySQL中插入的数据吗？
• 反过来，如果我们已经有数据被成功插入到mysql中，一定是插入的时候合法的！

所以mysql中，一般而言，数据类型本身也是一种：约束！

• 约束 —> 倒逼程序员尽可能进行正确插入。所以约束，约束的是使用者。另外如果你不是一个很好的使用者，mysql也能保证数据插入的合法性。
• 这样的话就能保证数据库中的数据是可预期，完整的。
• 以tinyint为例，它是有符号的，所以可预期的是未来插入的值范围一定在-128~127的。并且数据是完整的没有发生过截断或者隐式类型转化。

思考：

• 我们还可以发现一个细节，mysql表中建立属性列， 列名称在前 类型在后 如 num tinyint
• 如果反过来就是C/C++那一套形式。

tinyint我们说完了，其他类型自己推导。

2.2 bit类型

基本语法：

• bit [ (M) ]：位字段类型。M表示值比特位的位数，范围从1到64。如果M被忽略，默认为1。
• 创建表示例：

create table t3(
    id int,
    online bit(1)
);

使用一个比特位来表示用户是否在线。由于只有一个比特位，只能插入0或1。

• 查询位类型数据时，通常按照ASCLL码值显示。例如，插入的0和1在ASCLL码中是不可显示的，因此查询时可能看不到内容。可以使用hex函数以16进制形式显示。

2.3 浮点数类型

2.3.1 float

• 语法：float [ (m, d) ] [ unsigned ]：M指定显示长度，d指定小数位数，占用空间4个字节。
• 创建表示例：

create table if not exists t4(
    id int,
    salary float(4,2)
);

• float(4,2)表示的范围是-99.99 ~ 99.99。
• 插入数据时，如果精度不够会补0
• 如果精度超过会四舍五入，但超过总位数范围的数据也无法插入。

无符号float类型的表：

create table t5(
    id int,
    salary float(4,2) unsigned
);

无符号float(4,2)的取值范围是0 ~ 99.99，插入负数将失败。

浮点数在存储时可能会有精度损失。

2.3.2 decimal

• 语法：decimal(m, d) [unsigned]：定点数m指定长度，d表示小数点的位数。
• decimal类型的使用与float类似，但它可以有效地避免精度损失。
• float的默认精度大约是7位，而decimal可以精确到65位整数和30位小数。如果d被省略，默认为0。如果m被省略，默认是10。
• 对于精度要求高的场景，应使用decimal类型。

• 可以对比发现虽然float精度设为8但是实际上存的时候已经和插入数据不一样了。
• float类型往往在精度过大或者整体数字过大时会自作聪明帮我们做一些优化策略。
• 但是decimal不会，它能够完完全全让数据怎么存就怎么取。

3. 字符串类型

3.1 char

• 语法：char(L)：固定长度字符串，L是可以存储的长度，单位为字符，最大长度值可以为255。
• 示例：name char(2)，表示最多存两个字符。
• 插入数据时，如果超过定义的长度，则不允许插入。

❓为什么一个汉字可以插，两个汉字也可以插，但是插入三个中文的时候就不让我插了？

• MySQL中的字符和C/C++中的字符概念是不一样的，以前语言上的字符，一个字符对应一个字节，
• 而在MySQL中的字符真的代表一种符号，要么是1234、要么abcd、要么就是中文汉字，一个汉字就是一个字符。
• char(2) 表示可以存放两个字符，可以是字母或汉字，但是不能超过2个， 最多只能是255，超过不让你插，连表都不让你建！

🧫 总结：

• char 后面括号里面填的就是固定长度字符串的上限，一旦定义好之后该给你多少空间就给你申请好
• 你用多少是你的事 ，我给你多少由L决定。
• 另外如果插入字符超过L那就不给你插入

3.2 varchar

• 语法：varchar(L)：可变长度字符串，L表示字符长度，最大长度65535个字节。
• 示例：varchar(6)，表示最多可以插入6个字符。
• 与char的区别：varchar是变长字符串，实际使用空间根据字符串长度动态分配，而char是固定长度。
• **关于varchar(len)**，len的值与表的编码密切相关。在utf8编码中，varchar(n)的参数n最大值为21844。

MySQL在存储字符类型的时候，认为utf8编码，单个字符是三个字节，

换句话说varchar保存最大长度是65535个字节，但字符数是21845！

• 总结：varchar类型根据实际字符个数动态分配空间，最大字节数为65535，但需要预留1-3个字节用于记录实际字符长度。所以我们上面说的是 21844

计算：

实际我们算出来是21844，这里是21845主要原因是因为它也到那三个字节计数数据的也带上来了。所以是21845，但实际只有21844，这里21844成功，是因为MySQL这一行都给你了。

总结：

• varchar有自己长度上限，在上限范围内用多少给多少
• 做法就是在申请的众多字节中有1~3个字节用来表示有效字符长度，通过这种方式来确定实际字符是多少来实现变长。
• varchar最大字节数65535，但65535一定要包含1-3个记录数据长度的字段。在utf8保存在大字符个数是21844。
• 如果这个表很干净一行内没用其他字段。varchar能到21844，但是有其他字段那这个值就会变小。

3.3 char和varchar比较

• 共同点：都能保存字符串，都有上限。
• 区别：char是定长的，varchar是变长的。char一次分配固定空间，varchar根据实际使用分配空间。

如何选择：

• 如果数据确定长度都一样，就使用定长（char），比如：身份证，手机号，md5
• 如果数据长度有变化,就使用**变长(varchar), **比如：名字，地址，但是你要保证最长的能存的进去。
• 定长的磁盘空间比较浪费，但是效率高。
• 变长的磁盘空间比较节省，但是效率低。
• 定长的意义是，直接开辟好对应的空间
• 变长的意义是，在不超过自定义范围的情况下，用多少，开辟多少。

4. 日期和时间类型

常用的日期类型：

• **date**：日期 'yyyy-mm-dd'，占用三字节。
• datetime：日期时间 'yyyy-mm-dd HH:ii:ss'，表示范围从1000到9999，占用八字节。--手动设置
• **timestamp**：时间戳，从1970年开始的 'yyyy-mm-dd HH:ii:ss' 格式，占用四字节。--自动更新

当我们把表中t1更新了，没用改t3，但这个t3也会被自动跟新

timestamp会自动更新，适用于记录数据的最后修改时间。

要结合具体的场景选择时间:

• **imestamp **时间戳有什么用呢？ 比如说你在博客上面给别人评论的时候，用到的就是这。只要对评论更改或者插入，这个时间戳就会被更新到最新时间。
• datetime就是要存储一个固定时间，如记录你入职的时间。

5.enum和set

语法：

• enum：枚举，“单选”类型；enum('选项1','选项2','选项3',...)。
• set：集合，“多选”类型；set('选项值1','选项值2','选项值3', ...).

关于enum和set更多细节的东西，我们建一个问卷调查表，把enum和set用起来在说。

• enum枚举类型给我提供约束，换句话说插入时只能插入枚举的类型，不允许插入除该枚举类型外其他任何字符。
• 当enum枚举类型在插入的时候，可以直接写这个枚举限定的常量，也可以写对应常量的下标
• 注意这个数字下标从1开始，分别代表第一个枚举值,第二个枚举值等。有几个就只能到几，超过不行

数据库SET类型插入规则笔记

1.** 插入规则**

set

以数字形式插入绝对不是下标！

• 目前这里有5个爱好，在集合中我们把它想象成5个比特位 00000，这里我们从右到左表示从低比特位到高比特位。
• 插入1的时候00000 -> 00001，这个比特位的位置代表是代码这个爱好，这个比特位的内容为0为1代表也没有这个爱好，1代表有代码这个爱好，0就代表没有。这就是我们刚才插入1的时候，显示的是代码的爱好
• 插入 3 的时候00000 -> 00011，表示有前两个爱好
• 比如插入的是700000 -> 00111，表示当前表中低三个爱好都有，所以会显示出前三个爱好

2. SET类型的含义

因此，当我们在向一个

set

集合中插入的时候，这个数字代表的是位图。集合中有几个类型就有几个比特位，比特位从低向高依次代表

set

类型中从左向右。比特位的位置代表是那个类型，比特位为0为1代表是否是有这个类型。

3. ⭕ENUM与SET的区别

• enum和set在插入时提供特定的选项，enum为单选，set为多选。
• enum和set的查找可以通过常量或位图进行筛选。
• enum中插入数字代表的是下标，set中插入数字代表的是位图。

集合查询使用find_ in_ set函数：

在数据库查询中，对于集合类型的字段，可以使用

find_in_set

函数来进行查询。此函数用于确定一个子串是否存在于一个由逗号分隔的字符串列表中。

**

find_in_set(sub, str_list)

：**

• 如果sub在str_list中，则返回子串的位置下标；
• 如果不在，则返回0；
• str_list是由逗号分隔的字符串。

SELECT

语句可以执行表达式，同样，函数也可以执行表达式。

返回值解释

• 当子串存在于集合中时，返回的下标是从1开始的；
• 子串不存在于集合中时，返回0；
• 因此，非0值表示真（即存在），0表示假（即不存在）。

功能说明

• find_in_set只能用来检查一个元素是否在集合中。

• 如果存在，则返回对应的下标；

• 查找过程是判断元素是否在集合中，而非直接判断相等性。

应用实例

例如，在查找用户的爱好时，如果是“swim”，则可以用

find_in_set

来进行严格匹配；

复合条件查询示例

• 如果需要查询爱好的集合中同时包含“代码”和“羽毛球”的记录，可以通过组合多个find_in_set函数并使用逻辑运算符AND来实现；
• 示例SQL语句如下：

这样，我们就可以通过

WHERE

子句中的条件判断，结合

find_in_set

函数的结果，来筛选出符合多个条件的记录。这里的

AND

运算符起到了逻辑与的作用，确保两个条件同时满足。