Mysql 常用函数集

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)

div

exp(x)

floor(x)

log10(x)

log2(x)

mod(n,m) , n % m n mod m

pi()

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)

binary

bit_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()