JinLinC个人在线文档库

Appearance

数据类型 ​

Mysql中的数据类型，实际上是列类型，或者又叫字段类型

字符串类型 ​

在数据库的所有类型中，字符串类型是使用最频繁的，数据库中字符串类型下有非常多种类的类型，如下表所示：

类型大小用途
CHAR0-255 字符定长字符串
VARCHAR0-65535 字节变长字符串
TINYBLOB0-255 字符不超过 255 个字符的二进制字符串
TINYTEXT0-255 字符短文本字符串
BLOB0-65535 字节二进制形式的长文本数据
TEXT0-65535 字节长文本数据
MEDIUMBLOB0-16777215 字节二进制形式的中等长度文本数据
MEDIUMTEXT0-16777215 字节中等长度文本数据
LONGBLOB0-4294967295 字节二进制形式的极大文本数据
LONGTEXT0-4294967295 字节极大文本数据

字符串通常来讲分为二进制（mp3，照片和视频都属于二进制类型，我们可以作为二进制存放到mysql中）和非二进制类型（文本内容等等）

mysql可以存放二进制文件，但是我们一般不存放二进制文件，这些图片视频二进制数据会使我们的数据表体积变得很多，造成读取时间变慢，所以说二进制文件一般不使用mysql进行存储的，这些视频照片一般存储在第三方的oss中，将这些较大的静态数据放到云上，加载速度会更快

char和varchar字符串类型是我们用的比较多的字符串类型

  • char 类型是定长类型，比如定义了 20 长度的char类型，只存一个字符也占用 20 个长度，char 好处是处理速度快，缺点是空间占用大（空间换取速度，检索这一类字符的时候速度非常快），把手机号、邮箱、密码等长度相对固定的设置为 char 类型是不错的选择，或者牵扯到排序的字符串数据也可以设置为char类型

  • varchar 类型与 char 相反，点用空间受内容影响，可以把文章标题、介绍等设置为varchar类型更合适，同时，在存放文本时，也可用使用TEXT数据类型进行替换，将TEXT列视为VARCHER列，但是要注意TEXT不能有默认值，大小为0-65535字节

  • CHAR类型是固定长度字符串，最大存放255个字符；VARCHAR类型是可变长度字符串，最大存放65535个字节，其中有3个字节被系统用于记录大小的，所以可用最大字节为65532，字符和字节是有区别的，在不同的编码下，存放的字符数量是不同的，如在utf8编码下，最大存放的字符为：(65535-3)/3=21844；如果表的编码是gbk，那么最大存放的字符为：(65535-3)/2=32766

  • 使用细节1：

    • char(4)：其中4表示字符数（最大是255），不管是中文还是字母都是放四个，按字符计算
    • varchar(4)：其中4也表示字符数，不管是字母还是中文都以定义好的编码来存放数据，但是这4个字符占据多少个字节，还是要取决于具体的字符集

    不管是中文还是英文字母，都是最多存放4个，是按照字符来存放的

  • 使用细节2：

    • char(4)是定长（固定的大小），即使插入aa内容，也会占用分配的4个字符空间
    • varchar(4)是变长（变化的大小），如果插入了aa，实际占用空间大小并不是4个字符，而是按照实际占用空间来分配（其中，varchar本身还需占用1-3个字节来记录存放的内容长度）

字符集 ​

字符集决定了我们在字段中能存储那类的字符，不符合字符集的文字可能会在存储的时候出现乱码

查看系统都支持哪些字符集：SHOW CHARACTER SET;

当使用到字符串，就一定会使用到字符集，在现实当中的字符集就是字典，字符集的种类是非常多的，有些字符集是比较全面的，包含了大多数语言，如UTF8字符集，在创建数据库的时候可以进行设置字符集，在建表的时候也可以设置字符集，表中的字段也有字符集，最终的数据是存储在字段中的，所以字符集以字段的为准

如果表没有设置字符集，那么表的字符集就会继承于数据库的

如果字段没有设置字符集，就会继承于表的字符集

但是在后续修改上层的字符集，不会使下层已有内容的字符集发生改变，但是对于新建的内容，字符集就会继承修改后的新字符集

校对规则 ​

数据库的校对规则有很多， 比如对于字符串的校对规则有：区分大小写

在默认的情况下，通过字符串进行比较查询（甚至排序）是不区分大小写的，如果我们想要让其进行区分大小写，我们就需要更改校对规则进行实现，一般校对规则是跟在字符集后面的，如utf8_bin，其中utf8是字符集；bin是区分字符串大小写的校对规则

总之：字符串校对规则是影响字符串比较和排序的一种算法

数值类型 ​

数值类型就是用于存储我们的数字的，常见的数值类型有整型、小数类型

整型 ​

整型有以下的几个类型：

数据类型范围（有符号）（默认情况）范围（无符号）
tinyint(n)1 个字节的范围(-128~127)(0，255)
smallint(n)2 个字节的范围(-32768~32767)(0，65535)
mediumint(n)3 个字节的范围(-8388608~8388607)(0，16777215)
int(n)4 个字节的范围(-2147483648~2147483647)(0，4294967295)
bigint(n)8 个字节的范围(+-9.22*10^18)(0，18446744073709551615)

其中n是设置我们的显示位数

越往下范围越大，所占用的磁盘空间就越大，实际中选则合适大小的即可，如果选则的类型占用磁盘过大，数据在检索的时候时间就越长，效率就低

