How to use the TRUNCATE function in MySQL to truncate numbers to specify the number of decimal places for display

How to use the TRUNCATE function in MySQL to truncate a number to a specified number of decimal places for display

In the MySQL database, if we need to truncate a number to a specified number of decimal places for display, we can use the TRUNCATE function. The TRUNCATE function can help us achieve precise truncation of numbers, thereby retaining data with a specified number of decimal places.

The syntax of the TRUNCATE function is as follows:
TRUNCATE(number, decimal_places)

Among them, number is the number to be truncated, and decimal_places is the number of decimal places.

The following are several examples to demonstrate how to use the TRUNCATE function in MySQL.

First, we create a table named "products" to store product price information.

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10, 2)
);

Next, we insert some sample data into the "products" table:

INSERT INTO products (name, price)
VALUES ('Product 1', 19.99),
       ('Product 2', 49.99),
       ('Product 3', 79.99);

Now, we can use the TRUNCATE function to truncate the decimal places of the price fields so that they only display two decimal places.

SELECT name, TRUNCATE(price, 2) AS truncated_price
FROM products;

Running the above query, we will get the following results:

+------------+------------------+
| name       | truncated_price  |
+------------+------------------+
| Product 1  | 19.99            |
| Product 2  | 49.99            |
| Product 3  | 79.99            |
+------------+------------------+

As shown above, the TRUNCATE function successfully truncated the number of decimal places in the price field and displayed the result to only two decimal places.

We can also try to modify the value of the decimal_places parameter to see different truncation results. For example, setting decimal_places to 1 will produce the following results:

SELECT name, TRUNCATE(price, 1) AS truncated_price
FROM products;

Running the above query, we will get the following results:

+------------+------------------+
| name       | truncated_price  |
+------------+------------------+
| Product 1  | 19.9             |
| Product 2  | 49.9             |
| Product 3  | 79.9             |
+------------+------------------+

As shown above, the decimal places of the price field are successfully Truncated to 1 digit, the result shows only one decimal place.

It should be noted that the TRUNCATE function does not round, it simply truncates the number of decimal places. If rounding is required, the ROUND function should be used instead of the TRUNCATE function.

To sum up, the TRUNCATE function is a very convenient function in MySQL to truncate the number of decimal places in a number. By using the TRUNCATE function, we can easily control the precision of the numerical value to meet our business needs.

The above is the detailed content of How to use the TRUNCATE function in MySQL to truncate numbers to specify the number of decimal places for display. For more information, please follow other related articles on the PHP Chinese website!