mysql中的內置函數

mysql內置函數列表可以從mysql官方文檔查詢，這里僅分類簡單介紹一些可能會用到的函數。

1 數學函數

abs(x)
pi()
mod(x,y)
sqrt(x)
ceil(x)或者ceiling(x)
rand(),rand(N):返回0-1間的浮點數，使用不同的seed N可以獲得不同的隨機數
round(x, D)：四舍五入保留D位小數，D默認為0， 可以為負數， 如round(19, -1)返回20
truncate(x, D):截斷至保留D位小數，D可以為負數， 如trancate(19,-1)返回10
sign(x): 返回x的符號，正負零分別返回1， -1， 0
pow(x,y)或者power(x,y)
exp(x)：e^x
log(x)：自然對數
log10(x)：以10為底的對數
radians(x):角度換弧度
degrees(x):弧度換角度
sin(x)和asin(x):
cos(x)和acos(x):
tan(x)和atan(x):
cot(x):

2 字符串函數

char_length(str):返回str所包含的字符數，一個多字節字符算一個字符
length(str): 返回字符串的字節長度，如utf8中，一個漢字3字節，數字和字母算一個字節
concat(s1, s1, ...): 返回連接參數產生的字符串
concat_ws(x, s1, s2, ...): 使用連接符x連接其他參數產生的字符串
INSERT(str,pos,len,newstr):返回str,其起始于pos，長度為len的子串被newstr取代。
1. 若pos不在str范圍內，則返回原字符串str
2. 若str中從pos開始的子串不足len,則將從pos開始的剩余字符用newstr取代
3. 計算pos時從1開始，若pos=3,則從第3個字符開始替換
lower（str)或者lcase(str):
upper(str)或者ucase(str):
left(s,n):返回字符串s最左邊n個字符
right(s,n): 返回字符串最右邊n個字符
lpad(s1, len, s2): 用s2在s1左邊填充至長度為len, 若s1的長度大于len,則截斷字符串s1至長度len返回
rpad(s1, len, s2):
ltrim(s):刪除s左側空格字符
rtrim(s):
TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)或TRIM([remstr FROM] str)：從str中刪除remstr, remstr默認為空白字符
REPEAT(str,count)：返回str重復count次得到的新字符串
REPLACE(str,from_str,to_str)： 將str中的from_str全部替換成to_str
SPACE(N):返回長度為N的空白字符串
STRCMP(str1,str2):若str1和str2相同，返回0， 若str1小于str2, 返回-1， 否則返回1.
SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len), SUBSTRING(str FROM pos FOR len),MID(str,pos,len): 獲取特定位置，特定長度的子字符串
LOCATE(substr,str), LOCATE(substr,str,pos),INSTR(str,substr),POSITION(substr IN str): 返回字符串中特定子串的位置，注意這里INSTR與其他函數的參數位置是相反的
REVERSE(str)
ELT(N,str1,str2,str3,...)：返回參數strN, 若N大于str參數個數，則返回NULL
FIELD(str,str1,str2,str3,...): 返回str在后面的str列表中第一次出現的位置，若找不到str或者str為NULL, 則返回0
FIND_IN_SET(str,strlist)：strlist是由','分隔的字符串，若str不在strlist或者strlist為空字符串，則返回0；若任意一個參數為NULL則返回ＮＵＬＬ
MAKE_SET(bits,str1,str2,...): 由bits的作為位圖來選取strN參數，選中的參數用','連接后返回

3 日期和時間函數