sql
-- 为class数据表中添加status字段，字段是数值类型的，具体为TINYINT
alter table class add status TINYINT;

-- 有时候我们不想让数值类型有符号，让数值类型只能存储正数的类型
alter table class add status TINYINT UNSIGNED;
n和ZEROFILL ​

数值类型后面跟着的(n)表示设置我们数值的显示位数，并不是说只能输入n个数字字符

sql
alter table class add state int(5) ZEROFILL;

int(5)只控制显示长度，如果添加了前导零填充这个关键字ZEROFILL后，如果长度不够5位，会在数值前面进行补0，使其长度控制在5位，但是前导零在数据库图形化界面中有时难以显示，在命令行终端中可以正常显示

位类型 ​

位类型：bit(M)类型，具体细节：

  • bit字段显示时，按照位的方式显示（二进制的）
  • 查询时，仍然可以使用添加的数组
  • 如果一个值只有0，1，那么可以考虑使用bit(1)，可以节省空间
  • 位类型中的M指定位数，默认值为1，范围为1-64，如果指定为64位，就相当于8个字节，就等价于bigint()类型，但是数据的显示还是按照位的方式进行显示
  • 位类型的使用情况不多，了解即可
sql
CREATE TABLE t (num BIT(8));  -- 给定8位
INSERT INTO t VALUES(3);  -- 插入的值超过255，会抛出异常 255对应的二进制是11111111
SELECT * FROM t;  
-- 显示的是 11
-- 但是查询的时候，仍然可以根据数值来查询
SELECT * FROM t WHERE num = 3;

浮点数和定点数类型 ​

浮点数和定点数可以理解数值类型带小数的数据类型

浮点数数据类型：

类型精度
FLOAT单精度4个字节，7位，能保证6位不损耗
DOUBLE双精度8个字节，16位，能保证15位不损耗
DECIMALDECIMAL(M,D) ，M表示总位数（包括小数点前和后），D表示小数的位数

选择的时候根据需要进行选择，在够用的情况下选择最小的即可

FLOAT类型和DOUBLE的浮点数有精度的误差：

  • FLOAT：一共7位（包括小数点的前和后），意味着最多有7位有效数字，在7位以上会有精度的损耗（数值就会与我们添加的不一致，可能给你四舍五入了），但绝对能保证6位，即FLOAT的精度为6-7位有效数字
  • DOUBLE：一共16位，double数据类型的精度为15-16位
  • 对于货币之类的数据，推荐使用DECIMAL数据类型，通过设定M和D可以精准的显示，不损耗，如果D为0，则值没有小数点或分数部分，M最大值为65，D最大值为30，如果D被省略，默认值为0，如果M被省略，默认值为10
sql
alter table class add a FLOAT(10,2);
update class set a = 12345678.66 where id=1;   -- 出现损耗
alter table class add b DECIMAL(10,2);
update class set b = 12345678.66 where id=1;   -- 不会出现损耗

枚举类型 ​

对于有一些数据，有多个值，但是每次我们在设置的时候，只设置一个值，类似单选框的原理，这种数据类型，我们就可以使用EMUM枚举类型，如对于性别类型的数据，通常有：男、女类型，这种情况，就可以设置为枚举类型，我们将原先班级表的性别字段的类型修改为枚举类型：

sql
ALTER TABLE stu MODIFY sex ENUM('男','女') DEFAULT NULL;

系统在默认的情况下会将1当作男；将2当作女，所有插入一条数据时，可以有以下的两种方式：

sql
INSERT INTO stu (sname, class_id, sex) VALUE('小明',1,'男');
INSERT INTO stu (sname, class_id, sex) VALUE('小明',1,1);

-- 查找所有的女同学
SELECT * from stu WHERE sex = 2;

SET多选类型 ​

对应枚举类型是一个单选的类型，SET类似是一个多选的类型，字段内容可以选择SET类型设定种的一个或多个，对应SET中多选内容的个数，在mysql中规定了最多添加64个

sql
ALTER TABLE stu ADD flag SET('推荐','热门','图文');
-- 添加数据
INSERT INTI stu (sname, flag) VALUES('小明','推荐,图文')
-- 查找包含某个选项的数据条目
SELECT * FROM stu WHERE FIND_IN_SET('推荐', flag);
-- 也可以使用like进行模糊匹配的方式进行查找
SELECT * FROM stu WHERE flag LIKE '%推荐%';

二进制模糊匹配SET类型 ​

对于SET('推荐','热门','图文')的SET类型，其第一个内容推荐，其二进制表示为0001（2的0次方：1）；对于第二个内容热门，其二进制表示为0010（2的1次方：2）；第三个内容图文，其二进制表示为0100（2的2次方：4）；如果有第四个，其二进制表示为1000（2的3次方：8）

我们可以通过二进制的方式进行SET类型的模糊匹配：

sql
-- 匹配flag字段内容包含推荐的数据条目
SELECT * FROM stu WHERE flag & 1;

-- 匹配flag字段内容包含推荐或者热门的数据条目
SELECT * FROM stu WHERE flag & 3;

-- 匹配flag字段内容包含热门或者图文的数据条目
SELECT * FROM stu WHERE flag & 6;

Released under the MIT License.

Copyright © 2023-present JinLinC