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

Aforementioned

VARCHAR and CHAR are the two main string types. Unfortunately, it is difficult to explain exactly how these values are stored on disk and memory, because this is related to the specific implementation of the storage engine. The following description assumes that the storage engine used is InnoDB and / or MyISAM. If you are not using these two storage engines, refer to the documentation for the storage engine you are using.

Let's take a look at how VARCHAR and Char values are usually stored on disk. Note that the storage engine may store CHAR or VARCHAR values differently in memory than on disk, so the values read by the MySQL server from the storage engine may need to be converted to another storage format.

VARCHAR Typ

The VARCHAR type is used to store variable-length strings and is the most common string data type. It saves more space than a fixed-length type because it uses only the necessary space (for example, shorter strings use less space). With one exception, if the MySQL table is created using ROW_FORMAT=FIXED, each row uses fixed-length storage, which is a waste of space.

VARCHAR needs to use 1 or 2 extra bytes to record the length of the string: if the maximum length of the column is less than or equal to 255 bytes, only 1 byte is used, otherwise 2 bytes are used. Assuming the latin1 character set, an VARCHAR (10) column requires 11 bytes of storage. The column of VARCHAR (1000) requires 1002 bytes because 2 bytes are required to store length information.

VARCHAR saves storage space, so it is also good for performance. However, because the row is longer, it may make the row longer than it used to be in UPDATE, which results in extra work. If the space occupied by a row grows and there is no more space to store on the page, different storage engines handle it differently. For example, MyISAM splits rows into different fragments to store, while InnoDB needs to split the page so that rows can be put into the page. Some other storage engines may never update data in the original data location.

Application of VARCHAR

VARCHAR is appropriate in the following situations:

The maximum length of a string column is much larger than the average length.

Column updates are rare, so fragmentation is not a problem

Complex character sets such as UTF-8 are used, and each character is stored with a different number of bytes

CHAR Typ

The CHAR type is fixed-length: MySQL always allocates enough space based on the defined string length. When storing the Char value, MySQL removes all trailing spaces. The CHAR value is populated with spaces as needed to facilitate comparison.

CHAR is suitable for storing very short strings, or all values are close to the same length. For example, CHAR is ideal for storing the MD5 value of a password because it is a fixed-length value. CHAR is also better than VARCHAR for data that changes frequently, because fixed-length CHAR types are not prone to fragmentation. For very short columns, CHAR is also more efficient in storage space than VARCHAR. For example, using CHAR (1) to store only Y and N values requires only one byte if you use a single-byte character set, but VARCHAR (1) requires two bytes because there is an extra byte of record length.

test

Here's an example to illustrate the difference in behavior between CHAR and VARCHAR. First, let's create a table with only one CHAR (10) field and insert some values into it:

CREATE TABLE char_test (char_col CHAR (10)); INSERT INTO char_test VALUES ('string1'). ('string2'). ('string3')

When we retrieve these values, we find that the space at the end of the string3 is truncated.

SELECT CONCAT ("', char_col,"'") FROM char_test

Execution result:

If you use the VARCHAR (10) field to store the same value, you can get the following results:

CREATE TABLE varchar_test (varchar_col VARCHAR (10)); INSERT INTO varchar_test VALUES ('string1'). ('string2'). ('string3'); SELECT CONCAT ('', varchar_col,'') FROM varchar_test

Execution result

The difference between VARCHAR (5) and VARCHAR

If we use VARCHAR (5) and VARCHAR (200) to store 'hello', we know that the space overhead is the same. So can we keep the length of the VARCHAR large all the time? What are the advantages of using shorter columns?

It turns out to have a great advantage. Longer columns consume more memory because MySQL usually allocates fixed-size blocks of memory to hold internal values. It can be especially bad when sorting or manipulating with temporary tables in memory. It's just as bad when sorting using disk temporary tables.

So the best strategy is to allocate only the space you really need.

Summary

When we choose a type for a field of string type, we can decide whether to choose VARCHAR or CHAR. We can consider the following aspects:

Whether the difference between the average length and the maximum length of the dataset in this field is very small, if the difference is very small, the CHAR type is preferred, otherwise, the VARCHAR type is considered.

If the field stores the hash value after MD5, or some fixed-length values, the CHAR type is preferred.

If the field needs to be updated frequently, the CHAR type is preferred. Because the CHAR type is of fixed length, it is not prone to fragmentation.

For field values that store very small information, such as gender, the CHAR type is preferred because the VARCHAR type takes up extra bytes to save string length information.

In a word, when we can choose the CHAR type, or when space consumption is not the focus of the influencing factors, try to choose the CHAR type, because in other aspects, the CHAR type has more or less advantages. When space consumption becomes a big factor, we consider using the VARCHAR type.

These are the details of the difference between CHAR and VARCHAR in MySQL. Please pay more attention to other related articles!

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.