MySQL函数大全及用法示例

本文内容纲要：

1、字符串函数 ascii

(str)

返回字符串str的第一个字符的ascii值(str是空串时返回0)

mysql>selectascii(2);

->50

mysql>selectascii(2);

->50

mysql>selectascii(dete);

->100

ord(str) 如果字符串str句首是单字节返回与ascii()函数返回的相同值。

如果是一个多字节字符,以格式返回((firstbyteasciicode)*256+(secondbyteasciicode))[*256+thirdbyteasciicode...]

mysql>selectord(2);

->50

conv(n,from_base,to_base)

对数字n进制转换,并转换为字串返回(任何参数为null时返回null,进制范围为2-36进制,当to_base是负数时n作为有符号数否则作无符号数,conv以64位点精度工作)

mysql>selectconv("a",16,2);

->1010

mysql>selectconv("6e",18,8);

->172

mysql>selectconv(-17,10,-18);

->-h

mysql>selectconv(10+"10"+10+0xa,10,10);

->40

bin(n)

把n转为二进制值并以字串返回(n是bigint数字,等价于conv(n,10,2))

mysql>selectbin(12);

->1100

oct(n)

把n转为八进制值并以字串返回(n是bigint数字,等价于conv(n,10,8))

mysql>selectoct(12);

->14

hex(n)

把n转为十六进制并以字串返回(n是bigint数字,等价于conv(n,10,16))

mysql>selecthex(255);

->ff

char(n,...)

返回由参数n,...对应的ascii代码字符组成的一个字串(参数是n,...是数字序列,null值被跳过)

mysql>selectchar(77,121,83,81,76);

->mysql

mysql>selectchar(77,77.3,77.3);

->mmm

concat(str1,str2,...)

把参数连成一个长字符串并返回(任何参数是null时返回null)

mysql>selectconcat(my,s,ql);

->mysql

mysql>selectconcat(my,null,ql);

->null

mysql>selectconcat(14.3);

->14.3

length(str) octet_length(str) char_length(str) character_length(str)

返回字符串str的长度(对于多字节字符char_length仅计算一次)

mysql>selectlength(text);

->4

mysql>selectoctet_length(text);

->4

locate(substr,str) position(substrinstr)

返回字符串substr在字符串str第一次出现的位置(str不包含substr时返回0)

mysql>selectlocate(bar,foobarbar);

->4

mysql>selectlocate(xbar,foobar);

->0

locate(substr,str,pos)

返回字符串substr在字符串str的第pos个位置起第一次出现的位置(str不包含substr时返回0)

mysql>selectlocate(bar,foobarbar,5);

->7

instr(str,substr)

返回字符串substr在字符串str第一次出现的位置(str不包含substr时返回0)

mysql>selectinstr(foobarbar,bar);

->4

mysql>selectinstr(xbar,foobar);

->0

lpad(str,len,padstr)

用字符串padstr填补str左端直到字串长度为len并返回

mysql>selectlpad(hi,4,??);

->??hi

rpad(str,len,padstr)

用字符串padstr填补str右端直到字串长度为len并返回

mysql>selectrpad(hi,5,?);

->hi???

left(str,len)

返回字符串str的左端len个字符

mysql>selectleft(foobarbar,5);

->fooba

right(str,len)

返回字符串str的右端len个字符

mysql>selectright(foobarbar,4);

->rbar

substring(str,pos,len) substring(strfromposforlen) mid(str,pos,len)

返回字符串str的位置pos起len个字符mysql>selectsubstring(quadratically,5,6);

->ratica

substring(str,pos) substring(strfrompos)

返回字符串str的位置pos起的一个子串

mysql>selectsubstring(quadratically,5);

->ratically

mysql>selectsubstring(foobarbarfrom4);

->barbar

substring_index(str,delim,count)

返回从字符串str的第count个出现的分隔符delim之后的子串

(count为正数时返回左端,否则返回右端子串)

mysql>selectsubstring_index(www.mysql.com,.,2);

->www.mysql

mysql>selectsubstring_index(www.mysql.com,.,-2);

->mysql.com

ltrim(str)

返回删除了左空格的字符串str

mysql>selectltrim(barbar);

->barbar

rtrim(str)

返回删除了右空格的字符串str

mysql>selectrtrim(barbar);

