MySql char and varchar, mysqlvarchar

MySql char and varchar, mysqlvarchar
Differences between char and varchar in MySql

Char is a fixed-length type, and varchar is a variable-length type. Their differences are as follows:

1. in a char (M) data column, each value occupies M bytes. If the length of a data column is smaller than M, MySQL uses space characters on the right side of the data column. (The space characters filled in the search operation will be removed)
In a varchar (M) data column, each value only occupies enough bytes and adds a byte used to record its length (that is, the total length is L + 1 bytes ).

2. char is more efficient than varchar2.

3. Currently, varchar is a synonym for varchar2. The industry standard varchar type can store null strings, but oracle does not, although it reserves the right to do so in the future. Oracle has developed a data type varchar2, which is not a standard varchar. It changes the feature that can store null strings in the varchar column of the database to store null values. If you want backward compatibility, we recommend that you use varchar2 instead of varchar.

When should char be used and varchar2 be used?

Char and varchar2 are a contradictory unity, and they are complementary.

Varchar saves space than char, and is slightly less efficient than char. That is, to achieve efficiency, a certain amount of space must be sacrificed, this is what we often say in database design, "Change space for efficiency '.

Although varchar2 saves space than char, if a varchar2 column is often modified and the length of the data to be modified is different each time, this will cause Row Migration, this causes redundant I/O, which should be avoided during database design and adjustment. In this case, replacing varchar2 with char would be better.

The following is an experiment in mysql:

Mysql> # tee input the content in the Command box to 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 |
| 16 | this | NULL | is | great |
| 17 | Hello | NULL |
| 22 | QQ Group | NULL | 111 | Henan |
| 24 | 123 | NULL | 123 | 123 |
| 26 | Hahahaha | 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 |
+ ---- + ---------- + ------ + ------------- + ---------------- +
22 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 an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'falut '',
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> # differences between char and 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 |
+ ---- + ----- +
1 row in set (0.00 sec)

Mysql> # concat connects to string functions
Mysql> select concat (ca ,'! '), Concat (vca ,'! ') From test;
+ ---------------- + ----------------- +
| Concat (ca ,'! ') | Concat (vca ,'! ') |
+ ---------------- + ----------------- +
| Aa! | Aa! |
+ ---------------- + ----------------- +
1 row in set (0.18 sec)

Mysql> # char and varchar are fixed length and variable length types, respectively.
Mysql> # For char (N), there are not enough length, and N length can be supplemented with spaces at the end, which wastes the end.
Mysql> # For varchar (N), do not fill in spaces, but there are 1-2 bytes before the column content to mark the content length of the column.
Mysql> # char reading speed is fast, but storage space is wasted. When a string with spaces at the end is read, the space cannot be read.
Mysql> # varchar reading speed is not faster than char, but the storage space is fully utilized to store arbitrary characters.
Mysql> exit;

# Tee input the content in the Command box to the specified file