Differences between char and varchar in mysql

Differences between char and varchar

Char (13) is fixed in length. For example, the storage space of 'www .jb51.net' must be 12 characters long.

Varchar (13) Variable Length: for example, 'www .jb51.net' requires a bucket of 13 characters,

From the above we can see that the char length is fixed, no matter how much data you store, it will be fixed length. Varchar can be a variable in length, but it needs to add 1 character to the total length, which is used to store the location. Therefore, in actual applications, users can do this based on their own data types.

Let's look at the speed difference between char and varchar.
Copy codeThe Code is as follows:
Mysal> create tabe AB (v varchar (4), c char (4 ));
Query OK, 0 rows affected (0.02 sec)
Mysql> insert into abc values ('AB', 'AB ')
Query OK, 1 row affected (0.00 sec );
Mysql-> select concat (v, '+'), concat (c, '+') form abc
AB + | AB +
1 rows in set (0.00 sec)

It can be seen from the above that, for some reason, the char is fixed length, so the processing speed is much faster than that of varchar, but it is not suitable for storage space, however, if the speed requirement is met, the char type can be used. Otherwise, the varchar type can be used for instances.

Note:

When the char character type is used, the content must be followed by space. Otherwise, spaces will be automatically deleted.

Purpose:

We recommend that you use a fixed length for the myisam storage engine. Data columns replace variable-length data columns.
The memory storage engine currently uses fixed data Row Storage, so no matter whether the char varchar column is used,
The innodb Storage engine is intended to use the varchar type


The following is a supplement from other netizens:


Char is a fixed-length type, and varchar is a variable-length type.
In a char (M) data column, each value occupies M bytes. If the length of a data column is smaller than M, MySQL uses space characters on the right side of the data column. (The space characters filled in the search operation will be removed.) In the varchar (M) data column, each value takes only enough bytes and adds a byte to record its length (that is, the total length is L + 1 bytes ).

Rules Used in MySQL to determine whether data column type conversion is required

1. In a data table, if the length of each data column is fixed, the length of each data row will also be fixed.
2. As long as there is a variable length of a data column in the data table, the length of each data row is variable.
3. If the length of data rows in a data table is variable, in order to save storage space, mySQL converts a fixed-length data column in the data table to a variable-length data column.
Exception: char data columns with a length less than 4 characters will not be converted to varchar type

For MyISAM tables, use Char as much as possible, especially for myisam and isam data tables that often need to be modified to easily form fragments,Its disadvantage is that it occupies disk space.;

For an InnoDB table, because the internal storage format of its data rows does not distinguish between rows with a fixed length and data rows with a variable length (all data rows share a header, this header contains pointers to relevant data columns), so char type is not necessarily better than varchar type. In fact, because the char type usually occupies more space than the varchar type, it is more advantageous to use the varchar type from the perspective of reducing space usage and reducing disk I/o.

Article 2:
Character should be the most common one, but it seems that all databases are different, such as varchar2 in oracle. However, mysql seems to be concentrated on char and varchar at most.
Let's talk about the difference. Char is a fixed length, and varchar uses the storage space according to the specific length. For example, if char (255) and varchar (255) are used to store the string "hello world", char uses a 255 space to hold the 11 characters, varchar does not use 255 characters. First, it calculates the length and then uses only 11 characters plus the calculated String Length information. Generally, one or two bytes are used, in this way, the storage space of varchar is greatly reduced when the storage is uncertain.

In this case, varchar is much smarter than char. Is char useful? There are still many advantages.

1. Store very short information, such as the house number 101,201 ...... Char should be used for such a short piece of information, because varchar also occupies a byte for storing the length of information, which is intended to save the cost of storage.

2. Fixed Length. For example, if uuid is used as the primary key, char should be more suitable. Because of its fixed length, varchar dynamically disappears based on its length characteristics and occupies length information.

3. columns that change frequently. Because varchar requires extra computing for each storage, such as getting the length. If a change is very frequent, a lot of energy is required for computing, these are not required for char.


Another question about varchar is that since varchar can automatically adapt to the storage space, my varchar (8) and varchar (255) storage should be the same, when designing a table, we should move it to the greater direction, so as not to be difficult enough in the future. Is this idea correct? The answer is no. Mysql will put the table information in the memory (after the first query, it will be cached. It is obvious in linux, but it does not seem to exist in windows. I don't know why ), at this time, the memory application is based on a fixed length. If the varchar is large, the problem may occur. Therefore, you should still request it as needed.

Conclusion: take a closer look at the DZ data table. The fixed-length fields still use char ....