I have a table test with three fields in it.
Uid,one,two,uid is an int type, self-increment, one field is float (3,2) and the field is float (5,2).
According to the one field I can insert 123.12 such numbers, that is, the front three bits, the back two bits
Two fields I can insert the front 5 bit, the back two bit, but I insert a
INSERT into test values (null,123.12,12345.12) was inserted successfully, but the data displayed is not the same, which is why
If I need to insert the value 98.12, which is the front 2 bit, the decimal point two bit, this field should be set to float type
Reply to discussion (solution)
1. You defined the wrong
MySQL allows non-standard syntax: FLOAT (M,D) or real (m,d) or double PRECISION (m,d). Here, "(M,d)" indicates that the value displays a total of M-bit integers, where the D-bit is behind the decimal point. For example, a column defined as float (7,4) can be displayed as-999.9999. MySQL is rounded when the value is saved, so if you insert 999.00009 in float (7,4) aligns, the approximate result is 999.0001.
2, with precision requirements should be used
The decimal and numeric types are considered to be the same type in MySQL. They are used to hold values that must be of exact precision, such as currency data. When declaring columns of that type, you can (and usually want to) specify the precision and scale, for example:
Salary DECIMAL (5,2)
In this example, 5 is precision, and 2 is the scale. The precision represents the primary number of digits that hold the value, and the scale represents the number of digits that can be saved after the decimal point.
1. You defined the wrong
MySQL allows non-standard syntax: FLOAT (M,D) or real (m,d) or double PRECISION (m,d). Here, "(M,d)" indicates that the value displays a total of M-bit integers, where the D-bit is behind the decimal point. For example, a column defined as float (7,4) can be displayed as-999.9999. MySQL is rounded when the value is saved, so if you insert 999.00009 in float (7,4) aligns, the approximate result is 999.0001.
2, with precision requirements should be used
The decimal and numeric types are considered to be the same type in MySQL. They are used to hold values that must be of exact precision, such as currency data. When declaring columns of that type, you can (and usually want to) specify the precision and scale, for example:
Salary DECIMAL (5,2)
In this example, 5 is precision, and 2 is the scale. The precision represents the primary number of digits that hold the value, and the scale represents the number of digits that can be saved after the decimal point.
Well, understand, thank you Xu version, that is, such as currency storage must be positive, not negative, where the database can be directly to limit it, or through the PHP regular to limit. I tried to insert a negative number directly, such as a value of 91.22, MySQL can limit the storage of only positive numbers
A field salary decimal (5,2) Such a field to insert the value, that is, the maximum number of digits is the front 3 bits, followed by 2 bits, it must be, such as inserting 123.12 can be, if inserting 123456 such is not possible, although also 5 bits, but there is no two digits after the decimal point.
123456 saved in DECIMAL (5,2) is 12345.00 of course it crosses the border.
DECIMAL (5,2) can only be saved-999.99 ~ 999.99
Currency storage must be positive, what is the reason
Negative number is not OK? Indicate arrears
To limit positive numbers, you should set the unsigned property.
It is recommended that you use the decimal field