小白學習mysql之函數

標籤：

## 導語曾經我以為，學會了select、update、insert和delete之後，我就學會了資料庫~，要不是到公司看到SQL裡充滿了密密麻麻的的各種函數，我差點就信了~，當初的自己是多麼的天真,o(╯□╰)o現在的資料庫功能之強大，使得很多的商務邏輯轉移到了資料庫上，更多的事務和預存程序出現在系統中，因此，拼接字串成為了當代程式員必備的技能，要想練就拼接千百行的SQL，語句，你不能不熟練掌握以下函數.### CONCAT()可謂是，拼串圈函數的一哥，有了他，便可以實現兩個乃至多個語句的拼接。```mysql> select concat(‘ma yun‘, ‘ is cloud lao wang‘,‘!‘);+--------------------------------------------+| concat(‘ma yun‘, ‘ is cloud lao wang‘,‘!‘) |+--------------------------------------------+| ma yun is cloud lao wang!                  |+--------------------------------------------+```

特別注意，每個語句的串連處是否需要空格，而且如果裡面的每個語句都為空白的話，結果為null。

### CONCAT_WS() 和一樓是兄弟，使用方式CONCAT_WS(separator,str1,str2,...)，其中第一個為分隔字元，為後面的每兩個字串之間自動添加該分隔字元。有很多時候拼字的sql語句條件非常多，就常用該函數，比如where後面要加5個 and 條件句，這個時候它便顯身手了。```mysql> select concat_ws(‘ and ‘,‘c=5‘,IF(1>2,‘d=3‘,null),‘b=2‘);+---------------------------------------------------+| concat_ws(‘ and ‘,‘c=5‘,IF(1>2,‘d=3‘,null),‘b=2‘) |+---------------------------------------------------+| c=5 and b=2                                       |+---------------------------------------------------+```

技巧&注意點：第一個分隔字元若為and之類的關鍵字，注意倆邊要有空格。注意這個函數不能跳過空的字串，只能跳過null，所以如果裡面的字串項中若有判斷語句，一定不要把字串賦值為空白。

### ELT   *ELT(N,str1,str2,str3,...)*本函數是field函數的補充函數，根據N的值選擇第N個字串，比如性別在資料表中一般存為數字，如1代表男，2代表女，當我們需要根據性別代碼顯示性別的時候就可以使用ELT函數。```mysql> select elt(1,‘男‘,‘女‘);+------------------+| elt(1,‘男‘,‘女‘) |+------------------+| 男               |+------------------+```本函數也可以用作sql語句的擴充，比如可以根據傳入的參數進行選擇排序方式等，這樣就大大增強了一個sql語句的處理能力，類似程式設計語言中的case或者if語句等。

### IF   *IF(expr1,expr2,expr3)*IF函數大概很多人會覺得太過於簡單，即時確實如此，然而越是簡單的東西越容易忽視一些細節而犯錯，前段時間就發現了一個用IF所導致的bug。大致情況如下：```mysql> select if(‘hello‘,‘1‘,‘2‘);+---------------------+| if(‘hello‘,‘1‘,‘2‘) |+---------------------+| 2                   |+---------------------+```當然事實上第一個參數大部分請情況是一個變數（假設為V_STR），很多時候寫著寫著便可能會用到上面的語句來判斷V_STR是否為空白，這完全是來自對簡單問題的忽略以及有可能和IFNULL傻傻分不清楚。同時這裡的確和程式設計語言中的IF ELSE是不一樣的，上面的情況如果出現在程式設計語言中，則會返回‘1‘;

### FIND_IN_SET 
  原型 FIND_IN_SET(str,strlist) 在資料庫和資料檔案進行互動時，往往存在編碼和實際資料的轉化，例如現在需要向一張學生表中匯入一批學生資料，在性別這一列，excel中顯示的為“男，女”，而資料庫中儲存的則是’1，2‘,現在需要把實際資料轉化為編碼資料就可以使用該函數。```mysql> select find_in_set(‘男‘,‘男,女‘);+---------------------------+| find_in_set(‘男‘,‘男,女‘) |+---------------------------+|                         1 |+---------------------------+```函數返回，第一個參數在第二個參數中出現的下標位置（從1開始）。如果不存在返回0；當然例子舉得比較簡單，實際運用可能用於字串中有很多種類別時，比如一個公司的職位等。另外，strlist是以‘,‘號隔開的，所以謹慎考慮第一個參數中包含有‘,‘號，並檢查函數能湊效。

### LOCATE LOCATE，顧名思義是一個定位的函數，該函數返回第一個參數在第二個參數中的位置，比較簡單。但在資料庫中，LOCATE似乎承擔了更多的責任，更多的時候，它總在’試探‘某些條件是否成立，根據試探結果進行sql語句的分類拼接。```mysql> select if(locate(‘,‘,‘1,2,3‘),‘你們好‘,‘你好‘);+-----------------------------------------+| if(locate(‘,‘,‘1,2,3‘),‘你們好‘,‘你好‘) |+-----------------------------------------+| 你們好                                  |+-----------------------------------------+```上面的例子中假設’1,2,3‘是一個學生id,所組成的字串，那麼根據locate就能判斷是否是一個學生還是多個學生，同時locate還能根據where條件句後是否包含某個關聯字表的欄位進行選擇性關聯該表。locate給sql語句的拼接帶來了很多的便捷性。

### QUOTE最後一個但絕對是最重要的一個，有關安全帶問題永遠都是最重要的，當程式中有使用者輸入資料的話，一定要用本函數來進行處理，防止基本的sql注入問題。```mysql> select concat(‘where id = ‘,‘5411 or 1=1‘),concat(‘where id = ‘,quote(‘5411 or 1=1‘));+-------------------------------------+--------------------------------------------+| concat(‘where id = ‘,‘5411 or 1=1‘) | concat(‘where id = ‘,quote(‘5411 or 1=1‘)) |+-------------------------------------+--------------------------------------------+| where id = 5411 or 1=1              | where id = ‘5411 or 1=1‘                   |+-------------------------------------+--------------------------------------------+```上例那最基本的sql注入作為示範，如果在一個軟體的使用者介面是輸入學生的ID，這樣使用者輸入’5411 or 1=1‘的時候，全部學生的資訊就出來啦！這時如果經過quote函數進行處理，那麼就會什麼也搜不到，保證了資料的安全，同時quote也可以正確的處理轉義問題。

## 總結 mysql資料庫提供的這些功能強大的函數，雖然沒有程式設計語言函數那般強大，但只要動動腦筋，還是基本上可以滿足幾乎所有你想實現的。而且，講這些函數和基本的業務處理結合起來，就會發現每一個函數的強大。

小白學習mysql之函數