MySQL format()函數

本教程將向您展示如何使用MySQL FORMAT函數格式化各種語言環境中的十進位數。

MySQL FORMAT函數簡介

有時，您使用運算式或聚合函數(如AVG)來計算資料庫中的值，例如庫存周轉率，產品的平均價格，平均發票值等。

運算式的結果是十進位數，並帶有許多小數位。要格式化這些數字，您可以使用具有以下語法的FORMAT函數：

FOMRAT(N,D,locale);

FORMAT函數將數字N格式化為格式，如"#,###,###.##"，舍入到D位小數。它返回一個值作為字串。

FORMAT函數接受三個參數：

• N是要格式化的數字。
• D是要舍入的小數位數。
• locale是一個可選參數，用於確定千個分隔符號和分隔符號之間的分組。如果省略locale操作符，MySQL將默認使用en_US。以下鏈接提供MySQL支持的所有區域名稱：http://dev.mysql.com/doc/refman/5.7/en/locale-support.html

MySQL FORMAT函數示例

請參閱使用FORMAT函數的示例 -

mysql> SELECT FORMAT(14500.2018, 2);
+-----------------------+
| FORMAT(14500.2018, 2) |
+-----------------------+
| 14,500.20             |
+-----------------------+
1 row in set

以下語句使用FORMAT函數，第二個參數為0，因此，結果沒有任何小數位。

mysql> SELECT FORMAT(12500.2015, 0);
+-----------------------+
| FORMAT(12500.2015, 0) |
+-----------------------+
| 12,500                |
+-----------------------+
1 row in set

以下語句使用de_DE語言環境而不是en_US語言環境：

mysql> SELECT FORMAT(12500.2015, 2,'de_DE');
+-------------------------------+
| FORMAT(12500.2015, 2,'de_DE') |
+-------------------------------+
| 12.500,20                     |
+-------------------------------+
1 row in set

正如在上面的結果中看到的那樣，de_DE語言環境使用點(.)來分隔千位和逗號(，)來分隔小數點。

我們來看看示例資料庫(zaixiandb)中的products表，其結構如下所示 -

mysql> desc products;
+--------------------+---------------+------+-----+---------+------------------+
| Field              | Type          | Null | Key | Default | Extra            |
+--------------------+---------------+------+-----+---------+------------------+
| productCode        | varchar(15)   | NO   | PRI |         |                  |
| productName        | varchar(70)   | NO   | MUL | NULL    |                  |
| productLine        | varchar(50)   | NO   | MUL | NULL    |                  |
| productScale       | varchar(10)   | NO   |     | NULL    |                  |
| productVendor      | varchar(50)   | NO   |     | NULL    |                  |
| productDescription | text          | NO   |     | NULL    |                  |
| quantityInStock    | smallint(6)   | NO   |     | NULL    |                  |
| buyPrice           | decimal(10,2) | NO   |     | NULL    |                  |
| MSRP               | decimal(10,2) | NO   |     | NULL    |                  |
| stockValue         | double        | YES  |     | NULL    | STORED GENERATED |
+--------------------+---------------+------+-----+---------+------------------+
10 rows in set

要計算每個產品的總價值，可以使用庫存數量乘以價格，如下查詢語句：

SELECT
    productname, quantityInStock * buyPrice stock_value
FROM
    products;

執行上面查詢語句，得到以下結果 -

+---------------------------------------------+-------------+
| productname                                 | stock_value |
+---------------------------------------------+-------------+
| 1969 Harley Davidson Ultimate Chopper       | 387209.73   |
| 1952 Alpine Renault 1300                    | 720126.90   |
| 1996 Moto Guzzi 1100i                       | 457058.75   |
********** 此處省略了一大波數據 ******************************
| The Titanic                                 | 99932.04    |
| The Queen Mary                              | 272869.44   |
| American Airlines: MD-11S                   | 319901.40   |
| Boeing X-32A JSF                            | 159163.89   |
| Pont Yacht                                  | 13786.20    |
+---------------------------------------------+-------------+
110 rows in set

上面查詢結果顯示對閱讀不太友好，因為有很多位小數。

為了使結果更好閱讀，您可以組合兩個函數：FORMAT和CONCAT。 FORMAT函數將庫存值格式舍入為2位小數。 並且CONCAT函數在庫存值字串的開頭添加人民幣符號(￥)：

SELECT
    productname,
    CONCAT('￥',
            FORMAT(quantityInStock * buyPrice, 2)) stock_value
FROM
    products;

執行上面查詢語句，得到以下結果 -

+---------------------------------------------+--------------+
| productname                                 | stock_value  |
+---------------------------------------------+--------------+
| 1969 Harley Davidson Ultimate Chopper       | ￥387,209.73 |
| 1952 Alpine Renault 1300                    | ￥720,126.90 |
| 1996 Moto Guzzi 1100i                       | ￥457,058.75 |
| 2003 Harley-Davidson Eagle Drag Bike        | ￥508,073.64 |
| 1972 Alfa Romeo GTA                         | ￥278,631.36 |
********** 此處省略了一大波數據 ******************************
| American Airlines: MD-11S                   | ￥319,901.40 |
| Boeing X-32A JSF                            | ￥159,163.89 |
| Pont Yacht                                  | ￥13,786.20  |
+---------------------------------------------+--------------+
110 rows in set

請注意，FORMAT函數返回一個字串值。這意味著如果要使用ORDER BY子句對FORMAT函數的結果進行排序，MySQL將使用基於字串而非基於數字的方式對結果進行排序。

例如，以下語句按字母順序排列stock_value值。

SELECT
    productname,
    CONCAT('$',
            FORMAT(quantityInStock * buyPrice, 2)) stock_value
FROM
    products
ORDER BY stock_value;

執行上面查詢語句，得到以下結果(部分) -

要以數字方式對stock_value值進行排序，請將運算式放在ORDER BY子句中，如下所示：

SELECT
    productname,
    CONCAT('￥',
            FORMAT(quantityInStock * buyPrice, 2)) stock_value
FROM
    products
ORDER BY quantityInStock * buyPrice;

執行上面查詢語句，得到以下結果 -

+---------------------------------------------+--------------+
| productname                                 | stock_value  |
+---------------------------------------------+--------------+
| 1960 BSA Gold Star DBD34                    | ￥559.80     |
| 1928 Ford Phaeton Deluxe                    | ￥4,490.72   |
| 1968 Ford Mustang                           | ￥6,483.12   |
| 1997 BMW F650 ST                            | ￥11,911.76  |
| Pont Yacht                                  | ￥13,786.20  |
********** 此處省略了一大波數據 ******************************
| 1976 Ford Gran Torino                       | ￥670,743.23 |
| 1968 Dodge Charger                          | ￥685,684.68 |
| 1962 LanciaA Delta 16V                      | ￥702,325.22 |
| 1952 Alpine Renault 1300                    | ￥720,126.90 |
| 1995 Honda Civic                            | ￥917,493.08 |
+---------------------------------------------+--------------+
110 rows in set

在本教學中，我們向您展示了如何使用MySQL FORMAT函數來格式化各種語言環境中的數字值。