In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-02-24 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)05/31 Report--
This article mainly introduces "what is the varchar restriction rule in MySQL database". In daily operation, I believe many people have doubts about what is the varchar restriction rule in MySQL database. Xiaobian consulted all kinds of information and sorted out simple and easy to use operation methods. I hope to help you answer the doubts of "what is the varchar restriction rule in MySQL database"! Next, please follow the small series to learn together!
1. Limiting rules
Field restrictions have the following rules when defining fields:
a) Storage restrictions
The varchar field stores the actual content separately from the cluster index, and the content starts with 1 to 2 bytes to indicate the actual length (2 bytes are required when the length exceeds 255), so the maximum length cannot exceed 65535.
b) Code length limitation
If the character type is gbk, each character can occupy 2 bytes at most, and the maximum length cannot exceed 32766;
If the character type is utf8, each character can take up to 3 bytes, and the maximum length cannot exceed 21845.
If the definition exceeds the above limit, the varchar field will be forcibly converted to text type and a warning will be generated.
c) Line length limit
What causes the varchar length limitation in practice is the length of a line definition. MySQL requires that the definition length of a line cannot exceed 65535. ERROR1118(42000):Rowsizetoolarge.Themaximumrowsizefortheusedtabletype,notcountingBLOBs,is65535.YouhavetochangesomecolumnstoTEXTorBLOBs.
What are the restrictions on varchar in MySQL database?
2. Calculation example
Two examples illustrate the calculation of actual length.
a) If a table has only one varchar type, defined as createtablet4 (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 row storage starts at the second byte ';
The reason for subtracting 2 is that the 2 bytes of the varchar header indicate the length;
The reason for dividing by 2 is that the character encoding is gbk.
b) If a table is defined as createtablet4 (cint,c2char(30),c3varchar(N))charset=utf8; then the maximum value of N here is (65535-1-2-4-30*3)/3=21812.
Subtract 1 and subtract 2 for the same reason as in the previous example;
The reason for subtracting 4 is that c of int type takes 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 b rule above and is forced to be of type text, then each field occupies a defined length of 11 bytes, which of course is no longer "varchar".
At this point, the study of "what are the varchar restriction rules in MySQL database" is over, hoping to solve everyone's doubts. Theory and practice can better match to help everyone learn, go and try it! If you want to continue learning more relevant knowledge, please continue to pay attention to the website, Xiaobian will continue to strive to bring more practical articles for everyone!
Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.