Original URL: The difference between char and varchar in http://www.jb51.net/article/23575.htmmysql font: [Increase decrease] type: reprinted in MySQL tutorial the difference between char and varchar, are used to store strings, except that they are saved in a different way, Char has a fixed length, and varchar is a variable-length character type. The difference between char and varchar
CHAR (13) fixed length, such as ' www.jb51.net ' storage requires space of 12 characters
varchar (13) variable length as ' www.jb51.net ' requires storage space of 13 characters,
As you can see from the above, the char length is fixed, no matter how much data you store, he will be fixed length. The varchar is variable in length but he adds 1 characters to the total length, which is used to store the location. So the user can do it according to their own data type in actual application.
Then look at the difference between char and varchar in speed.
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+
1rows in Set (0.00 sec)
As can be seen from the above, for some reason char fixed length, so the processing speed is much faster than varchar, but to the cost of storage space, so the storage is not small, but the speed of the requirements can use the char type, the other can be used with the varchar type instance.
Indicate:
When there is space behind the content with the char character type, it must be handled, or the space will be automatically deleted.
Jian Yi:
The MyISAM storage engine recommends using fixed-length, data columns instead of variable-length data columns.
The memory storage engine is currently using fixed data row storage, so it doesn't matter whether you use char varchar columns,
InnoDB storage engine built to use varchar type
The following is a supplement to other users
Char is a fixed-length type, and varchar is a variable-length type
char (m) Type of data column, each value occupies M bytes, if a length of less than M,mysql will be on its right with a space character to complement. (The space characters that are filled in the retrieval operation are removed) in the varchar (M) Type of data column, each value occupies just enough bytes plus a byte to record its length (that is, the total length is l+1 bytes).
use MySQL to determine if a rule is required to convert a data column type
1, in a data table, if the length of each data column is fixed, then the length of each data row will be fixed.
2, as long as the data table has a variable length of the data column, then the length of the data rows are variable.
3. If the length of the data row in a data table is variable, MySQL converts the data column of the fixed-length type in the table to the corresponding variable-length type in order to save storage space.
Exception: A char data column that is less than 4 characters long is not converted to a varchar type
For MyISAM tables, use char as much as possible, especially for MyISAM and ISAM data tables that often require modification and are prone to fragmentation.
The disadvantage is that it takes up disk space；
For the InnoDB table, because its data row internal storage format does not differentiate between fixed-length data rows and variable-length rows of data (all data rows share a header section that holds pointers to individual data columns), it is not necessarily better to use the char type than 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 from the point of view of reducing space consumption and reducing disk I/O.
Article 2:
Characters should be one of the most common, but it seems that each database is different, such as what is VARCHAR2 in Oracle. However, MySQL seems to be the most concentrated on char and varchar.
Tell me the difference. Char is fixed-length, and varchar uses storage space depending on the length. For example char (255) and varchar (255), when storing the string "Hello World", Char will use a 255 space to put that 11 characters, and varchar will not use 255, He calculates the length with only 11 plus the string length information, typically 1-2 byte, so that varchar will significantly reduce storage space when storing an indeterminate length.
so it seems that varchar is much smarter than char, is that char useful? is 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 has a byte to store the information length, the intention of saving storage is now worth the candle.
Two, fixed-length. For example, using the UUID as the primary key, the use of char should be more appropriate. Because of his fixed length, the varchar dynamically disappears according to the length of the character, and also takes up a length of information.
Three, a very frequent change of column. Because varchar has to have extra calculations for each storage, to get the length of the work, if a very frequent change, then there will be a lot of energy for the calculation, which is not required for char.
There is also a question about varchar, since he can automatically adapt to storage space, then my varchar (8) and varchar (255) storage should be the same, that every time the table design in the direction of the big, so that the future is not enough trouble. That's the idea, right? 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 memory application is fixed length, if the varchar is very large there will be problems. So it should be requested on demand.
Summary: Carefully look at the data table dz, fixed length of the field is basically also used char .... 597
Analysis of the difference between char and varchar in MySQL
