盘点mysql中的那些函数-----数学与字符串函数(8.31更新)

数学函数

平方根与取余

mysql> select abs(-100)=abs(100),pi(),sqrt(9),sqrt(2),sqrt(-100),mod(12,11),mod(12.5,10);
+--------------------+----------+---------+--------------------+------------+------------+--------------+
| abs(-100)=abs(100) | pi()     | sqrt(9) | sqrt(2)            | sqrt(-100) | mod(12,11) | mod(12.5,10) |
+--------------------+----------+---------+--------------------+------------+------------+--------------+
|                  1 | 3.141593 |       3 | 1.4142135623730951 |       NULL |          1 |          2.5 |
+--------------------+----------+---------+--------------------+------------+------------+--------------+
1 row in set (0.00 sec)

1,负数没有平方根,所以返回值为null;
2,MOD(x,y)返回x被y除后的余数，MOD()对于带有小数部分的数值也起作用，它返回除法运算后的精确余数。
mod(12.5,10) 12.5除以10 商1余2.5;

最小整数与最大整数

mysql> select ceil(18.9999),ceil(-18.9999),ceiling(18.9999),ceiling(-18.9999);
+---------------+----------------+------------------+-------------------+
| ceil(18.9999) | ceil(-18.9999) | ceiling(18.9999) | ceiling(-18.9999) |
+---------------+----------------+------------------+-------------------+
|            19 |            -18 |               19 |               -18 |
+---------------+----------------+------------------+-------------------+
1 row in set (0.00 sec)

ceil(X)与ceiling(X)
两者都表示------返回不小于X的最小整数值，返回值转化为一个BIGINT。
注解----ceiling 为天花板的意思,可以简写为ceil

mysql> select floor(10.2),floor(-10.2);
+-------------+--------------+
| floor(10.2) | floor(-10.2) |
+-------------+--------------+
|          10 |          -11 |
+-------------+--------------+
1 row in set (0.00 sec)

FLOOR(x)返回不大于x的最大整数值，返回值转化为一个BIGINT。
floor 为地板的意思,不可简写;

随机函数—rand

mysql> select rand(),rand(),rand(12),rand(12),rand(13);
+--------------------+--------------------+---------------------+---------------------+---------------------+
| rand()             | rand()             | rand(12)            | rand(12)            | rand(13)            |
+--------------------+--------------------+---------------------+---------------------+---------------------+
| 0.9349382686754114 | 0.2838225227629976 | 0.15741774081943347 | 0.15741774081943347 | 0.40760085024647497 |
+--------------------+--------------------+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

