MySQL Choose the right data type

One. Char and varchar

Char is a fixed-length query that is faster than the varchar speed . The disadvantage of char is that it wastes storage space .

When retrieving a char column, the returned result removes trailing spaces , so the program needs to handle the whitespace.

For data with a small change in length and a higher demand for query speed, you can consider using char.

As MySQL continues to escalate,the performance of varchar continues to improve and improve .

Storage Engine Usage Principles:

MyISAM: It is recommended to use fixed-length columns instead of variable-length columns.

InnoDB: It is recommended to use varchar type

Two. Text and BLOBs

When you save large text, you typically select text or a blob.

The difference is that BLOBs can hold binary data, such as photos.

Text and blobs also include text, Mediumtext, Longtext, and BLOBs, Mediumblob, Longblob, and the difference between them is that the length of the stored text differs from the storage byte.

The minimum storage type that satisfies the requirement should be selected according to the situation.

1.blob and text Create a "hole" in the data when performing a large number of delete operations

Delete the memory of the database before and after the ID 7 record has not changed.

You can see that the data files are not reduced because of data deletion.

To optimize (optimize) operations on a table:

table t1;

You can see that the table data files are greatly reduced and the "empty" space has been reclaimed.

2. Improve query performance with synthetic (synthetic) indexes

Creates a hash value based on the contents of a large text field (text, blob), stores the value in a separate data column, and then uses the hash value to find the data row.

Cons: only accurate queries (<, >= range operators are useless)

Hash values can be generated from the MySQL self-contained function MD5 (), SHA1 (), CRC32 (), or hash values can be computed from the programming language.

Note: If the hash algorithm generates strings with trailing spaces, do not store them in char, varchar columns , and they will be affected by trailing whitespace removal.

If you need to make a fuzzy query on a blob or CLOB field, you can index it with a prefix:

#前缀索引: Creates an index on the first 100 characters of the context field, create, index Idx_blob on T2 (context), #查询方法select * from T2 where context is like ' drake% ';
Note:% cannot be placed at the front

Synthetic indexes can only be used for exact matching, which reduces I/O to a certain extent, thus improving query efficiency.

3. Avoid retrieving large blob or text values when unnecessary.

4. Separate the Blob or text column into a separate table.

Three, floating point and fixed-point number

1. There is an error in floating point number.

2. Data that is sensitive to precision, such as currency, should be represented or stored in fixed-point numbers.

3. In the programming, if the use of floating point number, pay special attention to the error problem, and try to avoid floating-point comparison.

4. Be aware of the handling of some special values.

Iv. Selection of date types

1. Choose the minimum storage date type that meets your application, depending on your actual needs.

2. If the recording date is a minute or minute, and the record year is older, it is best to use DateTime instead of using timestamp.

3. If the recorded date needs to be used by users in different time zones, it is best to use timestamp because only it can correspond to the actual time zone in the date type.

MySQL Choose the right data type