Float and decimal type in MySQL _ MySQL

Float and decimal type bitsCN.com in MySQL

Float and decimal types in MySQL

Float and double are prone to errors. when the precision requirements are relatively high, decimal is recommended for storage. decimal is stored in mysql memory as a string to define data with high precision required by currency. In data migration, float (M, D) is not defined as a standard, so it is best not to use it like this. M is the precision, and D is the scale.

Mysql> create table t1 (c1 float (10, 2), c3 decimal (10, 2 ));

Query OK, 0 rows affected (0.02 sec)

Mysql> insert into t1 values (1234567.23, 1234567.23 );

Query OK, 1 row affected (0.01 sec)

Mysql> select * from t1;

+ ------------ +

| C1 | c3 |

+ ------------ +

| 1234567.25/1234567.23 |

+ ------------ +

1 row in set (0.02 sec)

Mysql> insert into t1 values (9876543.21, 9876543.12 );

Query OK, 1 row affected (0.00 sec)

Mysql>

Mysql> select * from t1;

+ ------------ +

| C1 | c3 |

+ ------------ +

| 1234567.25/1234567.23 |

| 9876543.00/9876543.12 |

+ ------------ +

2 rows in set (0.00 sec)

When fload and double are not defined for precision and scale, storage is stored based on the given value, which is related to the current hardware.

Decimal: decimal (10, 0) by default)

Because of the error, range comparison can be performed if the floating point number is less =. If the value is compared, it is best to use the decimal type.

The characters are not included in the precision:

Mysql> insert into t1 values (-98765430.21,-98765430.12 );

Query OK, 1 row affected (0.01 sec)

Mysql> select * from t1;

+ -------------- +

| C1 | c3 |

+ -------------- +

| 1234567.25/1234567.23 |

| 9876543.00/9876543.12 |

|-98765432.00 |-98765430.12 |

+ -------------- +

3 rows in set (0.00 sec)

Float occupies 4 bytes, double occupies 8 bytes, and decimail (M, D) occupies M + 2 bytes.

The decimal type can accurately represent very large or very precise decimals. Up to 1028 (positive or negative) digits and up to 28 digits with a valid digit can be stored as decimal type without losing its accuracy. This type is useful for applications (such as accounting) that must avoid rounding errors.

Float is a floating point number and cannot specify a decimal place.

Decimal is the exact number. you can specify the precision.

For mysql 5, the maximum value of p in decimal (p, s) is 65, and the maximum value of S is 30.

A decimal data type can store up to 38 numbers. it stores an accurate (accurate) numeric expression without an approximate value.

When data values must be precisely stored as specified, decimal data types with decimal numbers can be used to store numbers.

Float and real data types are called similar data types. The exact value is not stored. these data types are not used when exact numeric states are required, such as in financial applications, in those operations that require rounding, or in equivalent checks. The integer, decimal, money, or smallmone data type is required.

Do not use float or real columns in WHERE clause search conditions (especially the = and <> operators. It is best to restrict the use of float and real columns for> or <comparison.

BitsCN.com