What is the number type of mysql?

The number types of mysql are: INT type, TINYINT type, SMALLINT type, MEDIUMINT type, BIGINT type, FLOAT type, DOUBLE type, and DECIMAL type.

(Recommended tutorial: mysql video tutorial)

mysql numeric type

1. MySQL integer type

The integer type is also called numerical data. The numerical data type is mainly used to store numbers.

MySQL provides a variety of numeric data types. Different data types provide different value ranges. The larger the value range that can be stored, the greater the storage space required.

The main integer types provided by MySQL are TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT, and AUTO_INCREMENT auto-increment constraints can be added to their attribute fields. The following table lists the numeric types in MySQL.

As you can see from the above table, different types of integers require different numbers of bytes to store. The one that occupies the smallest number of bytes is the TINYINT type, which occupies bytes The largest is the BIGINT type. The more bytes the type occupies, the larger the range of values ​​it can represent.

The value range of each data type can be found based on the number of bytes occupied. For example, TINYINT requires 1 byte (8bit) to store, then the maximum value of TINYINT unsigned number is 28-1, which is 255; the maximum value of TINYINT signed number is 27-1, which is 127. The calculation method for the value range of other types of integers is the same, as shown in the following table.

# Tip: The display width has nothing to do with the value range of the data type. The display width only specifies the maximum number of digits that MySQL may display. If the number of digits is less than the specified width, it will be filled with spaces. If a value larger than the display width is inserted, the value can still be inserted and displayed as long as the value does not exceed the value range of the integer of that type. For example, the year field is inserted into 19999. When using SELECT to query the column value, MySQL will display the complete 19999 with 5 digits instead of the 4-digit value.

Other integer data types can also specify the required display width when defining the table structure. If not specified, the system specifies a default width value for each type.

Different integer types have different value ranges and require different storage space. Therefore, the most appropriate type should be selected according to actual needs. This will help improve query efficiency and save storage space.

2. mysql decimal type

MySQL uses floating point numbers and fixed point numbers to represent decimals.

There are two floating-point types, single-precision floating-point numbers (FLOAT) and double-precision floating-point numbers (DOUBLE); there is only one fixed-point type, which is DECIMAL.

Both floating-point types and fixed-point types can be represented by (M, D), where M is called precision, indicating the total number of digits; D is called scale, indicating the number of decimal digits.

The value range of floating point number type is M (1~255) and D (1~30, and cannot be greater than M-2), which represent the display width and number of decimal places respectively. M and D are optional in FLOAT and DOUBLE, and the FLOAT and DOUBLE types will be saved to the maximum precision supported by the hardware. The default D value for DECIMAL is 0 and M value is 10.

The following table lists the decimal types and storage requirements in MySQL.

The DECIMAL type is different from FLOAT and DOUBLE. DOUBLE is actually stored in the form of a string. The possible maximum value range of DECIMAL is the same as DOUBLE, but the effective value range is determined by M and D. If M is changed and D is fixed, the value range will become larger as M becomes larger.

As can be seen from the above table, the storage space of DECIMAL is not fixed, but is determined by the precision value M, occupying M 2 bytes.

The value range of the FLOAT type is as follows:

• The signed value range: -3.402823466E 38～-1.175494351E-38.

• Unsigned value range: 0 and -1.175494351E-38～-3.402823466E 38.

The value range of DOUBLE type is as follows:

• Signed value range: -1.7976931348623157E 308～-2.2250738585072014E-308.

• Unsigned value range: 0 and -2.2250738585072014E-308～-1.7976931348623157E 308.

Tip: Regardless of whether it is a fixed-point or floating-point type, if the user-specified precision exceeds the precision range, it will be rounded for processing.

FLOAT and DOUBLE will default to the actual precision (determined by the computer hardware and operating system) when the precision is not specified. DECIMAL will default to (10, 0) if the precision is not specified.

The advantage of floating-point numbers over fixed-point numbers is that they can represent a larger range when the length is certain; the disadvantage is that it can cause accuracy problems.

Finally, let me emphasize: In MySQL, fixed-point numbers are stored in the form of strings. When the accuracy requirements are relatively high (such as currency, scientific data), it is better to use the DECIMAL type, and the other two float types are better. Point numbers are also prone to problems when performing subtraction and comparison operations, so you need to pay attention when using floating point numbers and try to avoid floating point comparisons.

The above is the detailed content of What is the number type of mysql?. For more information, please follow other related articles on the PHP Chinese website!