rand()与rand(x)的异同点—
1,都是返回0-1之间的值
2,rand(x)中的参数x被视作种子,如果该种子相同,则返回的值是一样的;(如果重新启用数据库则rand(x)会返回跟第一不同的值,

mysql> select rand(12)=rand(12),rand(12);
+-------------------+---------------------+
| rand(12)=rand(12) | rand(12)            |
+-------------------+---------------------+
|                 1 | 0.15741774081943347 |
+-------------------+---------------------+
1 row in set (0.00 sec)

四舍五入函数----round

mysql> select  round(0.123),round(0.123,2),truncate(0.123,2);
+--------------+----------------+-------------------+
| round(0.123) | round(0.123,2) | truncate(0.123,2) |
+--------------+----------------+-------------------+
|            0 |           0.12 |              0.12 |
+--------------+----------------+-------------------+
1 row in set (0.00 sec)

mysql> select  round(-0.123),round(-0.123,2),truncate(-0.123,2);
+---------------+-----------------+--------------------+
| round(-0.123) | round(-0.123,2) | truncate(-0.123,2) |
+---------------+-----------------+--------------------+
|             0 |           -0.12 |              -0.12 |
+---------------+-----------------+--------------------+
1 row in set (0.00 sec)

mysql> select  round(0.123),round(0.123,-2),truncate(0.123,-2);
+--------------+-----------------+--------------------+
| round(0.123) | round(0.123,-2) | truncate(0.123,-2) |
+--------------+-----------------+--------------------+
|            0 |               0 |                  0 |
+--------------+-----------------+--------------------+
1 row in set (0.00 sec)

mysql> select round (23.38,-1),round (232.38,-2);
+------------------+-------------------+
| round (23.38,-1) | round (232.38,-2) |
+------------------+-------------------+
|               20 |               200 |
+------------------+-------------------+
1 row in set (0.00 sec)

ROUND(x,y)返回最接近于参数x的数，其值保留到小数点后面y位，若y为负值，则将保留x值到小数点左边y位。

例如—round(23.38,-1) 表示 保留小数点前1位(舍弃小数部分),即个位如果大于5,进1,小于5则舍弃直接保存位0,所以结果为20;

y值为负数时，保留的小数点左边的相应位数直接保存为0，不进行四舍五入。

TRUNCATE(x,y)返回被舍去至小数点后y位的数字x。若y的值为0，则结果不带有小数点或不带有小数部分。若y设为负数，则截去（归零）x小数点左起第y位开始后面所有低位的值。

符号函数

判断一个数的正负------符号函数

mysql> select sign(-10000),sign(12.70),sign(0);
+--------------+-------------+---------+
| sign(-10000) | sign(12.70) | sign(0) |
+--------------+-------------+---------+
|           -1 |           1 |       0 |
+--------------+-------------+---------+
1 row in set (0.00 sec)

幂运算函数

POW(x,y)、POWER(x,y)返回x的y次方的值

EXP(x)–返回e的x乘方后的值。

mysql> select POW(2,2),power(2,3),exp(2),pow(2,0),power(2,-1),exp(0);
+----------+------------+------------------+----------+-------------+--------+
| POW(2,2) | power(2,3) | exp(2)           | pow(2,0) | power(2,-1) | exp(0) |
+----------+------------+------------------+----------+-------------+--------+
|        4 |          8 | 7.38905609893065 |        1 |         0.5 |      1 |
+----------+------------+------------------+----------+-------------+--------+
1 row in set (0.00 sec)

对数函数

mysql> select log(100),log10(100),log(-10);
+-------------------+------------+----------+
| log(100)          | log10(100) | log(-10) |
+-------------------+------------+----------+
| 4.605170185988092 |          2 |     NULL |
+-------------------+------------+----------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+--------------------------------+
| Level   | Code | Message                        |
+---------+------+--------------------------------+
| Warning | 3020 | Invalid argument for logarithm |
+---------+------+--------------------------------+
1 row in set (0.00 sec)

角度和弧度函数

mysql> select degrees(pi()),degrees(pi()/2);
+---------------+-----------------+
| degrees(pi()) | degrees(pi()/2) |
+---------------+-----------------+
|           180 |              90 |
+---------------+-----------------+
1 row in set (0.00 sec)
mysql> select radians(180),radians(90);
+-------------------+--------------------+
| radians(180)      | radians(90)        |
+-------------------+--------------------+
| 3.141592653589793 | 1.5707963267948966 |
+-------------------+--------------------+
1 row in set (0.00 sec)

mysql> select pi()=radians(180);
+-------------------+
| pi()=radians(180) |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.00 sec)

三角函数

mysql> select sin(pi()/2),asin(1),cos(pi()),acos(1), tan(pi()/3),atan(1),cot(pi());
+-------------+--------------------+-----------+---------+--------------------+--------------------+-----------------------+
| sin(pi()/2) | asin(1)            | cos(pi()) | acos(1) | tan(pi()/3)        | atan(1)            | cot(pi())             |
+-------------+--------------------+-----------+---------+--------------------+--------------------+-----------------------+
|           1 | 1.5707963267948966 |        -1 |       0 | 1.7320508075688767 | 0.7853981633974483 | -8.165619676597685e15 |
+-------------+--------------------+-----------+---------+--------------------+--------------------+-----------------------+
1 row in set (0.00 sec)

跟数学上的一样,不在解释赘述;

字符串函数

字符串长度函数

mysql> select char_length('qwert'),length('qwert');
+----------------------+-----------------+
| char_length('qwert') | length('qwert') |
+----------------------+-----------------+
|                    5 |               5 |
+----------------------+-----------------+
1 row in set (0.00 sec)

mysql> select char_length('qwert'),length(12345);
+----------------------+---------------+
| char_length('qwert') | length(12345) |
+----------------------+---------------+
|                    5 |             5 |
+----------------------+---------------+
1 row in set (0.00 sec)

mysql> select char_length('qwert'),length(123459),length('张三');
+----------------------+----------------+----------------+
| char_length('qwert') | length(123459) | length('张三') |
+----------------------+----------------+----------------+
|                    5 |              6 |              4 |
+----------------------+----------------+----------------+
1 row in set (0.00 sec)

