MySQL常用函数-数学函数与字符串函数

一、数学函数

1、绝对值函数

abs(x) 　　返回x的绝对值

select abs(2),abs(-3.3),abs(-33),abs(null)

 

2、圆周率函数

PI()  返回圆周率π的值，默认显示小数位数是6位

select pi();

 

3、平方根函数

sqrt(x)　　返回非负数x的二次方根

select sqrt(9),sqrt(40),sqrt(-49),sqrt(null);

 

4、求余函数

mod(x,y)　　返回x被y除后的余数，对于带有小数部分的数值也起作用，返回触发运算后的精确余数

select mod(31,8),mod(-234,10),mod(45.5,6),mod(null,2),mod(2,null),mod(null,null);

 

5、取整函数

①ceil(x)、ceiling(x)　　返回不小于x的最小整数，返回值类型为bigint

select ceil(-3.35),ceil(-3),ceil(0),ceil(3),ceil(3.35),ceiling(-3.35),ceiling(-3),ceiling(0),ceiling(3),ceiling(3.35),ceil(null),ceiling(null);

 

②floor(x)　　返回不大于x的最大整数，返回值类型为bigint

select floor(-3.35),floor(-3),floor(0),floor(3),floor(3.35),floor(null);

 

6、随机数函数

rand(x)　　返回一个随机浮点数，结果在[0，1.0]之间。如果rand()不带参数则每次产生的随机数值是不同的，如果rand(x)带参数时，参数相同将产生相同的随机数，不同的参数产生的随机数值不同

select rand(),rand(),rand(),rand(10),rand(10),rand(11),rand(null);

 

7、截取函数

①round(x)　　返回最接近于参数x的整数，对x进行四舍五入

select round(-1.14),round(-1.67),round(1,14),round(1.66),round(-1),round(1),round(0),round(null);

 

 ②round(x,y)　　返回最接近参数x的数，如果y为0，则与round()相同；如果y为正整数，则其值保留到小数点后面y位；如果y为负整数，则其值保留到小数点左边y位，并且保留的小数点左边的相应位数直接保存为0，不进行四舍五入

select round(1.38,1),round(1.38,0),round(232.38,-1),round(232.38,-2),round(236.38,-1),round(null,1),round(1.2,null),round(null,null);

 

 ③truncate(x,y)　　返回被舍去至小数点后y位的数字x，如果y为0，则结果舍去小数点及小数点后小数部分，如果y为负整数，则截去x小数点左起第y位开始后面所有低位的值

select truncate(1.38,1),truncate(1.38,0),truncate(232.38,-1),truncate(232.38,-2),truncate(236.38,-1),truncate(null,1),truncate(1.2,null),truncate(null,null);

  注意：round(x,y)函数在截取值的时候会四舍五入，而truncate(x,y)直接截取值，并不进行四舍五入。

 

8、符号函数

sign(x)　　返回参数的符号，x的值为负、0、正时结果依次为-1、0、1

select sign(-2.1),sign(0),sign(2),sign(null);

 

 

 

9、幂运算函数

①pow(x,y)、power(x,y)　　结果相同，返回x的y次乘方的结果值

select pow(2,2),power(2,2),pow(2,-2),power(2,-2),pow(null,2),power(2,null),pow(null,null);

 

 

 ②exp(x)　　返回e的x乘方的结果值

select exp(2),exp(-2),exp(0),exp(null);

 

 

 

10、对数运算函数

①log(x)　　返回x的自然对数，即x相对于基数e的对数

②log10(x)　　返回x相对于基数10的对数

select log(3),log(-3),log(null),log10(2),log10(100),log10(-1000),log10(null),log(0);

 

 

 注意：对数定义域必须为正数。

 

11、角度与弧度相互转换函数

①radians(x)　　将参数x由角度转为弧度

②degrees(x)　　将参数x由弧度转为角度

select radians(90),radians(180),pi(),degrees(pi()),degrees(pi()/2);

 

 

 

12、正玄函数和反正玄函数

①sin(x)　　返回x的正玄，其中x为弧度值

②asin(x)　　返回x的反正玄，即正玄为x的值。若x不在-1和1的范围之内，则返回null

select sin(radians(0)),sin(radians(90)),sin(radians(-90)),asin(-1),asin(0),asin(-1),degrees(asin(-1)),degrees(asin(0)),degrees(asin(1));

 

 

 注意：asin和sin互为反函数。

13、余玄函数和反余玄函数

①cos(x)　　返回x的余玄，其中x为弧度值

②acos(x)　　返回x的反余玄，即余玄是x的值。若x不在-1和1的范围之内，则返回NULL

select cos(radians(0)),cos(pi()),cos(pi()/2),cos(radians(90)),cos(radians(180)),degrees(acos(-1)),degrees(acos(0)),degrees(acos(1)),acos(2);

 

 

 注意：acos和cos互为反函数。