->barbar

trim([[both|leading|trailing][remstr]from]str)

返回前缀或后缀remstr被删除了的字符串str(位置参数默认both,remstr默认值为空格)

mysql>selecttrim(bar);

->bar

mysql>selecttrim(leadingxfromxxxbarxxx);

->barxxx

mysql>selecttrim(bothxfromxxxbarxxx);

->bar

mysql>selecttrim(trailingxyzfrombarxxyz);

->barx

soundex(str)

返回str的一个同音字符串(听起来“大致相同”字符串有相同的

同音字符串,非数字字母字符被忽略,在a-z外的字母被当作元音)

mysql>selectsoundex(hello);

->h400

mysql>selectsoundex(quadratically);

->q36324

space(n)

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

mysql>selectspace(6);

->

replace(str,from_str,to_str)

用字符串to_str替换字符串str中的子串from_str并返回

mysql>selectreplace(www.mysql.com,w,ww);

->wwwwww.mysql.com

repeat(str,count)

返回由count个字符串str连成的一个字符串(任何参数为null时

返回null,count<=0时返回一个空字符串)

mysql>selectrepeat(mysql,3);

->mysqlmysqlmysql

reverse(str)

颠倒字符串str的字符顺序并返回

mysql>selectreverse(abc);

->cba

insert(str,pos,len,newstr)

把字符串str由位置pos起len个字符长的子串替换为字符串

newstr并返回

mysql>selectinsert(quadratic,3,4,what);

->quwhattic

elt(n,str1,str2,str3,...)

返回第n个字符串(n小于1或大于参数个数返回null)

mysql>selectelt(1,ej,heja,hej,foo);

->ej

mysql>selectelt(4,ej,heja,hej,foo);

->foo

field(str,str1,str2,str3,...)

返回str等于其后的第n个字符串的序号(如果str没找到返回0)

mysql>selectfield(ej,hej,ej,heja,hej,

foo);

->2

mysql>selectfield(fo,hej,ej,heja,hej,

foo);

->0

find_in_set(str,strlist)

返回str在字符串集strlist中的序号(任何参数是null则返回

null,如果str没找到返回0,参数1包含","时工作异常)

mysql>selectfind_in_set(b,a,b,c,d);

->2

make_set(bits,str1,str2,...)

把参数1的数字转为二进制,假如某个位置的二进制位等于1,对应

位置的字串选入字串集并返回(null串不添加到结果中)

mysql>selectmake_set(1,a,b,c);

->a

mysql>selectmake_set(1|4,hello,nice,world);

->hello,world

mysql>selectmake_set(0,a,b,c);

->

export_set(bits,on,off,[separator,[number_of_bits]])

按bits排列字符串集,只有当位等于1时插入字串on,否则插入

off(separator默认值",",number_of_bits参数使用时长度不足补0

而过长截断)

mysql>selectexport_set(5,y,n,,,4)

->y,n,y,n

lcase(str) lower(str)

返回小写的字符串str

mysql>selectlcase(quadratically);

->quadratically

ucase(str) upper(str)

返回大写的字符串str

mysql>selectucase(quadratically);

->quadratically

load_file(file_name)

读入文件并且作为一个字符串返回文件内容(文件无法找到,路径

不完整,没有权限,长度大于max_allowed_packet会返回null)

mysql>updatetable_namesetblob_column=load_file

("/tmp/picture")whereid=1;

2、数学函数 abs(n)

返回n的绝对值

mysql>selectabs(2);

->2

mysql>selectabs(-32);

->32

sign(n)

返回参数的符号(为-1、0或1)

mysql>selectsign(-32);

->-1

mysql>selectsign(0);

->0

mysql>selectsign(234);

->1

mod(n,m)

取模运算,返回n被m除的余数(同%操作符)

mysql>selectmod(234,10);

->4

mysql>select234%10;

->4

mysql>selectmod(29,9);

->2

floor(n)

返回不大于n的最大整数值

mysql>selectfloor(1.23);

->1

mysql>selectfloor(-1.23);

->-2

ceiling(n)

返回不小于n的最小整数值

mysql>selectceiling(1.23);

->2

mysql>selectceiling(-1.23);

->-1

round(n,d)

返回n的四舍五入值,保留d位小数(d的默认值为0)

mysql>selectround(-1.23);

->-1

mysql>selectround(-1.58);

->-2

mysql>selectround(1.58);

->2

mysql>selectround(1.298,1);

->1.3

mysql>selectround(1.298,0);

->1

exp(n)

返回值e的n次方(自然对数的底)

mysql>selectexp(2);

->7.389056

mysql>selectexp(-2);

->0.135335

log(n)

返回n的自然对数

mysql>selectlog(2);

->0.693147

mysql>selectlog(-2);

->null

log10(n)

返回n以10为底的对数

mysql>selectlog10(2);

->0.301030

mysql>selectlog10(100);

->2.000000

mysql>selectlog10(-100);

->null

pow(x,y) power(x,y)

返回值x的y次幂

mysql>selectpow(2,2);

->4.000000

mysql>selectpow(2,-2);

->0.250000

sqrt(n)

返回非负数n的平方根

mysql>selectsqrt(4);

->2.000000

mysql>selectsqrt(20);

->4.472136

pi()

返回圆周率

mysql>selectpi();

->3.141593

cos(n)

返回n的余弦值

mysql>selectcos(pi());

->-1.000000

sin(n)

返回n的正弦值

mysql>selectsin(pi());

->0.000000

tan(n)

返回n的正切值

mysql>selecttan(pi()+1);

->1.557408

acos(n)

返回n反余弦(n是余弦值,在-1到1的范围,否则返回null)

mysql>selectacos(1);

->0.000000

mysql>selectacos(1.0001);

->null

mysql>selectacos(0);

->1.570796

asin(n)

返回n反正弦值

mysql>selectasin(0.2);

->0.201358

mysql>selectasin(foo);

->0.000000

atan(n)

返回n的反正切值

mysql>selectatan(2);

->1.107149

mysql>selectatan(-2);

->-1.107149

atan2(x,y)

返回2个变量x和y的反正切(类似y/x的反正切,符号决定象限)

mysql>selectatan(-2,2);

->-0.785398

mysql>selectatan(pi(),0);

->1.570796

cot(n)

返回x的余切

mysql>selectcot(12);

->-1.57267341

mysql>selectcot(0);

->null

rand() rand(n) 返回在范围0到1.0内的随机浮点值(可以使用数字n作为初始值)

mysql>selectrand();

->0.5925

mysql>selectrand(20);

->0.1811

mysql>selectrand(20);

->0.1811

mysql>selectrand();

->0.2079

mysql>selectrand();

->0.7888

degrees(n)

把n从弧度变换为角度并返回

mysql>selectdegrees(pi());

->180.000000

radians(n)

把n从角度变换为弧度并返回

mysql>selectradians(90);

->1.570796

truncate(n,d)

保留数字n的d位小数并返回

mysql>selecttruncate(1.223,1);

->1.2

mysql>selecttruncate(1.999,1);

->1.9

mysql>selecttruncate(1.999,0);

->1

least(x,y,...)

返回最小值(如果返回值被用在整数(实数或大小敏感字串)上下文或所有参数都是整数(实数或大小敏感字串)则他们作为整数(实数或大小敏感字串)比较,否则按忽略大小写的字符串被比较)

mysql>selectleast(2,0);

->0

mysql>selectleast(34.0,3.0,5.0,767.0);

->3.0

mysql>selectleast("b","a","c");

->"a"

greatest(x,y,...)

返回最大值(其余同least())

mysql>selectgreatest(2,0);

->2

mysql>selectgreatest(34.0,3.0,5.0,767.0);

->767.0

mysql>selectgreatest("b","a","c");

->"c"

3、时期时间函数 dayofweek(date)

返回日期date是星期几(1=星期天,2=星期一,……7=星期六,odbc标准)

mysql>selectdayofweek(1998-02-03);

->3

weekday(date) 返回日期date是星期几(0=星期一,1=星期二,……6=星期天)。

mysql>selectweekday(1997-10-0422:23:00);

->5

mysql>selectweekday(1997-11-05);

->2

dayofmonth(date)

返回date是一月中的第几日(在1到31范围内)

mysql>selectdayofmonth(1998-02-03);

->3

dayofyear(date)

返回date是一年中的第几日(在1到366范围内)

mysql>selectdayofyear(1998-02-03);

->34

month(date)

返回date中的月份数值

mysql>selectmonth(1998-02-03);

->2

dayname(date)

返回date是星期几(按英文名返回)

mysql>selectdayname("1998-02-05");

->thursday

monthname(date)

返回date是几月(按英文名返回)

mysql>selectmonthname("1998-02-05");

->february

quarter(date)

返回date是一年的第几个季度

mysql>selectquarter(98-04-01);

->2

week(date,first)

返回date是一年的第几周(first默认值0,first取值1表示周一是

周的开始,0从周日开始)

mysql>selectweek(1998-02-20);

->7

mysql>selectweek(1998-02-20,0);

->7

mysql>selectweek(1998-02-20,1);

->8

year(date)

返回date的年份(范围在1000到9999)

mysql>selectyear(98-02-03);

->1998

hour(time)

返回time的小时数(范围是0到23)

mysql>selecthour(10:05:03);

->10

minute(time)

返回time的分钟数(范围是0到59)

mysql>selectminute(98-02-0310:05:03);

->5

second(time)

返回time的秒数(范围是0到59)

mysql>selectsecond(10:05:03);

->3

period_add(p,n)

增加n个月到时期p并返回(p的格式yymm或yyyymm)

mysql>selectperiod_add(9801,2);

->199803

period_diff(p1,p2)

返回在时期p1和p2之间月数(p1和p2的格式yymm或yyyymm)

mysql>selectperiod_diff(9802,199703);

->11

date_add(date,intervalexprtype)

date_sub(date,intervalexprtype)

adddate(date,intervalexprtype)

subdate(date,intervalexprtype)

对日期时间进行加减法运算

(adddate()和subdate()是date_add()和date_sub()的同义词,也

可以用运算符+和-而不是函数

date是一个datetime或date值,expr对date进行加减法的一个表

达式字符串type指明表达式expr应该如何被解释

[type值含义期望的expr格式]:

second秒seconds

minute分钟minutes

hour时间hours

day天days

month月months

year年years

minute_second分钟和秒"minutes:seconds"

hour_minute小时和分钟"hours:minutes"

day_hour天和小时"dayshours"

year_month年和月"years-months"

hour_second小时,分钟，"hours:minutes:seconds"

day_minute天,小时,分钟"dayshours:minutes"

day_second天,小时,分钟,秒"days

hours:minutes:seconds"

expr中允许任何标点做分隔符,如果所有是date值时结果是一个

date值,否则结果是一个datetime值)

如果type关键词不完整,则mysql从右端取值,day_second因为缺

少小时分钟等于minute_second)

如果增加month、year_month或year,天数大于结果月份的最大天

数则使用最大天数)

mysql>select"1997-12-3123:59:59"+interval1second;

->1998-01-0100:00:00

mysql>selectinterval1day+"1997-12-31";

->1998-01-01

mysql>select"1998-01-01"-interval1second;

->1997-12-3123:59:59

mysql>selectdate_add("1997-12-3123:59:59",interval1

second);

->1998-01-0100:00:00

mysql>selectdate_add("1997-12-3123:59:59",interval1

day);

->1998-01-0123:59:59

mysql>selectdate_add("1997-12-3123:59:59",interval

"1:1"minute_second);

->1998-01-0100:01:00

mysql>selectdate_sub("1998-01-0100:00:00",interval"1

1:1:1"day_second);

->1997-12-3022:58:59

mysql>selectdate_add("1998-01-0100:00:00",interval"-1

10"day_hour);

->1997-12-3014:00:00

mysql>selectdate_sub("1998-01-02",interval31day);

->1997-12-02

mysql>selectextract(yearfrom"1999-07-02");

->1999

mysql>selectextract(year_monthfrom"1999-07-02

01:02:03");

->199907

mysql>selectextract(day_minutefrom"1999-07-02

01:02:03");

->20102

to_days(date)

返回日期date是西元0年至今多少天(不计算1582年以前)

mysql>selectto_days(950501);

->728779

mysql>selectto_days(1997-10-07);

->729669

from_days(n)

给出西元0年至今多少天返回date值(不计算1582年以前)

mysql>selectfrom_days(729669);

->1997-10-07

date_format(date,format)

根据format字符串格式化date值

(在format字符串中可用标志符:

%m月名字(january……december)

%w星期名字(sunday……saturday)

%d有英语前缀的月份的日期(1st,2nd,3rd,等等。）

%y年,数字,4位

%y年,数字,2位

%a缩写的星期名字(sun……sat)

%d月份中的天数,数字(00……31)

%e月份中的天数,数字(0……31)

%m月,数字(01……12)

%c月,数字(1……12)

%b缩写的月份名字(jan……dec)

%j一年中的天数(001……366)

%h小时(00……23)

%k小时(0……23)

%h小时(01……12)

%i小时(01……12)

%l小时(1……12)

%i分钟,数字(00……59)

%r时间,12小时(hh:mm:ss[ap]m)

%t时间,24小时(hh:mm:ss)

%s秒(00……59)

%s秒(00……59)

%pam或pm

%w一个星期中的天数(0=sunday……6=saturday）

%u星期(0……52),这里星期天是星期的第一天

%u星期(0……52),这里星期一是星期的第一天

%%字符%)

mysql>selectdate_format(1997-10-0422:23:00,%w%m%

y);

->saturdayoctober1997

mysql>selectdate_format(1997-10-0422:23:00,%h:%i:%

s);

->22:23:00

mysql>selectdate_format(1997-10-0422:23:00,%d%y%a

%d%m%b%j);

->4th97sat0410oct277

mysql>selectdate_format(1997-10-0422:23:00,%h%k%i

%r%t%s%w);

->22221010:23:00pm22:23:00006

time_format(time,format)

和date_format()类似,但time_format只处理小时、分钟和秒(其

余符号产生一个null值或0)

curdate() current_date()

以yyyy-mm-dd或yyyymmdd格式返回当前日期值(根据返回值所

处上下文是字符串或数字)

mysql>selectcurdate();

->1997-12-15

mysql>selectcurdate()+0;

->19971215

curtime() current_time()

以hh:mm:ss或hhmmss格式返回当前时间值(根据返回值所处上

下文是字符串或数字)

mysql>selectcurtime();

->23:50:26

mysql>selectcurtime()+0;

->235026

now() sysdate() current_timestamp()

以yyyy-mm-ddhh:mm:ss或yyyymmddhhmmss格式返回当前日期

时间(根据返回值所处上下文是字符串或数字)

mysql>selectnow();

->1997-12-1523:50:26

mysql>selectnow()+0;

->19971215235026

unix_timestamp() unix_timestamp(date)

返回一个unix时间戳(从1970-01-0100:00:00gmt开始的秒

数,date默认值为当前时间)

mysql>selectunix_timestamp();

->882226357

mysql>selectunix_timestamp(1997-10-0422:23:00);

->875996580

from_unixtime(unix_timestamp)

以yyyy-mm-ddhh:mm:ss或yyyymmddhhmmss格式返回时间戳的

值(根据返回值所处上下文是字符串或数字)

mysql>selectfrom_unixtime(875996580);

->1997-10-0422:23:00

mysql>selectfrom_unixtime(875996580)+0;

->19971004222300

from_unixtime(unix_timestamp,format)

以format字符串格式返回时间戳的值

mysql>selectfrom_unixtime(unix_timestamp(),%y%d%m%

h:%i:%s%x);

->199723rddecember03:43:30x

sec_to_time(seconds)

以hh:mm:ss或hhmmss格式返回秒数转成的time值(根据返回值所处上下文是字符串或数字)

mysql>selectsec_to_time(2378);

->00:39:38

mysql>selectsec_to_time(2378)+0;

->3938

time_to_sec(time)

返回time值有多少秒

mysql>selecttime_to_sec(22:23:00);

->80580

mysql>selecttime_to_sec(00:39:38);

->2378

转换函数

cast

用法：cast(字段as数据类型)[当然是否可以成功转换，还要看数据类型强制转化时注意的问题]

实例：selectcast(aasunsigned)asbfromcardserverwhereorderbybdesc;

convert：

用法：convert(字段,数据类型)

实例：selectconvert(a,unsigned)asbfromcardserverwhereorderbybdesc;

本文参考http://blog.sina.com.cn/s/blog_4586764e0100h5ct.html

本文内容总结：

原文链接：https://www.cnblogs.com/xuyulin/p/5468102.html