Home  >  Article  >  Database  >  How to implement division in MySQL with two decimal places

How to implement division in MySQL with two decimal places

青灯夜游
青灯夜游Original
2022-01-06 14:34:2035253browse

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

How to implement division in MySQL with two decimal places

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

How to implement division in MySQL with two decimal places

How to implement division in MySQL with two decimal places

The first calculation Method:

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

How to implement division in MySQL with two decimal places

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

How to implement division in MySQL with two decimal places

##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

How to implement division in MySQL with two decimal places

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!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn