The type of float for MySQL is very strange

Source: Internet
Author: User
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

  • Contact Us

    The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

    If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

    A Free Trial That Lets You Build Big!

    Start building with 50+ products and up to 12 months usage for Elastic Compute Service

    • Sales Support

      1 on 1 presale consultation

    • After-Sales Support

      24/7 Technical Support 6 Free Tickets per Quarter Faster Response

    • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.