The float type of mysql is very strange.

The float type of mysql is very strange. I have a table named test, which contains three fields,
Uid, one, two, uid is int type, auto-increment, one field is float (3, 2) two field is float (5, 2 ).

According to the principle, I can insert a number like 123.12 in the one field, right, that is, the first three digits and the last two digits.
For the two field, I can insert the first five digits and the last two digits, but I insert

Insert into test values (null, 123.12, 12345.12) is successfully inserted, but the data displayed is different. why?


If I want to insert a value of 98.12, that is, the first two digits and two decimal places, should this field be set to float type?

Reply to discussion (solution)

1. your definition is incorrect.
MySQL allows the use of non-standard syntax: FLOAT (M, D), REAL (M, D), or double precision (M, D ). Here, "(M, D)" indicates that the value displays a total of M-bit integers, where D is behind the decimal point. For example, a column defined as FLOAT (999.9999) can be displayed. MySQL returns a rounded value when saving the value. Therefore, if 999.00009 is inserted in the FLOAT (999.0001) column, the approximate result is.

2. precision requirements should be used
The DECIMAL and NUMERIC types are treated as the same type in MySQL. They are used to save values that must be accurate, such as currency data. When declaring a column of this type, you can (and usually need to) specify the precision and scale. for example:
Salary DECIMAL (5, 2)
In this example, 5 is the precision and 2 is the scale. The precision indicates the primary digits of the saved value, and the scale indicates the digits that can be saved after the decimal point.

1. your definition is incorrect.
MySQL allows the use of non-standard syntax: FLOAT (M, D), REAL (M, D), or double precision (M, D ). Here, "(M, D)" indicates that the value displays a total of M-bit integers, where D is behind the decimal point. For example, a column defined as FLOAT (999.9999) can be displayed. MySQL returns a rounded value when saving the value. Therefore, if 999.00009 is inserted in the FLOAT (999.0001) column, the approximate result is.

2. precision requirements should be used
The DECIMAL and NUMERIC types are treated as the same type in MySQL. They are used to save values that must be accurate, such as currency data. When declaring a column of this type, you can (and usually need to) specify the precision and scale. for example:
Salary DECIMAL (5, 2)
In this example, 5 is the precision and 2 is the scale. The precision indicates the primary digits of the saved value, and the scale indicates the digits that can be saved after the decimal point.

Well, I understand. thanks to the Xu version. for example, when storing currency, you must set a positive number instead of a negative number. can you directly restrict it in the database or use PHP regular expressions. I tried to insert a negative number directly, such as-91.22. can mysql only store positive numbers?

The value of a field such as salary DECIMAL (123.12), that is, the maximum number of digits is the first three digits and the last two digits. this is required. for example, you can insert, if 123456 is inserted, it will not work. although it is also five digits, there are no two decimal places.

123456 is saved in DECIMAL (12345.00), which means is out of bounds.
DECIMAL (999.99) can only be saved ~ 999.99

What is the reason that a positive number is required for currency storage?
Negative number? Arrears

To limit positive numbers, set the UNSIGNED attribute.

Decimal field is recommended.