Optimal Choice of Mysql Data Types

It is important to carefully select a data type. Why? It can improve performance and the principle is as follows:

● Storage (memory, disk), thus saving I/O (retrieval of the same data)
● Computing, reducing the CPU load

There are three types of data: Numbers, characters, and periods.

(I) Numbers

① Category

② It makes no sense to specify the width for the integer type. Simply put, it is also to display the number of characters and the humanization point.
Because, for storage and computing, INT (1) and INT (100) are the same

③ For floating point, we recommend that you only specify the data type, instead of the precision.

④ If possible, it is used only for precise decimal calculations, such as the storage of financial data.
However, some financial software in tianchao uses float as the wage type in its database.
The ugly display and contempt of naked capitalists !!!

⑤ When the data volume is large, convert the real number type to the integer type.
The reason is simple:
● Inaccurate floating point
● Fixed-point computing is expensive
For example:
To store financial data accurate to one thousandth, You can multiply all amounts by 1 million, and then there is a bigint



(Ii) String


① Category

② How data is stored depends on the storage engine
Char and varchar may be different in memory and disk of different engines
However, for char, it is the same in filling and intercepting, because,
This is done at the server layer.

③ Common char application scenarios

● Short string orAll valuesAre close to the same length, such as the MD5 value of the Stored Password
● Columns that are frequently changed and contain less fragments due to Char
● Very short columns, such as gender and char (1), are more efficient. After all, varchar (1) Wastes 1 byte.

④ It's tricky with character sets.

Csdn has a very popular post: Does someone think Unicode is equivalent to UTF-8? Click to view discussion posts
I have no idea how to discuss it, because these two are two different things at all.
Unicode is character encoding.
Utf8 is a character set

We recommend that you use varchar for Unicode character sets and wall cracking.
Obviously, utf8 is also unicode encoded, and, with the international trend, utf8 is the first choice
The result is that, in the competition between char and varchar, varchar is the biggest winner.

You can view the character sets supported by MySQL Unicode and the current character sets in the following ways

mysql> select CHARACTER_SET_NAME from character_sets     ->  where DESCRIPTION like '%Unicode%'\G;*************************** 1. row ***************************CHARACTER_SET_NAME: utf8*************************** 2. row ***************************CHARACTER_SET_NAME: ucs2*************************** 3. row ***************************CHARACTER_SET_NAME: utf8mb4*************************** 4. row ***************************CHARACTER_SET_NAME: utf16*************************** 5. row ***************************CHARACTER_SET_NAME: utf325 rows in set (0.06 sec)mysql> status;--------------mysql  Ver 14.14 Distrib 5.5.16, for Linux (i686) using  EditLine wrapperConnection id:          1Current database:       information_schemaCurrent user:           root@localhostSSL:                    Not in useCurrent pager:          stdoutUsing outfile:          ''Using delimiter:        ;Server version:         5.5.16-log Source distributionProtocol version:       10Connection:             Localhost via UNIX socketServer characterset:    latin1Db     characterset:    utf8Client characterset:    utf8Conn.  characterset:    utf8UNIX socket:            /tmp/mysql.sockUptime:                 2 min 9 sec

⑤ Varchar is recommended for InnoDB
Now InnoDB has been used in many occasions, and varchar has won again.

⑥ Store big data

● BLOB: Binary storage, no character set or sorting rules
● Text: character storage, character sets, and sorting rules

It is best to avoid using the two. If possible, you can separate the Blob text column into a separate table.

When blob or text is used to delete or update a large number of objects,
We recommend that you use optimize table fragment regularly.



(Iii) Date and Time types

Common examples include datatime, timestamp, and date.
In terms of storage, the timestamp occupies 4 bytes and the other two 8 bytes.
Therefore, if there is no accident, it is best to use timestamp whenever possible, even if it is more naughty, such as time zone-related




By David Lin
2013-06-02
Good luck