How to use the TRUNCATE function to truncate decimals in MySQL

How to use the TRUNCATE function to truncate decimals in MySQL

In the database, we often need to process numerical data. Sometimes we need to truncate decimals to suit our needs. MySQL provides the TRUNCATE function to help us achieve this function.

The TRUNCATE function is a mathematical function used to truncate decimals and return the truncated result. Its syntax is as follows:

TRUNCATE(x, d)

where, x is the decimal to be truncated, and d is the number of digits to retain.

Let's look at an example below. In a student performance table, there is a field score used to store students' test scores (in decimal form). We want to truncate the scores to two decimal places.

First, we need to create a sample table and insert some sample data. The sample table structure is as follows:

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    score DECIMAL(5,2)
);

INSERT INTO students (name, score) VALUES ('张三', 89.765);
INSERT INTO students (name, score) VALUES ('李四', 92.348);
INSERT INTO students (name, score) VALUES ('王五', 78.1234);

Next, we use the TRUNCATE function to truncate decimals. The sample code is as follows:

SELECT name, TRUNCATE(score, 2) AS truncated_score FROM students;

In the above code, we use the SELECT statement to query the students' Name and truncated grade. Use the TRUNCATE function in the SELECT statement to truncate the score field score, and assign the truncated result to the alias truncated_score.

After executing the above code, you will get the following results:

+--------+-----------------+
| name   | truncated_score |
+--------+-----------------+
| 张三   |           89.76 |
| 李四   |           92.34 |
| 王五   |           78.12 |
+--------+-----------------+

As can be seen from the above results, the score field score is truncated to two decimal places and displayed in the correct format.

It should be noted that the TRUNCATE function processes decimals based on the truncation principle and does not round. If we want to perform rounding operations, we can use MySQL's ROUND function.

To summarize, the TRUNCATE function in MySQL can truncate decimals and retain the decimal part with specified digits. By using the TRUNCATE function, we can easily meet the need to truncate decimals.

The above is the detailed content of How to use the TRUNCATE function to truncate decimals in MySQL. For more information, please follow other related articles on the PHP Chinese website!