mysql常用函数
mysql常用函数
字符串函数
对于针对字符串位置的操作,第一个位置被标记为1。
函数 | 功能 |
---|---|
concat(str1,str2,...) | 连接字符串 |
lower(str) | 转换成小写 |
upper(str) | 转换成大写 |
length(str) | 返回str的长度 |
char_length(str) | 返回str的长度,对于多字节字符,仅计算一次 |
insert(str,start,length,str2) | 将字符串str中start位置开始长度为length的字符串用str2替换。该函数对多字节是可靠的。 |
trim(str) | 去掉str前缀和后缀的空格 |
replace(str,from_str,to_str) | 把str中的from_str替换成to_str |
lpad(str,length,padstr) | 返回字符串str,左面用字符串padstr填补直到str是length个字符长。 |
rpad(str,length,padstr) | 返回字符串str,右面用字符串padstr填补直到str是length个字符长。 |
repeat(str,times) | 把字符串重复times次 |
ascii(str) | 返回字符串str的最左面字符的ASCII代码值。如果str是空字符串,返回0。如果str是NULL,返回NULL。 |
ord(str) | 如果字符串str最左面字符是一个多字节字符,通过以格式((first byte ASCII code)256+(second byte ASCII code))[256+third byte ASCII code...]返回字符的ASCII代码值来返回多字节字符代码。如果最左面的字符不是一个多字节字符。返回与ASCII()函数返回的相同值。 |
locate(substr,str) | 返回子串substr在字符串str第一个出现的位置,如果substr不是在str里面,返回0. |
locate(substr,str,pos) | 返回子串substr在字符串str第一个出现的位置,从位置pos开始。如果substr不是在str里面,返回0。 |
position(substr IN str) | 返回子串substr在字符串str第一个出现的位置,如果substr不是在str里面,返回0. |
instr(str,substr) | 返回子串substr在字符串str中的第一个出现的位置。这与有2个参数形式的LOCATE()相同,除了参数被颠倒。 |
left(str,len) | 返回字符串str的最左面len个字符。该函数是多字节可靠的。 |
right(str,len) | 返回字符串str的最右面len个字符。该函数是多字节可靠的。 |
substring(str,pos,len) | 从字符串str返回一个len个字符的子串,从位置pos开始。使用FROM的变种形式[substring(str FROM pos FOR len)]是ANSI SQL92语法。该函数是多字节可靠的。 |
mid(str,pos,len) | 从字符串str返回一个len个字符的子串,从位置pos开始。使用FROM的变种形式[mid(str FROM pos FOR len)]是ANSI SQL92语法。该函数是多字节可靠的。 |、 |
substring_index(str,delim,count) | 返回从字符串str的第count个出现的分隔符delim之后的子串。如果count是正数,返回最后的分隔符到左边(从左边数) 的所有字符。如果count是负数,返回最后的分隔符到右边的所有字符(从右边数)。该函数对多字节是可靠的。 |
ltrim(str) | 返回删除了其前置空格字符的字符串str。 |
rtrim(str) | 返回删除了其拖后空格字符的字符串str。 |
TRIM([[BOTH | LEADING | TRAILING] [remstr] FROM] str) | 返回字符串str,其所有remstr前缀或后缀被删除了。如果没有修饰符BOTH、LEADING或TRAILING给出,BOTH被假定。如果remstr没被指定,空格被删除。 该函数对多字节是可靠的。 |
soundex(str) | 返回str的一个同音字符串。听起来“大致相同”的2个字符串应该有相同的同音字符串。一个“标准”的同音字符串长是4个字符,但是SOUNDEX()函数返回一个任意长的字符串。你可以在结果上使用SUBSTRING()得到一个“标准”的 同音串。所有非数字字母字符在给定的字符串中被忽略。所有在A-Z之外的字符国际字母被当作元音。 用户比较一些读音相似拼写却不同的字串 |
space(n) | 返回有n个空格组成的字串 |
reverse(str) | 返回颠倒字符顺序的字符串str。该函数对多字节是可靠的。 |
elt(N,str1,str2,str3,...) | 如果N= 1,返回str1,如果N= 2,返回str2,等等。如果N小于1或大于参数个数,返回NULL。elf()是field()反运算。 |
field(str,str1,str2,str3,...) | 返回str在str1, str2, str3, ...清单的索引。如果str没找到,返回0。field()是elf()反运算。 |
find_in_set(str,strlist) | 如果字符串str在由N子串组成的表strlist之中,返回一个1到N的值。一个字符串表是被“,”分隔的子串组成的一个字符串。如果第一个参数是一个常数字符串并且第二个参数是一种类型为SET的列,FIND_IN_SET()函数被优化而使用位运算!如果str不是在strlist里面或如果strlist是空字符串,返回0。如果任何一个参数是NULL,返回NULL。如果第一个参数包含一个“,”,该函数将工作不正常。 |
make_set(bits,str1,str2,...) | 返回一个集合 (包含由“,”字符分隔的子串组成的一个字符串),由相应的位在bits(二进制的1所在位对应的字串,具体见例子)集合中的的字符串组成。str1对应于位0,str2对应位1,等等。在str1, str2, ...中的NULL串不添加到结果中。 |
export_set(bits,on,off,[separator,[number_of_bits]]) | 返回一个字符串,在这里对于在“bits”中设定每一位,你得到一个“on”字符串,并且对于每个复位(reset)的位,你得到一个“off”字符串。每个字符串用“separator”分隔(缺省“,”),并且只有“bits”的“number_of_bits” (缺省64)位被使用。 |
lcase(str) | 返回字符串str,根据当前字符集映射(缺省是ISO-8859-1 Latin1)把所有的字符改变成小写。该函数对多字节是可靠的。 |
ucase(str) | 返回字符串str,根据当前字符集映射(缺省是ISO-8859-1 Latin1)把所有的字符改变成大写。该函数对多字节是可靠的。 |
load_file(file_name) | 读入文件并且作为一个字符串返回文件内容。文件必须在服务器上,你必须指定到文件的完整路径名,而且你必须有file权限。文件必须所有内容都是可读的并且小于max_allowed_packet。如果文件不存在或由于上面原因之一不能被读出,函数返回NULL。 |
范例
mysql> select concat('111','222','333'),lower('ABCJDKDK'),upper('ADkjkjADD'),length('1223'),insert('abcdefg',2,4,'xy'),length('abc '),length(trim('abc ')),replace('abcdefg','abc','xxx'),lpad('abc',10,'xyz'),rpad('abc',10,'xyz'),repeat('abc',5),ascii('abc'),ord('哈哈哈'),length('哈哈哈'),char_length('哈哈哈'),character_length('哈哈哈'),locate('c','abcd'),position('c' in 'abcd'),locate('c','abcde',5),instr('abcde','c'),left('abcde',3),right('abcde',3),substring('abcdef',2,3),substring('abcdef' from 2 for 3),mid('abcdef',2,3),mid('abcdef' from 2 for 3),substring_index('abc,def,ghi.jhi',',',2),substring_index('abc,def,ghi.jhi',',',-2),trim(TRAILING 'xyz' FROM 'barxxyz'),soundex('too'),soundex('to'),length(space(6)),reverse('测试字串'),elt(1,'abc','def'),field('a','b','bac','bg','abc','a'),find_in_set('a','aa,bb'),find_in_set('a','aa,bb,a'),make_set(1|4,'hello','nice','world'),bin(1|4),export_set(5,'Y','N',',',4),bin(5),lcase('ADADasd'),ucase('ASASsdfds'),load_file('/tmp/test.txt')\G;
*************************** 1. row ***************************
concat('111','222','333'): 111222333
lower('ABCJDKDK'): abcjdkdk
upper('ADkjkjADD'): ADKJKJADD
length('1223'): 4
insert('abcdefg',2,4,'xy'): axyfg
length('abc '): 6
length(trim('abc ')): 3
replace('abcdefg','abc','xxx'): xxxdefg
lpad('abc',10,'xyz'): xyzxyzxabc
rpad('abc',10,'xyz'): abcxyzxyzx
repeat('abc',5): abcabcabcabcabc
ascii('abc'): 97
ord('哈哈哈'): 15045512
length('哈哈哈'): 9
char_length('哈哈哈'): 3
character_length('哈哈哈'): 3
locate('c','abcd'): 3
position('c' in 'abcd'): 3
locate('c','abcde',5): 0
instr('abcde','c'): 3
left('abcde',3): abc
right('abcde',3): cde
substring('abcdef',2,3): bcd
substring('abcdef' from 2 for 3): bcd
mid('abcdef',2,3): bcd
mid('abcdef' from 2 for 3): bcd
substring_index('abc,def,ghi.jhi',',',2): abc,def
substring_index('abc,def,ghi.jhi',',',-2): def,ghi.jhi
trim(TRAILING 'xyz' FROM 'barxxyz'): barx
soundex('too'): T000
soundex('to'): T000
length(space(6)): 6
reverse('测试字串'): 串字试测
elt(1,'abc','def'): abc
field('a','b','bac','bg','abc','a'): 5
find_in_set('a','aa,bb'): 0
find_in_set('a','aa,bb,a'): 3
make_set(1|4,'hello','nice','world'): hello,world
bin(1|4): 101
export_set(5,'Y','N',',',4): Y,N,Y,N
bin(5): 101
lcase('ADADasd'): adadasd
ucase('ASASsdfds'): ASASSDFDS
load_file('/tmp/test.txt'): 111111111111111111111111111111111111111111111111111111111111111111
数字函数
函数 | 功能 |
---|---|
abs(x) | 返回绝对值 |
ceil(x) | 进一法取整,返回大于等于x的整数中最小的一个 |
floor(x) | 去尾法取整,返回小于等于x的整数中最大的一个 |
mod(x,y) | 返回x%y的模 |
rand() | 返回0-1之间的随机数 |
round(x,y) | 返回x四舍五入后有y位小数的值 |
truncate(x,y) | 返回x截断后有y位小数的值 |
CONV(N,from_base,to_base) | 在不同的数字基之间变换数字。返回数字N的字符串数字,从from_base基变换为to_base基,如果任何参数是NULL,返回NULL。参数N解释为一个整数,但是可以指定为一个整数或一个字符串。最小基是2且最大的基是36。如果to_base是一个负数,N被认为是一个有符号数,否则,N被当作无符号数。 CONV以64位点精度工作。 |
bin(n) | 返回二进制值N的一个字符串表示,在此N是一个长整数(BIGINT)数字,这等价于CONV(N,10,2)。如果N是NULL,返回NULL。 |
oct(n) | 返回八进制值N的一个字符串的表示,在此N是一个长整型数字,这等价于CONV(N,10,8)。如果N是NULL,返回NULL。 |
hex(n) | 返回十六进制值N一个字符串的表示,在此N是一个长整型(BIGINT)数字,这等价于CONV(N,10,16)。如果N是NULL,返回NULL。 |
CHAR(N,...) | 将参数解释为整数并且返回由这些整数的ASCII代码字符组成的一个字符串。NULL值被跳过。 |
日期函数
函数 | 功能 |
---|---|
dayofweek(date) | 返回日期date的星期索引(1=星期天,2=星期一, ……7=星期六)。这些索引值对应于ODBC标准。 |
weekday(date) | 返回date的星期索引(0=星期一,1=星期二, ……6= 星期天)。 |
dayofmonth(date) | 返回date的月份中日期,在1到31范围内。 |
dayofyear(date) | 返回date在一年中的日数, 在1到366范围内。 |
month(date) | 返回date的月份,范围1到12。 |
year(date) | 返回date的年份,范围在1000到9999。 |
hour(date) | 返回time的小时,范围是0到23。 |
dayname(date) | 返回date的星期名字。 |
monthname(date) | 返回date的月份名字。 |
quarter(date) | 返回date一年中的季度,范围1到4。 |
week(date,first) | 对于星期天是一周的第一天的地方,有一个单个参数,返回date的周数,范围在0到52。2个参数形式WEEK()允许你指定星期是否开始于星期天或星期一。如果第二个参数是0,星期从星期天开始,如果第二个参数是1,从星期一开始。 |
minute(date) | 返回time的分钟,范围是0到59。 |
second(date) | 回来time的秒数,范围是0到59。 |
period_add(P,N) | 增加N个月到阶段P(以格式YYMM或YYYYMM)。以格式YYYYMM返回值。注意阶段参数P不是日期值。 |
period_diff(P1,P2) | 返回在时期P1和P2之间月数,P1和P2应该以格式YYMM或YYYYMM。注意,时期参数P1和P2不是日期值。 |
to_days(date) | 不打算用于使用格列高里历(1582)出现前的值。 |
from_days(N) | 给出一个天数N,返回一个DATE值。 |
date_format(date,format) | 根据format字符串格式化date值。在format字符串中可用修饰符:见表格下方 |
date_format(date,format) | 这象上面的DATE_FORMAT()函数一样使用,但是format字符串只能包含处理小时、分钟和秒的那些格式修饰符。其他修饰符产生一个NULL值或0。 |
curdate() | 以'YYYY-MM-DD'或YYYYMMDD格式返回今天日期值,取决于函数是在一个字符串还是数字上下文被使用。 |
current_date | 以'YYYY-MM-DD'或YYYYMMDD格式返回今天日期值,取决于函数是在一个字符串还是数字上下文被使用。 |
curtime() | 以'HH:MM:SS'或HHMMSS格式返回当前时间值,取决于函数是在一个字符串还是在数字的上下文被使用。 |
current_time | 以'HH:MM:SS'或HHMMSS格式返回当前时间值,取决于函数是在一个字符串还是在数字的上下文被使用。 |
now() | 以'YYYY-MM-DD HH:MM:SS'或YYYYMMDDHHMMSS格式返回当前的日期和时间,取决于函数是在一个字符串还是在数字的上下文被使用。 |
sysdate() | 以'YYYY-MM-DD HH:MM:SS'或YYYYMMDDHHMMSS格式返回当前的日期和时间,取决于函数是在一个字符串还是在数字的上下文被使用。 |
current_timestamp | 以'YYYY-MM-DD HH:MM:SS'或YYYYMMDDHHMMSS格式返回当前的日期和时间,取决于函数是在一个字符串还是在数字的上下文被使用。 |
unix_timestamp(date) | 如果没有参数调用,返回一个Unix时间戳记(从'1970-01-01 00:00:00'GMT开始的秒数)。如果UNIX_TIMESTAMP()用一个date参数被调用,它返回从'1970-01-01 00:00:00' GMT开始的秒数值。date可以是一个DATE字符串、一个DATETIME字符串、一个TIMESTAMP或以YYMMDD或YYYYMMDD格式的本地时间的一个数字。 |
from_unixtime(unix_timestamp) | 以'YYYY-MM-DD HH:MM:SS'或YYYYMMDDHHMMSS格式返回unix_timestamp参数所表示的值,取决于函数是在一个字符串还是或数字上下文中被使用。 |
from_unixtime(unix_timestamp,format) | 返回表示 Unix 时间标记的一个字符串,根据format字符串格式化。format可以包含与DATE_FORMAT()函数列出的条目同样的修饰符。 |
sec_to_time(seconds) | 返回seconds参数,变换成小时、分钟和秒,值以'HH:MM:SS'或HHMMSS格式化,取决于函数是在一个字符串还是在数字上下文中被使用。 |
time_to_sec(time) | 返回time参数,转换成秒 |
%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)
%p AM或PM
%w 一个星期中的天数(0=Sunday ……6=Saturday )
%U 星期(0……52), 这里星期天是星期的第一天
%u 星期(0……52), 这里星期一是星期的第一天
%% 一个文字“%”。
所有的其他字符不做解释被复制到结果中。
范例如下
mysql> select dayofweek('2016-10-26'),weekday('2016-10-26'),dayofmonth('2016-10-26'),dayofyear('2016-10-26'),month('2016-10-26'),dayname('2016-10-26'),monthname('2016-10-26'),quarter('2016-10-26'),week(20161026),year(20161026),hour('11:12:13'),minute(111213),second(111213),period_add(1610,2),period_diff(1610,1612),to_days(20161026),from_days(736628),date_format(now(),'%Y'),date_format(now(),'%T'),curdate()+0,current_date,curtime(),current_time+0,now(),now()+0,sysdate(),sysdate()+0,current_timestamp,current_timestamp+0,unix_timestamp(now()),from_unixtime(unix_timestamp()),from_unixtime(unix_timestamp())+0,sec_to_time(61),sec_to_time(61)+0,time_to_sec('00:01:01')\G;
*************************** 1. row ***************************
dayofweek('2016-10-26'): 4
weekday('2016-10-26'): 2
dayofmonth('2016-10-26'): 26
dayofyear('2016-10-26'): 300
month('2016-10-26'): 10
dayname('2016-10-26'): Wednesday
monthname('2016-10-26'): October
quarter('2016-10-26'): 4
week(20161026): 43
year(20161026): 2016
hour('11:12:13'): 11
minute(111213): 12
second(111213): 13
period_add(1610,2): 201612
period_diff(1610,1612): -2
to_days(20161026): 736628
from_days(736628): 2016-10-26
date_format(now(),'%Y'): 2016
date_format(now(),'%T'): 17:11:06
curdate()+0: 20161026
current_date: 2016-10-26
curtime(): 17:11:06
current_time+0: 171106
now(): 2016-10-26 17:11:06
now()+0: 20161026171106
sysdate(): 2016-10-26 17:11:06
sysdate()+0: 20161026171106
current_timestamp: 2016-10-26 17:11:06
current_timestamp+0: 20161026171106
unix_timestamp(now()): 1477473066
from_unixtime(unix_timestamp()): 2016-10-26 17:11:06
from_unixtime(unix_timestamp())+0: 20161026171106
sec_to_time(61): 00:01:01
sec_to_time(61)+0: 101
time_to_sec('00:01:01'): 61
其他函数
函数 | 功能 |
---|---|
version() | 返回数据库版本 |
database() | 返回当前使用的库 |
user() | 放回当前登陆的用户 |
password(str) | 返回字符串str加密之后的结果 |
md5(str) | 返回字符串md5加密之后的结果 |
inet_aton(ip) | 返回ip地址的十进制值 |
inet_ntoa(x) | 返回x数值转换成ip地址的值 |
encode(str,pswd_str) | 返回使用pswd_str位秘钥加密的str的值 |
encode(str,pswd_str) | 返回使用pswd_str位秘钥解密的str的值 |
例子
mysql> select version(),database(),user(),md5('123456'),inet_aton('192.168.1.62'),inet_ntoa(3232235838),encode('123456','888'),decode(encode('123456','888'),'888')\G;
*************************** 1. row ***************************
version(): 5.6.33
database(): NULL
user(): root@localhost
md5('123456'): e10adc3949ba59abbe56e057f20f883e
inet_aton('192.168.1.62'): 3232235838
inet_ntoa(3232235838): 192.168.1.62
encode('123456','888'): ??? ??
decode(encode('123456','888'),'888'): 123456