A brief introduction to the usage of decimal type in MySQL

The floating-point types supported in MySQL are FLOAT , DOUBLE , and DECIMAL . The DECIMAL type is different from FLOAT and DOUBLE. DECIMAL is actually stored as a string. The maximum possible range of DECIMAL values ​​is the same as that of DOUBLE, but its effective range is determined by the values ​​of M and D. If M is changed and D is fixed, its value range will increase as M increases.

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

DECIMAL was introduced in MySQL 5.1. The column declaration syntax is DECIMAL(M,D). In MySQL 5.1, the parameter value range is as follows:

• M is the maximum number of numbers (precision). Its range is 1 to 65 (in older MySQL versions, the allowed range is 1 to 254), and the default value of M is 10.
• D is the number of digits to the right of the decimal point (scale). Its range is 0 to 30, but must not exceed M.

Note: float occupies 4 bytes, double occupies 8 bytes, and decimail (M, D) occupies M+2 bytes.

For example, the maximum value of DECIMAL(5,2) 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).

Type Description Value Range (MySQL < 3.23) Value Range (MySQL >= 3.23)
DECIMAL(4,1) -9.9 to 99.9 -999.9 to 9999.9
DECIMAL(5,1) -99.9 to 999.9 -9999.9 to 99999.9
DECIMAL(6,1) -999.9 to 9999.9 -99999.9 to 999999.9
DECIMAL(6,2) -99.99 to 999.99 -9999.99 to 99999.99
DECIMAL(6,3) -9.999 to 99.999 -999.999 to 9999.999

The range of values ​​for a given DECIMAL type depends on the version of the MySQL data type. For versions prior to MySQL 3.23, each value of a DECIMAL(M, D) column occupies M bytes, and the sign (if necessary) and decimal point are included in the M bytes. Thus, a column of type DECIMAL(5, 2) can have values ​​ranging from -9.99 to 99.99, because they cover all possible 5-character values.

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

in conclusion:

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

JAVA+MySQL+JPA Practice

msyql-Decimal corresponds to java-BigDecimal

Data table definition

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

Test results and explanation

 /**
     * 1.mysql-Decimal(9+2,2) corresponds to java-BigDecimal
     * 2. The integer part is 9 digits, the decimal part is 2 digits, and the decimal is rounded off. * 3. If the divisible part exceeds the limit of 9 digits, an error will be reported.
     * 4. If the decimal part exceeds the number of digits, it will be rounded off and truncated to 2 decimal places*/
    TestEntity entity = new TestEntity();
    entity.price = new BigDecimal(Double.toString(123456789.12d));
    entity.save();
    // Integer exceeds 9 digits.
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 results

1 123456789.12
2 123456789.12
3 123456789.13
4 123456789.00
5 123456.24
6 123456.24

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links