MySQL Build table field length limit

Brain Tonic, varchar (n), n refers to the maximum number of characters, not the number of bytes. First, the test instructions:

When MySQL builds a table, it encounters a strange phenomenon:

Root@localhost: Test10:30:54>CREATETABLETb_test (RecordIDvarchar32)NotNull,Areashowvarchar10000)DEFAULTNull,AreaShow1varchar10000)DEFAULTNull,AreaShow2varchar10000)DEFAULTNull,-PRIMARYKEY(RecordID)) ENGINE=innodbDEFAULT CHARSET=UTF8;ERROR 1118 (42000): Row size too large. The maximum row sizeFor the UsedTable Type,Not counting BLOBs,Is65535.To changeSome columnsToTEXTOrBLOBsError
Root@localhost: Test10:31:01>CREATETABLETb_test (RecordIDvarchar32)NotNull,Areashowvarchar30000)DEFAULTNull,AreaShow1varchar30000)DEFAULTNull,AreaShow2varchar30000)DEFAULTNull,-PRIMARYKEY(RecordID)) ENGINE=innodbDEFAULT CHARSET=UTF8; Query OK,0 rows affected,3 Warnings (0.26Seccan establish，just the type was converted. 
Root@localhost: Test10:31:14>Showwarnings; +-------+------+----------------------------------------------------+|Level| Code| Message|+-------+------+----------------------------------------------------+| Note|1246| ConvertingColumn‘Areashow‘FromVARCHARToTEXT|| Note|1246| ConvertingColumn‘AreaShow1‘FromVARCHARToTEXT| | Note | 1246 | Converting column   ' from  VARCHAR to text |+-------+------+---------------------------------- ------------------+3 rows in set (

Questions:

Why the field is small (10000) instead of error, while large (30000) can be established. Why the small cannot be converted directly?

Solve:

Thanks to Orczhou's help here, MySQL has a limitation when it comes to building a table: MySQL requires a line definition to be no longer than 65535. The specific reasons can be seen:

Http://dev.mysql.com/doc/refman/5.1/en/silent-column-changes.html

(1) If a single field is greater than 65535, it is converted to text.

(2) Single line maximum limit is 65535, this does not include text, BLOB.

In accordance with the limitations summarized above, to explain the occurrence of the phenomenon:

The first case is:
Single Field length: varchar (10000), Bytes: 10000*3 (UTF8) + (1 or 2) = 30000, less than 65535, can be established.
Single-row record length: varchar (10000) *, Bytes: 30000*3 (UTF8) + (1 or 2) = 90000, greater than 65535, cannot be established, so error:

1118 (or BLOBs

The second situation is:
Single Field length: varchar (30000), Bytes: 30000*3+ (1 or 2) = 90000, greater than 65535, required to be converted to text before it can be established. So the newspaper warnings.
Single-row record length: varchar (30000) * Because each field is converted to text and text is not limited, you can create a table.

Root@localhost: Test10:31:14>Show warnings;+-------+------+----------------------------------------------------+|Level| Code| Message|+-------+------+----------------------------------------------------+| Note|1246| ConvertingColumn‘Areashow‘FromVARCHARToTEXT|| Note|1246| ConvertingColumn‘AreaShow1 ' from varchar to text || Note | 1246 | Converting column   ' from  VARCHAR to text |+-------+------+---------------------------------- ------------------+ 

With so long MySQL, this basic building restrictions are not yet known, ashamed AH.

The reasons are as follows:

Asked a question: what is the maximum length of varchar in MySQL? This is not a fixed number. This article briefly describes the restriction rules.

1. Restriction Rules

The restriction of a field has the following rules when the field is defined:

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 in length over 255), so the maximum length cannot exceed 65535.

b) Encoding length limit

If the character type is GBK, each character can be up to 2 bytes, and the maximum length cannot exceed 32766.

If the character type is UTF8, each character can be up to 3 bytes and the maximum length cannot exceed 21845.

If the limit above is defined, the varchar field is forcibly converted to the text type and generates warning.

c) Limit of the length of the president

The length of a row definition is the limit of the varchar length in the actual application. MySQL requires that a row's definition length cannot exceed 65535. If the defined table length exceeds this value, the prompt

ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have the to change some columns to TEXT or BLOBs.

2. Calculation Examples

Give two examples to illustrate the actual length of the calculation.

A) If a table has only one varchar type, as defined as

CREATE table t4 (c varchar (N)) CHARSET=GBK;

The maximum value for n here is (65535-1-2)/2= 32766.

The reason for minus 1 is that the actual row storage starts with the second byte ';

The reason for minus 2 is that the 2 bytes of the varchar header represent the length;

The reason for the addition of 2 is that the character encoding is GBK.

b) If a table is defined as

CREATE table t4 (c int, C2 char (+), 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 as in the previous example;

The reason for minus 4 is that the int type C accounts for 4 bytes;

The reason for reducing 30*3 is that char (30) occupies 90 bytes and the encoding is UTF8.

If the varchar exceeds the B rule above, and is strongly turned into the text type, then each field occupies a defined length of 11 bytes, which is not "varchar", of course.

MySQL Build table field length limit