【MySQL】知识点

扩展

1. 笔记。
2. 流程控制语句。
3. 存储过程。
4. 内置函数。

一、约束

由于MySQL约束与Oracle几乎完全相同，故在此不作赘述，可阅读 #【Oracle】外键、唯一键、check、not null 学习。

唯一不同：8.0以下版本会解析check约束、但忽略（即不生效）。

二、int族数据类型

参考笔记二，P62.1、P63.7。

2.1 数据范围通式

无论何种类型，底层存储方式都是二进制，而数据存储范围则取决于表示数据所需二进制的位数。也包括int族数据类型。

int族数据类型的所有类型如下：

1. tinyint，占1个字节，默认长度为4，数据范围：-128 ~ 127；
2. smallint，占2个字节，默认长度为6；
3. mediumint，占3个字节，默认长度为9；
4. int，占4个字节，默认长度为11；
5. bigint，占8个字节，默认长度为20；

为何tinyint 类型的数据存储范围是-128 ~ 127?

可阅读 # 二进制相关概念、运算与应用 的第二栏学习。

从这篇文章可知：8位二进制的表示范围就是-128 ~ 127。

规则如此，在MySQL中会不会有所变动？

做如下测试进行验证。

上图数据表的字段syllabus_id的数据类型是tinyint，图一存储128报错：超出值范围；图二存储127，可提交。验证通过。

结论：

综上可推断出， int族数据类型的数据存储范围通式为：

-2{8n-1} ~ 2{8n-1} - 1（n是所占字节数）

示例：

1. tinyint，占1字节。数据存储范围：-2(8*1-1) ~ 2{8*1-1}- 1 → -128 ~ 127；
2. smallint，占2字节。数据存储范围：-2{8*2-1} ~ 2{8*2-1} - 1 → -32768 ~ 32767。

2.2 长度

int族数据类型数据存储范围取决于所占二进制的位数，与定义时指定的长度无关。

长度的作用是什么？ 统一长度显示。也就是这样：

这是DDL：

`填充列` tinyint(3) unsigned zerofill DEFAULT NULL

因此，得出两个结论：

1. 必须使用zerofill修饰，长度才能生效；
2. 使用zerofill修饰后，会自动加上unsigned（无符号）。

三、char族数据类型的选择问题

参考笔记二，P62.2/4、P63.5。

在下文中，大家会看到xchar/nxchar这样的数据类型，这是我自定义的，目的是便于阐述，可能会提升阅读难度，请保持耐心。

自定义规范：

• 形式一：x 代指 n。如：xchar → char 和 nchar；xvarchar → varchar 和 nvarchar。

• 形式二：x 代指 var。如：xchar → char 和 varchar；nxchar → nchar 和 nvarchar。

形式一优先级高于形式二。

3.1 char与nchar的区别

区别：存储方式不同。

1. char以字节存储，一个字符占1个字节，一个汉字占2个字节。如：char(20)，可存储20个字符或10个汉字，共占20个字节；
2. nchar以字符存储，一个字符或汉字都占2个字节。如：nchar(20)，可存储20个字符或汉字，共占40个字节。

3.2 xchar和xvarchar的区别

区别：是否定长。

举个栗子：

1. char(20)，表示最多可存储20个字符或10个汉字，固定占20字节空间。假设仅存储了4个字符，则后16个字节为空；
2. varchar(20)，表示最多可存储20字节数据。假设已存储了4个字符，则共占4字节空间。

同理：

1. nchar(20)，表示最多可存储20个字符或汉字，固定占40字节空间。假设仅存储了4个字符，则后32个字节为空；
2. nvarchar(20)，表示最多可存储40字节数据。假设已存储了4个字符，则共占8字节空间。

因为我的电脑默认编码是GBK，因此，上文是基于“一个汉字占2个字节”的标椎进行阐述的。

补充说明：在实际应用中，一个汉字所占字节数取决于“编码格式”。例如，若编码格式是GBK/ISO-8859-1，则一个汉字占2个字节，此时char(20)最多可存储10个汉字；而若编码是UTF-8，则一个汉字占3个字节，此时char(20)最多可存储6个汉字和2个字符。

扩展：创建数据库时编码普遍设置为UTF-8。

3.3 类型选择建议

1. xchar比 xvarchar占据空间大，但执行速度快。因为 xchar 的索引效率高，不过，在存储数据时，建议将数据前后多余的空格去除（trim()）；
2. 若所存储的数据集中包含字母、数字、汉字或其他语言字符，则nxchar比xchar合适。因为 nxchar 使用Unicode统一编码，能降低乱码的几率。（因为一般情况下，数据库的编码兼容性强于程序编码。因此，数据存储时不会乱码，但读取数据时可能会）
3. 当所存储数据长度较短或数据集的长度近似时，使用xchar。举个例：身份证号，18或19位，选择char(21)或nchar(21)；（为什么是21，大家可参考第5点）
   注：有关数据长短，与所占字节数无关，故这里的 x 代指 n。
4. xchar所占空间是一次性分配的，而xvarchar是根据实际长度分配的。若所存储的数据在修改后相较于修改前短很多，则容易产生大量碎片，需要额外的导入和导出工作来清除碎片。因此，选择哪种char类型需要预测后续数据的变动情况；
5. 同样是存储90个字符，varchar(100)与varchar(200)是不同的。若此字段涉及到文件排序或基于磁盘的临时表，使用varchar(200)会给内存作业造成影响。因此，在定义长度时，需评估合适长度。一般情况下，定义的长度为所存储数据的最大长度的110%左右最佳。

四、数据类型

4.1 text

text类型与 Oracle 中的clob类型相似，主要用于存储长文本。此类型族存储的唯一限制是存储大小。如：tinytext的存储大小是256B，text是64KB，mediumtext是16MB，longtext是4GB。

4.2 blob

MySQL 和 Oracle 数据库中都包含 blob 类型，此类型以二进制的形式进行存储，主要用于存储二进制文件，如：视频、图片，此类型族的存储大小同text类型。

4.3 varchar、text、blob存储方面的区别

varchar类型的存储大小为实际存储的字符个数+1，超出范围无法存储，故需要指定长度；text和blob类型的存储大小固定，超出范围部分截断，且若blob类型存储的二进制过大，会导致性能降低。

本文持续更新中。。。