【⑭MySQL | 数据类型(二)】字符串 | 二进制类型

痛定思痛。 2023-10-14 23:56 30阅读 0赞

## 前言 ##

> **✨欢迎来到[小K][K]的[MySQL专栏][MySQL]，本节将为大家带来MySQL字符串 | 二进制类型类型的分享**✨

#### 目录 ####

*  前言
 *   *   *  5 字符串类型
         *  6 二进制类型
         *  总结

--------------------

![在这里插入图片描述][c8bf44e46cd54d668094c64665df59be.png]

#### 5 字符串类型 ####

字符串类型用来存储字符串数据，还可以存储图片和声音的二进制数据。字符串可以区分或者不区分大小写的串比较，还可以进行正则表达式的匹配查找。

下表中列出了 MySQL 中的字符串数据类型，括号中的`M`表示可以为其指定长度。

<table> 
 <thead> 
  <tr> 
   <th>类型名称</th> 
   <th>说明</th> 
   <th>长度范围</th> 
   <th>占用的存储空间</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>CHAR(M)</td> 
   <td>固定长度</td> 
   <td>0&lt;=M&lt;=255</td> 
   <td>M 个字节</td> 
  </tr> 
  <tr> 
   <td>VARCHAR(M)</td> 
   <td>变长字符串</td> 
   <td>0&lt;=M&lt;=65535</td> 
   <td>M+1个字节</td> 
  </tr> 
  <tr> 
   <td>TINYTEXT</td> 
   <td>非常小字符串</td> 
   <td>0&lt;=L&lt;=255</td> 
   <td>L+1字节</td> 
  </tr> 
  <tr> 
   <td>TEXT</td> 
   <td>小的字符串</td> 
   <td>0&lt;=L&lt;=65535</td> 
   <td>L+2字节</td> 
  </tr> 
  <tr> 
   <td>MEDIUMTEXT</td> 
   <td>中等大小的字符串</td> 
   <td>0&lt;=L&lt;=16777215</td> 
   <td>L+3字节</td> 
  </tr> 
  <tr> 
   <td>LONGTEXT</td> 
   <td>大的字符串</td> 
   <td>0&lt;=L&lt;=4294967295</td> 
   <td>L+4字节</td> 
  </tr> 
  <tr> 
   <td>ENUM</td> 
   <td>枚举类型，只能有一个枚举字符串值</td> 
   <td>0&lt;=L&lt;=65535</td> 
   <td>1或2个字节，取决于枚举值的数目 (最大值为65535)</td> 
  </tr> 
  <tr> 
   <td>SET</td> 
   <td>字符串集合，字符串对象可以有零个或 多个SET成员</td> 
   <td>0&lt;=L&lt;=64</td> 
   <td>1、2、3、4或8个字节，取决于集合 成员的数量（最多64个成员）</td> 
  </tr> 
 </tbody> 
</table>

CHAR 和 VARCHAR 类型

CHAR 和 VARCHAR 类型都可以存储比较短的字符串。

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>特点</th> 
   <th>长度</th> 
   <th>长度范围</th> 
   <th>占用存储空间</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>CHAR(M)</td> 
   <td>固定长度</td> 
   <td>M</td> 
   <td>0&lt;=M&lt;=255</td> 
   <td>M个字节</td> 
  </tr> 
  <tr> 
   <td>VARCHAR(M)</td> 
   <td>可变长度</td> 
   <td>M</td> 
   <td>0&lt;=M&lt;=65535</td> 
   <td>(实际长度+1/2)个字节</td> 
  </tr> 
 </tbody> 
</table>

**CHAR类型：**

*  CHAR(M) 为固定长度字符串，在定义时指定字符串长度。如果不指定，则默认为1个字符。
 *  如果保存时，数据的实际长度比CHAR类型声明的长度小，则会在`右侧填充空格`以达到指定的长度。当检索 CHAR 值时，尾部的空格将被删除。
 *  定义CHAR类型字段时，申明的字段长度即为CHAR类型字段所占的存储空间的字节数。

