MySql常用函數大全講解

林炳文Evankaka原創作品。轉載請注明出處http://blog.csdn.net/evankaka

MySQL數據庫中提供了很豐富的函數。MySQL函數包括數學函數、字符串函數、日期和時間函數、條件判斷函數、系統信息函數、加密函數、格式化函數等。通過這些函數，可以簡化用戶的操作。例如，字符串連接函數可以很方便的將多個字符串連接在一起。在這一講中將講解的內容包括：
數學函數
字符串函數
日期和時間函數
條件判斷函數
系統信息函數
加密函數
格式化函數

MySQL函數是MySQL數據庫提供的內部函數。這些內部函數可以幫助用戶更加方便的處理表中的數據。本小節中將簡單介紹MySQL中包含哪幾類函數，以及這幾類函數的的使用范圍和作用。MySQL函數包括數學函數、字符串函數、日期和時間函數、條件判斷函數、系統信息函數、加密函數等。SELECT語句及其條件表達式都可以使用這些函數。同時，INSERT 、UPDATE、DELECT語句及其條件表達式也可以使用這些函數。例如，表中的某個數據是負數，現在需要將這個數據顯示為正數。這就可以使用絕對值函數。從上面可以知道，MySQL函數可以對表中數據進行相應的處理，以便得到用戶希望得到的數據。這些函數可以使MySQL數據庫的功能更加強大。

一、數學函數

數學函數是MySQL中常用的一類函數。主要用於處理數字，包括整型、浮點數等。數學函數包括絕對值函數、正弦函數、余弦函數、獲取隨機數的函數等。

ABS(X):返回X的絕對值
select ABS(-32);

 

MOD(N,M)或%:返回N被M除的余數。
select MOD(15,7);
select 15 % 7;

 

 

FLOOR(X):返回不大於X的最大整數值。
select FLOOR(1.23);
select FLOOR(-1.23);

 

 

CEILING(X):返回不小於X的最小整數值。
select CEILING(1.23);
select CEILING(-1.23);

 

 

ROUND(X) :返回參數X的四捨五入的一個整數。
select ROUND(1.58);
select ROUND(-1.58);

 

二、字符串函數

ASCII(str):返回字符串str的最左面字符的ASCII代碼值。如果str是空字符串，返回0。如果str是NULL，返回NULL。
select ASCII('2');
select ASCII(2);
select ASCII('dx')

CONCAT(str1,str2,...):返回來自於參數連結的字符串。如果任何參數是NULL，返回NULL。可以有超過2個的參數。一個數字參數被變換為等價的字符串形式。
select CONCAT('My', 'S', 'QL');
select CONCAT('My', NULL, 'QL');
select CONCAT(14.3);

 

LENGTH(str):返回字符串str的長度。
select LENGTH('text');

 

LOCATE(substr,str):返回子串substr在字符串str第一個出現的位置，如果substr不是在str裡面，返回0.
select LOCATE('bar', 'foobarbar');
select LOCATE('xbar', 'foobar');

 

INSTR(str,substr):返回子串substr在字符串str中的第一個出現的位置。
select INSTR('foobarbar', 'bar');
select INSTR('xbar', 'foobar');

 

 

 

LEFT(str,len):返回字符串str的最左面len個字符。
select LEFT('foobarbar', 5);

 

RIGHT(str,len):返回字符串str的最右面len個字符。
select RIGHT('foobarbar', 4);

 

SUBSTRING(str,pos):從字符串str的起始位置pos返回一個子串。
select SUBSTRING('Quadratically',5);

 

TRIM(str):返回字符串str，所有前綴或後綴被刪除了。
select TRIM(' bar ');

 

LTRIM(str):返回刪除了其前置空格字符的字符串str。
select LTRIM(' barbar');

RTRIM(str):返回刪除了其拖後空格字符的字符串str。
select RTRIM(‘barbar ’);

REPLACE(str,from_str,to_str):返回字符串str，其字符串from_str的所有出現由字符串to_str代替。

select REPLACE('www.mysql.com', 'w', 'Ww');

 

REPEAT(str,count):返回由重復countTimes次的字符串str組成的一個字符串。如果count <= 0，返回一個空字符串。如果str或count是NULL，返回NULL。
select REPEAT('MySQL', 3);

 

REVERSE(str):返回顛倒字符順序的字符串str。
select REVERSE('abc');

 

INSERT(str,pos,len,newstr):返回字符串str，在位置pos起始的子串且len個字符長的子串由字符串newstr代替。
select INSERT(‘whatareyou', 5, 3, ‘is');

 

 

