MySQL字符串列类型区分

MySQL字符串列类型区分

 

1.

 

首先CHAR, VARCHAR, TEXT和BINARY, VARBINARY, BLOB的区别：

 

CHAR, VARCHAR, TEXT称为非二进制字符串；

 

BINARY, VARBINARY, BLOB称为二进制字符串；

 

二进制字符串和非二进制字符串的区别是：
二进制字符串包含的是字节字符串，非二进制字符串包含的是字符字符串；

 

后者可以定义字符集，前者不可以；

 

而且排序和比较前者基于列值字节的数值值，后者则根据字母顺序进行排序或比较；

 

 

BINARY类似与CHAR类型，但是保存二进制字符串而不是非二进制字符串；

 

VARBINARY类似与VARCHAR类型，但是保存二进制字符串而不是非二进制字符串；
同样类似的BLOB对应TEXT类型(相应的有TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB和TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT)

 

2.

 

CHAR和VARCHAR相同之处：

 

都可以定义字符串的长度，如CHAR(M), VARCHAR(M)；

 

 

不同之处：

 

CHAR(M)定义的列的长度为固定的，M取值可以为0～255之间，当保存CHAR值时，在它们的右边填充空格以达到指定的长度。当检索到CHAR值时，尾部的空格被删除掉。在存储或检索过程中不进行大小写转换。

 

 

VARCHAR(M)定义的列的长度为可变长字符串，M取值可以为0~65535之间，(VARCHAR的最大有效长度由最大行大小和使用的字符集确定。整体最大长度是65,532字节）。VARCHAR值保存时只保存需要的字符数，另加一个字节来记录长度(如果列声明的长度超过255，则使用两个字节)。VARCHAR值保存时不进行填充。当值保存和检索时尾部的空格仍保留，符合标准SQL。

注意：MySQL 数据库的varchar类型在4.1以下的版本中的最大长度限制为255，其数据范围可以是0~255或1~255（根据不同版本数据库来定）。在 MySQL5.0以上的版本中，varchar数据类型的长度支持到了65535，也就是说可以存放65532个字节的数据，起始位和结束位占去了3个字 节，也就是说，在4.1或以下版本中需要使用固定的TEXT或BLOB格式存放的数据可以使用可变长的varchar来存放，这样就能有效的减少数据库文 件的大小。

 

 

CHAR和VARCHAR最大的不同就是一个是固定长度，一个是可变长度。由于是可变长度，因此实际存储的时候是实际字符串再加上一个记录字符串长度的字节(如果超过255则需要两个字节)

 

 

如果分配给CHAR或VARCHAR列的值超过列的最大长度，则对值进行裁剪以使其适合。如果被裁掉的字符不是空格，则会产生一条警告。如果裁剪非空格字符，则会造成错误(而不是警告)并通过使用严格SQL模式禁用值的插入。

 

 

由于BINARY和VARBINARY分别与CHAR和VARCHAR对应，因此两者的区别也和CHAR和VARCHAR的区别类似。

 

VARBINARY定义的M值范围为0<=M<=255

 

3. VARCHAR和TEXT类型的区别：

 

TEXT类型不需要指定M值，其他方面与VARCHAR都类似：比如存储的是可变长度，即不够的部分不需要右边空格填充；等等。

 

TEXT与VARCHAR类似，实际需要保存字符串本身外加两个字节保存字符串的实际长度。

 

看下表容易理解上面讲述的区别：

 

字符串类型的存储需求（M为最大长度，L实际存储字符串的长度）

 

列类型	存储需求
CHAR(M)	M个字节，0 <= M <= 255 (L为固定的=255，不够补空格)
VARCHAR(M)	L+1个字节，其中L <= M 且0 <= M <= 65535(参见下面的注释)(MySQL5.0之前都是最大255)
BINARY(M)	M个字节，0 <= M <= 255
VARBINARY(M)	L+1个字节，其中L <= M 且0 <= M <= 255
TINYBLOB, TINYTEXT	L+1个字节，其中L < 28 (256)
BLOB, TEXT	L+2个字节，其中L < 216 (65536)
MEDIUMBLOB, MEDIUMTEXT	L+3个字节，其中L < 224
LONGBLOB, LONGTEXT	L+4个字节，其中L < 232
ENUM('value1','value2',...)	1或2个字节，取决于枚举值的个数(最多65,535个值)
SET('value1','value2',...)	1、2、3、4或者8个字节，取决于set成员的数目(最多64个成员)

解释：

 

CHAR(M)为固定长度，而且M必须要在0～255之间；

 

VARCHAR(M)保存可变长度，M定义了最大长度，M取值0～65535之间；

 

TINYTEXT不需要定义长度M值，长度范围不超过2⁸ (256)；

 

TEXT不需要定义长度M值，长度范围不超过2¹⁶ (65536)；

 

下面类似。。。

 

 

对于二进制字符串形式：BINARY(M)，VARBINARY(M)，TINYBLOB，BLOB等类似。

 

 

 

更详细的信息参考如下链接：

 

http://dev.mysql.com/doc/refman/5.1/zh/column-types.html#string-types

 

 

4.

 

其他一些参考信息：

 

1、CHAR。CHAR存储定长数据很方便，CHAR字段上的索引效率级高，比如定义char(10)，那么不论你存储的数据是否达到了10个字节，都要占去10个字节的空间,不足的自动用空格填充。

 

2、VARCHAR。存储变长数据，但存储效率没有CHAR高。如果一个字段可能的值是不固定长度的，我们只知道它不可能超过10个字符，把它定义为 VARCHAR(10)是最合算的。VARCHAR类型的实际长度是它的值的实际长度+1。为什么“+<chmetcnv unitname="”" sourcevalue="1" hasspace="False" negative="False" numbertype="1" tcsc="0" w:st="on">1”</chmetcnv>呢？这一个字节用于保存实际使用了多大的长度。从空间上考虑，用varchar合适；从效率上考虑，用char合适，关键是根据实际情况找到权衡点。

 

3．NCHAR、NVARCHAR、NTEXT。这三种从名字上看比前面三种 多了个“N”。它表示存储的是Unicode数据类型的字符。我们知道字符中，英文字符只需要一个字节存储就足够了，但汉字众多，需要两个字节存储，英文 与汉字同时存在时容易造成混乱，Unicode字符集就是为了解决字符集这种不兼容的问题而产生的，它所有的字符都用两个字节表示，即英文字符也是用两个 字节表示。nchar、nvarchar的长度是在1到4000之间。和char、varchar比较起来，nchar、nvarchar则最多存储 4000个字符，不论是英文还是汉字；而char、varchar最多能存储8000个英文，4000个汉字。可以看出使用nchar、nvarchar 数据类型时不用担心输入的字符是英文还是汉字，较为方便，但在存储英文时数量上有些损失。

 

所以一般来说，如果含有中文字符，用nchar/nvarchar，如果纯英文和数字，用char/varchar

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

但是注意