CURDATE(), CURRENT_DATE, CURRENT_DATE():用于獲取當前日期，格式為'YYYY-MM-DD'; 若+0則返回YYYYMMDD
UTC_DATE, UTC_DATE():返回當前世界標準時間
CURTIME([fsp]), CURRENT_TIME, CURRENT_TIME([fsp]): 用于獲取當前時間， 格式為'HH:MM:SS' 若+0則返回 HHMMSS
UTC_TIME, UTC_TIME([fsp])
CURRENT_TIMESTAMP, CURRENT_TIMESTAMP([fsp]), LOCALTIME, LOCALTIME([fsp]), SYSDATE([fsp]), NOW([fsp]): 用于獲取當前的時間日期，格式為'YYYY-MM-DD HH:MM:SS'，若+0則返回YYYYMMDDHHMMSS
UTC_TIMESTAMP, UTC_TIMESTAMP([fsp])
UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date)：返回一個unix時間戳（'1970-01-01 00:00:00' UTC至今或者date的秒數），這實際上是從字符串到整數的一個轉化過程
FROM_UNIXTIME(unix_timestamp), FROM_UNIXTIME(unix_timestamp,format)：從時間戳返回'YYYY-MM-DD HH:MM:SS' 或者YYYYMMDDHHMMSS，加入format后根據所需的format顯示。
MONTH(date)
MONTHNAME(date)
DAYNAME(date)
DAY(date)，DAYOFMONTH(date)：1-31或者0
DAYOFWEEK(date)：1-7==>星期天-星期六
DAYOFYEAR(date)： 1-365（366）
WEEK(date[,mode])：判斷是一年的第幾周，如果1-1所在周在新的一年多于4天，則將其定為第一周；否則將其定為上一年的最后一周。mode是用來人為定義一周從星期幾開始。
WEEKOFYEAR(date)：類似week(date,3)，從周一開始計算一周。
QUARTER(date)：返回1-4
HOUR(time)：返回時間中的小時數，可以大于24
MINUTE(time)：
SECOND(time)：
EXTRACT(unit FROM date)：提取日期時間中的要素

    SELECT EXTRACT(YEAR FROM '2009-07-02'); ##2009SELECT EXTRACT(YEAR_MONTH FROM '2009-07-02 01:02:03');##200907SELECT EXTRACT(DAY_MINUTE FROM '2009-07-02 01:02:03');##20102SELECT EXTRACT(MICROSECOND FROM '2003-01-02 10:30:00.000123');##123

TIME_TO_SEC(time)
SEC_TO_TIME(seconds)

TO_DAYS(date): 從第0年開始的天數
TO_SECNDS(expr)：從第0年開始的秒數

ADDDATE(date,INTERVAL expr unit), ADDDATE(expr,days),DATE_ADD(date,INTERVAL expr unit)
DATE_SUB(date,INTERVAL expr unit), DATE_SUB(date,INTERVAL expr unit)
ADDTIME(expr1,expr2)
SUBTIME(expr1,expr2)

    SELECT ADDTIME('2007-12-31 23:59:59.999999', '1 1:1:1.000002');##'2008-01-02 01:01:01.000001'SELECT ADDTIME('01:00:00.999999', '02:00:00.999998');##'03:00:01.999997'

注意：時間日期的加減也可以直接用+/-來進行

    date + INTERVAL expr unitdate - INTERVAL expr unit如：SELECT '2008-12-31 23:59:59' + INTERVAL 1 SECOND;##'2009-01-01 00:00:00'SELECT INTERVAL 1 DAY + '2008-12-31';##'2009-01-01'SELECT '2005-01-01' - INTERVAL 1 SECOND;##'2004-12-31 23:59:59'

DATE_FORMAT(date,format):
DATEDIFF(expr1,expr2):返回相差的天數
TIMEDIFF(expr1,expr2)：返回相隔的時間

4 條件判斷函數

IF(expr1,expr2,expr3)：如果expr1不為0或者NULL,則返回expr2的值，否則返回expr3的值
IFNULL(expr1,expr2)：如果expr1不為NULL,返回expr1,否則返回expr2
NULLIF(expr1,expr2): 如果expr1=expr2則返回NULL, 否則返回expr2
CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END
當compare_value=value時返回result
CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END
當condition為TRUE時返回result

    SELECT CASE 1 WHEN 1 THEN 'one'WHEN 2 THEN 'two' ELSE 'more' END;##'one'SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;##'true'SELECT CASE BINARY 'B'WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;##NULL

5 系統信息函數

