A description of the difference between char and varchar in MySQL database

In the MySQL database system, char and varchar are very common, and they are two very similar, are used to save relatively short strings, such as the preservation of the article title, message, email, user name and so on.

The main difference between the two is storage mode:
The Char column length is the length declared when the table was created and fixed, and the length is limited to 0 to 255. The value in the varchar column is a variable-length string with a length of 0-255, which is extended to 65535 after 5.0.3.

When the query displays the data, char deletes the trailing space (just the tail) of the string, and varchar retains the spaces entirely. So we don't need to trim the trailing blanks when we display char type strings.

Char is suitable for saving short character content, such as "YS", "AD", "OK", "NO", "DL", and so on.
For the purposes of retrieval, but the premise is that it must be a fixed-length word character character to ensure efficiency,
Vchar is suitable for storing larger content. It is not appropriate to save the flag class information, his advantage is to save storage space.
In order to improve efficiency, it doesn't make sense for Vchar to transfer braces.

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

Performance test

VarChar average length of 200,char length 250, other configurations are as follows:
Configuration Items
Configuration

Number of records
10 million, 20 million, 50 million, 100 million.

Storage Engine
Innodb

Row format
Compact

Table structure used during testing

code is as follows

copy code

CREATE TABLE ' Mysqlchar ' ( ' username ' char () default NULL ) engine=myisam default Charset=utf8; CREATE TABLE ' Mysqlchar ' ( ' username ' varchar () default NULL ) engine=myisam default Charset=utf8; CREATE TABLE ' Mysqlchar ' ( ' username ' char () default NULL ) engine=innodb default Charset=utf8; CREATE TABLE ' Mysqlchar ' ( ' username ' varchar () default NULL ) engine= InnoDB default CHARSET

The code is as follows

Copy Code

<?php function Getrandom ($length) { $str = ' abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz_1234567890 '; $result = '; for ($i = $length; $i >0; $i-) { $result. = $str {Mt_rand (0,62)}; } return $result; } function Insert () { $dsn = ' mysql:dbname=test;host=127.0.0.1 '; $user = ' root '; $password = ' xxxxx '; $DBH = new PDO ($DSN, $user, $password); $sth = $dbh->prepare (' INSERT into Mysqlchar (' username ') VALUES (?) '); Echo Microtime (); Echo '; For ($i =0 $i <20; $i + +) { $sth->execute (Getrandom (Mt_rand (6,32))); } Echo Microtime (); } function Select () { $dsn = ' mysql:dbname=test;host=127.0.0.1 '; $user = ' root '; $password = ' xxxxxx '; $DBH = new PDO ($DSN, $user, $password); $sth = $dbh->prepare (' SELECT username from Mysqlchar WHERE username =? '); Echo Microtime (); Echo '; $sth->execute (Array (' boklenl2onf ')); Echo Microtime (); } function Addkey () { $dsn = ' mysql:dbname=test;host=127.0.0.1 '; $user = ' root '; $password = ' xxxxx '; $DBH = new PDO ($DSN, $user, $password); Echo Microtime (); Echo '; $DBH->query (' ALTER TABLE mysqlchar ADD KEY Test (' username ') '); Echo Microtime (); } Insert (); Select (); Addkey (); ?>

Test results (in seconds)

MyISAM			InnoDB
Options	CHAR (32)	VARCHAR (32)	Options	CHAR (32)	VARCHAR (32)
1, insert 200W data time (PHP command line execution)	443.34701	422.386431	1, insert 1W data time (PHP command line execution)	227.850326	215.932341
2, insert 20 data (three times average)	0.004720	0.004445	2, insert 20 data (three times average)	0.381118	0.405421
3, Query 1 data (three times average)	0.858885	0.827146	3, Query 1 data (three times average)	0.017053	0.011800
4, the establishment of the index	30.322559	24.306851	4, the establishment of the index	0.502228	0.455406
6, insert 20 data (three times average)	0.006587	0.006420	6, insert 20 data (three times average)	0.359265	0.418550
7, Query 1 data (three times average)	0.005637	0.002780	7, Query 1 data (three times average)	0.000867	0.000679
Result description	Under the MyISAM engine, VARCHAR (32) performs better in all aspects than char (32). Under the InnoDB engine, the performance of the query is better than char (32) In addition to the performance of the Insert data varchar (32) with a slight loss of char (32).