How to implement division in MySQL with two decimal places

Mysql division method with two decimal places: 1. Use the "DECIMAL(P,D)" function, syntax "convert(value 1/value 2, decimal(15,2))"; 2. Use TRUNCATE() function, syntax "TRUNCATE(value1/value2,2)".

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

At work, we will encounter decimal calculations and need to show the 0 at the end of the decimal. We will use the DECIMAL function, which is a very powerful function: specific usageDECIMAL(P,D);

In the above syntax:

• P is the precision indicating the number of significant digits. P range is 1~65.

• D represents the number of digits after the decimal point. The range of D is 0~30. MySQL requires D to be less than or equal to (

DECIMAL(P,D)Indicates that the column can store P digits of D decimal places. The actual range of a decimal column depends on precision and scale.

Like the INT data type, the DECIMAL type also has UNSIGNED and ZEROFILL attributes. If the UNSIGNED attribute is used, negative values ​​will not be accepted for DECIMAL UNSIGNED columns.

If ZEROFILL is used, MySQL will pad the display value to 0 to display the width specified by the column definition. Also, if we use ZERO FILL on a DECIMAL column, MySQL will automatically add the UNSIGNED attribute to the column.

Test case:

Table structure of the database

The first calculation Method:

select convert(t/100,decimal(15,2)) as money from test

select convert(t/100,decimal(10,2)) as money from test

##Second calculation method

Return numbers X, truncated to D decimal places. If D is 0, the result has no decimal point or decimal part. D is a negative number, causing the D digit to the left of the decimal point of the value X to become zero. (Simply put, there is no rounding)

SELECT TRUNCATE(t/100,2) as g from test

TRUNCATE(x,d): The function returns the number x that is rounded to d decimal places. If the value of d is 0, the result will have no decimal point or decimal part. If d is set to a negative number, all low-order values ​​starting from the dth digit from the left of the decimal point of x are truncated (returned to zero).

[Related recommendations:

mysql video tutorial]

The above is the detailed content of How to implement division in MySQL with two decimal places. For more information, please follow other related articles on the PHP Chinese website!