CREATE TABLE test_char
    (
    	f1 CHAR,
    	f2 CHAR(5)
    );
    
    DESC test_char;
    
    INSERT INTO test_char(f1) VALUES('h');
    #Data too long for column 'f1' at row 1
    INSERT INTO test_char(f1) VALUES('he');
    INSERT INTO test_char(f1) VALUES('你');
    
    INSERT INTO test_char(f2) VALUES('C语言');
    #Data too long for column 'f2' at row 1
    INSERT INTO test_char(f2) VALUES('C语言Plus');
    
    #测试在右侧填充的空格，会在查询的时候去掉的情况
    SELECT CONCAT(f2,'***') FROM test_char;
    #注意：自己插入的空格也会被去掉
    INSERT INTO test_char(f2) VALUES('fk  ');
    
    SELECT * FROM test_char;

**VARCHAR类型:**

*  VARCHAR(M) 定义时，必须指定长度M，否则会报错。
 *  VARCHAR(M)指的是能存储M个字符，而实际占用的空间为字符串的实际长度加 1或者2。
 *  VARCHAR 在值保存和检索时尾部的空格仍保留。

#错误，没有指定长度
    CREATE TABLE test_varchar
    (
    	name VARCHAR
    );
    
    CREATE TABLE test_varchar
    (
    	name VARCHAR(5)
    );
    
    INSERT INTO test_varchar VALUES('星星'),('太阳月亮');
    #Data too long forcolumn 'NAME' at row 1
    INSERT INTO test_varchar VALUES('星星太阳月亮');

TEXT 类型

TEXT用来保存文本类型的字符串，如文章内容、评论等。当保存或查询 TEXT 列的值时，不删除尾部空格。

TEXT 类型分为 4 种：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。不同的 TEXT 类型的存储空间和数据长度不同。

在向TEXT类型的字段插入数据时，系统自动按照实际长度存储，不需要预先定义长度。

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>说明</th> 
   <th>大小(字节)</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>TINYTEXT</td> 
   <td>小文本</td> 
   <td>0&lt;=L&lt;=255</td> 
  </tr> 
  <tr> 
   <td>TEXT</td> 
   <td>普通文本</td> 
   <td>0&lt;=L&lt;=65535</td> 
  </tr> 
  <tr> 
   <td>MEDIUMTEXT</td> 
   <td>中等大小文本</td> 
   <td>0&lt;=L&lt;=16777215</td> 
  </tr> 
  <tr> 
   <td>LONGTEXT</td> 
   <td>长文本</td> 
   <td>0&lt;=L&lt;=4294967295 (相当于4GB字符)</td> 
  </tr> 
 </tbody> 
</table>

TEXT文本类型，可以存储比较大的文本段，搜索速度稍慢，因此如果不是特别大的内容，建议使用CHAR，VARCHAR来替代。还有TEXT类型不用加默认值，加了也没用。

ENUM 类型

ENUM 类型也叫作枚举类型，ENUM类型的取值范围需要在定义字段是进行指定。设置字段值时，ENUM类型只允许从成员中选取单个值，不能一次选取多个值。如果创建的成员中有空格，尾部的空格将自动被删除。

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>长度范围</th> 
   <th>占用存储空间</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>ENUM</td> 
   <td>1&lt;=L&lt;=65535</td> 
   <td>1或2个字节</td> 
  </tr> 
 </tbody> 
</table>

*  ENUM 值在内部用整数表示，每个枚举值均有一个索引值；
 *  列表值所允许的成员值从 1 开始编号，MySQL 存储的就是这个索引编号，枚举最多可以有 65535 个元素。

ENUM 值依照列索引顺序排列，并且空字符串排在非空字符串前，NULL 值排在其他所有枚举值前。

> 提示：ENUM 列总有一个默认值。如果将 ENUM 列声明为 NULL，NULL 值则为该列的一个有效值，并且默认值为 NULL。如果 ENUM 列被声明为 NOT NULL，其默认值为允许的值列表的第 1 个元素。

