SLTechnology News&Howtos › Database ›

How to test the maximum length of varchar type in mysql

Shulou Source: shulou.com Published: 2022-05-31 19:22:55 09月29日 Update

This article shows you how to test the maximum length of varchar type in mysql. The content is concise and easy to understand. It will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

1. First, when the character set is latin1, each character should occupy a 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)

two。 When the character set is GBK, each character should occupy 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. When the character set is UTF8, each character occupies 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)

Finally, if the sum of the column lengths of all varchar types in the table exceeds 65535, an error will also be reported when the table is created

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

The above is how to test the maximum length of varchar type in mysql. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.