The difference between MYSQL, char and varchar!

The difference between char and varchar:

1) Range of values:

Char: Value range: 0~255

VARCHAR: Range of values: 0~65535

2) space occupancy and speed:

Char: fixed-length string, large space-intensive, fast, generally used for the constant-size form submission data storage, such as: Identity account, phone number, password, etc.

VARCHAR: variable length string, small footprint, slow speed

3) storing and retrieving data:

Char: Whether the stored data has reached the given value of the bytes, the size of the given values to occupy the space, insufficient to automatically fill with space, the space after the retrieval will be automatically cut off.

such as: the definition of Tel char (8), the actual storage value example is 10086, occupies 4 bytes, the conclusion is: the actual consumption of bytes or 8, insufficient space padding.

The value of Varchar:varchar saves only the required number of bytes, plus one byte to record the length (if the column declaration is longer than 255, 2 bytes are used), and the varchar value is not populated when it is saved.

The trailing spaces remain on the value save and retrieve.

such as: Define Tel char (8), the actual storage value example is 10086, occupies 4 bytes, the conclusion is: the actual consumption of bytes is 4+1

4) Special points for varchar:

4.1) Storage Limits

The Varcahr field is to store the actual content separately from the clustered index, and the content begins with 1 or 2 of its own to represent the actual length, so the maximum length cannot exceed 65535

4.2) Encoding length limit

If the character type is GBK, each character can be up to 2 bytes, and the maximum length cannot exceed 32766.

If the character type is UTF8, each character can be up to 3 bytes and the maximum length cannot exceed 21845.

The difference between char and varchar and text three:

1) the way to store and retrieve data is different, the efficiency of data retrieval: Char>varchar>text

2) both char and varchar can have default values, text cannot specify default values

Three: note the point:

1, if the database engine is InnoDB, it is recommended to use varchar instead of char

2, from the space for the use of varchar, from the efficiency of the appropriate use of char, depending on the actual situation

3, MySQL rules a line can not exceed 65535 of the length, otherwise there will be a warning, it is recommended to switch to use text or blobs

The difference between MYSQL, char and varchar!