Mysql 常用函數集

標籤：utc   asi   lca   nat   xor   date   color   file   span   

 

1.分類導航

一共分為5類函數 
1. 字元型函數 
2. 數值型函數 
3. 日期型函數 
4. 統計型函數 
5. 其它型函數

2.字元型函數[約48個]

ascii(str)bin(n)bit_length(str)char(n,... [using charset])character_length(str)char_length(str)compress(string_to_compress)concat_ws(separator,str1,str2,...)conv(n,from_base,to_base)elt(n,str1,str2,str3,...)export_set(bits,on,off[,separator[,number_of_bits]])field(str,str1,str2,str3,...)find_in_set(str,strlist)hex(n_or_s)insert(str,pos,len,newstr)instr(str,substr)lcase(str)left(str,len)length(str)load_file(file_name)locate(substr,str) , locate(substr,str,pos)lower(str)lpad(str,len,padstr)ltrim(str)make_set(bits,str1,str2,...)mid(str,pos,len)oct(n)octet_length(str)ord(str)position(substr in str)quote(str)repeat(str,count)replace(str,from_str,to_str)reverse(str)right(str,len)rpad(str,len,padstr)rtrim(str)soundex(str)space(n)strcmp(expr1,expr2)substring(str,pos) , substring(str from pos) substring(str,pos,len) , substring(str from pos for len)substring_index(str,delim,count)trim([{both | leading | trailing} [remstr] from] str) trim(remstr from] str)ucase(str)uncompress(string_to_uncompress)uncompressed_length(compressed_string)unhex(str)upper(str)

3.數值型函數[約25個]

abs(x)acos(x)asin(x)atan(x)atan2(y,x)ceiling(x) ceil(x)cos(x)cot(x)crc32(expr)degrees(x)divexp(x)floor(x)log10(x)log2(x)mod(n,m) , n % m n mod mpi()pow(x,y) , power(x,y)radians(x)rand() rand(n)round(x) round(x,d)sign(x)sin(x)sqrt(x)tan(x)

4.日期型函數[約65個]

adddate(date,interval expr type)adddate(expr,days)addtime(expr,expr2)convert_tz(dt,from_tz,to_tz)curdate()current_date()current_time()current_timestamp()curtime()date(expr)datediff(expr,expr2)date_add(date,interval expr type) date_format(date,format)date_add(date,interval expr type) day(date)dayname(date)dayofweek(date)dayofyear(date)extract(type from date)dayofmonth(date)from_days(n)from_unixtime(unix_timestamp)from_unixtime(unix_timestamp,format)get_format(date|time|datetime, ‘eur‘|‘usa‘|‘jis‘|‘iso‘|‘internal‘)hour(time)last_day(date)ln(x)localtime()localtimestamp()log(x) log(b,x)makedate(year,dayofyear)maketime(hour,minute,second)microsecond(expr)minute(time)month(date)monthname(date)now()period_add(p,n)period_diff(p1,p2)quarter(date)second(time)sec_to_time(seconds)str_to_date(str,format)subdate(date,interval expr type) subdate(expr,days)subtime(expr,expr2)sysdate()time(expr)timediff(expr,expr2)timestampadd(interval,int_expr,datetime_expr)timestampdiff(interval,datetime_expr1,datetime_expr2)time_format(time,format)time_to_sec(time)to_days(date)truncate(x,d)unix_timestamp()unix_timestamp(date)utc_date()utc_time()utc_timestamp()week(date[,mode])weekday(date)weekofyear(date)year(date)yearweek(date)yearweek(date,start)

5.統計型函數[約14個]

avg([distinct] expr)bit_and(expr)bit_or(expr)bit_xor(expr)count(distinct expr,[expr...])group_concat(expr)min([distinct] expr), max([distinct] expr)std(expr) stddev(expr)stddev_pop(expr)stddev_samp(expr)sum([distinct] expr)variance(expr)var_pop(expr)var_samp(expr)

6.其它型函數[約44個]

aes_encrypt(str,key_str) , aes_decrypt(crypt_str,key_str)benchmark(count,expr)binarybit_count(n)cast(expr as type), convert(expr,type) , convert(expr using transcoding_name)collation(str)connection_id()current_user()database()decode(crypt_str,pass_str)default(col_name)des_decrypt(crypt_str[,key_str])des_encrypt(str[,(key_num|key_str)])encode(str,pass_str)encrypt(str[,salt])format(x,d)found_rows()get_lock(str,timeout)if(expr1,expr2,expr3)ifnull(expr1,expr2)inet_aton(expr)inet_ntoa(expr)isnull(expr)is_free_lock(str)is_used_lock(str)master_pos_wait(log_name,log_pos[,timeout])md5(str)name_const(name,value)nullif(expr1,expr2)old_password(str)password(str)release_lock(str)row_count()schema()session_user()sha1(str) sha(str)sleep(duration)system_user()timestamp(expr) timestamp(expr,expr2)user()uuid()values(col_name)version()

 

 

 

 

 

轉：79033856

參考：78710482

 

Mysql 常用函數集