mysql块内存储长度的研究

 

分类： Mysql/postgreSQL

 

关于mysql块内存储长度的研究。欢迎指正。。
mysql存储，一行最多存储65532字节，即所有字段加起来，长度不能超过这个值。65532字节换算下就是63.99KB(64KB=65536字节)。
BLOB and TEXT列在一行中只会占8个字节，因为它们是另外单独存储的。关于null值和非null值存储引擎不同，储的方式不同，具体参照官网。
参考:http://dev.mysql.com/doc/refman/5.0/en/column-count-limit.html

gbk:每个字符占2个字节.（最大长度是:65532/2=32766)
--验证text在行中只会占8个字节

mysql> create table t4(id varchar(32761),name text) ENGINE=InnoDB DEFAULT CHARSET=gbk;
Query OK, 0 rows affected (0.04 sec)

mysql> drop table t4;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t4(id varchar(32762),name text) ENGINE=InnoDB DEFAULT CHARSET=gbk;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs

-------------------------我是分隔线--------------------------------------
--验证一个列最长只能存储65535个字节

即65535/2=16383，另外还需要一部分空间来存储字段长度。测试如下：

mysql> create table t4(id1 varchar(16382),id2 varchar(16383)) ENGINE=InnoDB DEFAULT CHARSET=gbk;
Query OK, 0 rows affected (0.02 sec)

mysql> drop table t4;
Query OK, 0 rows affected (0.02 sec)

mysql> create table t4(id1 varchar(16383),id2 varchar(16383)) ENGINE=InnoDB DEFAULT CHARSET=gbk;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs

-------------------------我是分隔线--------------------------------------
再来看一下varchar和char的存储。
VARCHAR列中的值为可变长字符串。长度可以指定为0到65,535之间的值。(VARCHAR的最大有效长度由最大行大小和使用的字符集确定。整体最大长度是65,532字节）。
65532/2=32766

mysql> create table t4(id varchar(32766)) ENGINE=InnoDB DEFAULT CHARSET=gbk;
Query OK, 0 rows affected (0.05 sec)

mysql> desc t4;
+-------+----------------+------+-----+---------+-------+
| Field | Type           | Null | Key | Default | Extra |
+-------+----------------+------+-----+---------+-------+
| id    | varchar(32766) | YES  |     | NULL    |       |
+-------+----------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> drop table t4;
Query OK, 0 rows affected (0.02 sec)

mysql> create table t4(id varchar(32767)) ENGINE=InnoDB DEFAULT CHARSET=gbk;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs

char最长支持为255

mysql>  create table t4(id char(255)) ENGINE=InnoDB DEFAULT CHARSET=gbk;
Query OK, 0 rows affected (0.05 sec)

mysql> desc t4;
+-------+-----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| id    | char(255) | YES  |     | NULL    |       |
+-------+-----------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql>  create table t5(id char(256)) ENGINE=InnoDB DEFAULT CHARSET=gbk;
ERROR 1074 (42000): Column length too big for column 'id' (max = 255); use BLOB or TEXT instead

---------------------我是分隔线---------------------------------------------------------------
BLOB 列被视为二进制字符串(字节字符串)。TEXT列被视为非二进制字符串(字符字符串)。BLOB列没有字符集，并且排序和比较基于列值字节的数值值。
--关于text和varchar的区别
找了下资料，发现5.0.3版本之前，mysql varchar最大支持长度是255,那时候需要大于255的字段，只能使用text来代替。
5.0.3版本之后，varchar和text的支持长度都是65535，那么使用哪种类型比较好呢？
我们都知道,varchar和数据行本身都是存储在同一个数据块内的，如果能在一个行中放下，使用varchar当然可以做到I/O最少。但是如果不能放少，那么只能用text。
此外,TEXT列有一个字符集，并且根据字符集的 校对规则对值进行排序和比较。
varchar:如果插入内容后面有多余的空格，不会储存，节省空间。
一般描述性文字比较多的时候，尽量使用text。使用text，在进行模糊查询的时候，并不会影响速度的。
仅仅最前面的max_sort_length 字节是排序的， max_sort_length 默认值是1024，可以在session级别改变这个值的设置。
mysql> SET max_sort_length = 2000;
mysql> SELECT id, comment FROM t
    -> ORDER BY comment;

另外还有许多因素的不同:text的限制比较多（类似oracle的clob），比如text不能有默认值,索引时必需要指定一个前缀长度。
参考:http://dev.mysql.com/doc/refman/5.1/en/blob.html


其实在mysql中，各个字段都是一种映射关系，mysql对此都是兼容的。因为在很多类型之前存在映射的关系，mysql类型的限制很宽松，所以建了表之后，最好查看下字段的类型。
如:

mysql> create table t3(id varchar(65500));
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> desc t3;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | mediumtext | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql>  create table t4(id varchar(30000)) ENGINE=InnoDB DEFAULT CHARSET=gbk;
Query OK, 0 rows affected (0.05 sec)

mysql> desc t4;
+-------+----------------+------+-----+---------+-------+
| Field | Type           | Null | Key | Default | Extra |
+-------+----------------+------+-----+---------+-------+
| id    | varchar(30000) | YES  |     | NULL    |       |
+-------+----------------+------+-----+---------+-------+
1 row in set (0.00 sec)

创建text类型的表

mysql>  create table tt(name text) ENGINE=InnoDB DEFAULT CHARSET=gbk;
Query OK, 0 rows affected (0.04 sec)

mysql> desc tt;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| name  | text | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
1 row in set (0.00 sec)


mysql> create table tt(name text(65532)) ENGINE=InnoDB DEFAULT CHARSET=gbk;
Query OK, 0 rows affected (0.05 sec)

mysql> desc tt;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| name  | mediumtext | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.01 sec)


mysql> drop table tt;
Query OK, 0 rows affected (0.02 sec)

mysql> create table tt(name text(30000)) ENGINE=InnoDB DEFAULT CHARSET=gbk;
Query OK, 0 rows affected (0.05 sec)

mysql> desc tt;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| name  | text | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
1 row in set (0.00 sec)