MySQL format()函式
2019-10-16 22:54:51
本教學將向您展示如何使用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語言環境使用點(.)來分隔千位和逗號(，)來分隔小數點。
我們來看看範例資料庫(yiibaidb)中的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函式來格式化各種語言環境中的數位值。