*  查看enum字段的所有值

SELECT
    column_type
    FROM
    information_schema. COLUMNS
    WHERE
    #TABLE_SCHEMA = "test" AND
    DATA_TYPE = 'enum'
    AND table_name="test_enum"
    AND column_name="ch";

*  测试

CREATE TABLE test_enum
    (
    	gender ENUM('男','女','unknown')
    );
    
    INSERT INTO test_enum VALUES('男');
    #Data truncated for column 'ch' at row 1
    INSERT INTO test_enum VALUES('中');
    #可以使用索引来插入枚举元素(注意：索引从1开始)
    INSERT INTO test_enum VALUES(1),('2');
    
    #当enum字段没有申明为NOT NULL时,插入NULL也是合法的
    INSERT INTO test_enum VALUES(NULL);
    
    SELECT * FROM test_enum;

SET 类型

SET 是一个字符串的对象，可以有零或多个值，SET 列最多可以有 64 个成员，值为表创建时规定的一列值。

<table> 
 <thead> 
  <tr> 
   <th>成员个数范围</th> 
   <th>占用的存储空间</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1&lt;=L&lt;=8</td> 
   <td>1个字节</td> 
  </tr> 
  <tr> 
   <td>9&lt;=L&lt;=16</td> 
   <td>2个字节</td> 
  </tr> 
  <tr> 
   <td>17&lt;=L&lt;=24</td> 
   <td>3个字节</td> 
  </tr> 
  <tr> 
   <td>25&lt;=L&lt;=32</td> 
   <td>4个字节</td> 
  </tr> 
  <tr> 
   <td>33&lt;=L&lt;=64</td> 
   <td>8个字节</td> 
  </tr> 
 </tbody> 
</table>

*  与 ENUM 类型相同，SET 值在内部用整数表示，列表中每个值都有一个索引编号。当创建表时，SET 成员值的尾部空格将自动删除。
 *  但与 ENUM 类型不同的是，ENUM 类型的字段只能从定义的列值中选择一个值插入，而 SET 类型的列可从定义的列值中选择多个字符的联合。

> 提示：**如果插入 SET 字段中的列值有重复，则 MySQL 自动删除重复的值；插入 SET 字段的值的顺序并不重要，MySQL 会在存入数据库时，按照定义的顺序显示；**

**测试**

CREATE TABLE test_set
    (
    	hobby SET('吃饭','睡觉','写Bug')
    );
    
    INSERT INTO test_set VALUES('吃饭');
    
    INSERT INTO test_set VALUES('吃饭,睡觉');
    
    #重复插入时，会自动删除重复数据
    INSERT INTO test_set VALUES('吃饭,睡觉,吃饭');
    #插入的值在set中不存在时，会报错
    INSERT INTO test_set VALUES('吃饭,睡觉,花钱');
    
    SELECT * FROM test_set;

#### 6 二进制类型 ####

MySQL中的二进制字符串类型主要存储一些二进制数据，比如可以存储图片、音频、视频等二进制数据。

包括 BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。

下表中列出了 MySQL 中的二进制数据类型，括号中的`M`表示可以为其指定长度。

<table> 
 <thead> 
  <tr> 
   <th>类型名称</th> 
   <th>说明</th> 
   <th>大小(字节)</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>BIT(M)</td> 
   <td>位字段类型</td> 
   <td>大约 (M+7)/8 字节</td> 
  </tr> 
  <tr> 
   <td>BINARY(M)</td> 
   <td>固定长度二进制字符串</td> 
   <td>M 字节</td> 
  </tr> 
  <tr> 
   <td>VARBINARY (M)</td> 
   <td>可变长度二进制字符串</td> 
   <td>M+1 字节</td> 
  </tr> 
  <tr> 
   <td>TINYBLOB (M)</td> 
   <td>非常小的BLOB</td> 
   <td>L+1 字节，在此，L&lt;2^8</td> 
  </tr> 
  <tr> 
   <td>BLOB (M)</td> 
   <td>小 BLOB</td> 
   <td>L+2 字节，在此，L&lt;2^16</td> 
  </tr> 
  <tr> 
   <td>MEDIUMBLOB (M)</td> 
   <td>中等大小的BLOB</td> 
   <td>L+3 字节，在此，L&lt;2^24</td> 
  </tr> 
  <tr> 
   <td>LONGBLOB (M)</td> 
   <td>非常大的BLOB</td> 
   <td>L+4 字节，在此，L&lt;2^32</td> 
  </tr> 
 </tbody> 