三、日期和時間函數

 

DAYOFWEEK(date):返回日期date的星期索引(1=星期天，2=星期一, …7=星期六)。
select DAYOFWEEK('1998-02-03');
select DAYOFWEEK(now());

 

WEEKDAY(date):返回date的星期索引(0=星期一，1=星期二, ……6= 星期天)。
select WEEKDAY('1997-11-05');

 

 

DAYOFMONTH(date):返回date的月份中的日期，在1到31范圍內。
select DAYOFMONTH('1998-02-03');

 

 

DAYOFYEAR(date):返回date在一年中的日數, 在1到366范圍內。
select DAYOFYEAR('1998-02-03');

 

 

MONTH(date):返回date的月份，范圍1到12。
select MONTH('1998-02-03');

 

 

DAYNAME(date):返回date的星期名字。
select DAYNAME("1998-02-05");

 

 

MONTHNAME(date) :返回date的月份名字。
select MONTHNAME("1998-02-05");

 

QUARTER(date):返回date一年中的季度，范圍1到4。
select QUARTER('98-04-01');

WEEK(date,first):對於星期天是一周的第一天的地方，有一個單個參數，返回date的周數，范圍在0到52。2個參數形式WEEK()允許你指定星期是否開始於星期天或星期一。如果第二個參數是0，星期從星期天開始，如果第二個參數是1，從星期一開始。
select WEEK('2009-02-20');
select WEEK('2009-02-20',0);
select WEEK('2009-02-20',1);

YEAR(date):返回date的年份，范圍在1000到9999。
select YEAR('98-02-03');

 

HOUR(time):返回time的小時，范圍是0到23。
select HOUR('10:05:03');

 

 

MINUTE(time):返回time的分鐘，范圍是0到59。
select MINUTE('98-02-03 10:05:03');

 

 

SECOND(time):回來time的秒數，范圍是0到59。
select SECOND('10:05:03');

 

 

DATE_ADD(date,INTERVAL expr type) ,進行日期增加的操作，可以精確到秒
DATE_SUB(date,INTERVAL expr type) ，進行日期減少的操作，可以精確到秒

 

SELECT "1997-12-31 23:59:59" + INTERVAL 1 SECOND;

 

SELECT INTERVAL 1 DAY + "1997-12-31";
SELECT "1998-01-01" - INTERVAL 1 SECOND;
SELECT DATE_ADD("1997-12-31 23:59:59", INTERVAL 1 SECOND);
SELECT DATE_ADD("1997-12-31 23:59:59", INTERVAL "1:1" MINUTE_SECOND);

 

 

SELECT DATE_SUB("1998-01-01 00:00:00", INTERVAL "1 1:1:1" DAY_SECOND);
SELECT DATE_SUB("1998-01-02", INTERVAL 31 DAY);

 

 

CURRENT_DATE:以‘YYYY-MM-DD’或YYYYMMDD格式返回今天日期值，取決於函數在一個字符串還是數字上下文被使用。
select CURDATE();

 

 

CURRENT_TIME:以‘HH:MM:SS’或HHMMSS格式返回當前時間值
select CURTIME();

 

 

NOW():以‘YYYY-MM-DD HH:MM:SS’或YYYYMMDDHHMMSS格式返回當前的日期和時間
select NOW();

 

 

四、控制流程函數

CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE result] END CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END
在第一個方案的返回結果中， value=compare-value。而第二個方案的返回結果是第一種情況的真實結果。如果沒有匹配的結果值，則返回結果為ELSE後的結果，如果沒有ELSE 部分，則返回值為 NULL。
SELECT CASE 11 WHEN 1 THEN 'one'
WHEN 2 THEN 'two' ELSE 'more' END;
SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;

 

 

SELECT CASE BINARY 'B'
WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;

 

IF(expr1,expr2,expr3)
如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL)，則 IF()的返回值為expr2; 否則返回值則為 expr3。IF() 的返回值為數字值或字符串值，具體情況視其所在語境而定。
SELECT IF(1>2,2,3);
SELECT IF(1<2,'yes ','no');
SELECT IF(STRCMP('test','test1'),'no','yes');

 

Strcmp(str1,str2):如果str1>str2返回1,str1=str2反回0，str1

 

STRCMP(expr1,expr2)

如果字符串相同，STRCMP()返回0，如果第一參數根據當前的排序次序小於第二個，返回-1，否則返回1。
select STRCMP('text', 'text2');
select STRCMP('text2', 'text');
select STRCMP('text', 'text');

五、系統信息函數

