MySQL TRUNCATE()用法及代码示例

在本文中，您将看到TRUNCATE()函数的工作方式。 MySQL中的TRUNCATE函数用于将数字截断为指定的小数位数。

用法:

TRUNCATE( X, D)

参数：
TRUNCATE()函数接受上述和以下描述的两个参数。

• X -要截断的数字。
• D -给定数字将被截断的小数位数。如果为0，它将删除所有十进制值，并且仅返回整数。如果它是负数，那么数字将被截断到小数点的左侧。

返回值：
截断到指定位置后，它返回数字。

示例1：
当D为0时截断数字。

截断负数-

SELECT TRUNCATE(-10.11, 0) AS Truncated_Number ;

输出：

+------------------+
| Truncated_Number |
+------------------+
|              -10 |
+------------------+

截断正数-

SELECT TRUNCATE(100.61, 0) AS Truncated_Number ;

输出：

+------------------+
| Truncated_Number |
+------------------+
|              100 |
+------------------+

示例2：
当D为负数(-ve)时截断数字。
截断负数-

SELECT TRUNCATE(-19087.1560, -3) AS Truncated_Number;

输出：

+------------------+
| Truncated_Number |
+------------------+
|           -19000 |
+------------------+
1 row in set (0.00 sec)

截断正数-

SELECT TRUNCATE(10876.5489, -1) AS Truncated_Number;

输出：

+------------------+
| Truncated_Number |
+------------------+
|            10870 |
+------------------+

示例3：
当D为正数(+ ve)时截断数字。
截断负数(最多2个小数位)-

SELECT TRUNCATE(-7767.1160, 2) AS Truncated_Number;

输出：

+------------------+
| Truncated_Number |
+------------------+
|         -7767.11 |
+------------------+
1 row in set (0.00 sec)

截断正数(最多3个小数位)-

mysql> SELECT TRUNCATE(17646.6019, 3) AS Truncated_Number;

输出：

+------------------+
| Truncated_Number |
+------------------+
|        17646.601 |
+------------------+

示例4：
TRUNCATE函数也可用于查找列数据的截断值。在此示例中，我们将为“价格”列查找截断的值。为了演示创建一个名为Product的表

CREATE TABLE Product
(
    Product_id INT AUTO_INCREMENT,  
    Product_name VARCHAR(100) NOT NULL,
    Buying_price DECIMAL(13, 6) NOT NULL,
    Selling_price DECIMAL(13, 6) NOT NULL,
        Selling_Date Date NOT NULL,
    PRIMARY KEY(Product_id)

);

现在将一些数据插入到Product表中：

INSERT INTO  
Product(Product_name, Buying_price, Selling_price, Selling_Date)
VALUES
    ('P6', 1060.865460, 1700.675400, '2020-08-26' ),
    ('P2', 2000.154300, 3050.986700, '2020-08-27' ),
    ('P1', 4000.874300, 5070.786500, '2020-08-28' ),
    ('P2', 2090.654300, 3050.896500, '2020-09-01' ),
    ('P3', 5900.543280, 7010.654700, '2020-09-04' ),
    ('P4', 4000.353200, 4500.125400, '2020-09-05' ),
    ('P5', 5010.768900, 6000.873200, '2020-09-08' ),
    ('P6', 1060.865460, 1400.675430, '2020-09-11' );

因此，产品表为：

mysql> SELECT * FROM Product;

输出：

+------------+--------------+--------------+---------------+--------------+
| Product_id | Product_name | Buying_price | Selling_price | Selling_Date |
+------------+--------------+--------------+---------------+--------------+
|          1 | P6           |  1060.865460 |   1700.675400 | 2020-08-26   |
|          2 | P2           |  2000.154300 |   3050.986700 | 2020-08-27   |
|          3 | P1           |  4000.874300 |   5070.786500 | 2020-08-28   |
|          4 | P2           |  2090.654300 |   3050.896500 | 2020-09-01   |
|          5 | P3           |  5900.543280 |   7010.654700 | 2020-09-04   |
|          6 | P4           |  4000.353200 |   4500.125400 | 2020-09-05   |
|          7 | P5           |  5010.768900 |   6000.873200 | 2020-09-08   |
|          8 | P6           |  1060.865460 |   1400.675430 | 2020-09-11   |
|          9 | P6           |  1060.865460 |   1700.675400 | 2020-08-26   |
|         10 | P2           |  2000.154300 |   3050.986700 | 2020-08-27   |
|         11 | P1           |  4000.874300 |   5070.786500 | 2020-08-28   |
|         12 | P2           |  2090.654300 |   3050.896500 | 2020-09-01   |
|         13 | P3           |  5900.543280 |   7010.654700 | 2020-09-04   |
|         14 | P4           |  4000.353200 |   4500.125400 | 2020-09-05   |
|         15 | P5           |  5010.768900 |   6000.873200 | 2020-09-08   |
|         16 | P6           |  1060.865460 |   1400.675430 | 2020-09-11   |
+------------+--------------+--------------+---------------+--------------+

现在，我们将截断Buying_price和Selling_price列最多两个小数位。

   SELECT  
   Product_name,
   Buying_price,
   TRUNCATE(Buying_price, 2) Trucated_Bprice,  
   Selling_price,
   TRUNCATE(Selling_price, 2) Trucated_Sprice
   FROM Product ;

输出：

+--------------+--------------+-----------------+---------------+-----------------+
| Product_name | Buying_price | Trucated_Bprice | Selling_price | Trucated_Sprice |
+--------------+--------------+-----------------+---------------+-----------------+
| P6           |  1060.865460 |         1060.86 |   1700.675400 |         1700.67 |
| P2           |  2000.154300 |         2000.15 |   3050.986700 |         3050.98 |
| P1           |  4000.874300 |         4000.87 |   5070.786500 |         5070.78 |
| P2           |  2090.654300 |         2090.65 |   3050.896500 |         3050.89 |
| P3           |  5900.543280 |         5900.54 |   7010.654700 |         7010.65 |
| P4           |  4000.353200 |         4000.35 |   4500.125400 |         4500.12 |
| P5           |  5010.768900 |         5010.76 |   6000.873200 |         6000.87 |
| P6           |  1060.865460 |         1060.86 |   1400.675430 |         1400.67 |
| P6           |  1060.865460 |         1060.86 |   1700.675400 |         1700.67 |
| P2           |  2000.154300 |         2000.15 |   3050.986700 |         3050.98 |
| P1           |  4000.874300 |         4000.87 |   5070.786500 |         5070.78 |
| P2           |  2090.654300 |         2090.65 |   3050.896500 |         3050.89 |
| P3           |  5900.543280 |         5900.54 |   7010.654700 |         7010.65 |
| P4           |  4000.353200 |         4000.35 |   4500.125400 |         4500.12 |
| P5           |  5010.768900 |         5010.76 |   6000.873200 |         6000.87 |
| P6           |  1060.865460 |         1060.86 |   1400.675430 |         1400.67 |
+--------------+--------------+-----------------+---------------+-----------------+