number_format() 与 MySQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1939326/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
number_format() with MySQL
提问by antpaw
hey i need a way to get a formated number from my column decimal(23,2) NOT NULL DEFAULT '0.00'
嘿，我需要一种方法来从我的专栏中获取格式化的数字 decimal(23,2) NOT NULL DEFAULT '0.00'
in php i could use this function number_format('1111.00', 2, ',', '.');it would return 1.111,00(in Germany we use , to define decimal numbers)
在 php 中，我可以使用number_format('1111.00', 2, ',', '.');它会返回的函数1.111,00（在德国，我们使用 , 来定义十进制数）
how would i do this in mysql? with string replaces?
我将如何在 mysql 中做到这一点？用字符串替换？
回答by MindStalker
http://blogs.mysql.com/peterg/2009/04/
http://blogs.mysql.com/peterg/2009/04/
In Mysql 6.1 you will be able to do FORMAT(X,D [,locale_name] )
在 Mysql 6.1 中，您将能够执行 FORMAT(X,D [,locale_name] )
As in
如
SELECT format(1234567,2,'de_DE');
For now this ability does not exist, though you MAY be able to set your locale in your database my.ini check it out.
现在这种能力不存在，尽管你可以在你的数据库 my.ini 中设置你的语言环境，看看它。
回答by Patrick
With performance penalty and if you need todo it only in SQL you can use the FORMATfunction and 3 REPLACE:
After the format replace the .with another char for example @, then replace the ,with a .and then the chararacter you choose by a ,which lead you for your example to 1.111,00
由于性能损失，如果您只需要在 SQL 中执行此操作，您可以使用该 FORMAT函数和 3 REPLACE：.例如@，在格式替换为另一个字符之后，然后,用 a替换，然后用 a替换.您选择的字符，,这将引导您进行示例到1.111,00
SELECT REPLACE(REPLACE(REPLACE(FORMAT("1111.00", 2), ".", "@"), ",", "."), "@", ",")
回答by Alex
You can use
您可以使用
SELECT round(123.4566,2) -> 123.46
回答by valli
FORMAT(X,D) 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.
FORMAT(X,D) 将数字 X 格式化为类似 '#,###,###.##' 的格式，四舍五入到 D 位小数，并将结果作为字符串返回。如果 D 为 0，则结果没有小数点或小数部分。
SELECT FORMAT(12332.123456, 4);
-> '12,332.1235'回答by eyebeebai
Antonio's answer
安东尼奥的回答
CONCAT(REPLACE(FORMAT(number,0),',','.'),',',SUBSTRING_INDEX(FORMAT(number,2),'.',-1))
is wrong; it may produce incorrect results!
是错的; 它可能会产生错误的结果！
For example, if "number" is 12345.67, the resulting string would be:
例如，如果“数字”是 12345.67，则结果字符串将是：
'12.346,67'
instead of
代替
'12.345,67'
because FORMAT(number,0) rounds "number" up if fractional part is greater or equal than 0.5 (as it is in my example)!
因为如果小数部分大于或等于 0.5（如我的示例中所示），则 FORMAT(number,0) 会将“数字”向上舍入！
What you COULD use is
你可以使用的是
CONCAT(REPLACE(FORMAT(FLOOR(number),0),',','.'),',',SUBSTRING_INDEX(FORMAT(number,2),'.',-1))
if your MySQL/MariaDB's FORMAT doesn't support "locale_name" (see MindStalker's post - Thx 4 that, pal). Note the FLOOR function I've added.
如果您的 MySQL/MariaDB 的格式不支持“locale_name”（请参阅 MindStalker 的帖子 - Thx 4 that，pal）。注意我添加的 FLOOR 函数。
回答by Pete
At least as far back as MySQL 5.5 you can use format:
至少早在 MySQL 5.5 之前，您就可以使用格式：
SELECT FORMAT(123456789.123456789,2);
/* produces 123,456,789.12 */
SELECT FORMAT(123456789.123456789,2,'de_DE');
/*
produces 123.456.789,12
note the swapped . and , for the de_DE locale (German-Germany)
*/
From the MySQL docs: https://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_format
来自 MySQL 文档：https: //dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_format
Available locales are listed elsewhere in the docs: https://dev.mysql.com/doc/refman/5.5/en/locale-support.html
文档中的其他地方列出了可用的语言环境：https: //dev.mysql.com/doc/refman/5.5/en/locale-support.html
回答by Konstantin XFlash Stratigenas
CREATE DEFINER=`yourfunctionname`@`%` FUNCTION `money`(
`betrag` DECIMAL(10,2)
)
RETURNS varchar(128) CHARSET latin1
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
return(
select replace(format(cast(betrag as char),2),',',"'") as betrag
)
will creating a MySql-Function with this Code:
将使用此代码创建一个 MySql 函数：
select replace(format(cast(amount as char),2),',',"'") as amount_formated
回答by Antonio
You need this:
你需要这个：
CONCAT(REPLACE(FORMAT(number,0),',','.'),',',SUBSTRING_INDEX(FORMAT(number,2),'.',-1))
