The difference between [MySQL] char and varchar

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.

Char 0-255 byte fixed length string
 VARCHAR 0-65535 byte variable length string

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

Tags: MySQL

Posted on Tue, 12 May 2020 08:40:22 -0700 by kincet7