深入理解MySQL varchar类型

关于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，t3为innodb引擎，字符集为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类型。