String data column type (reference) _ MySQL

A string can be used to represent any value, so it is one of the most basic types. We can use the string type to store binary data such as images or sounds, or gzip compressed data. The following table describes the various string types: Table1.3. the maximum length of the string type data column occupies the storage space CHAR [(M)] M byte VARCHAR string can be used to represent any type of value, so it is one of the most basic types. We can use the string type to store binary data such as images or sounds, or gzip compressed data. The following table describes various string types:

Table 1.3. string data column type

Type	Maximum length	Occupied storage space
CHAR [(M)]	MB bytes	MB bytes
VARCHAR [(M)]	MB bytes	L + 1 byte
TINYBLOD, TINYTEXT	2 ^ 8-1 bytes	L + 1 byte
BLOB, TEXT	2 ^ 16-1 bytes	L + 2
MEDIUMBLOB, MEDIUMTEXT	2 ^ 24-1 bytes	L + 3
LONGBLOB, LONGTEXT	2 ^ 32-1 bytes	L + 4
ENUM ('value1', 'value2 ',...)	65535 members	1 or 2 bytes
SET ('value1', 'value2 ',...)	64 members	1, 2, 3, 4, or 8 bytes

L + 1 and L + 2 indicate that the data column is of variable length. the space occupied by the column is changed based on the increase or decrease of the data row. The total length of a data row depends on the length of the data value stored in the data column. The extra bytes in L + 1 or L + 2 are used to save the length of the data value. When processing variable-length data, MySQL needs to save both the data content and data length.

If you place data that exceeds the maximum length of a string in the character-type data column, MySQL automatically truncates the data.

The data column definitions of the ENUM and SET types have a list. the elements in the list are valid values of the data column. If you try to put a value not in the list in the data column, it will be converted to an empty string ("").

String-type values are stored as a continuous byte sequence and are treated as bytes or characters according to whether they hold binary strings or non-binary characters:

A binary string is considered as a continuous byte sequence and is irrelevant to the character set. MySQL treats BLOB data columns and data in CHAR and VARCHAR data columns with BINARY attributes as BINARY values.

A non-binary string is considered as a continuous character sequence. It depends on the character set. MySQL treats the data in the TEXT column and the CHAR and VARCHAR data columns without the BINARY attribute as BINARY values.

In versions later than MySQL4.1, different character sets can be used for different data columns. Before MySQL, MySQL used the server character set as the default character set.

A non-binary string is a string that is generally called a string. it is compared and sorted by character in the order of character sets. Binary strings are not sorted in character order because they are irrelevant to character sets. Instead, binary values of bytes are used as the basis for comparison and sorting. The following describes how to compare two types of strings:

The binary string is compared by one byte and one byte. the comparison is based on the binary values of two bytes. That is to say, it is case-sensitive, because the numbers of the same letter are not the same.

A non-binary string is compared by a single character. the comparison is based on the order of two characters in the character set. In most character sets, the case sensitivity of the same letter is usually the same order, so it is case insensitive.

The binary string has nothing to do with the character set. Therefore, the length of the binary string is the same for both character calculation and byte calculation. Therefore, VARCHAR (20) does not mean that it can contain up to 20 characters, but rather it can only contain up to 20 characters. For a single-byte character set, each character occupies only one byte, so the length of the two is the same, but for a multi-byte character set, it can hold less than 20 characters.

1.2.2.1. CHAR and VARCHAR
CHAR and VARCHAR are the two most commonly used string types. The difference between them is:

CHAR is a fixed length, and each value occupies the same byte. MySQL uses space characters on the right side of it to fill up the space.

VARCHAR is a type of variable length. each value occupies the maximum number of bytes, plus a byte used to record its length, that is, L + 1 bytes.

Both CHAR (0) and VARCHAR (0) are valid. VARCHAR (0) starts with MySQL 4.0.2. They act as placeholders or represent various on/off switch values.

Here are two principles for how to select CHAR and VARCHAR:

If the data has the same length, VARCHAR occupies more space because one of them is used to store the length. If the data length is different, VARCHAR can save storage space. CHAR occupies the same space regardless of the length of characters, and even a null value is no exception.

If tables of the MyISAM or ISAM type are used, CHAR is better than VARCHAR, because tables of the MyISAM and ISAM types are more efficient in processing rows of a fixed length.

In a data table, as long as the length of one data column is variable, the length of all data columns will be variable. MySQL performs automatic conversion. One exception is that if the CHAR length is less than 4, it will not be converted automatically, because MySQL will think that this is unnecessary and will not save much space. Instead, MySQL converts a large number of small-sized VARCHAR to CHAR to reduce space usage.