mysql> create table t4(c int, c2 char(30), c3 varchar(21812)) charset=utf8; Query OK, 0 rows affected (0.05 sec) mysql>
mysql> create table t5(c int, c2 char(30), c3 varchar(21813)) charset=utf8; ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs mysql>
Finally, let's take an example
CREATE TABLE t6 ( id int, a VARCHAR(100) DEFAULT NULL, b VARCHAR(100) DEFAULT NULL, c VARCHAR(100) DEFAULT NULL, d VARCHAR(100) DEFAULT NULL, e VARCHAR(100) DEFAULT NULL, f VARCHAR(100) DEFAULT NULL, g VARCHAR(100) DEFAULT NULL, h VARCHAR(100) DEFAULT NULL, i VARCHAR(N) DEFAULT NULL ) CHARSET=utf8;
So what is the maximum value of varchar(N) in the above statement?
Let's calculate it
Each NULL field is identified by 1bit, and the 10 fields are default null, so (10+7)/8bit = 2 bytes is required to store the NULL identification bits. int takes up 4 bytes.
(65535 - 1 - 2*8 -4 - 100*3*8 -2)/3=21037
mysql> CREATE TABLE t6 ( id int, a VARCHAR(100) DEFAULT NULL, b VARCHAR(100) DEFAULT NULL, c VARCHAR(100) DEFAULT NULL, d VARCHAR(100) DEFAULT NULL, e VARCHAR(100) DEFAULT NULL, f VARCHAR(100) DEFAULT NULL, g VARCHAR(100) DEFAULT NULL, h VARCHAR(100) DEFAULT NULL, i VARCHAR(21037) DEFAULT NULL ) CHARSET=utf8; Query OK, 0 rows affected (0.01 sec) mysql>
mysql> CREATE TABLE t7 ( id int, a VARCHAR(100) DEFAULT NULL, b VARCHAR(100) DEFAULT NULL, c VARCHAR(100) DEFAULT NULL, d VARCHAR(100) DEFAULT NULL, e VARCHAR(100) DEFAULT NULL, f VARCHAR(100) DEFAULT NULL, g VARCHAR(100) DEFAULT NULL, h VARCHAR(100) DEFAULT NULL, i VARCHAR(21038) DEFAULT NULL ) CHARSET=utf8; ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs mysql>
You can see that one more character has been reported as an error.
How many characters can varchar save? This is related to the character set used. Latin1, gbk, and utf8 encodes need to store a character of 1, 2, and 3 bytes respectively.
3. Varchar physical storage
On physical storage, varchar uses 1 to 2 additional bytes to represent the actual stored string length (bytes). If the maximum length of the column is less than 256 bytes, it is represented by one byte (identified). If the maximum length is greater than or equal to 256, use two bytes.
When the selected character set is latin1, one character takes up one byte
varchar(255) stores a character, and a total of 2 bytes physical spaces are used to store the actual data length and data value of the data.
varchar(256) stores a character, using 2 bytes to represent the actual data length, and a total of 3 bytes are required for physical storage space.
varchar has a disagreeable physical storage method for different RDBMS engines, although it has a unified logical significance. For different storage engines of mysql, their implementation methods are also different from the physical storage methods of data.
4. varchar in InnoDB
The physical storage method of varchar in InnoDB is related to the innodb_file_format used by InnoDB. The Antelope file format used by the early innodb_file_forma supports two row_formats, redundant and compact. Starting from 5.5 or InnoDB1.1, a new file format, Barracuda can be used. Barracuda is compatible with Redundant, and also supports two row_formats, dynamic and compressed.
When innodb_file_format=Antelope, ROW_FORMAT=REDUNDANT or COMPACT.
The cluster index of innodb only stores the first 768 bytes of the varchar, text, and blob fields. The excess bytes are stored in an independent overflow page. This column is also called off-page. The 768-byte prefix is followed by a 20-byte pointer, pointing to the location of the overflow pages.
In addition, in the case of innodb_file_format=Antelope, up to 10 large fields can be stored in InnoDB (need to use off-page storage). The default page size of innodbd is 16KB. The length of a single line of InnoDB cannot exceed 16k/2=8k bytes, (768+20)*10 < 8k.
When innodb_file_format=Barracuda, ROW_FORMAT=DYNAMIC or COMPRESSED
Whether all varchar, text, and blob fields in innodb are completely stored off-page depends on the length of the field and the total length of the entire row. For the columns stored in the off-page, the cluster index only stores a 20-byte pointer, pointing to the actual overflow page storage location. If the length of a single row is too large to fully adapt to the cluster index page, innodb will select the longest column as the off-page storage until the length of the row can adapt to the cluster index page.
5. varchar in MyISAM
For the MyISAM engine, all data in the varchar field is stored in the data line (in-line). The row_format of the myisam table also affects the physical storage behavior of varchar.
MyISAM row_format can be set to fixed and dynamic through create or alter sql statements. In addition, the storage format of row_format=compress can be generated through myisampack.
When there is no field of text or blob type in the myisam table, then row_format can be set to fixed (or dynamic), otherwise it can only be dynamic.
When the varchar field exists in the table, row_format can be set to fixed or dynamic. Use row_format=fixed to store varchar field data, waste storage space, varchar will be stored in fixed length at this time. row_format is fixed and dynamic, and the physical implementation methods of varchar are also different (you can view the source code files and), so when myisam's row_format converts between fixed and dynamic, the physical storage method of varchar field will also change.
References:
/doc/refman/5.5/en/
<<Insider of MySQL Technology--InnoDB Engine Second Edition>>