14、正切函数、反正切函数、余切函数

①tan(x)　　返回x的正切，其中x为给定的弧度值

②atan(x)　　返回x的反正切，即正切为x的值

③cot(x)　　返回x的余切

select tan(radians(-90)),tan(radians(-45)),tan(radians(0)),tan(radians(45)),tan(radians(90)),degrees(atan(-1)),degrees(atan(0)),degrees(atan(1));

 

 

select cot(radians(0.1)),cot(radians(45)),cot(radians(90)),cot(radians(135)),cot(radians(180));

 

 

二、字符串函数

1、字符串字符数、字符串长度

①char_length(str)　　返回字符串str的字符数，一个多字节字符算作一个字符。

②length(str)　　返回字符串str的字节数，使用utf8编码字符集时，一个汉字是3个字节，一个数字或字母是1个字节

select char_length('date'),char_length('101'),char_length('中国'),char_length(null),char_length(''),length('date'),length('101'),length('中国'),length(null),length('');

 

 2、拼接字符串

①concat(s1,s2,...)　　返回结果为连接参数产生的字符串。如果有任何一个参数为NULL，则返回值为NULL。如果所有参数均为非二进制字符串，则返回值为非二进制字符串。如果有任何一个参数为二进制字符串，则返回值为二进制字符串

select concat('My SQL','8.0'),concat('My',NULL,'SQL');

 

 ②concat_ws(x,s1,s2,...)　　第一个参数x是其它参数的分隔符，分隔符的位置放在要连接的两个字符串之间，分隔符可以是一个字符串，也可以是其他参数。如果分隔符是NULL，则结果为NULL。函数会忽略任何分隔符参数后的NULL值

select concat_ws('-','1st','2st','3st'),concat_ws('*','1st','null','3st'),concat_ws('*','1st',null,'3st'),concat_ws(null,'1st','2st','3st');

 

 3、替换字符串

insert(s1,x,len,s2)　　s1表示原字符串，x表示字符串s1的位置(从1开始)，len表示需替换的长度，即从x开始的len长度(包含x对应的字符)，s2表示替换的字符串，最终返回替换后的字符串。

如果x超过字符串s1的长度，则返回值为原字符串s1。

如果替换长度len超出了原字符串s1的长度，则从x位置开始，截取后面所有的字符，并替换为指定的字符串s2。

如果人一个参数为NULL，则返回值为NULL。

select insert('Quest',2,4,'What'),insert('Quest',-1,4,'What'),insert('Quest',3,100,'What'),insert('Quest',3,100,null);

 4、字母大小写转换

①lower(str)、lcase(str)　　将字符串str中的字母字符全部替换成小写字母

②upper(str)、ucase(str)　　将字符串str中的字母字符全部替换成大写字母

select lower('This is 红太阳'),lcase('This is 苹果'),upper('This is 红太阳'),ucase('This is 红太阳');

 

 5、获取指定长度的字符串

①left(str,n)　　返回字符串str最左边n个字符

②right(str,n)　　返回字符串str最右边n个字符

select left('football',5),right('football',4),left('football',8),right('football',8),left('football',-1),right('football',-1),isnull(right('football',-1)),right('football',-1)='';

 

 注意：如果n大于字符串的长度，则返回原字符串，如果n为负数，则返回空字符串'(')，而不是null。

6、填充字符串

①lpad(s1,len,s2)　　s1表示原字符串，len表示结果字符串总长度，s2表示填充的字符串，即，在s1左边被s2填补到len字符长度，如果s1的长度大于len，则返回值被压缩至len字符

②rpad(s1,len,s2)　　s1表示原字符串，len表示结果字符串总长度，s2表示填充的字符串，即，在s1右边被s2填补到len字符长度，如果s1的长度大于len，则返回值被压缩至len字符

select lpad('hello',4,'?'),lpad('hello',10,'?'),lpad('中国',4,'*'),rpad('hello',4,'?'),rpad('hello',10,'?'),rpad('中国',4,'*'),lpad('中国',-1,'*'),rpad('中国',-1,'*'),lpad('中国',-1,'*') is null,lpad('中国',-1,'*')='';

 

 注意：如果s1的长度大于len，则返回值为s1被压缩到len长度；如果len为负数，则返回值为null，并不是空字符串。

7、删除空格

①ltrim(str)　　返回值为删除字符串str左侧空格后的字符串，不会删除str右侧的空格

②rtrim(str)　　返回值为删除字符串str右侧空格后的字符串，不会删除str左侧的空格

③trim(str)　　返回值为删除字符串str两侧空格后的字符串

select '(  book  )',concat('(',ltrim('  book  '),')'),concat('(',rtrim('  book  '),')'),concat('(',trim('  book  '),')');

 

 8、删除指定字符串

trim(s1 from str)　　删除字符串str中两端所有的子字符串s1，s1为可选项，在未指定的情况下，删除空格

