【Mysql】：搞清楚字符串类型char、varchar、text

Joyo

Mysql数据库

发布于：2020年7月4日

次浏览

字数：2.3k字

时长：8分钟

• 在Mysql中表示字符串类型的有char、varchar、text

• 它们之间有什么区别呢？用到什么场景才更加合适呢？相信很多人搞不清

• 本文主要从字符串的存储、查询效率、字符串表示范围进行讨论

Github issues:https://github.com/littlejoyo/Blog/issues/

1.字符串类型的表示

• char和varchar代表字符串的格式为char(N)和varchar(N)

• 其中N在Mysql5.0之前代表的字节数，在5.0后代表的字符的个数，字符数超过N会被截断，超过N的部分丢弃。

• char(10)和varchar(10)分别表示存储10个字符的字符串，不过它们的存储策略是不同的，后面细讲

• text,无必要写N，直接用text表示字符串

2.存储上的不同

• 对char来说，最多存放255个字符，和编码无关

• varchar可以表示65535个字节，但是最多存放65532个字节,因为需要2个字节来存放字符串的长度，以及结尾还要用1个字节表示结束，所以有效长度就是65535-1-2=65532。

• text，字符存储上限为65535字节，会用2字节记录存储数据大小，但是不同的是2字节不占用text数据的空间，没有空间浪费，速度慢，尤其创建临时表的时候会异常悲剧。

text的类型还有TINYTEXT、MEDIUMTEXT、LONGTEXT，分别能代表不同的字符串长度，如下表：

注意：这些大数据类型，一旦存储数据后，检索数据会占用大量的IO及CPU，考虑到性能，一般不建议使用text相关数据类型对数据进行存储。

3.定长和边长

• char(N)：char是定长的，插入数据不足规定长度N，右边使用空格补全，然而字符数超过N就会被截断，超过N的部分丢弃。（如果是严格模式，则会拒绝插入并提示错误信息）

• varchar(N)：varchar是不定长的，字符数超过N的同样根据模式进行处理，如果字符数小于N，则只会占用字符加上1到2字节的空间，另外加上一个代表结束的字节，其他的就是所需表示字符

注：当N<=255是需要1个字节来表示，当N>255就需要2个字节来表示

• text：text是变长的，不需要进行空格的填充，可表示的字符串长度比char和varchar都长

4.查找效率对比

首先给出结果：char > varchar > text

char和varchar的查找效率对比

• 可以看到char类型在存放数据的时候，中间是没有间隔的，数据长度是固定的，而且数据段之间没有间隔

• 因为我们在创建列的时候已经告诉MySQL列的长度了，MySQL在查询数据的时候，只需要按部就班寻找就行了，不需要在中途计算这个数据段的长度。

• varchar类型的存放方式就不同了，在每个数据段开头，都要有一段空间（1~2个字节）存放数据段的长度，在数据段的结尾还有一段空间（1个字节）标记此字段的节数。

• MySQL在读取一个数据段的时候，首先要读开头，比如读到了3，说明数据段的长度是3，之后就不多不少，只读3个字节。

• 又因为数据段被隔开了，所以MySQL在遍历varchar类型数据的时候，磁针要比char类型的列多读很多次磁盘来获取字段的真实长度，这就是为什么varchar比char查询效率低的原因了。

那text为什么查询效率比其余两个都低呢？

• Mysql内存临时表不支持TEXT这样的大数据类型，如果查询中包含这样的数据，在排序等操作时，就不能使用内存临时表，必须使用磁盘临时表进行。

• 对于这种数据，Mysql还是要进行二次查询，会使sql性能变得很差，但是不是说一定不能使用这样的数据类型。

• 如果一定要使用，建议把TEXT列分离到单独的扩展表中，查询时一定不要使用select * 而只需要取出必要的列，不需要TEXT列的数据时不要对该列进行查询。

• TEXT类型只能使用前缀索引,因为MySQL对索引字段长度是有限制的，并且TEXT列上是不能有默认值的。

5.末尾空格处理

• char(N):会去掉结尾的空格

• varchar(N):不会去掉结尾空格

• text:不会去掉结尾空格

举个例子：

• char(10)，保存字符串”hello “（末尾有一个空格），存到数据库就是’hello ’（有5个空格），然后查询出来只是”hello”（没有空格），占10个字节。

• varchar(10)，保存字符串”hello “（有一个空格），存到数据库就是”hello “（有1个空格），查询出来也是”hello “（有1个空格），占6个字节。

6.varchar的再讨论

• varchar是不定长的，varchar类型的列是不定长的，在5.0版本以后的最大长度是65535字节,但是这个长度只是“系统长度”，这并不意味着你真的可以完全利用65535字节来存储数据

• 因为varchar是不定长的，所以需要前两个字节标记字段的实际长度，结尾还要用一个字节表示结束

• 然后，需要注意的是65535只是字节个数，而且是理论字节个数，在减去头尾的”系统”占用字节后，只剩下65532可用字节。那么我们建表的时候，能不能直接写varchar(65532)呢？当然是不可以的，因为4.0之后，varchar后面的小括号里就不再是字节长度了，而是字符长度。

字节和字符个数之间的换算关系是根据编码决定的：

那么，问题又来了，是否意味着，在utf8mb4编码下我们可以用varchar(16383)来定义一个列呢？

答案是不一定，需要分情况

• MySQL规定了一个数据行row所有的字段加起来总长度不能超过65535字节，所以如果一个表只有一个列，那完全可以用varchar(16383)来定义这个列

• 如果这个表还有其他列，无论其他列多么短，都是会占用字节数的，所以，使用varchar(16383)来定义的时候，MySQL会返回错误提示：ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs,意思是row的容量太大，超出了row的最大容量65535，如果不改变列的长度的话，推荐使用TEXT类型。

举个例子：

创建一个只包含两个字段的表（编码是utf8mb4），一列是主键，一列是字符串，字符串的最大长度是多少呢？你可以先自己算一下，再往下看。

为什么65535要减去4呢？

因为int(11)占4个字节，那么在utf8编码情况下，还是同样的数据结构，content的最大长度有事多少呢？

6.使用建议

• 在设置Mysql字符串字段属性的时候结合业务场景进行选择，不要脱离实际业务盲目选择

• 存储定长字符串，尽量用char，索引速度极快。(例如：手机号、身份证号)

• 长度255以上字符串，只能用varchar和text，能用varchar尽量不要用text

• text不能设置默认值，并且只能使用前缀索引

• 和char或者varchar之类的字段不同，text中存储的内容不会和行数据存在一起，而是数据库另外找地方存储的，数据库自己记了指针。据说varchar(255+)也是这么存的。

• text上限比较高，安全性上需要注意，在异常状态下可能会存储非常大的数据，造成很多问题，哪怕用varchar(10000)都能给截断一下呢。

• 理论上varchar的长度最大65535字符，但能不能这么设置也得看字符集，因为mysql有规定，除了text和blob之类的类型外，单字段长度不能超过65535字节，所以，如果字符集是设置为utf8，一个字符3个字节，那就只能定义varchar(21485),如果字符集设置为utf8mb4，一个字符4个字节，那就只能定义varchar(16382)

微信公众号

扫一扫关注Joyo说公众号，共同学习和研究开发技术。

更新于：2021年2月21日

数据库

Mysql