varchar类型字段为什么经常定义成255

MySQL | ver < 4.1: VARCHAR以字节为单位存储,所以假设全部为常用汉字(UTF-8

3字节编码长度),则VARCHAR(255)共可存放约85个汉字;

MySQL | ver >= 4.1:

VARCHAR以字符为单位存储,假设输入仍然为常用汉字,则VARCHAR(255)可以存放255个汉字。 

另外,据我所知,MySQL对UTF-8的支持也仅仅限于1~3字节编码长度(Unicode:0x0000~0xFFFF),可以满足大部分需求,但是生僻字就不行了。

那么VARCHAR的最大值是多少呢?

根据官方文档,VARCHAR 最多可以是 65535 字节(这也意味着一条记录只有这一个字段,因为 MySQL 一行只能包含 65535 字节)。

然而,无法为这么长的 VARCHAR 做索引,对于 MyISAM,可以对前 1000 个字节做索引,对于 InnoDB,则只有 767 字节。(来源依据)

在varchar长度接近256时,varchar长度设置成255的好处:

1、方便InnoDB建索引,对于 MyISAM,可以对前 1000 个字节做索引,对于 InnoDB,则只有 767 字节。(来源依据)。255X3=765

2、少申请一个字节,记录字符创长度,一个8位的tinyint,可以表示的无符号数值的范围是,0-255,如果长度超过了255,需要在申请个字节


1.MySQL建立索引时假设没有限制索引的大小,索引长度会默认採用的该字段的长度。也就是说varchar(20)和varchar(255)相应的索引长度分别为20*3(utf-8)(+2+1),255*3(utf-8)(+2+1)。当中"+2"用来存储长度信息,“+1”用来标记是否为空。载入索引信息时用varchar(255)类型会占用很多其它的内存; (备注:当字段定义为非空的时候。是否为空的标记将不占用字节)

比如。測试sql(InnoDB引擎)例如以下:

CREATE DATABASE TestDataBase

USE TestDataBase