一个字母/数组占一个字节,一个汉字占2个字节;

合并字符串函数

CONCAT(s1,s2,…)

mysql> select concat('q',1,'wer',null),concat('q','werr',1,2,3);
+--------------------------+--------------------------+
| concat('q',1,'wer',null) | concat('q','werr',1,2,3) |
+--------------------------+--------------------------+
| NULL                     | qwerr123                 |
+--------------------------+--------------------------+
1 row in set (0.00 sec)

有null则返回null

CONCAT_WS(M,s1,s2,…)
M—表示连接符

mysql> select concat_ws('-','q',1,'wer'),concat_ws('\'','q','werr',1,2,3);
+----------------------------+----------------------------------+
| concat_ws('-','q',1,'wer') | concat_ws('\'','q','werr',1,2,3) |
+----------------------------+----------------------------------+
| q-1-wer                    | q'werr'1'2'3                     |
+----------------------------+----------------------------------+

有NULL值则不添加(即为空)

替换字符串的函数

mysql> select insert('hello',2,3,'world');
+-----------------------------+
| insert('hello',2,3,'world') |
+-----------------------------+
| hworldo                     |
+-----------------------------+
1 row in set (0.00 sec)

mysql> select insert('hello',2,-3,'world');
+------------------------------+
| insert('hello',2,-3,'world') |
+------------------------------+
| hworld                       |
+------------------------------+
1 row in set (0.00 sec)

mysql> select insert('hello',2,-1,'world');
+------------------------------+
| insert('hello',2,-1,'world') |
+------------------------------+
| hworld                       |
+------------------------------+
1 row in set (0.00 sec)

mysql> select insert('hellohello',2,-1,'world');
+-----------------------------------+
| insert('hellohello',2,-1,'world') |
+-----------------------------------+
| hworld                            |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> select insert('hello',2,30,'world');
+------------------------------+
| insert('hello',2,30,'world') |
+------------------------------+
| hworld                       |
+------------------------------+
1 row in set (0.00 sec)

mysql> select insert('hello',-2,3,'world');
+------------------------------+
| insert('hello',-2,3,'world') |
+------------------------------+
| hello                        |
+------------------------------+
1 row in set (0.00 sec)

mysql>

insert (x,a,b,y)

当a<0 的 时候直接返回该字符串

mysql> select insert('hello',-2,3,'world');
+------------------------------+
| insert('hello',-2,3,'world') |
+------------------------------+
| hello                        |
+------------------------------+

当a>0的时候且b大于0表示从该字符串第a的位置取b个字符串替换为字符串y,剩下的保留;

 insert('hello',2,3,'world') |
+-----------------------------+
| hworldo

当a>0的时候且b小于0表示从字符串第a的位置开始插入字符串y剩下的不保留;

mysql> select insert('hellohello',2,-1,'world');
+-----------------------------------+
| insert('hellohello',2,-1,'world') |
+-----------------------------------+
| hworld                            |
+-----------------------------------+

insert(x,a,b,d) 参数的含义是将字符串x的第a到b位替换为d,不管a到b有多少字符;

以上是通过插入方式来替换字符串,
还有一种是替换的方式
将字符串中不的某字符串替换为另一个字符串
如----将’xxxx.csdn.net’,中的x 替换为 w

mysql> select replace('xxxx.csdn.net','x','w');
+----------------------------------+
| replace('xxxx.csdn.net','x','w') |
+----------------------------------+
| wwww.csdn.net                    |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select replace('xxxx.csdn.net','x','ww');
+-----------------------------------+
| replace('xxxx.csdn.net','x','ww') |
+-----------------------------------+
| wwwwwwww.csdn.net                 |
+-----------------------------------+
1 row in set (0.00 sec)

大小写转换函数

mysql> select lower('HELLO'),Lcase('WORLD');
+----------------+----------------+
| lower('HELLO') | Lcase('WORLD') |
+----------------+----------------+
| hello          | world          |
+----------------+----------------+
1 row in set (0.00 sec)


mysql> select upper('hello'),Ucase('hello');
+----------------+----------------+
| upper('hello') | Ucase('hello') |
+----------------+----------------+
| HELLO          | HELLO          |
+----------------+----------------+
1 row in set (0.00 sec)

获取指定长度的字符串的函数

mysql> select left('hello',3),left('hello',-3),left('hello',10);
+-----------------+------------------+------------------+
| left('hello',3) | left('hello',-3) | left('hello',10) |
+-----------------+------------------+------------------+
| hel             |                  | hello            |
+-----------------+------------------+------------------+
1 row in set (0.00 sec)


mysql> select right('hello',3),right('hello',-3),right('hello',10);
+------------------+-------------------+-------------------+
| right('hello',3) | right('hello',-3) | right('hello',10) |
+------------------+-------------------+-------------------+
| llo              |                   | hello             |
+------------------+-------------------+-------------------+
1 row in set (0.00 sec)

left(a,x)----取字符串左边的x位为组成新的字符串;
如果x小于0 ,则返回空字符串
如果x大于字符串长度,则返回当前字符串;

right(a,x)------取字符串右边的x位组成新的字符串;
如果x小于0,则返回空字符串
如果x大于字符串长度,则返回当前字符串;

填充字符串的函数

LPAD(s1,len,s2)

-----LPAD(s1,len,s2)返回字符串s1，其左边由字符串s2填补到len字符长度。假如s1的长度大于len，则返回值被缩短至len字符。

mysql> select lpad('hello',5,'word');
+------------------------+
| lpad('hello',5,'word') |
+------------------------+
| hello                  |
+------------------------+
1 row in set (0.00 sec)

mysql> select lpad('hello',9,'word');
+------------------------+
| lpad('hello',9,'word') |
+------------------------+
| wordhello              |
+------------------------+
1 row in set (0.00 sec)

mysql> select lpad('hello',8,'word');
+------------------------+
| lpad('hello',8,'word') |
+------------------------+
| worhello               |
+------------------------+
1 row in set (0.00 sec)

mysql> select lpad('hello',3,'word');
+------------------------+
| lpad('hello',3,'word') |
+------------------------+
| hel                    |
+------------------------+
1 row in set (0.00 sec)

select lpad(‘hello’,3,‘word’); ----表示字符串 hello 将由word字符串填充至长度位3,hello字符串长度位5,比3大,所以不用填充,而是直接截取至长度为3的自字符串长度并返回该字符串 ‘hel’
lpad(‘hello’,8,‘word’)----表示字符串 hello 将由word字符串填充至长度位8,hello字符串长度位5,不足8,所以剩下的3位由字符串word填充,所以取word前三位就够了,最后返回 字符串 ‘worhello’

RPAD(s1,len,s2)

RPAD(s1,len,s2)返回字符串sl，其右边被字符串s2填补至len字符长度。假如字符串s1的长度大于len，则返回值被缩短到len字符长度。

mysql> select rpad('hello',5,'world');
+-------------------------+
| rpad('hello',5,'world') |
+-------------------------+
| hello                   |
+-------------------------+
1 row in set (0.00 sec)

mysql> select rpad('hello',10,'world');
+--------------------------+
| rpad('hello',10,'world') |
+--------------------------+
| helloworld               |
+--------------------------+
1 row in set (0.00 sec)

mysql> select rpad('hello',1,'world');
+-------------------------+
| rpad('hello',1,'world') |
+-------------------------+
| h                       |
+-------------------------+
1 row in set (0.00 sec)

原理跟lpad一样,只不过是放在填充之后的字符串放在右边;

删除空格的函数

trim(x)--------删除字符串两边的空格
ltrim(x)-------------删除字符串左边的空格
rtrim(x)--------------删除字符串右边的空格

mysql> select length(trim('   hello   ')),length(ltrim('     hello     ')),length(rtrim('     hello     '));
+-----------------------------+----------------------------------+----------------------------------+
| length(trim('   hello   ')) | length(ltrim('     hello     ')) | length(rtrim('     hello     ')) |
+-----------------------------+----------------------------------+----------------------------------+
|                           5 |                               10 |                               10 |
+-----------------------------+----------------------------------+----------------------------------+
1 row in set (0.00 sec)

删除指定字符串的函数

TRIM(A FROM B);-----在字符串B中删除两边的字符串A,如果没有则返回原来字符串;

mysql> select trim('o' from 'ohelloworldO'),trim('o' from 'HELLOWORLDO');
+-------------------------------+------------------------------+
| trim('o' from 'ohelloworldO') | trim('o' from 'HELLOWORLDO') |
+-------------------------------+------------------------------+
| helloworldO                   | HELLOWORLDO                  |
+-------------------------------+------------------------------+
1 row in set (0.00 sec)

重复生成字符串的函数

repeat(‘hello’,4)------
生成字符串hello 4次

mysql> select repeat('hello',4),repeat('hello',0),repeat('hello',-2);
+----------------------+-------------------+--------------------+
| repeat('hello',4)    | repeat('hello',0) | repeat('hello',-2) |
+----------------------+-------------------+--------------------+
| hellohellohellohello |                   |                    |
+----------------------+-------------------+--------------------+
1 row in set (0.00 sec)

空格函数和替换函数

space(4)
----生成4个空格

+---------------------------+------------------+
| concat('(', space(4),')') | length(space(4)) |
+---------------------------+------------------+
| (    )                    |                4 |
+---------------------------+------------------+
1 row in set (0.00 sec)

replace(‘aaa.baidu.com’,‘a’,‘w’),
将字符串’aaa.baidu.com’,中的a替换为’w’

mysql> select replace('aaa.baidu.com','a','w');
+----------------------------------+
| replace('aaa.baidu.com','a','w') |
+----------------------------------+
| www.bwidu.com                    |
+----------------------------------+
1 row in set (0.00 sec)

比较字符串大小的函数----strcmp()

strcmp----即stringcompare的缩写

mysql> select strcmp('hello','HELLO'),STRCMP(null,'HELLO'),STRCMP('12A','12B');
+-------------------------+----------------------+---------------------+
| strcmp('hello','HELLO') | STRCMP(null,'HELLO') | STRCMP('12A','12B') |
+-------------------------+----------------------+---------------------+
|                       0 |                 NULL |                  -1 |
+-------------------------+----------------------+---------------------+
1 row in set (0.00 sec)

相同返回0------不区分大小写
有null返回null,
其他情况----
由第一个不同的字符进行比较,
大于返回1,小于返回-1

如-----
STRCMP(‘12A’,‘12B’)第一个不同的字符----A 与B,(ASCII码)A<B返回-1;

mysql> select strcmp('qweeewrewre','qweweewqeqwe');
+--------------------------------------+
| strcmp('qweeewrewre','qweweewqeqwe') |
+--------------------------------------+
|                                   -1 |
+--------------------------------------+
1 row in set (0.00 sec)

获取子串的函数SUBSTRING(s,n,len)和MID(s,n,len)

mysql> select substring('helloworld',3),substring('helloworld',3,5),substring('helloworld',3,-2),substring('helloworld',-3,2),substring('helloworld',-3,-2);
+---------------------------+-----------------------------+------------------------------+------------------------------+-------------------------------+
| substring('helloworld',3) | substring('helloworld',3,5) | substring('helloworld',3,-2) | substring('helloworld',-3,2) | substring('helloworld',-3,-2) |
+---------------------------+-----------------------------+------------------------------+------------------------------+-------------------------------+
| lloworld                  | llowo                       |                              | rl                           |                               |
+---------------------------+-----------------------------+------------------------------+------------------------------+-------------------------------+
1 row in set (0.00 sec)

substring(‘helloworld’,3)----从第三位开始返回到结尾的字符串
substring(‘helloworld’,3,5)----返会从第3 位开始向后取5个字符串
substring(‘helloworld’,3,-2)-----从正数第三位开始向后取两个字符串----不符合逻辑,返回空
substring(‘helloworld’,-3,2)----返回从倒数第三位开始正数2个字符串

mid()和substring()效果一样;

select mid('helloworld',3),mid('helloworld',3,5),mid('helloworld',3,-2),mid('helloworld',-3,2),mid('helloworld',-3,-2);
+---------------------+-----------------------+------------------------+------------------------+-------------------------+
| mid('helloworld',3) | mid('helloworld',3,5) | mid('helloworld',3,-2) | mid('helloworld',-3,2) | mid('helloworld',-3,-2) |
+---------------------+-----------------------+------------------------+------------------------+-------------------------+
| lloworld            | llowo                 |                        | rl                     |                         |
+---------------------+-----------------------+------------------------+------------------------+-------------------------+

> 注释-----之前将 substring(‘helloworld’,3,5)----返会从第3 位开始向后取5个字符串 -------注解错误,抱歉;

匹配子串开始位置的函数

locate(‘hello’ ,‘helloworldhello’)------
返回hello字符串在’helloworldhello’字符串中的位置;

position(‘hello’ in ‘helloworldhello’)-----------
返回hello字符串在’helloworldhello’字符串中的位置; ----注意格式

instr(‘helloworld’ ,‘hello’)
在’helloworld’字符串中返回 字符串hello的位置----注意格式

mysql> select locate('hello' ,'helloworldhello'),position('hello' in 'helloworldhello'),instr('helloworld' ,'hello');
+------------------------------------+----------------------------------------+------------------------------+
| locate('hello' ,'helloworldhello') | position('hello' in 'helloworldhello') | instr('helloworld' ,'hello') |
+------------------------------------+----------------------------------------+------------------------------+
|                                  1 |                                      1 |                            1 |
+------------------------------------+----------------------------------------+------------------------------+
1 row in set (0.00 sec)

字符串逆序的函数—reverse()

mysql> select reverse('hello');
+------------------+
| reverse('hello') |
+------------------+
| olleh            |
+------------------+
1 row in set (0.00 sec)

返回指定位置的字符串—elt()

ELT(N，字符串1，字符串2，字符串3，…，字符串N)：若N =
1，则返回值为字符串1；若N=2，则返回值为字符串2；以此类推；若N小于1或大于参数的数目，则返回值为NULL。

返回指定字符串位置的函数—field()

mysql> select field(2,'1','2','3'),field(2,'1','20','3'),field(2,'1','2','3',null);
+----------------------+-----------------------+---------------------------+
| field(2,'1','2','3') | field(2,'1','20','3') | field(2,'1','2','3',null) |
+----------------------+-----------------------+---------------------------+
|                    2 |                     0 |                         2 |
+----------------------+-----------------------+---------------------------+
1 row in set (0.00 sec)

返回子串位置的函数FIND_IN_SET(s1,s2)

FIND_IN_SET(s1,s2)----返回s1在s2中的位置,
如果s1为null,则返回null
如果s1为非null,但是s2中没有字串s1,则返回0;

mysql> select find_in_set('hello','你好,hello,world,hello') as ss,find_in_set('hello','你好,hello,world,hello,null') as s2;
+----+----+
| ss | s2 |
+----+----+
|  2 |  2 |
+----+----+
1 row in set (0.00 sec)

mysql> select find_in_set('hello','你好,hello,world,hello') as ss,find_in_set(null,'你好,hello,world,hello,null') as s2;
+----+------+
| ss | s2   |
+----+------+
|  2 | NULL |
+----+------+
1 row in set (0.00 sec)

mysql> select find_in_set('hello','你好,hello,world,hello') as ss,find_in_set('null','你好,hello,world,hello,null') as s2;
+----+----+
| ss | s2 |
+----+----+
|  2 |  5 |
+----+----+
1 row in set (0.00 sec)

选取字符串的函数MAKE_SET(x,s1,s2,…,sn)

mysql> select make_set(1|2,'q','w','e','r','t');
+-----------------------------------+
| make_set(1|2,'q','w','e','r','t') |
+-----------------------------------+
| q,w                               |
+-----------------------------------+
1 row in set (0.00 sec)

1|2的值为3,二进制 0011,从右到左第一个和第二个为1,所以make_set(1|2,‘q’,‘w’,‘e’,‘r’,‘t’) 返回字符q,w

数字格式化函数函数

format(x,n)
表示将数字x保留小数点后n位(四舍五入);

mysql> select format(12333.3455,3);
+----------------------+
| format(12333.3455,3) |
+----------------------+
| 12,333.346           |
+----------------------+
1 row in set (0.00 sec)

mysql> select format(pi(),5), format(10.898989,1);
+----------------+---------------------+
| format(pi(),5) | format(10.898989,1) |
+----------------+---------------------+
| 3.14159        | 10.9                |
+----------------+---------------------+
1 row in set (0.00 sec)

进制准换

conv(原数,原数的进制数,要转换为的进制数)

conv(10,10,2)表示10进制的10转换为2进制的数 结果为1010;

mysql> select conv(10,10,2),conv(10,16,8),conv(88,10,16);
+---------------+---------------+----------------+
| conv(10,10,2) | conv(10,16,8) | conv(88,10,16) |
+---------------+---------------+----------------+
| 1010          | 20            | 58             |
+---------------+---------------+----------------+
1 row in set (0.00 sec)

进制说明：
●　二进制，采用0和1两个数字来表示的数。它以2为基数，逢二进一。
●　八进制，采用0、1、2、3、4、5、6、7八个数字，逢八进一，以数字0开头。
●　十进制，采用0~9，共10个数字表示，逢十进一。
●　十六进制，由0-9,A-F组成，以数字0x开头。

IP地址与数字相互转换的函数

inet_aton(‘ip地址’)---------将该ip地址转换为一个数字
inet_ntoa(数字)----------将该数字转换为IP

mysql> select inet_aton('192.168.1.1'),inet_ntoa(inet_aton('192.168.1.1'));
+--------------------------+-------------------------------------+
| inet_aton('192.168.1.1') | inet_ntoa(inet_aton('192.168.1.1')) |
+--------------------------+-------------------------------------+
|               3232235777 | 192.168.1.1                         |
+--------------------------+-------------------------------------+
1 row in set (0.00 sec)

加锁和解锁函数

mysql> select get_lock('locked','Helloworld') as '加锁',
    -> is_used_lock ('locekd') as '正在被使用?',
    -> is_free_lock ('locked') as '没有锁?',
    -> release_lock('locked') as '释放了锁?';
+------+-------------+---------+-----------+
| 加锁 | 正在被使用? | 没有锁? | 释放了锁? |
+------+-------------+---------+-----------+
|    1 |        NULL |       0 |         1 |
+------+-------------+---------+-----------+
1 row in set, 1 warning (0.00 sec)（connection ID）；否则，返回NULL。

get_lock(‘locked’,‘Helloworld’),给字符串’Helloworld’加一个锁,名为locked,超时为0;
is_used_lock (‘locekd’) 名为’locked’的锁在被使用吗?(如果没有叫locked’的锁,也返回null)没有返回null,有的话则返回使用该字符串的用户id,
is_free_lock(‘locked’) 名为locked的锁可以使用吗?—是返回1,不是返回0,没有叫locked的锁则返回null
release_lock(‘locked’)返回值为1，说明解锁成功.返回0则表是不成功,没有叫locked的锁则返回null;

重复执行指定操作的函数

BENCHMARK(count,expr)
函数重复count次执行表达式expr

mysql> select md5('尔曹身与名俱灭,不废江河万古流');
+--------------------------------------+
| md5('尔曹身与名俱灭,不废江河万古流') |
+--------------------------------------+
| 4d0bbdfc9c117005699ca0eb91d27f26     |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> select benchmark(50000000,md5('尔曹身与名俱灭,不废江河万古流'));
+----------------------------------------------------------+
| benchmark(50000000,md5('尔曹身与名俱灭,不废江河万古流')) |
+----------------------------------------------------------+
|                                                        0 |
+----------------------------------------------------------+
1 row in set (6.58 sec)

BENCHMARK报告的时间是客户端经过的时间，而不是在服务器端的CPU时间，每次执行后报告的时间并不一定是相同的。读者可以多次执行该语句，查看结果。

字符编码转换

mysql> select convert('hello' using latin1);
+-------------------------------+
| convert('hello' using latin1) |
+-------------------------------+
| hello                         |
+-------------------------------+
1 row in set (0.00 sec)


mysql> select charset('hello'),charset(convert('hello' using latin2));
+------------------+----------------------------------------+
| charset('hello') | charset(convert('hello' using latin1)) |
+------------------+----------------------------------------+
| gbk              | latin1                                 |
+------------------+----------------------------------------+
1 row in set (0.00 sec)


mysql> select convert('你好' using gbk);
+---------------------------+
| convert('你好' using gbk) |
+---------------------------+
| 你好                      |
+---------------------------+
1 row in set (0.00 sec)

改变数据类型的函数

CAST(x, AS type)和CONVERT(x,
type)函数将一个类型的值转换为另一个类型的值，可转换的type有BINARY、CHAR(n)、DATE、TIME、DATETIME、DECIMAL、SIGNED、UNSIGNED。

mysql> select cast('你好' as char(1)),convert(100,binary);
+-------------------------+------------------------------------------+
| cast('你好' as char(1)) | convert(100,binary)                      |
+-------------------------+------------------------------------------+
| 你                      | 0x313030                                 |
+-------------------------+------------------------------------------+
1 row in set, 1 warning (0.00 sec)