Simple selection of MySQL data types

Select the appropriate data type:
Char and varchar:

+---------+------------+| char (6) | varchar (6) |+---------+------------+|         | |            | str |     str        | | str123  | str123     | | str123  | str123     |+---------+------------+4 rows in Set (0.00 sec )

　　

The storage requirements for char (6) are always 6 bytes.
varchar (6) Storage "requires a 1 byte."
varchar (6) Stores ' str ' requires 4 bytes.
varchar (6) Stores ' str123 ' requires 7 bytes.
varchar (6) Stores ' str123no ' requires 7 bytes.

Summary: char (n) requires a storage byte of n
The required byte of varchar (n) depends on the inserted string m,m+1<= bytes <=n+1

Text and Blob
When storing a small number of strings, we will choose char or varchar, and we will usually select text or blob when storing large text. The main difference between the two is that blobs can be used to hold binary data, such as photos. Text can only be used to hold character data, such as a piece of paper.
Text is also divided into text, Mediumtext, Longtext
BLOBs are also divided into BLOBs, Mediumblob, Longblob
The main difference is that the length of the stored text differs from the storage byte.

Frequent use of blobs and text types can cause performance degradation. The delete operation leaves a large "void" in the table (and does not empty the space because of reduced data). Like a bookshelf, the book takes away, the space is still occupied, and you can periodically refine the table to defragment it. Optimize table

Selection of date types
DATE, Time, DATETIME, TIMESTAMP
Several principles:
If it only takes years, you can use year type.
If the required time range is large, use datetime
If used for different time zone users, use timestamp

Floating point and fixed point number
Floating-point numbers The average user contains numeric values that have decimal digits. float, double if inserting data into an over defined precision is likely to be rounded. The fixed-point number is actually stored as a string. So the fixed-point number can save the data accurately. If the actual inserted data is greater than the precision, it will be alerted. Decimal is used to denote fixed-point numbers.
In applications with high accuracy requirements, similar currencies are saved using fixed-point numbers.

Simple selection of MySQL data types