Length of varchar field

Mysql> CREATE TABLE t0008 (col1 varchar (65535)) 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 are have to change some columns to TEXT or BLOBs
Mysql> CREATE TABLE t0008 (col1 varchar (65534)) 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 are have to change some columns to TEXT or BLOBs
Mysql> CREATE TABLE t0008 (col1 varchar (65533)) 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 are have to change some columns to TEXT or BLOBs
Mysql> CREATE TABLE t0008 (col1 varchar (65532)) charset=latin1;
Query OK, 0 rows affected (0.02 sec)

The table varchar in the latin1 character set is capped at 65532, a character one byte

Mysql> CREATE TABLE t0009 (col1 varchar (65533)) Charset=utf8;
ERROR 1074 (42000): Column length too big for Column ' col1 ' (max = 21845); Use BLOB or TEXT instead
Mysql> CREATE TABLE t0009 (col1 varchar (21845)) 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 are have to change some columns to TEXT or BLOBs
Mysql> CREATE TABLE t0009 (col1 varchar (21844)) Charset=utf8;
Query OK, 0 rows Affected (0.00 sec)

The table varchar in the UTF8 character set is capped at 21844, that is, a character of three bytes 65535-1-2 The result divided by 3 ==21844

-1 indicates that the first byte does not contain data,-2 means that two bytes hold the length of the varchar, divided by 3 is the UTF8 character attribute, and one character is three bytes.

The varchar field is to store the actual content separately from the clustered index, with 1 to 2 bytes at the beginning of the content representing the actual length (2 bytes in length over 255), so that the maximum length cannot exceed 65535 of 2 (16)

VARCHAR: variable length , maximum 65,535 characters (both a single column limit and a full line limit)

Mysql> CREATE TABLE t0012 (ID int,name char (), col3 varchar (N)) Chaset=utf8;

The value of n is: (65535-1-2-4-20*3)/3=21822

Mysql> CREATE TABLE t0012 (ID int,name char (), col3 varchar (N)) charset=latin1;

The value of n is: 65535-1-2-4-20=65508

Length (): Return the length of a string in bytes. Returns the number of bytes (affected by encoding) of the string.

UTF8 code, a Chinese character is three bytes, a number or letter for one byte. In other encodings, a Chinese character occupies two bytes, and a number or letter occupies one byte.

Char_length (): Return count of characters in argument. Returns the number of characters in a string. In any code, a character or a number, or a letter.

Example:

Mysql> Show create table T2;
+-------+------------------------------------------------------------------------------------------------------ -------------------+
| Table | Create Table                                                                                                            |
+-------+------------------------------------------------------------------------------------------------------ -------------------+
| T2    | CREATE TABLE ' T2 ' (
  ' id ' int () default null,
  ' name ' varchar () default NULL
) Engine=innodb default CHARSET =utf8 |
+-------+----------------------------
INSERT into T2 (id,name) VALUES (1, ' Danny ');
INSERT into T2 (id,name) VALUES (2, ' Dandan ');
SELECT id,name,char_length (name), LENGTH (name) from T2;
    The ID  name    char_length (name)  length (name)  
------  ------  -----------------  ---- ----------
     1  Danny                      2               6
     2  Dandan                  6               6

Char and varchar comparisons: char: fixed length, (indexed) highly efficient, no fragmentation. all fixed-length strings or similar fixed-length strings are all char. such as ID number, cell phone number, bank card number, MD5, hash value, etc. this is the string is fixed length, there is no doubt with Char, there is a class of basic fixed length but slightly different, such as the Chinese name, the general length may be 2~5 a Chinese character, This type of information is also ideal for storing with char, as long as the allocation is slightly larger than the usual length. The
varchar is a variable-length string, which simply says that when we define the table body, the specified field length is the maximum length, and when the string does not reach its maximum length, it is stored in the actual length of the string and does not occupy the extra storage space. Thus, in general, varchar saves storage space than char, but when the modified data is larger than the current storage length, it is fragmented

-This article is from: MySQL max field number and varchar type summary, MySQL char, Some personal understanding of Varchar,text,blob