MySQL char and varchar

the difference between a MySQL char and a varchar

Char is a fixed-length type, and varchar is a variable-length type that differs from:

1. 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 out in the retrieval operation will be removed)
In a varchar (M)-type data column, each value occupies just enough bytes plus a byte to record its length (that is, the total length is l+1 bytes).

The efficiency of the 2.char is slightly higher than that of the varchar2.

3. Currently varchar is synonymous with VARCHAR2. The industry standard varchar type can store an empty string, but Oracle does not, although it retains the right to do so later. Oracle has developed a data type of VARCHAR2, which is not a standard varchar, and it will store null values in the database in which the varchar column stores the empty string. If you want to have backward compatibility capabilities, Oracle recommends using VARCHAR2 instead of varchar.

When should I use char and when should I use VARCHAR2?

Char and VARCHAR2 are a pair of contradictory unity, the two are complementary relations.

varchar saves space compared to Char, which is slightly less efficient than char, which means that a certain amount of space must be sacrificed to achieve efficiency, which is what we often call "space-for-efficiency" in database design.

Although VARCHAR2 is more space-saving than char, if a VARCHAR2 column is often modified and the length of each modified data is different, this causes a ' row-migration ' phenomenon, which creates redundant I/O, which is to be avoided in database design and tuning. , it would be better to use char instead of VARCHAR2 in this case.

The following is an experimental proof in MySQL:

Mysql> #tee to enter the contents of the command box into the specified file
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| DB1 |
| MySQL |
| Performance_schema |
| Student |
| Test |
+--------------------+
6 rows in Set (0.02 sec)

mysql> use student;
Database changed
Mysql> Show tables;
+-------------------+
| tables_in_student |
+-------------------+
| INFOTB |
+-------------------+
1 row in Set (0.00 sec)

mysql> desc INFOTB;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| ID | Tinyint (4) | NO | PRI | NULL | auto_increment |
| name | varchar (20) |     NO | |                NULL | |
| sex | varchar (5) |     YES | |                NULL | |
| Tel | varchar (20) |     YES | |                NULL | |
| Ad | varchar (30) |     YES | |                NULL | |
+-------+-------------+------+-----+---------+----------------+
5 rows in Set (0.00 sec)

Mysql> select * from INFOTB;
+----+----------+------+-------------+----------------+
| ID | name | sex | Tel | Ad |
+----+----------+------+-------------+----------------+
| 13 | Cyz | NULL | 123 | 123 |
| 15 | Cool | NULL | Cool | Cool |
| 16 | This | NULL | is | Great |
| 17 | Hello | NULL | NULL | NULL |
| 22 | QQ Group | NULL | 111 | Henan |
| 24 | 123 | NULL | 123 | 123 |
| 26 | haha haha | NULL | 123123 | Aston |
| 27 | Tom | NULL | 123 | NULL |
| 28 | Tom2 | NULL | 1233 | NULL |
| 29 | Tom | NULL | 123 | NULL |
| 30 | Tom2 | NULL | 1233 | NULL |
| 31 | Tom | NULL | 123 | NULL |
| 32 | Tom2 | NULL | 1233 | NULL |
+----+----------+------+-------------+----------------+
Rows in Set (0.01 sec)

Mysql> CREATE TABLE Test (
-CA char (4) NOT null Defalut ",
VCA varchar (4) NOT null Defalut "
);
Error 1064 (42000): You have a error in your SQL syntax; Check the manual-corresponds to your MySQL server version for the right syntax-use near ' Defalut ',
VCA varchar (4) NOT null Defalut "
) ' at line 2
Mysql> CREATE TABLE Test (
-CA char (4) NOT null default ' ',
VCA varchar (4) NOT null default ' '
);
Query OK, 0 rows affected (0.22 sec)

Mysql> #练习char与varchar的区别
mysql> INSERT INTO Test (CA,VCA) value (' AA ', ' AA ');
Query OK, 1 row affected (0.09 sec)

Mysql> select * from test;
+----+-----+
| CA | VCA |
+----+-----+
| AA | AA |
+----+-----+
1 row in Set (0.00 sec)

mysql> #concat Connection String function
Mysql> Select Concat (CA, '! '), concat (VCA, '! ') from test;
+----------------+-----------------+
| Concat (CA, '! ') | Concat (VCA, '! ') |
+----------------+-----------------+
|            Aa! |            Aa! |
+----------------+-----------------+
1 row in Set (0.18 sec)

Mysql> #char和varchar分别称为定长和变长类型
Mysql> #对于char (n), not enough n length, with a space in the tail to fill enough n length, waste the tail.
Mysql> #而对于varchar (N) without a space, but before the column content, there are 1-2 bytes to flag the content length of the column.
Mysql> #char读取速度比较快, but wasted storage space, at the end of the string read with a space, the space cannot be read
Mysql> #varchar读取速度没有char快, but takes full advantage of storage space and can store any character.
Mysql> exit;

#tee Enter the contents of the command box into the specified file

MySQL char and varchar