char和varchar类型相似,差别主要在存储,尾随空格和检索方式上。
char和varchar相同的是:char和varchar都指定了字符长度,注意是字符长度。例如char(30) 和 varchar(30)表示都可以存30个字符。有一点要注意的是在utf8mb4编码中,每个字符占4个节点。在utf8中,每个字符占3个字节。当要存储的字符超过char/varchar指定的最大长度。在sql mode 没开启的时候是截断要存储的字串,只存储前30位
char列中的值是定长的字符串。长度可以指定为0到255个字符(例如utf8mb4: 0-255*4个字节)之间的值当使用char存储字符时,以char(30)为例,不够30个字符的以空格向右填充。当检索char值时,除非启用sqlmode:pad_char_to_full-length sql模式,否则将删除尾部空格。
varchar列中的值是可变长度的字符串。长度可以指定为0到65535字节之间的值。varchar的有效最大长度取决于最大行大小(65535字节,在所有列之间共享)和使用的字符集
与char不同的是,varchar在存储数据的时候,会额外存储存储1-2个字节(例如varchar(4),这1-2个字节长度不会算在这4个字符长度里面,不用担心被截断),这1-2个字节用来记录数据字符的长度。假如数据字符长度小于等于255,则使用1个字节记录数据长度。大于则用两个字节。char只存储数据本身。如下图所示。
(误区:varchar在查询时比char慢是因为会计算varchar的字节长度(x))
在latin1编码中,latin1不能存中文。英文字母为1个字节。
value | char(4) | storage required | varchar(4) | storage required |
” | ‘ ‘ | 4 bytes | ” | 1 byte |
‘ab’ | ‘ab ‘ | 4 bytes | ‘ab’ | 3 byte |
‘abcd’ | ‘abcd’ | 4 bytes | ‘abcd’ | 5 byte |
‘abcdefgh’ | ‘abcd’ | 4 bytes | ‘abcd’ | 5 byte |
需要注意的是varchar值在存储时不填充空格。在存储和检索值时,尾部空格将被保留,这与标准sql一致。而char则相反,char在储存时会填充空格,在检索时尾部空格会去掉,无论这个尾部空格是自动填充的还是数据本身的。举个例子:
mysql> create table vc (v varchar(4), c char(4)); query ok, 0 rows affected (0.01 sec) mysql> insert into vc values ('ab ', 'ab '); query ok, 1 row affected (0.00 sec) mysql> select concat('(', v, ')'), concat('(', c, ')') from vc; +---------------------+---------------------+ | concat('(', v, ')') | concat('(', c, ')') | +---------------------+---------------------+ | (ab ) | (ab) | +---------------------+---------------------+ 1 row in set (0.06 sec)<br><br><br>
做三个实验。
mysql> show create table vc; +-------+------------------------------------------------------------------------------------------------------------------------------------------------------+ | table | create table | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------+ | vc | create table `vc` ( `v` varchar(4) default null, `c` char(4) default null, unique key `v_unique` (`v`) ) engine=innodb default charset=utf8mb4 | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
1.查询语句 where 字段=’ ab’;
mysql> select concat('(',v, ')'),concat('(',c, ')') from vc where v=' ab'; empty set (0.00 sec) mysql> select concat('(',v, ')'),concat('(',c, ')') from vc where c=' ab'; empty set (0.00 sec)
2.查询语句 where 字段=’ab ‘;
mysql> select concat('(',v, ')'),concat('(',c, ')') from vc where v='ab '; +--------------------+--------------------+ | concat('(',v, ')') | concat('(',c, ')') | +--------------------+--------------------+ | (ab ) | (ab) | +--------------------+--------------------+ 1 row in set (0.00 sec) mysql> select concat('(',v, ')'),concat('(',c, ')') from vc where c='ab '; +--------------------+--------------------+ | concat('(',v, ')') | concat('(',c, ')') | +--------------------+--------------------+ | (ab ) | (ab) | +--------------------+--------------------+ 1 row in set (0.00 sec)<br><br><br>在所有字符串的比较中,不论是varchar,text,char 都会忽略去掉尾部空格,除了like子句,like子句不会去掉尾部空格如下所示<br><br>
mysql> select concat('(',v, ')'),concat('(',c, ')') from vipshop_dba.vc where c like 'ab'; (无尾部空格) +--------------------+--------------------+ | concat('(',v, ')') | concat('(',c, ')') | +--------------------+--------------------+ | (ab ) | (ab) | | ( ab ) | (ab) | +--------------------+--------------------+ 2 rows in set (0.00 sec) mysql> select concat('(',v, ')'),concat('(',c, ')') from vipshop_dba.vc where c like 'ab '; (有尾部空格) empty set (0.00 sec) char在储存时会填充空格,在检索时尾部空格会去掉,无论这个尾部空格是自动填充的还是数据本身的。这个去掉尾部空格的动作在比较之前会发生 mysql> select concat('(',v, ')'),concat('(',c, ')') from vipshop_dba.vc where v like 'ab '; (有尾部空格) +--------------------+--------------------+ | concat('(',v, ')') | concat('(',c, ')') | +--------------------+--------------------+ | (ab ) | (ab) | +--------------------+--------------------+ 1 row in set (0.01 sec) mysql> select concat('(',v, ')'),concat('(',c, ')') from vipshop_dba.vc where v like 'ab'; (无尾部空格) empty set (0.00 sec)
3.varchar 设置unique 索引 观察 ‘ab’ 和 ‘ab ‘是否同时能存在
mysql> insert into vc (v,c)values('ab ', 'ab ') -> ; error 1062 (23000): duplicate entry 'ab ' for key 'v_unique' mysql> insert into vc (v,c)values(' ab ', 'ab ') -> ; query ok, 1 row affected, 1 warning (0.00 sec)<br>说明虽然varchar 尾部空格可以保留,但是索引上似乎额外做了限制。如果varchar是唯一索引,插入的值区别只在于尾部空格的数量的话则会报 duplicate key
说到字节限制这个问题,也想提醒一下。索引长度也是有限制的噢:
innodb引擎的每个索引列长度限制为767字节(bytes),所有组成索引列的长度和不能大于3072字节。注意是字节,varchar(256)在不同的编码字节计算不同。例如在utf8mb4中,一个字符占4个字节,256个字符=1024个字节。就达不到索引覆盖的效果噢。相当于只做了一个前缀索引(非常重要,前缀索引是需要回(主键)表的,二次查询)
innodb引擎可以通过配置innodb_large_prefix=on(全局参数,动态生效)来让单个索引列长度限制上升到3072字节
- 在mysql5.7里默认 innodb_large_prefix=1 解除了767bytes长度限制,但是单列索引长度最大还是不能超过3072bytes
- 在mysql5.6里默认 innodb_large_prefix=0 限制单列索引长度不能超过767bytes。需要设置innodb_large_prefix=on、innodb_file_format=barracuda、innodb_file_per_table=on ,且innodb表的存储格式为 dynamic 或 compressed,则前缀索引最多可包含3072个字节
以上就是mysql中 char 和 varchar的区别的详细内容,更多关于mysql char 和 varchar的资料请关注www.887551.com其它相关文章!