Shulou(Shulou.com)06/01 Report--

First of all, let's talk about the varchar field of MySQL database, which is really practical. It can store 65536 bytes of data, which is more than oracle and sqlserver, but there are a few points to pay attention to when using varchar.

1. 65536 just say that when you want to create a table, specifying a field 65536 will still report an error, because the field itself has other overhead and can only store 65532 bytes.

2. 65532 bytes can not be set for every varchar field, it is a sum, that is, if there is a table with 2 varchar fields, then each varchar field can only set the value of 65532 varchar 2.

3. Pay attention to the encoding format when building the table. Varchar (65532) represents the number of bytes. If you use GBK or UTF-8, you will not be able to build it successfully, because a character in GBK takes 2 bytes and a character in UTF-8 takes 3 bytes.

The above concept is relatively simple, we just build a table to try, there is no experiment here; you know, the PAGE is the minimum storage unit of the innoDB storage engine, the default size is 16KB, and 16384 bytes, and row data is stored in the page; what if a row of data exceeds the size that a page can store? For example, how to store 65532 bytes of varchar (65532) mentioned above? At this point, a row overflow occurs.

Line overflow:

The InnoDB storage engine can store some data in a record outside the real data page, typically a large object column type such as BLOB\ LOB. But not absolutely, BLOB can not put the data on the overflow page, and even the VARCHAR column data type may still be stored as row overflow data, such as the above example

We execute: insert into table name select repeat ('axiomagery 65532), insert a row of 65532 bytes of data into the table, in fact, at the bottom, the real data page only stores a small part of the data, followed by offset, pointing to the row overflow data, then there will be N uncompressed binary large object pages Uncompressed BLOB Page, these large object pages are the complete storage of 65532 bytes of data.

So, when a row of data is how big, the row overflow will occur? We know that the InnoDB storage engine table is indexed, that is, the B+ tree structure, so that there must be at least two pieces of data in a page, otherwise it will become a linked list. If only one piece of data can be stored, then the InnoDB storage engine will automatically store it in the overflow page. If you can put at least two rows of data in a page, there will be no row overflow

In fact, for BLOB type data, and varchar is also the same reason, depends on the actual size, of course, since users use the blob column type, it is generally impossible to store the length of data is too small, so in most cases BLOB row data will still occur row overflow, the actual data is saved in the BLOB page, the data page only stores the first 768 bytes of data.

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.