The difference between char and varchar in mysql

In the mysql tutorial, the difference between char and varchar is that they are both used to store strings, but their storage methods are different. char has a fixed length, while varchar is a variable-length character type.

The difference between char and varchar

char (13) has a fixed length, such as 'www.jb51.net', which requires 12 characters of storage space

varchar(13 ) Variable length such as 'www.jb51.net' requires storage space of 13 characters.

As can be seen from the above, the length of char is fixed. No matter how much data you store, it will be of fixed length. Varchar is of variable length, but it needs to add 1 character to the total length, which is used to store the position. Therefore, in actual applications, users can do it according to their own data types.

Let’s look at the difference in speed between char and varchar.

code show as below:

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 has a fixed length, so the processing speed is much faster than varchar, but it costs storage space, so the storage is not large, but there are speed requirements. You can use char type, otherwise you can use varchar type to instantiate.

Note:

When using the char character type, if there is space behind the content, relevant processing must be done, otherwise the spaces will be automatically deleted.

Suggestion:

myisam storage engine recommends using fixed-length data columns instead of variable-length data columns.
Memory storage engines currently use fixed data row storage, so it does not matter whether you use char varchar columns.
innodb storage engine recommends using varchar type


The following are other netizens Supplement

char is a fixed-length type, and varchar is a variable-length type
In the char(M) type data column, each value occupies M Bytes, if a length is less than M, MySQL will pad it with space characters on the right. (Padding space characters will be removed during the search operation.) In a varchar(M) type data column, each value only takes up just enough bytes plus one byte to record its length ( That is, the total length is L+1 bytes).

Rules used in MySQL to determine whether column type conversion is required

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

For MyISAM tables, try to use Char. For those myisam and isam data tables that often need to be modified and are easily fragmented, This is even more true. Its disadvantage is that it takes up disk space ;

For InnoDB tables, because its internal storage format of data rows is suitable for fixed-length data rows and variable-length data rows. There is no distinction (all data rows share a header part, and this header part stores pointers to each relevant data column), so using the char type is not necessarily better than using the varchar type. In fact, because the char type usually takes up more space than the varchar type, it is more beneficial to use the varchar type from the perspective of reducing space usage and reducing disk I/O.

Article 2: ## The # character should be the most common one, but it seems that each database is different. For example, there is varchar2 in Oracle. However, MySQL seems to focus most on char and varchar.
Talk about the difference. Char is of fixed length, while varchar uses storage space according to the specific length. For example, char(255) and varchar(255), when storing the string "hello world", char will use a space of 255 to store the 11 characters, while varchar will not use 255. It first calculates the length and then only Use 11 plus the calculated string length information, usually 1-2 bytes, so that varchar will greatly reduce the storage space when storing uncertain lengths.


It seems that varchar is much smarter than char, so is char useful? There are still many advantages.
First, store very short information, such as house numbers 101, 201... Such short information should use char, because varchar also takes up a byte to store the length of the information. It was originally intended to save Storage is now a waste of money.

Two, fixed length. For example, if uuid is used as the primary key, char should be more appropriate. Because it has a fixed length, the dynamic length-based characteristics of varchar disappear, and it also takes up length information.

3. Column that changes very frequently. Because every time varchar is stored, additional calculations are required to obtain the length and other work. If it changes very frequently, a lot of energy will be spent on calculations, which is not needed for char.


Another question about varchar is that since varchar can automatically adapt to the storage space, then the storage of varchar(8) and varchar(255) should be the same, so every table design It’s better to go in a larger direction when you need it, so as to avoid the trouble of not having enough in the future. Is this idea correct? the answer is negative. MySQL will put the table information into the memory (after the first query, it will be cached, which is obvious under Linux, but does not seem to be under Windows, I don’t know why). At this time, the memory application is based on a fixed length. There will be problems if varchar is large. So you should still ask for it on demand.

Summary: Look carefully at the DZ data table. Fixed-length fields are basically all char....

The above is the detailed content of The difference between char and varchar in mysql. For more information, please follow other related articles on the PHP Chinese website!