Defining the types of data fields in MySQL is very important for database optimization.

MySQL supports a variety of data types, which can be roughly divided into three categories: numeric date/time and string.

Second, the numerical type

1. Integer type

2. Floating point number

If you want to ensure accurate values, it is recommended to use fixed-point data types. The floating-point types in MySql are float, double and real. They are defined as floating-point (m, d), real-number (m, d) and double-precision (m, d).

The values of m and d in FLOAT and DOUBLE are both 0 by default, that is, there is no limit to the number of digits except the maximum and minimum values. Theoretically, the allowable values are-1.7961348 623157e+308 ~-2.5014e-308, 0 and 2.50548.56566566 and d are as follows.

(measured by MySql5.7, which is different from that calculated by IEEE standard, which is introduced below): the range of m is 0~255. FLOAT only guarantees the accuracy of 6 significant digits, so in FLOAT(M, d), where m < =6, the digits are usually accurate. If both m and d are clearly defined, the treatment after exceeding the range is the same as decimal.

The value range of d is from 0 to 30, and it must be

CHAR value, filling the space to the right of it to reach the specified length. When the CHAR value is retrieved, the space at the end is deleted, so we can't have a space on the right side of the string when storing it. Even if there is, it will be deleted after the query. No case conversion is performed during storage or retrieval.

Three. Time and Date Type (5)

The values of "0" are as follows:

Please click to enter a picture description.

Four, various types of self-use warehouse

1. value type

Please click to enter a picture description.

The number of fixed points is special, and it is also related to the specific version, which is explained separately here:

9 decimal (based on 10) numbers are compressed into 4 bytes in binary format to represent decimal column values. The storage of the integer and decimal parts of each value is determined separately. Each multiple of 9 digits needs 4 bytes, and the "remaining" bits need a part of 4 bytes. The following table shows the storage requirements beyond digits:

Please click to enter a picture description.

2. Time and date

Please click to enter a picture description.

Since version 5.6.4, storage requirements have changed, depending on accuracy. The storage required for the uncertain part is as follows:

Please click to enter a picture description.

For example, TIME(0), TIME(2), TIME(4) and TIME(6) use 3, 4, 5 and 6 bytes respectively.

3. Line

Please click to enter a picture description.

4. Type selection

In order to optimize storage, the most accurate type should be used in any case.

For example, if the value of the column ranges from 1 to 99999, and if integers are used, then mediumunsigned is a good type. Of all the types that can represent the value of this column, this type uses the least storage.

Perform all basic calculations (+,-,*,/) on small series with an accuracy of 65 bits (based on 10).

Calculate decimal values using double precision operations. If accuracy is not too important or speed is the most important, double type is enough. In order to achieve high accuracy, it can be converted into a fixed-point type saved in BIGINT. This allows you to perform all calculations using 64-bit integers and convert the results back to floating-point values as needed. ?

5. Use SQL statements from other databases

In order to execute code using SQL written for other databases, MySQL maps column types, as shown in the following table. Through these mappings, table definitions can be easily imported into MySQL from other database engines:

Please click to enter a picture description.