How to correctly select MySQL data column type

MySQL data column type selection is a common problem when we design the table, below teaches you how to correctly select the MySQL data column type, for your reference to learn.

Choosing the Right data column type can greatly improve the performance of the database and make the database highly scalable. When choosing a MySQL data column type, consider the following:

The type of data that is stored in the data column.

The value range of the data value.

Consider performance and processing efficiency.

Numeric manipulation is faster than character manipulation.

Small types of processing speed are faster than large types.

Fixed-length types and variable-length types have different processing efficiencies in different data tables.

Variable-length types are prone to fragmentation after deletion and modification, reduce system performance, and need to run the Optimize Table command regularly to optimize the data tables.

Fixed length types have fixed lengths, so it is easy to determine the starting point for each record to speed up the repair of data tables.

The use of fixed-length type data columns in MyISAM and ISAM tables helps improve database performance.

In the InnoDB table, fixed-length and variable-length data column types are stored in the same way, so fixed-length data column types do not have a performance advantage, but because the data column types with the available length have less storage space, processing speeds are faster.

An indexed type can speed up the query of data.

Explicitly specifying the NOT NULL property of a data column allows MySQL to speed up processing by not having to determine whether the data column is null during retrieval.

How the data is compared and case-sensitive.

Whether to index on the data column.

How to correctly select MySQL data column type