Shulou(Shulou.com)06/01 Report--

The types that support floating-point numbers in MySQL are FLOAT, DOUBLE, and DECIMAL. Unlike FLOAT and DOUBLE,DECIMAL, the DECIMAL type is actually stored in strings. The maximum possible range of values for DECIMAL is the same as that of DOUBLE, but the range of valid values is determined by the values of M and D. If M is changed and D is fixed, the range of values will increase with the increase of M.

For things with high precision, such as money, it is recommended to use the decimal type, do not consider float,double, because they are prone to errors, numeric and decimal synonym, numeric will be automatically converted to decimal.

DECIMAL was introduced from MySQL 5. 1, and the declaration syntax for columns is DECIMAL (MMagne D). In MySQL 5.1, the range of parameters is as follows:

M is the maximum number of numbers (precision). The range is 1: 65 (in older versions of MySQL, the allowed range is 1: 254), and the default value for M is 10. D is the number of digits to the right of the decimal point (scale). Its range is 030, but not more than M.

Description: 4 bytes for float, 8 bytes for double, and 2 bytes for decimail.

For example, the maximum value of DECIMAL (5J2) is 9999.99 because there are 7 bytes available.

Therefore, M and D are the key factors affecting the value range of DECIMAL (M, D).

Value range of type description (MySQL

3. 23) DECIMAL to 9999.9DECIMAL-999.9-9999.9 to 99999.9DECIMAL-999.9 to 9999.9-99999.9 to 999999.9DECIMAL-9.999 to 99.999-999.999 to 99999.99DECIMAL-9.999 to 99.999-999.999 to 99999.99DECIMAL

The range of values for a given DECIMAL type depends on the version of the MySQL data type. For previous versions of MySQL3.23, each value of the DECIMAL (M, D) column occupied M bytes, while symbols (if necessary) and decimal points were included in M bytes. Therefore, columns of type DECIMAL (5,2) range from-9.99 to 99.99 because they cover all possible five-character values.

# in MySQL 3.23 and later, the value range of DECIMAL (M, D) is equal to that of DECIMAL (M + 2, D) in earlier versions.

Conclusion:

When the value is within its range and there are more decimal places, the decimal places will be truncated directly. If the value is outside its range, it is filled with the maximum (minimum) value.

JAVA+Mysql+JPA practice

Msyql-Decimal corresponds to java-BigDecimal

Datasheet definition

@ Entitypublic class TestEntity extends Model {@ Column (nullable = true, columnDefinition = "decimal (11pm 2)") public BigDecimal price;}

Test results and description

/ * 1.mysql-Decimal (9 * 2jol 2) corresponds to java-BigDecimal * 2. The integer part is 9 digits, the decimal part is 2 digits, and the decimal part is rounded up * 3. The divider exceeds the limit by 9 digits, and an error is reported. * 4. Round off the decimal part to retain 2 decimal places * / TestEntity entity = new TestEntity (); entity.price = new BigDecimal (Double.toString (123456789.12d)); entity.save (); / / integer more than 9 places error / * entity = new TestEntity (); entity.price = new BigDecimal (Double.toString (1234567891.123d)); entity.save (); * / entity = new TestEntity () Entity.price = new BigDecimal (Double.toString (123456789.123d); entity.save (); entity = new TestEntity (); entity.price = new BigDecimal (Double.toString (123456789.126d)); entity.save (); entity = new TestEntity (); entity.price = new BigDecimal (Double.toString (123456789d)); entity.save (); entity = new TestEntity (); entity.price = new BigDecimal (Double.toString (123456.2355)) Entity.save (); entity = new TestEntity (); entity.price = new BigDecimal (Double.toString (123456.2356)); entity.save (); entity = TestEntity.find ("price =?", new BigDecimal (Double.toString (123456789.12d)). First (); System.out.println ("query results:" + entity.id + "," + entity.price) "

Insert result

1 123456789.12

2 123456789.12

3 123456789.13

4 123456789.00

5 123456.24

6 123456.24

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support. If you want to know more about it, please see the relevant links below.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.