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).
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!