CHAR and VARCHAR types are similar, but they are saved and retrieved in different ways. Their maximum length and whether trailing spaces are preserved are also different. There is no case conversion during storage or retrieval.
Create test table
mysql> create table char_test(char_col char(10)); Query OK, 0 rows affected (0.30 sec)
View the creation statements in MySQL (in this way, you can see more detailed creation statements, and you can convert aliases such as INTEGER and BOOL to the basic data type of MySQL.) ;
mysql> show create table char_test; +-----------+------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-----------+------------------------------------------------------------------------------------------------------+ | char_test | CREATE TABLE `char_test` ( `char_col` char(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-----------+------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
Insert 3 rows of data
mysql> insert into char_test(char_col) values -> ('string1'),(' string2'),('string3 '); Query OK, 3 rows affected (0.03 sec) Records: 3 Duplicates: 0 Warnings: 0
When retrieving these values later, it is found that the space at the end of string3 is truncated;
mysql> select concat("'", char_col, "'" ) from char_test; +-----------------------------+ | concat("'", char_col, "'" ) | +-----------------------------+ | 'string1' | | ' string2' | | 'string3' | +-----------------------------+ 3 rows in set (0.00 sec)
If you use the varchar(10) field to store the same value, you can get the following results:
mysql> select concat("'", varchar_col, "'" ) from varchar_test; +--------------------------------+ | concat("'", varchar_col, "'" ) | +--------------------------------+ | 'string1' | | ' string2' | | 'string3 ' | +--------------------------------+ 3 rows in set (0.00 sec)
Reference: high performance MySQL (version 3). By Baron Schwartz, Peter Zaitsev, Vadim Tkachenko; translated by Ning Haiyuan, Zhou Zhenxing, Peng Lixun, etc