【mysql】文本字符串类型

文本字符串类型

• 在实际的项目中，经常遇到一种数据，就是字符串数据。
• MySQL中，文本字符串总体上分为CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、ENUM、SET等类型。

在这里插入图片描述

1. CHAR与VARCHAR类型

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

CHAR类型：

• CHAR(M) 类型一般需要预先定义字符串长度。如果不指定(M)，则表示长度默认是1个字符。
• 如果保存时，数据的实际长度比CHAR类型声明的长度小，则会在右侧填充空格以达到指定的长度。当MySQL检索CHAR类型的数据时，CHAR类型的字段会去除尾部的空格。
• 定义CHAR类型字段时，声明的字段长度即为CHAR类型字段所占的存储空间的字节数。

CREATE TABLE test_char1(
c1 CHAR,
c2 CHAR(5)
);

DESC test_char1;

在这里插入图片描述

INSERT INTO test_char1
VALUES('a','Tom');
SELECT * FROM test_char1;

在这里插入图片描述

• CONCAT 函数时拼接两个字符串成一个字符串，测试数据的实际长度比CHAR类型声明的长度小，则会在右侧填充空格以达到指定的长度

SELECT CONCAT(c2,'***') FROM test_char1;

在这里插入图片描述

• 测试：当MySQL检索CHAR类型的数据时，CHAR类型的字段会去除尾部的空格。INSERT INTO test_char1(c2) VALUES('a '); SELECT CHAR_LENGTH(c2) FROM test_char1;
  

  在这里插入图片描述

VARCHAR类型：

• VARCHAR(M) 定义时，必须指定长度M，否则报错。
• MySQL4.0版本以下，varchar(20)：指的是20字节，如果存放UTF8汉字时，只能存6个（每个汉字3字节） ；MySQL5.0版本以上，varchar(20)：指的是20字符。
• 检索VARCHAR类型的字段数据时，会保留数据尾部的空格。VARCHAR类型的字段所占用的存储空间为字符串实际长度加1个字节。

CREATE TABLE test_varchar1(
NAME VARCHAR  #错误
);

• VARCHAR(M)，M 最大 为 21845#Column length too big for column 'NAME' (max = 21845); CREATE TABLE test_varchar2( NAME VARCHAR(65535) #错误 );
• 一个汉字的长度就是一个字符的长度CREATE TABLE test_varchar3( NAME VARCHAR(5) ); INSERT INTO test_varchar3 VALUES('兮动人'),('云兮动人');
  

  在这里插入图片描述
• 超出长度，就会报错
  

  在这里插入图片描述
• 字母或数字的长度，也算一个字符的长度

CREATE TABLE test_varchar4(
name VARCHAR(6)  
);

INSERT INTO test_varchar4
VALUES('xdr630');

在这里插入图片描述

在这里插入图片描述

2. 哪些情况使用 CHAR 或 VARCHAR 更好

情况1：存储很短的信息。比如门牌号码101，201……这样很短的信息应该用char，因为varchar还要占个byte用于存储信息长度，本来打算节约存储的，结果得不偿失。

情况2：固定长度的。比如使用uuid作为主键，那用char应该更合适。因为他固定长度，varchar动态根据长度的特性就消失了，而且还要占个长度信息。

情况3：十分频繁改变的column。因为varchar每次存储都要有额外的计算，得到长度等工作，如果一个非常频繁改变的，那就要有很多的精力用于计算，而这些对于char来说是不需要的。

情况4：具体存储引擎中的情况：

• MyISAM 数据存储引擎和数据列：MyISAM数据表，最好使用固定长度(CHAR)的数据列代替可变长度(VARCHAR)的数据列。这样使得整个表静态化，从而使数据检索更快，用空间换时间。
• MEMORY 存储引擎和数据列：MEMORY数据表目前都使用固定长度的数据行存储，因此无论使用CHAR或VARCHAR列都没有关系，两者都是作为CHAR类型处理的。
• InnoDB存储引擎，建议使用VARCHAR类型。因为对于InnoDB数据表，内部的行存储格式并没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），而且主要影响性能的因素是数据行使用的存储总量，由于char平均占用的空间多于varchar，所以除了简短并且固定长度的，其他考虑varchar。这样节省空间，对磁盘I/O和数据存储总量比较好。

3. TEXT类型

• 在MySQL中，TEXT用来保存文本类型的字符串，总共包含4种类型，分别为TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT 类型。
• 在向TEXT类型的字段保存和查询数据时，系统自动按照实际长度存储，不需要预先定义长度。这一点和 VARCHAR类型相同。
• 每种TEXT类型保存的数据长度和所占用的存储空间不同，如下：

• 由于实际存储的长度不确定，MySQL 不允许 TEXT 类型的字段做主键。遇到这种情况，你只能采用 CHAR(M)，或者 VARCHAR(M)。

举例：

创建数据表：

CREATE TABLE test_text(
tx TEXT
);

INSERT INTO test_text
VALUES('atguigu   ');

• 查询其长度

SELECT CHAR_LENGTH(tx)
FROM test_text; 

在这里插入图片描述

• 说明在保存和查询数据时，并没有删除TEXT类型的数据尾部的空格。

4. 开发中经验

• TEXT文本类型，可以存比较大的文本段，搜索速度稍慢，因此如果不是特别大的内容，建议使用CHAR，VARCHAR来代替。还有TEXT类型不用加默认值，加了也没用。而且text和blob类型的数据删除后容易导致“空洞”，使得文件碎片比较多，所以频繁使用的表不建议包含TEXT类型字段，建议单独分出去，单独用一个表。