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