</table>

BIT 类型

位字段类型。M 表示每个值的位数，范围为 1～64。如果 M 被省略，默认值为 1。如果为 BIT(M) 列分配的值的长度小于 M 位，在值的左边用 0 填充。例如，为 BIT(6) 列分配一个值 b’101’，其效果与分配 b’000101’ 相同。

BIT 数据类型用来保存位字段值，例如以二进制的形式保存数据 13，13 的二进制形式为 1101，在这里需要位数至少为 4 位的 BIT 类型，即可以定义列类型为 BIT(4)。大于二进制 1111 的数据是不能插入 BIT(4) 类型的字段中的。

> 提示：**默认情况下，MySQL 不可以插入超出该列允许范围的值，因而插入数据时要确保插入的值在指定的范围内。**

BINARY 和 VARBINARY 类型

BINARY 和 VARBINARY 类型类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字节字符串。使用的语法格式如下：

列名称 BINARY(M) 或者 VARBINARY(M)

BINARY 类型的长度是固定的，指定长度后，不足最大长度的，将在它们右边填充 “\\0” 补齐，以达到指定长度。例如，指定列数据类型为 BINARY(3)，当插入 a 时，存储的内容实际为 “a\\0\\0”，当插入 ab 时，实际存储的内容为“ab\\0”，无论存储的内容是否达到指定的长度，存储空间均为指定的值 M。

VARBINARY 类型的长度是可变的，指定好长度之后，长度可以在 0 到最大值之间。例如，指定列数据类型为 VARBINARY(20)，如果插入的值长度只有 10，则实际存储空间为 10 加 1，实际占用的空间为字符串的实际长度加 1。

BLOB 类型

BLOB 是一个二进制的对象，用来存储可变数量的数据。BLOB 类型分为 4 种：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB，它们可容纳值的最大长度不同，如下表所示。

<table> 
 <thead> 
  <tr> 
   <th>数据类型</th> 
   <th>存储范围</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>TINYBLOB</td> 
   <td>最大长度为255 (28-1)字节</td> 
  </tr> 
  <tr> 
   <td>BLOB</td> 
   <td>最大长度为65535 (216-1)字节</td> 
  </tr> 
  <tr> 
   <td>MEDIUMBLOB</td> 
   <td>最大长度为16777215 (224-1)字节</td> 
  </tr> 
  <tr> 
   <td>LONGBLOB</td> 
   <td>最大长度为4294967295或4GB (231-1)字节</td> 
  </tr> 
 </tbody> 
</table>

BLOB 列存储的是二进制字符串（字节字符串），TEXT 列存储的是非进制字符串（字符字符串）。BLOB 列是字符集，并且排序和比较基于列值字节的数值；TEXT 列有一个字符集，并且根据字符集对值进行排序和比较。

#### 总结 ####

**总的来说，选择合适的数据类型可以提高数据库的性能、节省存储空间、确保数据的一致性和有效性，并方便进行数据处理和计算。因此，在设计数据库时，选择合适的数据类型非常重要。**~下节带来MySQL视图的分享

[K]: https://blog.csdn.net/qq_72157449?spm=1000.2115.3001.5343
[MySQL]: https://blog.csdn.net/qq_72157449/category_12344760.html?spm=1001.2014.3001.5482
[c8bf44e46cd54d668094c64665df59be.png]: https://img-blog.csdnimg.cn/c8bf44e46cd54d668094c64665df59be.png