对Mysql的字符/字节的深入学习总结_MySQL

一直以来对mysql字符/字节长度这个地方就有误区：

1.int,varchar列类型的数据字段的length设置为4,8,16,32 这种长度就是字节的存储长度，就可以内存对齐了；

2.utf8字符集下一个汉字占用三个字节，GBK下一个汉字占用两个字节，任何字符集下一个英文字符占用一个字节，这个结论是正确的但是没有找到依据。

一、谈一下列类型的存储需求（此处的存储单位是字节）：

数值类型存储需求

列类型	存储需求
TINYINT	1个字节
SMALLINT	2个字节
MEDIUMINT	3个字节
INT, INTEGER	4个字节
BIGINT	8个字节
FLOAT(p)	如果0 p p
FLOAT	4个字节
DOUBLE [PRECISION], item REAL	8个字节
DECIMAL(M,D),   NUMERIC(M,D)	变长；参见下面的讨论
BIT(M)	大约(M+7)/8个字节

 

附上取值范围：

类型	字节	最小值	最大值
		(带符号的/无符号的)	(带符号的/无符号的)
TINYINT	1	-128	127
		0	255
SMALLINT	2	-32768	32767
		0	65535
MEDIUMINT	3	-8388608	8388607
		0	16777215
INT	4	-2147483648	2147483647
		0	4294967295
BIGINT	8	-9223372036854775808	9223372036854775807
		0	18446744073709551615

 

 

DECIMAL(和NUMERIC)的存储需求与具体版本有关：

使用二进制格式将9个十进制(基于10)数压缩为4个字节来表示DECIMAL列值。每个值的整数和分数部分的存储分别确定。每个9位数的倍数需要4个字节，并且“剩余的”位需要4个字节的一部分。下表给出了超出位数的存储需求：

剩余的	字节
位数	数目
0	0
1	1
2	1
3	2
4	2
5	3
6	3
7	4
8	4
9	4

 

日期和时间类型的存储需求

列类型	存储需求
DATE	3个字节
DATETIME	8个字节
TIMESTAMP	4个字节
TIME	3个字节
YEAR	1个字节

 

字符串类型的存储需求

列类型	存储需求
CHAR(M)	M个字节，0 M
VARCHAR(M)	L+1个字节，其中L M 且0 M
BINARY(M)	M个字节，0 M
VARBINARY(M)	L+1个字节，其中L M 且0 M
TINYBLOB, TINYTEXT	L+1个字节，其中L 8
BLOB, TEXT	L+2个字节，其中L 16
MEDIUMBLOB, MEDIUMTEXT	L+3个字节，其中L 24
LONGBLOB, LONGTEXT	L+4个字节，其中L 32
ENUM('value1','value2',...)	1或2个字节，取决于枚举值的个数(最多65,535个值)
SET('value1','value2',...)	1、2、3、4或者8个字节，取决于set成员的数目(最多64个成员)

 

VARCHAR、BLOB和TEXT类是变长类型。每个类型的存储需求取决于列值的实际长度(用前面的表中的L表示)，而不是该类型的最大可能的大小。例如，VARCHAR(10)列可以容纳最大长度为10的字符串。实际存储需求是字符串(L)的长度，加上一个记录字符串长度的字节。对于字符串'abcd'，L是4，存储需要5个字节。

备注：这个地方是英文字符，不包含中文字符（简单地将一个中文字理解为一个字符）

对于CHAR、VARCHAR和TEXT类型，前面的表中的值L和M应解释为字符数目，并且列定义中的这些类型的长度表示字符数目。例如，要想保存一个TINYTEXT值需要L字符+ 1个字节。

要想计算用于保存具体CHAR、VARCHAR或者TEXT列值的字节数，需要考虑该列使用的字符集。在具体情况中，当使用Unicode时，必须记住所有Unicode字符使用相同的字节数。

注释：VARCHAR列的有效最大长度为65,532字符。

 

Char和varchar的对比：

CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

CHAR和VARCHAR类型声明的长度表示你想要保存的最大字符数。例如，CHAR(30)可以占用30个字符。

