Int (11) What is the maximum length, and what is the maximum length of varchar in MySQL (RPM)

What is the maximum length of int (11), and what is the maximum length of varchar in MySQL?

　　What is the maximum length of int (11)?

In the SQL statement, int represents the type of field you want to create, int represents an integer, and 11 represents the length of the field.

This 11 represents the width of the display, the display width of the whole series and how many characters MySQL needs to display the column value, which is not related to the size of the storage space required by the integer, for example, regardless of how many characters the display width is set, bigint consumes 8 bytes.

int is integer, (11) is the length of the display character, but to add the parameter, the maximum is 255, for example, it is the ID of the number of records, insert 10 data, it shows 00000000001 ~~~00000000010, when the number of characters more than 11, it only shows 11 bits, If you don't add the argument that makes it less than 11 bits ahead plus 0, it won't add 0 to the front.

When declaring an integer data column, we can assign it a display width of M (1~255), such as int (5), specify a display width of 5 characters, and if it does not specify a display width, MySQL assigns it a default value. The display width is only used for display, and cannot limit the range of values and footprint, such as: Int (3) consumes 4 bytes of storage space, and the maximum allowed value is not 999, but the maximum allowed for int integer type.

MySQL has five types of integer data columns-Tinyint,smallint,mediumint,int and bigint. The difference between them is that the range of values is different and the storage space varies.
Adding the unsigned property to an integer data column suppresses negative numbers, starting with 0.

　　int range

Type	Bytes	Minimum Value	Maximum Value
		(signed/unsigned)	(signed/unsigned)
TINYINT	1	-128	127
		0	255
SMALLINT	2	-32768	32767
		0	65535
Mediumint	3	-8388608	8388607
		0	16777215
Int	4	-2147483648	2147483647
		0	4294967295
BIGINT	8	-9223372036854775808	9223372036854775807
		0	18446744073709551615

　　What is the maximum length of varchar in MySQL?

　　One. VarChar Storage rules:

4.0 versions, varchar (20), refers to a byte , if stored UTF8 kanji, can only save 6 (3 bytes per kanji)
More than 5.0 versions, varchar (20), refers to the number of characters , whether it is stored in numbers, letters or UTF8 Kanji (3 bytes per kanji), can be stored 20, the maximum size is 65532 bytes
The largest of the Mysql4 is only 20 bytes, but the MYSQL5 is different depending on the encoding and the storage size.

　　Two. The difference between varchar and char:

Char is a fixed-length type, and varchar is a variable-length type, with the difference being that each value occupies M-bytes in a data column of type char (m), and if a length is less than m,mysql, it is complemented by a space character on its right. (The space characters that are filled in the retrieval operation are removed) in the varchar (M) Type of data column, each value occupies just enough bytes plus a byte to record its length (that is, the total length is l+1 bytes).

Use MySQL to determine if a rule is required to convert a data column type

1, in a data table, if the length of each data column is fixed, then the length of each data row will be fixed.

2, as long as the data table has a variable length of the data column, then the length of the data rows are variable.

3. If the length of the data row in a data table is variable, MySQL converts the data column of the fixed-length type in the table to the corresponding variable-length type in order to save storage space. Exception: A char data column that is less than 4 characters long is not converted to a varchar type

　　three. what is the maximum length of varchar in MySQL?

This is not a fixed number. Let's briefly explain 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 example

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.

　　The maximum value of n here is (65535-1-2-4-30*3)/3=21812

　　CREATE table t4 (c int, C2 char (+), C3 varchar (21812)) Engine=innodb DEFAULT Charset=utf8

　　CREATE TABLE T5 (c int, C2 varchar (), C3 varchar (21812)) Engine=innodb DEFAULT Charset=utf8

　　varchar (30) and char (30) can hold up to:

　　Working in the Zoki work in the base of the run around large-scale integrated circuit City work on the left to win the city Haiyun Leather city-mounted molded ground work in the dream planting of the construction of the dream of the town of the city citrus molded run around bitter village molded leather leather citrus arable village molded base of the soul of the Foundation

　　Aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

　　Aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

　　111111111111111111111111111111111111111111111111111111

　　111111111111111111111111111111111111

Int (11) What is the maximum length, and what is the maximum length of varchar in MySQL (RPM)