文本字符串类型
-
在实际的项目中,经常遇到一种数据,就是字符串数据。
-
MySQL中,文本字符串总体上分为
CHAR
、VARCHAR
、TINYTEXT
、TEXT
、MEDIUMTEXT
、LONGTEXT
、ENUM
、SET
等类型。
1. CHAR与VARCHAR类型
CHAR和VARCHAR类型都可以存储比较短的字符串。
字符串(文本)类型 | 特点 | 长度 | 长度范围 | 占用的存储空间 |
---|---|---|---|---|
CHAR(M) | 固定长度 | M | 0 <= M <= 255 | M个字节 |
VARCHAR(M) | 可变长度 | M | 0 <= M <= 65535 | (实际长度 + 1) 个字节 |
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 更好
类型 | 特点 | 空间上 | 时间上 | 适用场景 |
---|---|---|---|---|
CHAR(M) | 固定长度 | 浪费存储空间 | 效率高 | 存储不大,速度要求高 |
VARCHAR(M) | 可变长度 | 节省存储空间 | 效率低 | 非CHAR的情况 |
情况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类型保存的数据长度和所占用的存储空间不同,如下:
文本字符串类型 | 特点 | 长度 | 长度范围 | 占用的存储空间 |
---|---|---|---|---|
TINYTEXT | 小文本、可变长度 | L | 0 <= L <= 255 | L + 2 个字节 |
TEXT | 文本、可变长度 | L | 0 <= L <= 65535 | L + 2 个字节 |
MEDIUMTEXT | 中等文本、可变长度 | L | 0 <= L <= 16777215 | L + 3 个字节 |
LONGTEXT | 大文本、可变长度 | L | 0 <= L<= 4294967295(相当于4GB) | L + 4 个字节 |
- 由于实际存储的长度不确定,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类型字段,建议单独分出去,单独用一个表。