CHAR 与 VARCHAR 详解

 关注
发布于: 2020 年 08 月 20 日
前言： 
﻿
前面写过一篇介绍int类型的文章，一直想写一篇介绍字符串字段类型的文章，一直拖着也没思路要怎么下手。最近多关注了下这方面的文章，决定还是把拖了好久的文章了结了吧。本篇文章主要会介绍字符串类型char及varchar的用法及区别。
﻿
本文实验环境为MySQL 5.7.23版本，存储引擎为Innodb，sql_mode采用严格模式，字符集是utf8。
﻿
▍1.CHAR类型介绍﻿
我们平时使用char类型定义字段时，往往会指定其长度M，即char(M)。其实M指的是字符数，即这个字段最多存储多少个字符，M可不指定，默认为1，范围是[0,255]，单个字母、数字、中文等都是占用一个字符。utf8字符集下一个中文字符占用3个字节。下面我们简单测试下：
﻿
# 假设以如下建表语句创建测试表
CREATE TABLE `char_tb1` (
  `col1` char DEFAULT NULL,
  `col2` char(5) DEFAULT NULL,
  `col3` char(10) DEFAULT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
# 进入数据库查询建表语句如下 发现char(M) M可不指定，默认为1
mysql> show create table char_tb1\G
*************************** 1. row ***************************
       Table: char_tb1
Create Table: CREATE TABLE `char_tb1` (
  `col1` char(1) DEFAULT NULL,
  `col2` char(5) DEFAULT NULL,
  `col3` char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
# 插入数据 可以看出M表示保存的最大字符数，字母、数字、中文等都是占用一个字符
mysql> insert into char_tb1 (col1) values ('a'),('1'),('王'),(']');
Query OK, 4 rows affected (0.01 sec)
mysql> insert into char_tb1 (col1) values ('aa'),('12');
ERROR 1406 (22001): Data too long for column 'col1' at row 1
mysql> select * from char_tb1;
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
| a    | NULL | NULL |
| 1    | NULL | NULL |
| 王   | NULL | NULL |
| ]    | NULL | NULL |
+------+------+------+
4 rows in set (0.00 sec)
mysql> insert into char_tb1 (col2) values ('abcd'),('王-123'),('^*123'),('12'),('一二三四五');
Query OK, 5 rows affected (0.01 sec)
mysql> insert into char_tb1 (col2) values ('abcdef');
ERROR 1406 (22001): Data too long for column 'col2' at row 1
mysql> select * from char_tb1;
+------+-----------------+------+
| col1 | col2            | col3 |
+------+-----------------+------+
| a    | NULL            | NULL |
| 1    | NULL            | NULL |
| 王   | NULL            | NULL |
| ]    | NULL            | NULL |
| NULL | abcd            | NULL |
| NULL | 王-123          | NULL |
| NULL | ^*123           | NULL |
| NULL | 12              | NULL |
| NULL | 一二三四五      | NULL |
+------+-----------------+------+
9 rows in set (0.00 sec)
# 下面测试发现M的范围是[0,255] 
mysql> alter table char_tb1 add column col4 char(0);
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> alter table char_tb1 add column col5 char(255);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> alter table char_tb1 add column col5 char(256);
ERROR 1074 (42000): Column length too big for column 'col5' (max = 255); use BLOB or TEXT instead
﻿
▍2.VARCHAR类型介绍﻿
同样的，varchar(M)中的的M表示保存的最大字符数，单个字母、数字、中文等都是占用一个字符。varchar可存储的长度范围为0-65535字节，此外，varchar需要使用1或者2个额外字节记录字符串的长度：如果列的最大长度小于或等于255字节，则只使用1个字节表示，否则使用2个字节。对于Innodb引擎，utf8字符集来说，单个中文字符占用3个字节，所以varchar(M)中的M最大不会超过21845，即M的范围是[0,21845)，并且M必须指定。另外MySQL规定：单个字段长度不大于65535字节；单行最大限制为65535，这里不包括TEXT、BLOB字段。即单张表中的所有varchar字段定义的长度之和不能大于65535，所以并不是所有varchar(M)字段中的M都可以取到21844，下面我们来验证下：
﻿
# 假设以如下建表语句创建测试表
CREATE TABLE `varchar_tb1` (
  `col1` varchar(0) DEFAULT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
# 查看建表语句 增加字段 发现M必须指定
mysql> show create table varchar_tb1\G
*************************** 1. row ***************************
       Table: varchar_tb1
Create Table: CREATE TABLE `varchar_tb1` (
  `col1` varchar(0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> alter table varchar_tb1 add column col2 varchar;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
# 下面测试证明M最大可取到21844
mysql> CREATE TABLE `varchar_tb2` (col1 varchar(21844));
Query OK, 0 rows affected (0.04 sec)
mysql> CREATE TABLE `varchar_tb3` (col1 varchar(218445));
ERROR 1074 (42000): Column length too big for column 'col1' (max = 21845); use BLOB or TEXT instead
# 下面测试证明单行最大限制为65535字节
mysql> CREATE TABLE `varchar_tb3` (col1 varchar(10));
Query OK, 0 rows affected (0.04 sec)
mysql> alter table varchar_tb3 add column col2 varchar(21844);
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
mysql> alter table varchar_tb3 add column col2 varchar(21834);
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
mysql> alter table varchar_tb3 add column col2 varchar(21833);
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show create table varchar_tb3\G
*************************** 1. row ***************************
       Table: varchar_tb3
Create Table: CREATE TABLE `varchar_tb3` (
  `col1` varchar(10) DEFAULT NULL,
  `col2` varchar(21833) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
﻿
▍3.CHAR与VARCHAR比较﻿
CHAR类型是定长的，MySQL总是根据定义的字符串长度分配足够的空间。当保存CHAR值时，在它们的右边填充空格以达到指定的长度，当检索到CHAR值时，尾部的空格被删除掉。
﻿
VARCHAR类型用于存储可变长字符串，存储时，如果字符没有达到定义的位数，也不会在后面补空格。但是，由于行是变长的，在UPDATE时可能使行变得比原来更长，这就导致需要做额外的工作。如果一个行占用的空间增长，并且在页内没有更多的空间可以存储，在这种情况下InnoDB需要分裂页来使行可以放进页内，这样会增加碎片。
﻿
下面简单总结下CHAR与VARCHAR字段类型的适用场景：
﻿
CHAR适合存储很短的字符串，或者所有值都接近同一个长度。例如，CHAR非常适合存储密码的MD5值，因为这是一个定长的值。对于经常变更的数据，CHAR也比VARCHAR更好，因为定长的CHAR类型不容易产生碎片。对于非常短的列，CHAR比VARCHAR在存储空间上也更有效率。例如用CHAR(1)来存储只有Y和N的值，如果采用单字节字符集只需要一个字节，但是VARCHAR(1)却需要两个字节，因为还有一个记录长度的额外字节。
﻿
下面这些情况下使用VARCHAR是合适的：字符串很长或者所要存储的字符串长短不一，差别很大；字符串列的最大长度比平均长度大得多；列的更新很少，所以碎片不是问题。
﻿
额外说明下，我们在定义字段最大长度时应该按需分配，提前做好预估。特别是对于VARCHAR字段，有人认为反正VARCHAR数据类型是根据实际的需要来分配长度的，还不如给大一点呢。但事实不是这样的，比如现在需要存储一个地址信息，根据评估，只要使用100个字符就可以了，我们可以使用VARCHAR(100)或VARCHAR(200)来存储，虽然它们用来存储90个字符的数据，其存储空间相同，但是对于内存的消耗是不同的。更长的列会消耗更多的内存，因为MySQL通常会分配固定大小的内存块来保存内部值，尤其是使用内存临时表进行排列或者操作时会特别糟糕。所以我们在分配VARCHAR数据类型时仍然不能够太过于慷慨。还是要评估实际需要的长度，然后选择一个最长的字段来设置字符长度。如果为了考虑冗余，可以留10%左右的字符长度。千万不能认为VARCHAR是根据实际长度来分配存储空间，而随意的分配长度，或者说干脆使用最大的字符长度。
﻿
总结： 
﻿
本文分别介绍了CHAR与VARCHAR字段类型的使用方法，并且给出了二者的对比以及适用场景。在实际生产情况，需要具体情况具体分析，合适的才是最好的，希望这篇文章能给到大家参考。
﻿
﻿
发布于: 2020 年 08 月 20 日阅读数: 30
原文链接:【http://xie.infoq.cn/article/3065cdf1639b992e091c2f4cd】。文章转载请联系作者。
Simon

 关注
MySQL技术学习者 2018.06.08 加入
公众号『MySQL技术』作者
发布
暂无评论