What is the maximum length of int( 1 1)?
In the SQL statement, int represents the type of field to be created, int represents an integer, and 1 1 represents the length of the field.
This 1 1 represents the display width. The display width of integer columns has nothing to do with how many characters mysql needs to display column values, nor with the size of storage space required by integers. For example, bigint will occupy 8 bytes no matter how many characters the display width is set to.
Int is an integer, and (1 1) refers to the length of displayed characters, but the maximum number of parameters to be added is 255. For example, it is the id of the number of rows recorded. If 10 data is inserted, 00000001~ 000000065438+will be displayed. It only displays 1 1 bits. If you don't add a parameter that makes it add 0 before 1 1 bit, it won't add 0 before it.
When declaring an integer data column, we can specify a display width M( 1~255), such as INT(5), and specify a display width of 5 characters. If the display width is not specified for it, MySQL will specify a default value for it. The display width is only used for display, and the value range and occupied space cannot be limited. For example, INT(3) will occupy 4 bytes of storage space, and the maximum allowable value will not be 999, but the maximum allowable value of INT integer.
MySQL has five types of integer data columns, namely TINYINT, SMALLINT, MEDIUMINT, INT and BIGINT. The difference between the two is that the range of values is different and the storage space is different. Adding an unsigned attribute after an integer data column can suppress negative numbers, and the value starts from 0.
Int range
type
byte
minimum value
maximum
(Signed/Unsigned)
(Signed/Unsigned)
TINYINT
1
- 128
127
255
SMALLINT
2
-32768
32767
65535
culture medium
three
-8388608
8388607
167772 15
Internationalorganizations (same as international organizations)
four
-2 147483648
2 147483647
4294967295
BIGINT
eight
-9223372036854775808
9223372036854775807
1844674407370955 16 15
What is the maximum length of varchar in MySQL?
I. varchar storage rules:
Under version 4.0, varchar(20) refers to 20 bytes. If UTF8 Chinese characters are stored, only 6 (3 bytes for each Chinese character) can be stored above version 5.0. Varchar(20) refers to 20 characters. No matter how to store numbers, letters or UTF8 Chinese characters (3 bytes for each Chinese character), you can store 20 characters. The maximum size is 65532 bytes Mysql4.
2. The difference between 2.varchar and char:
Char is a fixed length type, while varchar is a variable length type. The difference between them is that in a data column of type char(M), each value occupies m bytes. If the length is less than m, MySQL will make up for it with space characters on its right. (These filled space characters will be deleted in the retrieval operation. In a data column of type varchar(M), each value only takes up just enough bytes.
Rules used in MySQL to determine whether it is necessary to convert the listed types.
1. In the data table, if the length of each data column is fixed, the length of each data row will also be fixed.
2. As long as the length of the data column in the data table is variable, the length of each data row is also variable.
3. If the length of data rows in a data table is variable, MySQL will convert the fixed-length data columns in this data table into corresponding variable-length data columns in order to save storage space. Exception: char data columns less than 4 characters in length will not be converted to varchar type.
3. What is the maximum length of varchar in 3.MySQL?
This is not a fixed number. Let me briefly explain the restriction rules first.
1, restriction rule
When defining a field, the restrictions of the field have the following rules:
A) storage restrictions
The Varchar field stores the actual content independently of the clustered index, and the actual length is indicated by 1 to 2 bytes at the beginning of the content (it takes 2 bytes for the length to exceed 255), so the maximum length cannot exceed 65535.
B) code length limitation
If the character type is gbk, each character takes up at most 2 bytes, and the maximum length cannot exceed 32766;
If the character type is utf8, each character takes up at most 3 bytes, and the maximum length cannot exceed 2 1845.
If the definition exceeds the above limit, the varchar field will be cast to the text type and a warning will be generated.
C) line length limitation
In practical application, it is the length of line definition that limits the length of varchar. MySQL requires that the defined length of a row cannot exceed 65535. If the defined table length exceeds this value, you will be prompted.
Error1118 (42000): The row size is too large. The maximum row size (excluding BLOBs) of the table type used is 65535. You must change some columns to text or spots.
2. Calculation example
Give two examples to illustrate the calculation of actual length.
A) if a table has only one varchar type, such as defined as
Create table t4 (cvarchar (n)) charset = gbk;
Then the maximum value of n here is (65535- 1-2)/2= 32766.
The reason for subtracting 1 is that the actual line storage starts from the second byte? ;
The reason for subtracting 2 is that 2 bytes in the varchar header represent the length;
The reason for dividing by 2 is that the character encoding is gbk.
B) If a table is defined as
Create a table t4(c int, c2 char(30), C3 varchar (n)) charset = utf8;
Then the maximum value of n here is (65535-1-2-4-30 * 3)/3 = 21812.
Subtract 1 and 2 as above;
The reason for subtracting 4 is that C of type int takes up 4 bytes;
The reason for subtracting 30*3 is that char(30) takes up 90 bytes and the encoding is utf8.
If varchar exceeds the above rule B and is converted to text type, the defined length occupied by each field is 1 1 byte. Of course, isn't it? varchar？ Yes
Then the maximum value of n here is (65535-1-2-4-30 * 3)/3 = 21812.
Create table t4(c int, c2 char(30), C3 VarChar (21812)) Engine = InnoDB DefaultCharset = UTF8.
Create table t5(c int, c2 varchar(30), C3 Varchar (21812)) Engine = InnoDB DefaultCharset = UTF8.
Varchar(30) and char(30) can store at most:
Do foundation engineering in Jishun, do foundation engineering in large-scale integrated circuit city, run around, grab work in the city, carry molded foundation engineering in the city, plant civil engineering with dreams, plant whimsy with dreams, plant halls with dreams, plant oranges with molds, run around with molds, make leather with oranges, and plant foundations with dreams.
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 / kloc-0/ 1 1 1 16543 8+0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 16 5438+0 1 1 1 1 1 1 1 1 1 1
1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 / kloc-0/ 1 1 1 16543 8+0 1 1 1 1 1 1 1 1 1 1 1 1 1 1
What are the maximum lengths of int( 1 1) and varchar(rpm) in MySQL?
Label: