mysql中格式化数字详解

最近因为工作的需求，需要对mysql中数字进行格式化，但发现网上的资料较少，索性自己总结一下，方便自己也帮助有需要的朋友们，下面话不多说，来一起看看详细的介绍：

一、format函数：

格式化浮点数 format(number, length);

介绍：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. d should be a constant value.

示例代码

mysql> select format(12332.123456, 4);
 -> '12,332.1235' 
mysql> select format(12332.1,4);
 -> '12,332.1000' 
mysql> select format(12332.2,0);
 -> '12,332'

二、rpad 和 lpad 给定位数，不足补充自定义字符

rpad:

returns the string str,right-padded with the string padstr to a length of len characters. if
str is longer than len, the return value is shortened to len characters.      

示例代码  

mysql> select rpad('hi',5,'?'); 
  -> 'hi???'
 mysql> select rpad('hi',1,'?'); 
  -> 'h'
mysql>selet rpad(12, 5 ,0);
  ->12000

this function is multi-byte safe.

lpad:

returns the string str, left-padded with the string padstr to a length of lencharacters. if str is longer than len, the return value is shortened to lencharacters.

示例代码

mysql> select lpad('hi',4,'??');
 -> '??hi' 
mysql> select lpad('hi',1,'??');
 -> 'h' 

mysql>select lpad(12, 5 , 0)
  ->'00012'

参考：http://www.cnblogs.com/fenglie/articles/4409208.html

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作能带来一定的帮助，如果有疑问大家可以留言交流，谢谢大家对的支持。