MySQL数据类型char和varchar的区别

查看MySQL支持的字符集：
show character set;
查看MySQL支持的排序规则：
show collation;

varchar在MySQL中的存储：
mysql定义varchar数据类型的长度是0到65535，注意单位是字节。但是varchar后面的N代表的却是字符，字符就会存在字符集的概念。同时65535也是varchar类型的最大字节长度 (65,535 bytes, which is shared among all columns)，并且同一个表的所有的varchar列长度字节的总和不能超过65535.实际测试要比这个值还要小，原因是还有别的开销。

如果是latin1字符集，那么每个字符占用1个字节，那么varchar后面的N最大可以设置为65532；

mysql> create table t (name varchar(65535)) charset latin1;
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> create table t (name varchar(65532)) charset latin1;
Query OK, 0 rows affected (0.01 sec)

如果是utf8字符集，那么每个字符占用3个字节，那么varchar后面的N就只能设置为21844，创建varchar字段的时间会提示最大可以设置为21845，并不能设置这么大。

mysql> create table t (name varchar(65533)) charset utf8;
ERROR 1074 (42000): Column length too big for column 'name' (max = 21845); use BLOB or TEXT instead
mysql> select 21845*3;
+---------+
| 21845*3 |
+---------+
|   65535 |
+---------+
1 row in set (0.00 sec)
mysql> create table t (name varchar(21845)) charset utf8;
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> create table t (name varchar(21844)) charset utf8;
Query OK, 0 rows affected (0.01 sec)

如果是utf8mb4字符集，那么每个字符占用4个字节，那么varchar后面的N最大只能设置为16383.

mysql> create table t (name varchar(21844)) charset utf8mb4;
ERROR 1074 (42000): Column length too big for column 'name' (max = 16383); use BLOB or TEXT instead
mysql> create table t (name varchar(16383)) charset utf8mb4;
Query OK, 0 rows affected (0.02 sec)

char在MySQL中的存储：
char数据类型的长度是0到255，单位是字符，无论字符集是utf8、utf8mb4、latin1，N的取值范围最大都是255，那么char实际也是变长的，不同的字符集存储需要的字节也是不同的，utf8是3个字节，utf8mb4是4个字节，latin1是占用1个字节。

mysql> create table t (name char(255)) charset utf8;
Query OK, 0 rows affected (0.02 sec)
mysql> create table t (name char(255)) charset utf8mb4;
Query OK, 0 rows affected (0.02 sec)
mysql> create table t (name char(255)) charset latin1;
Query OK, 0 rows affected (0.02 sec)

下面通过创建表的方式来验证上面的定义：

	varchar类型：
	mysql> create table d (name varchar(65535)) charset=latin1 engine=innodb;		#65535报错，too large。
		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> create table d (name varchar(65532)) charset=latin1 engine=innodb;		#调整成65532创建成功。注意此时的charset是latin1的编码。
		Query OK, 0 rows affected (0.18 sec)
	mysql> create table d (name varchar(65532)) charset=utf8 engine=innodb;			#改变字符编码是utf8时，创建表报错（utf8一个字符占用3个字节）
		ERROR 1074 (42000): Column length too big for column 'name' (max = 21845); use BLOB or TEXT instead
	mysql> create table d (name varchar(65532)) charset=gbk engine=innodb;			#改变字符编码是gbk时，创建表报错（gbk编码一个字符占用2个字节），再次验证varchar长度的单位是字节非字符
		ERROR 1074 (42000): Column length too big for column 'name' (max = 32767); use BLOB or TEXT instead
	mysql> select 21845*3;
	+---------+
	| 21845*3 |
	+---------+
	|   65535 |
	+---------+
	1 row in set (0.01 sec)
	
	mysql> select 32767*2;
	+---------+
	| 32767*2 |
	+---------+
	|   65534 |
	+---------+
	1 row in set (0.00 sec)
	
	mysql> create table d (name varchar(32767)) charset=gbk engine=innodb;	#即使创建表的列的长度指定为报错提示的值，还是无法创建，因为还有别的开销
		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> create table d (name varchar(32762)) charset=gbk engine=innodb;
		Query OK, 0 rows affected (0.20 sec)
	mysql> create table d (name varchar(32766)) charset=gbk engine=innodb;
		Query OK, 0 rows affected (0.16 sec)
	mysql> create table d (name varchar(21845)) charset=utf8 engine=innodb;
		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> create table d (name varchar(21844)) charset=utf8 engine=innodb;
		Query OK, 0 rows affected (0.14 sec)
	mysql> create table d (name1 varchar(65532),name2 varchar(65532)) charset=latin1 engine=innodb;		#创建两列指定长度，报错
		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> select 65532/2;
	+------------+
	| 65532/2    |
	+------------+
	| 32766.0000 |
	+------------+
	1 row in set (0.00 sec)
	mysql> create table d (name1 varchar(32766),name2 varchar(32766)) charset=latin1 engine=innodb;		#通过65532/2的值指定列的长度，报错，应该还有别的开销
		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> create table d (name1 varchar(32765),name2 varchar(32765)) charset=latin1 engine=innodb;		#验证，每个列再减去一个字符长度就可以创建了
	Query OK, 0 rows affected (0.12 sec)
	
	char类型：无论编码是utf8还是gbk都可以创建，255指的是字符而不是字节长度。
	mysql> create table d (name char(255)) charset=utf8 engine=innodb;
		Query OK, 0 rows affected (0.16 sec)
	mysql> create table d (name char(255)) charset=gbk engine=innodb;
		Query OK, 0 rows affected (0.15 sec)

这个地方还有一个行溢出的问题，详看MySQL技术内幕 innodb存储引擎第二版4.3.3第123页。