CHAR列的长度固定为创建表时声明的长度。长度可以为从0到255的任何值。当保存CHAR值时，在它们的右边填充空格以达到指定的长度。当检索到CHAR值时，尾部的空格被删除掉。在存储或检索过程中不进行大小写转换。

VARCHAR列中的值为可变长字符串。长度可以指定为0到65,535之间的值。(VARCHAR的最大有效长度由最大行大小和使用的字符集确定。整体最大长度是65,532字节）。

同CHAR对比，VARCHAR值保存时只保存需要的字符数，另加一个字节来记录长度(如果列声明的长度超过255，则使用两个字节)。

VARCHAR值保存时不进行填充。当值保存和检索时尾部的空格仍保留，符合标准SQL。

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

 

下面的表显示了将各种字符串值保存到CHAR(4)和VARCHAR(4)列后的结果，说明了CHAR和VARCHAR之间的差别：

值	CHAR(4)	存储需求	VARCHAR(4)	存储需求
''	'    '	4个字节	''	1个字节
'ab'	'ab  '	4个字节	'ab '	3个字节
'abcd'	'abcd'	4个字节	'abcd'	5个字节
'abcdefgh'	'abcd'	4个字节	'abcd'	5个字节

 

所以这个地方就说明以下几个问题：

1.varchar型数据和char型并不是只是存储长度可变和不可变的区别，还在于二者的存储结构上，在小于等于255的长度内，同一数据保存为varchar比保存为char多占一个字节（我理解的是用于标识是char型还是varchar型），在大于255的长度时，varchar比char多占用两个字节的存储长度。

2.就int等固定存储长度的列类型而言，不管设计数据库的时候设置的length值为多少，仍然是占用4个字节。以前总是会误以为int(3)只能存储3个长度的数字，int(11)就会存储11个长度的数字，这是大错特错的。其实当我们在选择使用int的类型的时候，不论是int(3)还是int(11)，它在数据库里面存储的都是4个字节的长度，在使用int(3)的时候如果你输入的是10，会默认给你存储位010,也就是说这个3代表的是默认的一个长度，当你不足3位时，会帮你不全，当你超过3位时，就没有任何的影响。 int(10)与int(11)有什么区别，他们之间除了在存储的时候稍微有点区别外，在我们使用的时候是没有任何区别的。int(10)也可以代表2147483647这个值int(11)也可以代表。

 

http://blog.sina.com.cn/s/blog_610997850100wjrm.html

 

二、mysql里varchar/char等设置的length是字符长度还是字节长度

 

首先谈下字符和字节的区别：

 

http://www.regexlab.com/zh/encoding.htm

 

然后来看下下面的例子：

新建表为：

CREATE TABLE `qc_questionnaire_list` (

`id` int(16) unsigned NOT NULL AUTO_INCREMENT COMMENT 'questionnaire',

`title` varchar(31) DEFAULT NULL COMMENT '问卷标题（10个字）',

`theme` varchar(127) DEFAULT NULL COMMENT '问卷主题（30个字）',

`test` varchar(2) DEFAULT NULL,

`test1` char(2) DEFAULT NULL,

`test2` int(11) DEFAULT NULL,

`test3` int(16) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

 

插入数据时test和test1都为：啊啊啊，最终都只存入：啊啊

 

 

test和test1一个为varchar，一个为char，

 

解释下length()和char_length()

再修改下数据：

 

 

length()函数返回的是字节长度，char_length()返回的是字符的长度，所以可以看出：创建表时设置的length是字符的长度，而不是字节的长度。

 

 

http://cau99.blog.51cto.com/1855224/383023/

 

 

三、utf8字符集下一个汉字占用三个字节，GBK下一个汉字占用两个字节，任何字符集下一个英文字符占用一个字节的理论依据

 

从二中也可以看出，实际上utf8下汉字是占用了三个字节，英文字符占用一个字节

 

 

mysql> SHOW CHARACTER SET;

+----------+-----------------------------+---------------------+--------+

| Charset  | Description                 | Default collation   | Maxlen |

+----------+-----------------------------+---------------------+--------+

| big5     | Big5 Traditional Chinese    | big5_chinese_ci     |      2 |

| dec8     | DEC West European           | dec8_swedish_ci     |      1 |

| cp850    | DOS West European           | cp850_general_ci    |      1 |

| hp8      | HP West European            | hp8_english_ci      |      1 |

| koi8r    | KOI8-R Relcom Russian       | koi8r_general_ci    |      1 |

| latin1   | cp1252 West European        | latin1_swedish_ci   |      1 |

| latin2   | ISO 8859-2 Central European | latin2_general_ci   |      1 |

| swe7     | 7bit Swedish                | swe7_swedish_ci     |      1 |

| ascii    | US ASCII                    | ascii_general_ci    |      1 |

| ujis     | EUC-JP Japanese             | ujis_japanese_ci    |      3 |

| sjis     | Shift-JIS Japanese          | sjis_japanese_ci    |      2 |

| hebrew   | ISO 8859-8 Hebrew           | hebrew_general_ci   |      1 |

| tis620   | TIS620 Thai                 | tis620_thai_ci      |      1 |

| euckr    | EUC-KR Korean               | euckr_korean_ci     |      2 |

| koi8u    | KOI8-U Ukrainian            | koi8u_general_ci    |      1 |

| gb2312   | GB2312 Simplified Chinese   | gb2312_chinese_ci   |      2 |

| greek    | ISO 8859-7 Greek            | greek_general_ci    |      1 |

| cp1250   | Windows Central European    | cp1250_general_ci   |      1 |

| gbk      | GBK Simplified Chinese      | gbk_chinese_ci      |      2 |

| latin5   | ISO 8859-9 Turkish          | latin5_turkish_ci   |      1 |

| armscii8 | ARMSCII-8 Armenian          | armscii8_general_ci |      1 |

| utf8     | UTF-8 Unicode               | utf8_general_ci     |      3 |

| ucs2     | UCS-2 Unicode               | ucs2_general_ci     |      2 |

| cp866    | DOS Russian                 | cp866_general_ci    |      1 |

| keybcs2  | DOS Kamenicky Czech-Slovak  | keybcs2_general_ci  |      1 |

| macce    | Mac Central European        | macce_general_ci    |      1 |

| macroman | Mac West European           | macroman_general_ci |      1 |

| cp852    | DOS Central European        | cp852_general_ci    |      1 |

| latin7   | ISO 8859-13 Baltic          | latin7_general_ci   |      1 |

| utf8mb4  | UTF-8 Unicode               | utf8mb4_general_ci  |      4 |

| cp1251   | Windows Cyrillic            | cp1251_general_ci   |      1 |

| utf16    | UTF-16 Unicode              | utf16_general_ci    |      4 |

| utf16le  | UTF-16LE Unicode            | utf16le_general_ci  |      4 |

| cp1256   | Windows Arabic              | cp1256_general_ci   |      1 |

| cp1257   | Windows Baltic              | cp1257_general_ci   |      1 |

| utf32    | UTF-32 Unicode              | utf32_general_ci    |      4 |

| binary   | Binary pseudo charset       | binary              |      1 |

| geostd8  | GEOSTD8 Georgian            | geostd8_general_ci  |      1 |

| cp932    | SJIS for Windows Japanese   | cp932_japanese_ci   |      2 |

| eucjpms  | UJIS for Windows Japanese   | eucjpms_japanese_ci |      3 |

+----------+-----------------------------+---------------------+--------+

 

可以设置字符集为GBK，然后再看下char_length,length就明白了。

 

总结：

 

1.为了加快查询和更新速度，要考虑内存对齐原则，加上空间的考虑，优先SMALLINT,INT,BIGINT,FLOAT,DOUBLE ,最后才是考虑varchar,char型。

 

2.在设计数据表的时候对于int/float/double/date等数据类型不用纠结于设定多少长度，长度只是用于显示而已，对于存储没有任何意义，varchar/char长度只需设置到最大的字数（或者字符数）就可以了，除非能够准确地判断该字段只存汉字或者只存英文（这样的可以参照内存对齐原则）。

 

最后：大家一定要多看官方文档，这篇文章里的大多数东西都是来源于官文文档。