FORMAT

Oct 10, 2018

前言

遇到一个需求：将数字四舍五入保留两位小数格式化
查到一篇文章讲了三种方案：

  1. FORMAT

     MariaDB [(none)]> SELECT FORMAT(12345.6789,2);
 +----------------------+
 | FORMAT(12345.6789,2) |
 +----------------------+
 | 12,345.68            |
 +----------------------+

    没有达到预期结果，因为不想以逗号分隔

  2. TRUNCATE

     MariaDB [(none)]> SELECT TRUNCATE(12345.6789,2);
 +------------------------+
 | TRUNCATE(12345.6789,2) |
 +------------------------+
 |               12345.67 |
 +------------------------+

    没有达到预期结果，因为没有四舍五入

  3. CONVERT

     MariaDB [(none)]> SELECT CONVERT(12345.6789,decimal(10,2));
 +-----------------------------------+
 | CONVERT(12345.6789,decimal(10,2)) |
 +-----------------------------------+
 |                          12345.68 |
 +-----------------------------------+

    达到预期结果

我对这个结论感觉很奇怪，主要是FORMAT的结果只能以逗号分隔让我感觉很惊讶，于是就去查了查文档，发现了一个很有意思的事情，特记录如下

FORMAT

定义

官网对FORMAT函数的定义见这里
全文如下：

FORMAT(X,D[,locale])

Formats the number X to a format like '#,###,###.##', rounded to D decimal places, and returns the result as a string. If D is 0, the result has no decimal point or fractional part.

The optional third parameter enables a locale to be specified to be used for the result number's decimal point, thousands separator, and grouping between separators. Permissible locale values are the same as the legal values for the lc_time_names system variable (see Section 10.15, “MySQL Server Locale Support”). If no locale is specified, the default is 'en_US'.

翻译如下：

FORMAT(X,D[,locale])

将数字 X 格式化成 '#,###,###.##' 的形式，四舍五入保留 D 位小数，并且返回值是一个字符串。
如果 D 为 0，则返回值没有小数点和小数部分。

可选的第三个参数用于指定返回值的小数点、千分号和分隔符的分组设置。
允许的值和系统变量 lc_time_names 相同。如果未指定则默认 en_US。

也就是说，FORMAT函数并不是通常认为的两个参数，其可选的第三个参数可以指定分隔符
根据官网在Section 10.15, “MySQL Server Locale Support”给出的链接，我们继续探究

lc_time_names

在跳转页面可以发现如下内容：

lc_time_names may be set to any of the following locale values. The set of locales supported by MySQL may differ from those supported by your operating system.

翻译如下：

lc_time_names 可以设置为以下任何区域值。
MySQL支持的区域设置可能与您的操作系统支持的区域设置不同。

下方的表格共计 111 个区域值，我们将其取出

测试

依次用区域值格式化数字123456789.123456
执行命令举例如下：

MariaDB [(none)]> select FORMAT(123456789.123456, 4,'zh_CN');
+-------------------------------------+
| FORMAT(123456789.123456, 4,'zh_CN') |
+-------------------------------------+
| 123,456,789.1235                    |
+-------------------------------------+

然后我们将格式化后的内容做统计，结论如下：

+-------------------+-----+
| format            | num |
+-------------------+-----+
| 12,34,56,789.1235 |   3 |
| 123 456 789,1235  |   1 |
| 123 456 789,1235  |   9 |
| 123'456'789,1235  |   2 |
| 123'456'789.1235  |   1 |
| 123,456,789.1235  |  37 |
| 123.456.789,1235  |  20 |
| 123456789,1235    |  26 |
| 123456789.1235    |  12 |
+-------------------+-----+
9 rows in set (0.00 sec)

可见，可以满足我们格式化需求的区域值有12个，分别如下：

+----------+----------------+
| language | format         |
+----------+----------------+
| ar_SA    | 123456789.1235 |
| es_CR    | 123456789.1235 |
| es_DO    | 123456789.1235 |
| es_GT    | 123456789.1235 |
| es_HN    | 123456789.1235 |
| es_MX    | 123456789.1235 |
| es_NI    | 123456789.1235 |
| es_PA    | 123456789.1235 |
| es_PE    | 123456789.1235 |
| es_PR    | 123456789.1235 |
| es_SV    | 123456789.1235 |
| sr_RS    | 123456789.1235 |
+----------+----------------+
12 rows in set (0.00 sec)

挑一个验证一下：

MariaDB [(none)]> select FORMAT(123456789.123456, 4,'ar_SA');
+-------------------------------------+
| FORMAT(123456789.123456, 4,'ar_SA') |
+-------------------------------------+
| 123456789.1235                      |
+-------------------------------------+

