Comparison of float and decimal in MySQL database

Float,double is prone to error, when precision requirements are higher, it is recommended to use decimal to save, decimal in the MySQL memory is stored as a string, used to define the currency requirements of high precision data.

Example

Creates a new tab table (defines two fields, float and decimal, respectively)

The code is as follows	Copy Code
CREATE Table tab (Col_f float (10,2), col_d decimal (10,2)); Insert two lines of records as an experiment Insert into tab values (1234567.21, 1234567.21), (9876543.21, 9876543.12);

In data migrations, float (m,d) is not a standard definition and is best not to be used in this way. M is the precision, D is the scale.

code is as follows

copy code

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 is not defined, the precision and scale of the double are stored in the given value store, which is related to the OS and the current hardware.

decimal defaults to decimal (10,0)

Because of the error problem, in the program, less using floating-point number to do = comparison, you can do range comparison. If numeric comparisons are used, it is best to use the decimal type.

In precision, symbols are not counted:

The code is as follows	Copy Code
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 is 8 bytes, and Decimail (m,d) occupies m+2 bytes.

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

Float is a floating-point number and cannot specify decimal places.
Decimal is an exact number, and you can specify precision.
For the MySQL 5来 say the maximum of P in decimal (p,s) is 65,s Max is 30
A decimal data type can store up to 38 digits, storing an accurate (accurate) numeric representation without storing an approximate value.

When data values must be stored precisely as specified, you can store numbers with decimal data types with decimals.
The float and real data types are referred to as approximate data types. The exact value is not stored. When an exact number state is required, such as in a financial application, these data types are not used in operations that require rounding, or in an operation that checks for equivalence. Use an integer, decimal, Money, or smallmone data type.

Avoid the use of float or real columns in WHERE clause search conditions (especially = and <> operators). It is best to limit the use of float and real columns to do > or < comparisons.

Float uses the data type is the C language inside float, decimal uses is the MySQL definition decimal_t, this source file decimal.c I have read, the structure body is so defined:

The code is as follows

Copy Code

/** INTG is the number of *decimal* digits (not number of decimal_digit_t ' s!) Before the point Frac is the number of decimal digits on the point Len is the length of buf (length of allocated spaces) in decimal_digit_t ' s, Not in bytes Sign false means positive, true means negative BUF is an array of decimal_digit_t ' s */ typedef struct ST_DECIMAL_T { int INTG, frac, Len; My_bool sign; decimal_digit_t Buf[9]; } decimal_t;

So the precision and range of the number of representations are known.