“This is the 21st day of my participation in the First Challenge 2022. For details: First Challenge 2022.”

Char and varchar are the string types provided by mysql. What are the differences and similarities between them?

The same

Can store most characters

First of all, what are the similarities between the two? Why is it possible to store most characters? Because what can be stored is not only determined by the field type, but also by the character set of the database

And the well-known mysql character sets UTF8MB4 and UTF8, the former can store special Unicode encoded emojis, while the latter cannot.

The difference between

1. The maximum length is different

The char type can be set to a maximum length of 255, or an error will be reported if the string length exceeds 255.

In theory, the maximum length of vARCHAR is 65535 bytes. However, the maximum number of characters stored varies according to the encoding of different character sets. In this case, both the database and the data table use UTF8MB4.

2. Different storage methods

Char No matter how many characters you actually store, it will take up n characters. For example, if the sex field is set to 10, it will take up 10 characters

Note that when a char is stored, if the actual number of characters does not reach the specified length of the N character space, it will be filled with Spaces. However, when we retrieve the corresponding data, we will erase the extra Spaces and get the actual number of characters

Varchar Stores a variable length, as long as the actual stored length is less than the N length set when creating the table. For example, if the sex field is set to 10, the actual stored characters are only the length of the male character string.

3, efficiency,

Since char stores the same length, theoretically char should be more efficient than VARCHAR, but not always. Only when the space is completely filled will char be more efficient than VARCHAR.

Commonly used for

Char is usually used when the stored content is of a fixed length, such as mobile phone number, gender, id number and date of birth

Varchar usually uses variable length cases, such as remarks, addresses, etc