Differences between MySQL string column types

MySQL strings have many column types. The following describes the differences between MySQL strings and column types. We hope that you can have a deeper understanding of MySQL string types.

1.

First, the differences between CHAR, VARCHAR, TEXT, BINARY, VARBINARY, and BLOB:

HAR, VARCHAR, and TEXT are non-binary strings;

BINARY, VARBINARY, BLOB is called a BINARY string;

The difference between a binary string and a non-binary string is:
A binary string contains a byte string and a non-binary string contains a character string;

The latter can define character sets. The former cannot;

Sort and compare the values of the former based on the value of the column value byte, and the latter based on the alphabetic order for sorting or comparison;

BINARY is similar to CHAR type, but stores BINARY strings instead of non-BINARY strings;

VARBINARY is similar to VARCHAR, but stores binary strings instead of non-binary strings;
Similarly, BLOB corresponds to the TEXT type (TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB, TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT)

2.

CHAR and VARCHAR are the same:

You can define the length of a string, such as CHAR (M) and VARCHAR (M );

Differences:

The length of the column defined by CHAR (M) is fixed, and the M value can be 0 ~ 255. When the CHAR values are saved, fill in spaces on the right of them to reach the specified length. When the CHAR value is retrieved, the trailing space is deleted. Case-insensitive conversion is not performed during storage or retrieval.

The length of the column defined by VARCHAR (M) is a variable-length string, and the value of M can be 0 ~ 65535 (the maximum valid length of VARCHAR is determined by the maximum size of the row and the character set used. The total length is 65,532 bytes ). When the VARCHAR value is saved, only the number of characters is saved, and a byte is added to record length (if the length of the column declaration exceeds 255, two bytes are used ). The VARCHAR value is not filled when it is saved. When the value is saved and retrieved, the space at the end is retained, which complies with the standard SQL.

Note: The maximum length of the varchar type of MySQL database in versions earlier than 4.1 is 255, and its data range can be 0 ~ 255 or 1 ~ 255 depends on the database of different versions ). In MySQL 65535 or later versions, the varchar data type can be up to 65532 characters in length, that is, bytes of data can be stored, and the start bit and second bit occupy three character sections, that is, in versions 4.1 or earlier, you can use varchar to store data in fixed TEXT or BLOB format, which can effectively reduce the size of database files.

The biggest difference between CHAR and VARCHAR is the fixed length and variable length. Because it is a variable length, the actual storage is the actual string plus a record String Length byte (if it exceeds 255, it requires two bytes)

If the value assigned to a CHAR or VARCHAR column exceeds the maximum length of the column, crop the value to make it suitable. If the character to be dropped is not a space, a warning is generated. If you crop non-space characters, it will cause an error (rather than warning) and disable value insertion by using strict SQL mode.

Because BINARY and VARBINARY correspond to CHAR and VARCHAR respectively, their differences are similar to those of CHAR and VARCHAR.

Note that the M value range defined by VARBINARY is 0 <= M <= 255

3. Differences between VARCHAR and TEXT:

The M value does not need to be specified for the TEXT type. Other aspects are similar to VARCHAR. For example, the variable length is stored, that is, the insufficient part does not need to be filled with spaces on the right.

TEXT is similar to VARCHAR. Actually, you need to save the string itself and add two bytes to save the actual length of the string.

See the following table to understand the differences described above:

String type storage requirements M is the maximum length, L the actual length of the stored string)

Mysql query case

MySQL multiple condition judgment example

How to query duplicate records in MYSQL

How to Implement MySQL full-text Query

Example of the number of MySQL query results