mysql中varchar型別最大長度測試

1.先看字符集為latin1時，每個字元應該是佔據一個byte
mysql> create table test(a varchar(65535)) engine=innodb charset=latin1;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

mysql> create table test(a varchar(65533)) engine=innodb charset=latin1;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

mysql> create table test(a varchar(65532)) engine=innodb charset=latin1;
Query OK, 0 rows affected (0.01 sec)

2.再看字符集為GBK時，每個字元應該佔據2個byte
mysql> drop table test;
Query OK, 0 rows affected (0.00 sec)

mysql> create table test(a varchar(32767)) engine=innodb charset=gbk;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

mysql> create table test(a varchar(32766)) engine=innodb charset=gbk;
Query OK, 0 rows affected (0.00 sec)

3.當字符集為UTF8時，每個字元佔據了3個byte
mysql> drop table test;
Query OK, 0 rows affected (0.00 sec)

mysql> create table test(a varchar(65535)) engine=innodb charset=utf8;
ERROR 1074 (42000): Column length too big for column 'a' (max = 21845); use BLOB or TEXT instead

mysql> create table test(a varchar(21845)) engine=innodb charset=utf8;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

mysql> create table test(a varchar(21844)) engine=innodb charset=utf8;
Query OK, 0 rows affected (0.00 sec)


最後，如果表中所有varchar型別的列長度總和超過了65535，建立表時也會報錯
mysql> drop table test;
Query OK, 0 rows affected (0.00 sec)

mysql> create table test(a varchar(22000),b varchar(22000),c varchar(22000)) 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. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10972173/viewspace-2153140/，如需轉載，請註明出處，否則將追究法律責任。