系統信息函數用來查詢MySQL數據庫的系統信息。例如，查詢數據庫的版本，查詢數據庫的當前用戶等。本小節將詳細講解系統信息函數的作用和使用方法。

獲取MySQL版本號、連接數、數據庫名的函數

VERSION()函數返回數據庫的版本號；

 

CONNECTION_ID()函數返回服務器的連接數，也就是到現在為止MySQL服務的連接次數；

DATABASE()和SCHEMA()返回當前數據庫名。

獲取用戶名的函數

USER()、SYSTEM_USER()、SESSION_USER()、CURRENT_USER()和CURRENT_USER這幾個函數可以返回當前用戶的名稱。

獲取字符串的字符集和排序方式的函數

CHARSET(str)函數返回字符串str的字符集，一般情況這個字符集就是系統的默認字符集；COLLATION(str)函數返回字符串str的字符排列方式。

獲取最後一個自動生成的ID值的函數
LAST_INSERT_ID()函數返回最後生成的AUTO_INCREMENT值。

六、加密函數

加密函數是MySQL中用來對數據進行加密的函數。因為數據庫中有些很敏感的信息不希望被其他人看到，就應該通過加密方式來使這些數據變成看似亂碼的數據。例如用戶的密碼，就應該經過加密。本小節將詳細講解加密函數的作用和使用方法。
下面是各種加密函數的名稱、作用和使用方法。

加密函數PASSWORD(str)
PASSWORD(str)函數可以對字符串str進行加密。一般情況下，PASSWORD(str)函數主要是用來給用戶的密碼加密的。下面使用PASSWORD(str)函數為字符串“abcd”加密。

加密函數MD5(str)

 

MD5(str)函數可以對字符串str進行加密。MD5(str)函數主要對普通的數據進行加密。下面使用MD5(str)函數為字符串“abcd”加密。

加密函數ENCODE(str,pswd_str)
ENCODE(str,pswd_str)函數可以使用字符串pswd_str來加密字符串str。加密的結果是一個二進制數，必須使用BLOB類型的字段來保存它。
解密函數
DECODE(crypt_str,pswd_str)函數可以使用字符串pswd_str來為crypt_str解密。crypt_str是通過ENCODE(str,pswd_str)加密後的二進制數據。字符串pswd_str應該與加密時的字符串pswd_str是相同的。下面使用DECODE(crypt_str,pswd_str)為ENCODE(str,pswd_str)加密的數據解密。

 

七、其它函數

格式化函數FORMAT(x,n)

FORMAT(x,n)函數可以將數字x進行格式化，將x保留到小數點後n位。這個過程需要進行四捨五入。例如FORMAT(2.356,2)返回的結果將會是2.36；FORMAT(2.353,2)返回的結果將會是2.35。下面使用FORMAT(x,n)函數來講235.3456和235.3454進行格式化，都保留到小數點後3位。

不同進制的數字進行轉換的函數

ASCII(s)返回字符串s的第一個字符的ASCII碼；BIN(x)返回x的二進制編碼；HEX(x)返回x的十六進制編碼；OCT(x)返回x的八進制編碼；CONV(x,f1,f2)將x從f1進制數變成f2進制數。

 

和下面的
IP地址與數字相互轉換的函數

 

INET_ATON(IP)函數可以將IP地址轉換為數字表示；INET_NTOA(n)函數可以將數字n轉換成IP的形式。其中，INET_ATON(IP)函數中IP值需要加上引號。這兩個函數互為反函數。

加鎖函數和解鎖函數

GET_LOCT(name,time)函數定義一個名稱為nam、持續時間長度為time秒的鎖。如果鎖定成功，返回1；如果嘗試超時，返回0；如果遇到錯誤，返回NULL。RELEASE_LOCK(name)函數解除名稱為name的鎖。如果解鎖成功，返回1；如果嘗試超時，返回0；如果解鎖失敗，返回NULL；IS_FREE_LOCK(name)函數判斷是否使用名為name的鎖。如果使用，返回0；否則，返回1。
重復執行指定操作的函數

BENCHMARK(count,expr)函數將表達式expr重復執行count次，然後返回執行時間。該函數可以用來判斷MySQL處理表達式的速度。

改變字符集的函數

CONVERT(s USING cs)函數將字符串s的字符集變成cs

CAST(x AS type)和CONVERT(x,type)這兩個函數將x變成type類型。這兩個函數只對BINARY、CHAR、DATE、DATETIME、TIME、SIGNED INTEGER、UNSIGNED INTEGER這些類型起作用。但兩種方法只是改變了輸出值的數據類型，並沒有改變表中字段的類型。