Chinaunix首页 | 论坛 | 博客
  • 博客访问: 689130
  • 博文数量: 95
  • 博客积分: 1754
  • 博客等级: 上尉
  • 技术积分: 1607
  • 用 户 组: 普通用户
  • 注册时间: 2010-03-12 10:06
文章分类

全部博文(95)

文章存档

2015年(3)

2013年(15)

2012年(77)

分类: Mysql/postgreSQL

2012-06-08 22:59:12

关于INNODB存储引擎varchar类型分析

 //下面用来存储varchar(65534)字段,用6个页来存储,实际上已经转存为mediumtext。

root@test 09:42>create table t(a varchar(65534));

Query OK, 0 rows affected, 1 warning (0.09 sec)

 

root@test 09:43>desc t;

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

| Field | Type       | Null | Key | Default | Extra |

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

| a     | mediumtext | YES  |     | NULL    |       |

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

1 row in set (0.00 sec)

 

[root@oradba mysql]# ./py_innodb_page_info.py /data/dbdata/test/t.ibd

Total number of page: 6:

Freshly Allocated Page: 2

Insert Buffer Bitmap: 1

File Space Header: 1

B-tree Node: 1

File Segment inode: 1

 

 

innodb存储引擎并不支持65535长度的varchar,由于有另外的开销,实际存放长度为65532.这个长度大小与字符集有关。下面分析utf8和latin1两种类型的字符。

1,t3innodb引擎,字符集为utf8,长度超过21844,就会导致导致varchar变为mediumtext

 

//varchar类型转为mediumtext

root@test 09:42>create table t(a varchar(21846));

Query OK, 0 rows affected, 1 warning (0.09 sec)

 

root@test 10:18>show create table t3;

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

|.Table.| CreateTable                                             |

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

| t3   | CREATE TABLE `t3` (

  `a` mediumtext

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

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

1 row in set (0.00 sec)

 

 

//创建t3表没有转为varchar类型

root@test 10:11>create table t3(a varchar(21844));         

Query OK, 0 rows affected (0.09 sec)

 

root@test 10:17>desc t3;                          

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

| Field | Type           | Null | Key | Default | Extra |

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

| a     | varchar(21844) | YES  |     | NULL    |       |

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

1 row in set (0.00 sec)

分析t3表占用页数为6页。

[root@oradba test]# /share/softwares/mysql/py_innodb_page_info.py t3.ibd
Total number of page: 6:
Freshly Allocated Page: 2
Insert Buffer Bitmap: 1
File Space Header: 1
B-tree Node: 1
File Segment inode: 1

 

 

//超过21844个字符就会导致varchar变为mediumtext

root@test 10:17>create table t3(a varchar(21846));

Query OK, 0 rows affected, 1 warning (0.11 sec)

 

root@test 10:17>desc t3;

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

| Field | Type       | Null | Key | Default | Extra |

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

| a     | mediumtext | YES  |     | NULL    |       |

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

1 row in set (0.00 sec)

 

2,如果字符集为latin1,那么就会varchar的长度设置为65532,超过该值是否能创建处决与你SQL_MODE

root@test 10:19>create table t4(a varchar(65532)) charset=latin1 engine=innodb;

Query OK, 0 rows affected (0.10 sec)

 

root@test 10:23>desc t4;

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

| Field | Type           | Null | Key | Default | Extra |

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

| a     | varchar(65532) | YES  |     | NULL    |       |

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

1 row in set (0.00 sec)

 

root@test 10:23>drop table t4;

Query OK, 0 rows affected (0.13 sec)

//无法创建

root@test 10:24>create table t4(a varchar(65534)) 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. You have to change some columns to TEXT or BLOBs

 

当数据没有发生行溢出时,数据存放在B-tree NODE的页类型中,存放行溢出的页类型为uncompress BLOB page类型。

阅读(2395) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~