The difference between char and varchar
char (13) length fixed, such as ' www.jb51.net ' storage requires space 12 characters
varchar (13) variable length as ' www.jb51.net ' requires storage space of 13 characters,
From the above you can see that the char length is fixed, no matter how much data you store it will be fixed length. The varchar is a variable length but he wants to add 1 characters to the total length, which is used to store the location. So the actual application of the user can be based on their own data types to do.
And look at char, the difference between speed and varchar.
Copy Code code 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+
1rows in Set (0.00 sec)
As can be seen from the above, for some reason char fixed length, so in processing speed than varchar a lot faster, but the cost of storage space, so the storage is small, but the speed of the requirements can use the char type, conversely can be used varchar type to instance.
Indicate:
When a char character type is used, the content must be processed when there is space behind it, or the space will be automatically deleted.
Jian Yi:
The MyISAM storage engine recommends a fixed length, with data columns instead of variable-length data columns.
The memory storage engine currently uses fixed data row storage, so it doesn't matter whether you use a char varchar column,
InnoDB storage Engine is intended to use varchar type
The following are the supplements of other netizens
Char is a fixed-length type, and varchar is a variable-length type
In a data column of type char (m), each value occupies M bytes, and if a length is less than m,mysql, it is replenished with a space character on its right. (The space characters that are filled out in the retrieval operation will be removed) in a varchar (M) Type of data column, each value takes just enough bytes, plus a byte to record its length (that is, the total length is l+1 bytes).
rules used in MySQL to determine whether to convert to a column type
1, in a data table, if the length of each data column is fixed, then the length of each data row will also be fixed.
2, as long as the data table has a variable length of the data column, then the length of each data row is variable.
3, if the length of the data row in a data table is variable, then, in order to save storage space, MySQL will be the data table of the fixed-length type of data column into the corresponding variable length type.
Exception: Char data columns that are less than 4 characters long will not be converted to the varchar type
For MyISAM tables, use char as much as possible, especially for MyISAM and ISAM datasheets that often need to be modified to form fragments,
The disadvantage is that it takes up disk space；
For a InnoDB table, because its data row internal storage format does not differentiate between fixed-length data rows and variable-length data rows (all data rows share one header part, this header section holds pointers to the respective data columns, so using the char type is not necessarily better than using the varchar type. In fact, because the char type typically takes up more space than the varchar type, it is more advantageous to use the varchar type in terms of reducing space consumption and reducing disk I/O.
Article 2:
Characters should be the most common type, but it seems that each database is different, such as what is VARCHAR2 in Oracle. But MySQL seems to be the most concentrated on char and varchar.
Talk about the difference. Char is fixed length, and varchar uses the storage space based on the specific length. For example, char (255) and varchar (255), when storing the string "Hello World", Char uses a 255 space for that 11 character, and the varchar does not use 255, He first calculates the length and then uses only 11, plus the computed string length information, typically 1-2 byte, so that the varchar will greatly reduce storage space when storing an indeterminate length.
so it seems that varchar is much smarter than char, is that char available? It's still a lot of advantages.
One, store very short information, such as house number 101,201 ... Such a short message should be char, because varchar also takes a byte to store the length of the information, and it's not worth saving.
Two, fixed length. For example, using a UUID as the primary key should be more appropriate with char. Because of his fixed length, the varchar dynamic is based on the characteristics of the length of the disappeared, but also to occupy a length of information.
Three, very frequently changed column. Because varchar have to have extra calculations for each storage, get lengths, and so on, if a very frequent change, there's a lot of energy to compute, which is not needed for char.
There is also a question about varchar, varchar since he can automatically adapt to storage space, then I varchar (8) and varchar (255) storage should be the same, that each time the table design to the big direction to go well, lest later not enough trouble. Is that the right idea? The answer is in the negative. MySQL will put the table information in memory (after the first query, cached, Linux is obvious, but Windows does not seem to know why), then the application of memory is based on fixed length, if the varchar is very large will have problems. So you should take it on request.
Summary: Carefully look at DZ's datasheet, fixed-length fields are basically all using char ....
