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 stringstr
,right-padded with the stringpadstr
to a length oflen
characters. Ifstr
is longer thanlen
, the return value is shortened tolen
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 len
characters. If str
is longer than len
, the return value is shortened to len
characters.
mysql>SELECT LPAD('hi',4,'??');
-> '??hi'
mysql>SELECT LPAD('hi',1,'??');
-> 'h'
mysql>SELECT LPAD(12, 5 , 0)
->'00012'