VERSION():返回mysql服務器的版本，是utf8編碼的字符串
CONNECTION_ID()：顯示連接號（連接的線程號）
DATABASE()，SCHEMA()：顯示當前使用的數據庫
SESSION_USER(), SYSTEM_USER(), USER(), CURRENT_USER, CURRENT_USER():返回當前的用戶名@主機，utf8編碼字符串
CHARSET(str)
COLLATION(str)
LAST_INSERT_ID()：自動返回最后一個insert或者update查詢， 為auto_increment列設置的第一個發生的值

6 加密和壓縮函數

PASSWORD(str):這個函數的輸出與變量old_password有關。old_password 在mysql5.6中默認為0。 不同取值的效果如下表

old_password=1時， password(str)的效果與old_password(str)相同，由于其不夠安全已經棄用（5.6.5以后）。
old_password=2時，在生成哈希密碼時會隨機加鹽。

MD5(str):計算MD5 128位校驗和，返回32位16進制數構成的字符串，當str為NULL時返回NULL。可以用作哈希密碼
SHA1(str), SHA(str)：計算160位校驗和，返回40位16進制數構成的字符串，當str為NULL時返回NULL。
SHA2(str, hash_length)：計算SHA-2系列的哈希方法(SHA-224, SHA-256, SHA-384, and SHA-512). 第一個參數為待校驗字符串，第二個參數為結果的位數（224， 256， 384， 512）
ENCRYPT(str[,salt]): 用unix crypt()來加密str. salt至少要有兩位字符，否則會返回NULL。若未指定salt參數，則會隨機添加salt。

ECODE(crypt_str,pass_str):解密crypt_str, pass_str用作密碼
ENCODE(str,pass_str)：用pass_str作為密碼加密str

DES_ENCRYPT(str[,{key_num|key_str}])：用Triple-DES算法編碼str， 這個函數只有在mysql配置成支持ssl時才可用。
DES_DECRYPT(crypt_str[,key_str])

AES_ENCRYPT(str,key_str[,init_vector])
AES_DECRYPT(crypt_str,key_str[,init_vector])

COMPRESS(string_to_compress)：返回二進制碼
UNCOMPRESS(string_to_uncompress)

7 聚合函數

若在沒使用group by時使用聚合函數，相當于把所有的行都歸于一組來進行處理。除非特殊說明，一般聚合函數會忽略掉NULL.
AVG([DISTINCT] expr): 返回expr的平均值，distinct選項用于忽略重復值
COUNT([DISTINCT] expr)：返回select中expr的非0值個數，返回值為bigint類型
group_concat:連接組內的非空值，若無非空值，則返回NULL

        GROUP_CONCAT([DISTINCT] expr [,expr ...][ORDER BY {unsigned_integer | col_name | expr}[ASC | DESC] [,col_name ...]][SEPARATOR str_val])

MAX([DISTINCT] expr)
MIN([DISTINCT] expr)

SUM([DISTINCT] expr)
VAR_POP(expr)
VARIANCE(expr)：同VAR_POP(expr)，但是這是標準sql的一個擴展函數
VAR_SAMP(expr)
STD(expr): 這是標準sql的一個擴展函數
STDDEV(expr)：這個函數是為了跟oracle兼容而設置的
STDDEV_POP(expr)：這個是sql標準函數

STDDEV_SAMP(expr)：樣本標準差

8 格式或類型轉化函數

FORMAT(X,D[,locale])：將數字X轉化成'#,###,###.##'格式，D為保留的小數位數
CONV(N,from_base,to_base)：改變數字N的進制，返回值為該進制下的數字構成的字符串
INET_ATON(expr)：ip字符串轉數字
INET_NTOA(expr)：數字轉ip字符串
CAST(expr AS type)：轉換數據類型
CONVERT(expr,type), CONVERT(expr USING transcoding_name)： type可以為BINARY[(N)]，CHAR[(N)]，DATE，DATETIME， DECIMAL[(M[,D])]，DECIMAL[(M[,D])]，TIME，UNSIGNED [INTEGER]等等。transcoding_name如utf8等等