Mysql欄位長度限制你真的瞭解嗎？

標籤：mysql   字元類型   欄位長度   

   一行中可以容納多少欄位長度？每個欄位擁有長度又可以是多少？是否資料類型的限制長度固定不變？帶著這幾個問題，我們開始進行一系列研究。

一、行容納的欄位長度

   眾所周知，記錄是以行的形式進行儲存的，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) )charset=latin1;Query OK, 0 rows affected (0.00 sec)

可以看出來65531+1=65532<=65532(65535-1-2， -1是因為一個位元組不存資料，-2是因為兩個位元組varchar的長度)

mysql> 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)

本文出自 “業精於勤而荒於嬉” 部落格，轉載請與作者聯絡！

Mysql欄位長度限制你真的瞭解嗎？