What is the maximum length of mysql VARCHAR

Previously 1 always thought that there were two bytes to record the length (a small length can also be recorded by one byte), so this problem was quite boring at that time, but later someone in the group gave an explanation and suddenly realized that things were not so simple

MYSQL COMPACT format, each record has 1 byte to represent the NULL field distribution. If a field is allowed to be empty in the table, the maximum can only be set to 65532. If no field is allowed to be empty, that byte can be saved, and the maximum can be defined to 65533

So I looked at some information on the Internet and did some local experiments. It turns out that the maximum length of vachar is really variable (depending on whether there are non-empty fields).
We did a local experiment, innodb+latin environment

 
-- success 
drop table if exists test; 
create table test(name varchar(65533) not null)engine=innodb DEFAULT CHARSET=latin1 
-- too large 
drop table if exists test; 

create table test(name varchar(65533))engine=innodb DEFAULT CHARSET=latin1
For the second case, the length of 65533 cannot be added when empty fields are allowed, and the maximum length can only be 65532, which should be the quotation.

Also somebody did a similar experiment, online reference http: / / stackoverflow com/questions / 8295131 / best - practise - for sql - varchar column -- length

 
name varchar(100) not null will be 1 byte (length) + up to 100 chars (latin1) 
name varchar(500) not null will be 2 bytes (length) + up to 500 chars (latin1) 
name varchar(65533) not null will be 2 bytes (length) + up to 65533 chars (latin1) 
name varchar(65532) will be 2 bytes (length) + up to 65532 chars (latin1) + 1 null byte 

In summary 1, although the maximum length of mysql's vachar field is 65535, it cannot store so much data. The maximum length can be 65533 (when non-empty fields are not allowed), and it can only be 65532 when non-empty fields are allowed.

The following are additional notes from other netizens:

This is not a fixed number. This article briefly explains the following restriction rules.

strlen calculates the length of a string, 1 Chinese character as 2 characters
According to its character encoding mode, mb_strlen statistics character quot

count calculates the number of elements in an array or the number of attributes in an object

 
<?php 
header('Content-Type:text/html;charset=UTF-8'); 
$string1=" Xie Chunye ";// Define Chinese character variables  
$string2="xcy";// Define English character variables  
// Output directly to see how long they are  
echo strlen($string1); 
echo "</br>"; 
echo strlen($string2); 
echo "</br>"; 
// with  php  Multibyte extension function  mb_strlen Give it a try  
echo mb_strlen($string1,'utf8'); 
echo "</br>"; 
echo mb_strlen($string2,'utf8'); 
echo "</br>"; 
?> 

The output is:
9
3
3
3

1. Restrictive rules
The restrictions on fields are defined with the following rules:
a) storage limits
The varchar field stores the actual content separately from the clustered index, and the content begins with 1 to 2 bytes representing the actual length (2 bytes if the length exceeds 255), so the maximum length cannot exceed 65535.
b) encoding length limitation
If the character type is gbk, each character takes up to 2 bytes, and the maximum length cannot exceed 32766.
If the character type is utf8, each character takes up to 3 bytes and the maximum length cannot exceed 21845.
For forums with more English, GBK takes 2 bytes per character, while UTF-8 takes only 1 byte.
If this limit is exceeded, the varchar field is forced to be of type text and warning is generated.
c) length limit
What causes the varchar length limitation in practice is the length defined by one line. MySQL requires that a line be defined no longer than 65535. If the defined table is longer than this value, it is prompted
ERROR 1118 (42000) : Row size too large. The maximum row for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs.
2. Calculation examples
Take two examples to illustrate the calculation of actual length 1.
a) if a table has only one varchar type, as defined by
create table t4(c varchar(N)) charset=gbk;
Then the maximum value of N here is (65535-1-2)/2= 32766.
The reason for minus 1 is that the actual row storage starts at the second byte ';
The reason for the minus 2 is that the 2 bytes in the head of varchar represent the length;
The reason for dividing by 2 is that the character encoding is gbk.

b) if 1 table is defined as
create table t4(c int, c2 char(30), c3 varchar(N)) charset=utf8;
The maximum value of N here is (65535-1-2-4-30*3)/3=21812
Minus 1 and minus 2 are the same;
The reason for minus 4 is that c of type int takes 4 bytes;
The reason for the minus 30*3 is that char(30) takes 90 bytes and the encoding is utf8.
If varchar exceeds the b rule above and is strongly converted to text type, then each field occupies a defined length of 11 bytes, which is no longer "varchar".