CREATE TABLE ABC (

  `id` int(11) DEFAULT NULL,

  `name` varchar(20) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

ALTER  TABLE  `ABC`  ADD  INDEX `nameIndex` (`name`)

explain select name from ABC

alter table ABC  modify name varchar(255)

explain select name from ABC

结果中的ken_len表示索引使用的字节数:

key_len的长度计算公式:

varchr(10)变长字段且同意NULL:10*(Character Set:utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)

varchr(10)变长字段且不同意NULL:10*(Character Set:utf8=3,gbk=2,latin1=1)+2(变长字段)

char(10)固定字段且同意NULL:10*(Character Set:utf8=3,gbk=2,latin1=1)+1(NULL)

char(10)固定字段且同意NULL:10*(Character Set:utf8=3,gbk=2,latin1=1)

依据这个值。就能够推断索引使用情况,特别是在组合索引的时候,推断全部的索引字段都被查询用到。

2.varchar(20)与varchar(255)都是保持可变的字符串,当使用ROW_FORMAT=FIXED创建MyISAM表时,会为每行使用固定的长度空间,这样设置不同的varchar长度值时。存储相同数据所占用的空间是不一样。

通常情况下使用varchar(20)和varchar(255)保持'hello'占用的空间都是一样的,但使用长度较短的列却有巨大的优势。较大的列使用很多其它的内存。由于MySQL一般会分配固定大小的内存块来保存值,这对排序或使用基于内存的暂时表尤其不好。相同的事情也会发生在使用文件排序或者基于磁盘的暂时表的时候。


很多时候我们看到一些表字符串类型的字段定义为varchar(255),开始以为varchar只能定义为255这个长度值,其实不然。

官方文档所说,varchar有效的最大长度取决于行的容量,以及用的字符集,整行的所有列的定义长度不能超过65535字节(bytes),text、blob等大字段类型除外,

P.S. https://dev.mysql.com/doc/refman/5.7/en/char.html

《小白学习MySQL - 变通创建索引的案例一则》提到了,

InnoDB,如果需要建索引,就不能超过767bytes,utf8编码,255*3=765 bytes,是能建索引情况下的最大值,utf8mb4编码,默认字符长度则应该是767除以4向下取整,就是191。如果设置了innodb_large_prefix,最大长度是3072字节,utf8编码,1024*3=3072 bytes,utf8mb4编码,768*4=3072。

MyISAM,如果需要建索引,就不能超过1000bytes,utf8编码,333*3=999 bytes,是能建索引情况下的最大值,utf8mb4编码,默认字符长度则应该是1000除以4,就是250。

一般情况下,我们用的是InnoDB引擎,utf8则是常用字符集,因此varchar类型字段定义为255比较合适。

但实际上,varchar(255)并不是最优的字符定义长度,究竟定成多少,还是要根据实际需求来决定,例如这个字段我就不需要创建索引,定义超过255,是可以的,只能说255是常规情况下较少出错的一个值。


有没有觉得我们设置varchar长度的时候,很多时候都设置成255,这是为什么呢?

其实是因为在5.5.3版本之前

InnoDB存储引擎的表索引的前缀长度最长是767字节(bytes),

MyIsam存储引擎的表索引的前缀长度最长是1000字节(bytes)。

所以如果字段要创建索引的话,长度就不能超过对应存储引擎的要求 767 bytes或 1000 bytes,而如果中文按1个字符占用3个字节计算,总字节数刚好为765。

1071 - Specified key was too long; max key length is 767 bytes

要控制字节长度,就要说到字节编码了。

一、位(bit)、字节(byte)、字符

位(bit):计算机内部数据储存的最小单位,10001000是一个八位二进制数。

字节(byte):计算机中数据处理 的基本单位,习惯上用大写 B 来表示。

1 B = 8bit

1 KB = 1024 B

1 MB = 1024 KB (2^10 B)

1 GB = 1024 MB (2^20 B)

1 TB = 1024 GB (2^30 B)

字符:计算机中使用的字母、数字、字和符号,如 a、A、中、+、*、の......

二、编码

UTF-8:是用以解决国际上字符的一种多字节编码。包含全世界所有国家需要用到的字符,是国际编码,通用性强。一个汉字 = 3个字节,英文是一个字节

UTF-8编码的文字可以在各国支持UTF8字符集额的浏览器上显示。如果是UTF8编码,则在外国人的英文IE也能显示中文,他们无需下载IE的中文语言支持包。

GBK:GB2312基础上扩容后的国家标准,兼容GB2312。

GBK的文字编码是用双字节来表示的,即不论中、英文字符均使用双字节来表示,为了区分中文,将其最高位都设定成1。

GBK包含全部中文字符,是国家编码,通用性比UTF8差,不过UTF8占用的数据库比GBK大。

utf8mb4:在MySQL5.5.3之后增加的,专门用来兼容四字节的unicode的utf-8的超集,比utf-8能表示更多的字符。mb4即为most bytes 4。

latin1:ISO-8859-1的别名,单字节编码,在支持Latin1编码的系统中传输和存储其他任何编码的字节流都不会被抛弃。换言之,把其他任何编码的字节流当作Latin1编码看待都没有问题

三、中文与编码

MySql 5.0 以上的版本:

一个汉字占多少长度与编码有关:

UTF-8:一个汉字 = 3个字节,英文 = 1个字节

GBK: 一个汉字 = 2个字节,英文 = 1个字节

varchar(n) 在mysql 5.0.3之前表示n个字节,之后修改为表示n个字符,无论汉字和英文,mySql都能存入n个字符,仅实际字节长度有所区别。

MySQL检查长度,可用一下SQL语言查询

SELECT LENGTH(fieldname) FROM tablename

测试

(1)测试 UTF-8

CREATE TABLE `test_char_length_utf8` (

`v1` varchar(9) ,

`v2` varchar(9)

)ENGINE=InnoDB CHARSET=utf8;

创建表单(UTF-8)

SELECT LENGTH(v1), LENGTH(v2) FROM test_char_length_utf8

查询长度

(2)测试 GKB

CREATE TABLE `test_char_length_gbk` (

`v1` varchar(9) ,

`v2` varchar(9)

)ENGINE=InnoDB CHARSET=gbk;

创建表单(GBK)

SELECT LENGTH(v1), LENGTH(v2) FROM test_char_length_gbk

查询长度

四、总结

varchar(255) 是为了索引而设置,能够存储255个汉字。

varchar(255) 后来成为一些人使用的惯性,其实应根据实际业务场景设置长度。

varchar(n) 中的n在mysql 5.0.3之前表示n个字节,之后表示n个字符,按照实际使用的数据库字符编码集,占用不同字节数量,比如GBK、UTF8MB4等等。

  • 25
    点赞
  • 11
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值