How to correctly select the MySQL DATA column type

MySQL DATA column type selection is a problem that we often encounter when designing tables. The following describes how to correctly select the MySQL DATA column type for your reference.

Selecting the correct data column type can greatly improve the database performance and make the database highly scalable. When selecting the MySQL DATA column type, consider the following:

The data type stored in the data column.

The value range of the data value.

Consider performance and processing efficiency.

Numeric operations are faster than character operations.

The processing speed of small types is faster than that of large types.

The processing efficiency of fixed-length and variable-length data tables is different.

The variable length type is prone to fragmentation after deletion and modification, reducing system performance. You need to regularly run the optimize table command to OPTIMIZE the data TABLE.

Since the fixed length type has a fixed length, it is easy to determine the starting point of each record, which can speed up data table repair.

Using fixed-length data columns in MyISAM and ISAM tables helps improve database performance.

In the InnoDB table, the data column types with fixed length and variable length are stored in the same way. Therefore, the data column types with fixed length have no performance advantage, however, the processing speed is faster because the data column type occupies less storage space.

The index type can speed up data query.

Specifying the not null attribute of a data column does NOT enable MySQL to determine whether the data column is NULL during the retrieval process, so it can speed up processing.

Whether the data is case sensitive and compared.

Whether to create an index on the data column.
 
 

In-depth parsing MySQL Association Table Creation

Alternative usage of MySQL table alias

MySQL left join query Experience Summary

How to display MYSQL table information

Three Common MySQL table creation statements