Mysql字段长度限制你真的了解吗？

cike0415

　　一行中可以容纳多少字段长度？每个字段拥有长度又可以是多少？是否数据类型的限制长度固定不变？带着这几个问题，我们开始进行一系列研究。
　　一、行容纳的字段长度
　　众所周知，记录是以行的形式进行保存的，Mysql5.1以后，行的保存格式默认为Compact格式。行记录Compact格式为：
变长字段NULL标志位记录头信息列1数据列2数据列3数据...　　第一个变长字段是记录这行的总字段长度，如果行记录的字段总长小于255字节，变长字段就占一个字节（一个字节有8位，8位的二进制最多能表示到255）。当大于255时，变长字段的长度就是两个字节。Mysql规定变成字段不超过两个字节，就意味着行的段总长最多不能超过65535个字节（两个字节有16位，16位最多能表示65535）
mysql> create table test_limit (idvarchar(65531),name char(1) )　　
Query OK, 0 rows affected (0.00 sec)
　　可以看出来65531+1=65532 create table test_limit  (id varchar(65531),name char(2))charset=latin1;　　
ERROR 1118 (42000): Row size too large. Themaximum row size for the used table type, not counting BLOBs, is 65535. Thisincludes storage overhead, check the manual. You have to change some columns toTEXT or BLOBs
　　65533>65532所以不被允许
　　二、每个字段容纳长度
　　每个字段长度首先要符合字符类型限制的长度，然后再看是否符合行的段总长。
　　值得注意的是，对于varchar的最大长度来说要根据字符集而变化。
mysql> create table varchar_var (idvarchar(65531)) charset=utf8;　　
ERROR 1074 (42000): Column length too bigfor column 'id' (max = 21845); use BLOB or TEXT instead
　　Utf8中一个字符相当于3个字节，所以需要65 536/3≈21845
mysql> create table varchar_var (idvarchar(41843),name int )charset=gbk;　　
ERROR 1074 (42000): Column length too bigfor column 'id' (max = 32767); use BLOB or TEXT instead
　　Gbk中一个字符相当于2个字节，65 536/2≈32767
mysql> create table varchar_var (idvarchar(75536) )charset=latin1;　　
ERROR 1074 (42000): Column length too bigfor column 'id' (max = 65535); use BLOB or TEXT instead
　　latin1是一个字符相当于一个字节
mysql> create table extend_char (namechar(1));　　

　　
mysql> insert into extend_char values ('的我');
　　
ERROR 1406 (22001): Data too long forcolumn 'name' at row 1
　　可见，char(1)只保存一个字符，在存储中utf8字符集的占3个字节
　　int固定占的是4个字节，4个8位二进制所以表示范围为-2 147 483 648~2147 483 647。
　　既然是固定占4个字节，那么Int(1),int(2)在存储空间上就差别不大。唯一的区别在于当你设置zerofill才能显现，他会用0补足显示宽度：
mysql> create table extend_int_zero(name int zerofill) charset=latin1;　　
Query OK, 0 rows affected (0.02 sec)
　　

　　
mysql> insert into extend_int_zerovalues (1);
　　
Query OK, 1 row affected (0.01 sec)
　　

　　
mysql> select * from extend_int_zero;
　　
+------------+
　　
| name      |
　　
+------------+
　　
| 0000000001 |
　　
+------------+
　　当int不写显示宽度时默认为int(11)
　　三、实战分析
mysql> create table row_for (idvarchar(21843),name int );　　
　　这是在utf8字符集下的，你看看这句是否会执行成功呢？
　　21843*3=65529##utf8中一个字符等于三个字集
　　65529+4=65533##int占4个字节
　　65533+1+2=65535##前面提及的，一个字节不存数据，两个字节存放长度
　　所以这一行的总字段长度65535,，因为65535正好超过了行的段规定长度所以不被允许。
　　如果将varchar类型下调一个字符，那就正确了
mysql> create table row_d (idvarchar(21842),name int );　　
Query OK, 0 rows affected (0.02 sec)