The difference between varchar (10) and varchar (100) in MySQL ==>> and the pros and cons of Char

The general first learned that the two occupy the same space. For example, I store 5 char, both of which are actually taking up 5 char "errata: varchar is actually stored with a byte to hold the length."
But what about the two when designing the database?
The answer is no. "At least the varchar type needs to use one or two bytes to store the length of the data before the data" "the way they operate in memory is also different, as shown in the following example." Look at the following example .
As now, the user needs to store an address information. According to the evaluation, only 100 characters are used. However, some database administrators would argue that the varchar data types are allocated lengths based on actual needs. It's better to give it a bigger one. For this reason they may allocate a 200-character storage space for this field at once. Is this varchar (100) really the same as varchar (200)? The result is negative. Although they are used to store 90 characters of data, their storage space is the same. However, the consumption of memory is different.For a varchar data type, the storage space on the hard disk allocates storage space based on the actual character length, but not for memory . It uses a fixed-size block of memory to hold the value. Simply put, it is the length defined in the character type, which is 200 character space. Obviously, this can have a significant adverse effect on the sort or temporary tables (which all need to be implemented through memory). Explanations can be found here . So if some fields involve file sorting or disk-based temporal tables, it is still not too generous to allocate the varchar data type. Or, evaluate the length of the actual need, and then select a longest field to set the character length. If you want to consider redundancy, you can leave a character length of about 10%. You cannot think of it as allocating storage space based on actual length, arbitrarily allocating length, or simply using the maximum character length.
----------------------------------Char------------------------------------------
1.From the point of view of fragmentation, when using the char character type, storage space is allocated once. For this purpose, the contents of a field are stored together. From this point of view, there is no fragmentation problem. The variable-length character data type has a variable length of storage. When the data length is inconsistent before and after the change, the problem of fragmentation inevitably occurs. Therefore, when using variable-length character data, the database administrator periodically organizes the fragments. such as performing a database export import job, to eliminate fragmentation.
2.Consider whether the length is similar, if the length of a field is long, but its length is always approximate, such as generally between 90 to 100 characters, or even the same length. It is more appropriate to use Char character types at this time. A more typical application is the MD5 hash value. When using the MD5 hash value to store user passwords, it is very useful to use char character types. Because its length is the same. In addition, it is generally recommended to use char type data, such as for storing the user's ID number and so on.
Please also consider a question, CHAR (1) and varchar (1) The definition of two, what is the difference? Although both can only be used to hold a single character, varchar consumes a storage location more than char. This is mainly because when you use the varchar data type, you use more than 1 bytes to store the length information. This administrative overhead of the char character type is not.

---------------------------------Summary---------------------------------------------

----------------------------------------------- ------------------------------------

What is the maximum length of varchar?

See here

mysql field type although the maximum length is 65535, but it is not able to save so much data, Maximum of 65533 (when non-null fields are not allowed), when non-null fields are allowed only to 65532 "when NULL is allowed, varchar (65532) will is 2 bytes (length) + Up to 65532 chars (latin1) + 1 Null byte "" .

----------------------------------------------- --------------------------------------

see here

For proof:

According to the MySQL manual provided at the address above, if a unique index is created on a char or varchar column, then ' a ' and ' a ' will cause duplicate-key error.

The difference between varchar (10) and varchar (100) in MySQL ==>> and the pros and cons of Char