MySQL data type optimization (iv)---Select identifiers, special type data

First, select an identifier

Once a type has been selected, to ensure that the same type is used in all associations, the exact match between types is required, including properties such as unsigned.

Issue: Implicit type conversions can also cause difficult-to-find errors when comparing operations.

(1) Integers: Usually the best choice for identifying columns, because it they are very fast and may use auto_increment.

(2) enum and set type: usually not available.

(3) String type: If possible, you should avoid using string types as identity columns because they are very space-consuming and often slower than numeric types.

There is also a need to pay attention to a completely "random" string, such as a string produced by MD5 (), SHA1 (), or UUID (). The new values generated by these functions are distributed in a large amount of space, which causes the INSERT and some SELECT statements to become slow.

If you store the UUID value, you should remove the "-" symbol, or, better yet, use the Unhex () function to convert the UUID () value to a 16-byte number and store it in a binary (16) column, which can be formatted with the hex () function to format the hexadecimal format.

II. Special Data types

IPV4 address. A varchar (15) column is commonly used to store IP addresses. However, they are actually 32 unsigned integers, not strings. Using a decimal point to divide an address into four segments is just a way to make people read easily. Therefore, the IP address should be stored with an unsigned integer. MySQL provides the Inet_aton () and Inet_ntoa () functions to convert between the two presentation methods.

Example: Select Inet_aton (' 192.168.222.255 '); Output: 3232292607

Select Inet_ntoa (3232292607); Output:192.168.222.255

MySQL data type optimization (iv)---Select identifiers, special type data