select trim('xy' from 'xyxboxyokxxyxy');

 

 注意：不存在ltrim(s1 from str)、rtrim(s1 from str)

9、重复生成字符串

repeat(str,n)　　返回一个由重复的字符串str组成的字符串，重复的次数等于n。若n小于等于0，则返回一个空字符串。若s或n为null，则返回null

select repeat('mysql',3),repeat('mysql',0),repeat('mysql',-1),repeat('mysql',0)='',repeat('mysql',0) is null,repeat('mysql',null),repeat(null,3);

 

 10、空格函数

space(n)　　返回一个由n个空格组成的字符串

select concat('(',space(6),')'),length(space(6));

 

 11、替换函数

replace(s,s1,s2)　　使用字符串s2替换字符串s中所有的字符串s1。若s1或s2为null，则返回值也为null。

select replace('xxx.mysql.com','x','w'),replace('xxx.mysql.com','t','w'),replace('xxx.mysql.com',null,'w'),replace('xxx.mysql.com','x',null);

 

 12、比较字符串大小

strcmp(s1,s2)　　若s1与s2相同，则返回0，根据当前次序，第一个参数s1小于第二个参数s2，则返回-1，第一个参数s1大于第二个参数s2，则返回1。如果有至少一个参数为null，则结果为null。

select strcmp('txt','txt2'),strcmp('txt2','txt'),strcmp('txt','txt'),strcmp(null,'txt2'),strcmp('txt',null),strcmp(null,null);

 

 13、获取子串

①substring(str,n,len)　　如果带有参数len，则从字符串str返回一个长度为len的子字符串，起始于位置n，如果没有参数len，则返回起始于位置n后的整个子串。如果n为负值，则子字符串的位置起始于字符串str结尾的n字符，即倒数第n个字符，而不是字符串str的开头位置

②mid(str,n,len)　　作用与substring相同

select substring('breakfast',5),substring('breakfast',5,3),substring('breakfast',-3),substring('breakfast',-5,3),substring('breakfast',0),substring('breakfast',0)='',substring('breakfast',5,0),substring('breakfast',5,0)='';

 

 注意：如果len小于1，则结果始终为空字符串；n的值为0，则结果为空字符串，即开始位置为1。

14、匹配子串开始位置

locate(str1,str)、position(str1 in str)、instr(str,str1)　　作用相同，返回子字符串str1在字符串str中的开始位置

select locate('ball','football'),position('ball' in 'football'),instr('football','ball'),locate('ab','football'),position('ab' in 'football'),instr('football','ab'),locate('ball',null),locate(null,'football');

 

 注意：如果子字符串str1在str中不存在，则返回0；如果str或str1为null，则返回值为null。

15、字符串逆序

reverse(str)　　将字符串str反转

select reverse('abc'),reverse(null)

 

 16、返回列表中指定位置的字符串

elt(n,字符串1,字符串2,字符串3,...,字符串n)　　若n=1，则返回字符串1；若n=2，则返回字符串2；若n=n，则返回字符串n；若n小于1或者n大于参数中字符串的数目，则返回null

select elt(1,'1st','2nd','3rd'),elt(3,'net','os'),elt(0,'a','b');

 

 17、返回指定字符串在列表中的位置

field(s,str1,str2,...,strn)　　返回字符串s在列表str1,str2,...,strn中第一次出现的位置，从1开始；在找不到s的情况下返回0；如果s为null，则返回0，因为null不能同任何值进行同等比较

select field('Hi','hihi','hi','hey','Hi','bye'),field('Hii','hihi','hi','hey','Hi','bye'),field(null,'hihi','hi','hey','Hi','bye');

 

 注意：不区分大小写。

18、返回指定字符串在列表中的位置

find_in_set(s1,s2)　　返回字符串s1在字符串列表s2中出现的位置，字符串列表是一个由多个逗号','分割的字符串组成的列表。如果s1不在s2中或s2为空字符串，则返回值为0。如果任意一个参数为null，则返回值为null。如果第一个参数s1包含一个逗号','，则无法正常运行。

select find_in_set('Hi','hihi,hi,hey,Hi,bye'),find_in_set('Hii','hihi,hi,hey,Hi,bye'),find_in_set(null,'hihi,hi,hey,Hi,bye'),find_in_set('Hii',null),find_in_set('a,b','d,a,b,c');

 

 19、从字符串列表中选择字符串

make_set(x,s1,s2,...,sn)　　按x的二进制数从s1,s2,...,sn中选取字符串。例如5的二进制是0101，这个二进制从右往左的第1位和第3位是1，则选取s1，s3。 其中s1,s2,...,sn中的null不会被添加到结果中。

select make_set(1,'a','b','c'),make_set(1 | 4,'hello','nice','world'),make_set(1 | 4,'hello','nice',null,'world'),make_set(0,'hello','nice','world'),make_set(0,'hello','nice','world') is null,make_set(0,'hello','nice','world')='';