结论

  1. FORMAT的结果支持 9 种分隔方式
  2. 最终用CONVERT实现的需求
    ┓( ´∀` )┏

附录

111种格式化详情

DROP TABLE IF EXISTS `mysql_format`;

CREATE TABLE `mysql_format` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `language` varchar(10) DEFAULT NULL,
  `format` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `mysql_format` (`id`, `language`, `format`)
VALUES
	(1,'ar_AE','123,456,789.1235'),
	(2,'ar_BH','123,456,789.1235'),
	(3,'ar_DZ','123,456,789.1235'),
	(4,'ar_EG','123,456,789.1235'),
	(5,'ar_IN','123,456,789.1235'),
	(6,'ar_IQ','123,456,789.1235'),
	(7,'ar_JO','123,456,789.1235'),
	(8,'ar_KW','123,456,789.1235'),
	(9,'ar_LB','123,456,789.1235'),
	(10,'ar_LY','123,456,789.1235'),
	(11,'ar_MA','123,456,789.1235'),
	(12,'ar_OM','123,456,789.1235'),
	(13,'ar_QA','123,456,789.1235'),
	(14,'ar_SA','123456789.1235'),
	(15,'ar_SD','123,456,789.1235'),
	(16,'ar_SY','123,456,789.1235'),
	(17,'ar_TN','123,456,789.1235'),
	(18,'ar_YE','123,456,789.1235'),
	(19,'be_BY','123.456.789,1235'),
	(20,'bg_BG','123\0456\0789,1235'),
	(21,'ca_ES','123456789,1235'),
	(22,'cs_CZ','123 456 789,1235'),
	(23,'da_DK','123.456.789,1235'),
	(24,'de_AT','123456789,1235'),
	(25,'de_BE','123.456.789,1235'),
	(26,'de_CH','123\'456\'789.1235'),
	(27,'de_DE','123.456.789,1235'),
	(28,'de_LU','123.456.789,1235'),
	(29,'el_GR','123456789,1235'),
	(30,'en_AU','123,456,789.1235'),
	(31,'en_CA','123,456,789.1235'),
	(32,'en_GB','123,456,789.1235'),
	(33,'en_IN','12,34,56,789.1235'),
	(34,'en_NZ','123,456,789.1235'),
	(35,'en_PH','123,456,789.1235'),
	(36,'en_US','123,456,789.1235'),
	(37,'en_ZA','123,456,789.1235'),
	(38,'en_ZW','123,456,789.1235'),
	(39,'es_AR','123.456.789,1235'),
	(40,'es_BO','123456789,1235'),
	(41,'es_CL','123456789,1235'),
	(42,'es_CO','123456789,1235'),
	(43,'es_CR','123456789.1235'),
	(44,'es_DO','123456789.1235'),
	(45,'es_EC','123456789,1235'),
	(46,'es_ES','123456789,1235'),
	(47,'es_GT','123456789.1235'),
	(48,'es_HN','123456789.1235'),
	(49,'es_MX','123456789.1235'),
	(50,'es_NI','123456789.1235'),
	(51,'es_PA','123456789.1235'),
	(52,'es_PE','123456789.1235'),
	(53,'es_PR','123456789.1235'),
	(54,'es_PY','123456789,1235'),
	(55,'es_SV','123456789.1235'),
	(56,'es_US','123,456,789.1235'),
	(57,'es_UY','123456789,1235'),
	(58,'es_VE','123456789,1235'),
	(59,'et_EE','123 456 789,1235'),
	(60,'eu_ES','123456789,1235'),
	(61,'fi_FI','123 456 789,1235'),
	(62,'fo_FO','123.456.789,1235'),
	(63,'fr_BE','123456789,1235'),
	(64,'fr_CA','123456789,1235'),
	(65,'fr_CH','123456789,1235'),
	(66,'fr_FR','123456789,1235'),
	(67,'fr_LU','123456789,1235'),
	(68,'gl_ES','123456789,1235'),
	(69,'gu_IN','123,456,789.1235'),
	(70,'he_IL','123,456,789.1235'),
	(71,'hi_IN','123,456,789.1235'),
	(72,'hr_HR','123456789,1235'),
	(73,'hu_HU','123.456.789,1235'),
	(74,'id_ID','123.456.789,1235'),
	(75,'is_IS','123.456.789,1235'),
	(76,'it_CH','123\'456\'789,1235'),
	(77,'it_IT','123456789,1235'),
	(78,'ja_JP','123,456,789.1235'),
	(79,'ko_KR','123,456,789.1235'),
	(80,'lt_LT','123.456.789,1235'),
	(81,'lv_LV','123 456 789,1235'),
	(82,'mk_MK','123 456 789,1235'),
	(83,'mn_MN','123.456.789,1235'),
	(84,'ms_MY','123,456,789.1235'),
	(85,'nb_NO','123.456.789,1235'),
	(86,'nl_BE','123456789,1235'),
	(87,'nl_NL','123456789,1235'),
	(88,'no_NO','123.456.789,1235'),
	(89,'pl_PL','123456789,1235'),
	(90,'pt_BR','123456789,1235'),
	(91,'pt_PT','123456789,1235'),
	(92,'rm_CH','123\'456\'789,1235'),
	(93,'ro_RO','123.456.789,1235'),
	(94,'ru_RU','123 456 789,1235'),
	(95,'ru_UA','123.456.789,1235'),
	(96,'sk_SK','123 456 789,1235'),
	(97,'sl_SI','123456789,1235'),
	(98,'sq_AL','123.456.789,1235'),
	(99,'sr_RS','123456789.1235'),
	(100,'sv_FI','123 456 789,1235'),
	(101,'sv_SE','123 456 789,1235'),
	(102,'ta_IN','12,34,56,789.1235'),
	(103,'te_IN','12,34,56,789.1235'),
	(104,'th_TH','123,456,789.1235'),
	(105,'tr_TR','123.456.789,1235'),
	(106,'uk_UA','123.456.789,1235'),
	(107,'ur_PK','123,456,789.1235'),
	(108,'vi_VN','123.456.789,1235'),
	(109,'zh_CN','123,456,789.1235'),
	(110,'zh_HK','123,456,789.1235'),
	(111,'zh_TW','123,456,789.1235');