[MySQL]知识点

【版权声明】未经博主同意，谢绝转载！（请尊重原创，博主保留追究权）
https://www.cnblogs.com/cnb-yuchen/p/18032063
出自【进步*于辰的博客】

目录

• 1、知识点扩展
• 2、int族数据类型存储范围
  • 2.1 数据范围通式
  • 2.2 对定义类型时所指定长度的说明
• 3、char族数据类型的选择问题
  • 3.1 char与nchar的区别
  • 3.2 xchar和xvarchar的区别
  • 3.3 类型选择建议
• 4、数据类型
  • 4.1 text
  • 4.2 blob
  • 4.3 varchar、text、blob存储方面的区别
• 5、内置函数
  • 5.1 字符处理函数
  • 5.2 非空判断函数
• 最后

1、知识点扩展

1. 细节与使用经验。
2. 流程控制语句。
3. 存储过程。

2、int族数据类型存储范围

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

2.1 数据范围通式

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

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

1. tinyint，占1字节，数据范围：-128 ~ 127；
2. smallint，占2字节；
3. mediumint，占3字节；
4. int，占4字节；
5. bigint，占8字节；

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

详述可查阅博文《二进制相关概念、运算与应用》的第1.2项。

从这篇文章可知：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族数据类型数据存储范围取决于所占二进制的位数，与定义时指定的长度无关，如：tinyint(5)，并不是表示此字段可以存储5位数的整数，其中的5无意义。因此，数据范围仍是： -128 ~ 127。

此结论针对的是int族数据类型，若是其他族数据类型则不同。例如：decimal(5, 2)可存储3位数小数，保留2位小数；char(20)占20个字节，可存储20个字符或10个汉字。

3、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、数据类型

参考笔记二，P12.1~3。

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类型存储的二进制过大，会导致性能降低。

5、内置函数

5.1 字符处理函数

摘要	参数说明	返回值类型 / 返回值	说明
length()			获取字节数
char_length()			获取字符数
concat(a, b)			拼接
strcmp(a, b)			比较a、b的大小。若 a 大于、等于或小于 b 时，返回1/0/-1

MySQL字符处理函数（也属于单行函数）与Oracle单行函数有许多是相同的，只是参数列表可能略有不同（PS：需要大家自行测试了）。如果大家想进一步了解，可查阅博文《[Oracle]知识点》中的【内置函数】。

并且，如instr()，返回位置也是从1开始，而不是0。

5.2 非空判断函数

参考笔记二，P12.5。

摘要	参数说明	返回值类型 / 返回值	说明
isnull(a)			若 a 为 null，返回1，否则返回0
ifnull(a, b)			若 a 为 null，返回 b，否则返回 a
nullif()			同 Oracle 中的 nullif()
case...when...then			同 Oracle 中的case...
coalesce(a, b, ...)			返回第一个非 null 的值
if(a, b, c)			若 a 为 true，返回 b，否则返回 c

最后

本文中的例子是为了方便大家理解和阐述知识点而简单举出的，旨在阐明知识点，并不一定有实用性，仅是抛砖引玉。

本文持续更新中。。。