MySQL – 常用函數

測試環境為 CentOS 7 x86_64 虛擬機.

先建立一個測試用資料庫 (testdb) , 與 tables (employee) ,下面範例會使用到 . 格式為 K1 int(11) – auto_increment & PRIMARY KEY , Name char(20), Dept char(20), jobTitle char(20) ,email char(30) 以及 Salary int(11).

SELECT 查詢需注意資料庫字元編碼 Character Sets 與 文字排序 Collations ,請參考資料庫編碼 – http://benjr.tw/102156 .

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 10.3.11-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CREATE DATABASE testdb DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> USE testdb;
Database changed
MariaDB [testdb]> CREATE TABLE employee (K1 int(11) NOT NULL auto_increment, Name char(20) NOT NULL, Dept char(20) NOT NULL, JobTitle char(20) NOT NULL, Salary int(11) NOT NULL , email char(30) NOT NULL, PRIMARY KEY (K1)) ;
Query OK, 0 rows affected (0.010 sec)
 
MariaDB [testdb]> DESCRIBE employee; 
+----------+----------+------+-----+---------+----------------+
| Field    | Type     | Null | Key | Default | Extra          |
+----------+----------+------+-----+---------+----------------+
| K1       | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name     | char(20) | NO   |     | NULL    |                |
| Dept     | char(20) | NO   |     | NULL    |                |
| JobTitle | char(20) | NO   |     | NULL    |                |
| Salary   | int(11)  | NO   |     | NULL    |                |
| email    | char(30) | NO   |     | NULL    |                |
+----------+----------+------+-----+---------+----------------+
6 rows in set (0.004 sec)

透過 INSERT 新增加了五筆資料.

MariaDB [testdb]> INSERT INTO employee (Name , Dept , JobTitle , Salary , email) VALUES ('Ben' , 'Testing' , 'Engineer' , '45000' , 'ben@benjr.tw') , ('Afa' , 'Power' , 'Engineer' , '48000' , 'Afa@benjr.tw') , ('Boss' ,'Testing' , 'Manager' , '75000' , 'Boss@benjr.tw') , ('JoJo' , 'Testing' , 'Engineer' , '45000' , 'jojo@benjr.tw') ,  ('Ian' , 'PM' , 'Engineer' , '85000' , 'ian@benjr.tw');
Query OK, 5 rows affected (0.002 sec)
Records: 5  Duplicates: 0  Warnings: 0
 
MariaDB [testdb]> SELECT * FROM employee;
+----+------+---------+----------+--------+---------------+
| K1 | Name | Dept    | JobTitle | Salary | email         |
+----+------+---------+----------+--------+---------------+
|  1 | Ben  | Testing | Engineer |  45000 | ben@benjr.tw  |
|  2 | Afa  | Power   | Engineer |  48000 | Afa@benjr.tw  |
|  3 | Boss | Testing | Manager  |  75000 | Boss@benjr.tw |
|  4 | JoJo | Testing | Engineer |  45000 | jojo@benjr.tw |
|  5 | Ian  | PM      | Engineer |  85000 | ian@benjr.tw  |
+----+------+---------+----------+--------+---------------+
5 rows in set (0.001 sec)

字串處理函數

  • CONCAT
    CONTACT 函數可以將兩個或更多個字串連接在一起.

    CONCAT(expression1, expression2, expression3,...)
    
    MariaDB [(none)]> SELECT CONCAT("APPLE", ", Banana", ", Cherry") AS Fruit; 
    +-----------------------+
    | Fruit                 |
    +-----------------------+
    | APPLE, Banana, Cherry |
    +-----------------------+
    1 row in set (0.00 sec)
    
  • CONCAT_WS
    相較於 CONTACT 函數 CONCAT_WS 可以加上固定的分隔字元.

    CONCAT_WS(separator, expression1, expression2, expression3,...)
    
    MariaDB [(none)]> SELECT CONCAT_WS(", ", "APPLE", "Banana", "Cherry") AS Fruit; 
    +-----------------------+
    | Fruit                 |
    +-----------------------+
    | APPLE, Banana, Cherry |
    +-----------------------+
    1 row in set (0.01 sec)
    
  • GROUP_CONCAT
    通常搭配 GROUP BY 使用,透過 GROUP_CONCAT 函數,可以把同 Group 的欄位文字整合在一起.

    GROUP_CONCAT([DISTINCT] expr [,expr ...]
                 [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
                 [SEPARATOR str_val])
    
    MariaDB [testdb]> SELECT Dept, GROUP_CONCAT(Name SEPARATOR ',') AS "Member Name" FROM employee GROUP BY Dept;
    +---------+---------------+
    | Dept    | Member Name   |
    +---------+---------------+
    | PM      | Ian           |
    | Power   | Afa           |
    | Testing | Ben,Boss,JoJo |
    +---------+---------------+
    3 rows in set (0.001 sec)
    

    如果 GROUPBY 的資料要按照順序,可以加 ORDER BY DESC 或是 ORDER BY ASC

    MariaDB [testdb]> SELECT Dept, GROUP_CONCAT(Name ORDER BY Name DESC SEPARATOR ',') AS "Member Name" FROM employee GROUP BY Dept;
    +---------+---------------+
    | Dept    | Member Name   |
    +---------+---------------+
    | PM      | Ian           |
    | Power   | Afa           |
    | Testing | JoJo,Boss,Ben |
    +---------+---------------+
    3 rows in set (0.001 sec)
    
    MariaDB [testdb]> SELECT Dept, GROUP_CONCAT(Name ORDER BY Name ASC SEPARATOR ',') AS "Member Name" FROM employee GROUP BY Dept;
    +---------+---------------+
    | Dept    | Member Name   |
    +---------+---------------+
    | PM      | Ian           |
    | Power   | Afa           |
    | Testing | Ben,Boss,JoJo |
    +---------+---------------+
    3 rows in set (0.001 sec)
    
  • LOCATE
    檢視字串裡面是否有要搜尋的字,substr (搜尋關鍵字), str (字串), pos(從字串的哪一個字元開始搜尋).

    LOCATE(substr,str), LOCATE(substr,str,pos)
    
    MariaDB [testdb]> SELECT LOCATE('bar', 'foobarbar') AS 'Search Result';
    +---------------+
    | Search Result |
    +---------------+
    |             4 |
    +---------------+
    1 row in set (0.000 sec)
    
    MariaDB [testdb]> SELECT LOCATE('xbar', 'foobar') AS 'Search Result';
    +---------------+
    | Search Result |
    +---------------+
    |             0 |
    +---------------+
    1 row in set (0.000 sec)
    
    MariaDB [testdb]> SELECT LOCATE('bar', 'foobarbar', 5) AS 'Search Result';
    +---------------+
    | Search Result |
    +---------------+
    |             7 |
    +---------------+
    1 row in set (0.001 sec)
    
  • TRIM
    移除特定字串.

    TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM([remstr FROM] str)
    

    可以用 BOTH (全部), LEADING (字頭) , TRAILING (字尾) 來決定取代哪邊的字串.

    MariaDB [testdb]> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx') AS 'TRIM Result';
    +-------------+
    | TRIM Result |
    +-------------+
    | barxxx      |
    +-------------+
    1 row in set (0.001 sec)
    
    MariaDB [testdb]> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx') AS 'TRIM Result';
    +-------------+
    | TRIM Result |
    +-------------+
    | bar         |
    +-------------+
    1 row in set (0.000 sec)
    
    MariaDB [testdb]> SELECT TRIM(TRAILING 'x' FROM 'xxxbarxxx') AS 'TRIM Result';
    +-------------+
    | TRIM Result |
    +-------------+
    | xxxbar      |
    +-------------+
    1 row in set (0.000 sec)
    
  • REPLACE
    用 REPLACE 可以取代字串裡的特定字串.

    REPLACE(str, find_string, replace_with)
    

    \n 為 Linux 的換行字元.

    MariaDB [testdb]> SELECT '123\n456' AS ' TEXT';
    +---------+
    | TEXT    |
    +---------+
    | 123
    456 |
    +---------+
    1 row in set, 1 warning (0.001 sec)
    
    MariaDB [testdb]> SELECT REPLACE('123\n456' ,'\n' ,'') AS ' Replace Result';
    +----------------+
    | Replace Result |
    +----------------+
    | 123456         |
    +----------------+
    1 row in set, 1 warning (0.000 sec)
    
  • FIND_IN_SET
    回傳值為 string 位於 string_list (須以 , 分隔)的哪一個位置(不存在時,回傳 0 ).

    FIND_IN_SET(string, string_list)
    
    MariaDB [testdb]> SELECT FIND_IN_SET('b' , 'a,b,c') AS 'FIND_IN_SET Result';
    +--------------------+
    | FIND_IN_SET Result |
    +--------------------+
    |                  2 |
    +--------------------+
    1 row in set (0.001 sec)
    
    MariaDB [testdb]> SELECT FIND_IN_SET('f' , 'a,b,c') AS 'FIND_IN_SET Result';
    +--------------------+
    | FIND_IN_SET Result |
    +--------------------+
    |                  0 |
    +--------------------+
    1 row in set (0.001 sec)
    

    雖然 FIND_IN_SET 函數可以解決單一欄位儲存多個資料 (以欄位中的 “, comma separated ” 為分隔) 所造成的搜尋問題,但不建議儲存成這樣的欄位內容.使用資料庫正規化 (Database normalization) – http://benjr.tw/101850 重新定義 Table 與其相關性.

  • IFNULL
    用來判斷 Expression 字串是否為 NULL ,是就以 ALTER_Value 來取代.

    IFNULL(Expression, ALTER_Value)
    
    MariaDB [testdb]> SELECT IFNULL(NULL,'Ben') AS 'IFNULL Result';
    +---------------+
    | IFNULL Result |
    +---------------+
    | Ben           |
    +---------------+
    1 row in set (0.002 sec)
    
    MariaDB [testdb]> SELECT IFNULL(1,'Ben') 'IFNULL Result';
    +---------------+
    | IFNULL Result |
    +---------------+
    | 1             |
    +---------------+
    1 row in set (0.000 sec)
    

    下面範例使用 GROUP BY (依據不同單位做分類,再加總各單位的薪水 )加上 WITH ROLLUP (可以把分類的資料做個總結).

    MariaDB [testdb]> SELECT Dept, SUM(salary) AS Total FROM employee GROUP BY Dept WITH ROLLUP;
    +---------+--------+
    | Dept    | Total  |
    +---------+--------+
    | PM      |  85000 |
    | Power   |  48000 |
    | Testing | 165000 |
    | NULL    | 298000 |
    +---------+--------+
    4 rows in set (0.001 sec)
    

    但很討厭的是在總結資料中會顯示為 NULL ,這時候可以利用 IFNULL 來取代 NULL 字串.

    MariaDB [testdb]> SELECT IFNULL(Dept,'SUM') AS Dept, SUM(salary) AS Total FROM employee GROUP BY Dept WITH ROLLUP;
    +---------+--------+
    | Dept    | Total  |
    +---------+--------+
    | PM      |  85000 |
    | Power   |  48000 |
    | Testing | 165000 |
    | SUM     | 298000 |
    +---------+--------+
    4 rows in set, 1 warning (0.001 sec)
    
  • IF
    前面函數 IFNULL 用來判斷 Expression 字串是否為 NULL ,是就以 ALTER_Value 來取代,那相反過來有 IFNOTNULL 函數嗎?沒有,但是我們可以透過 IF 的方式來完成.
    這個 IF 是 Function 與 STATEMENT 的 IF 不同.

    IF(Express IS NULL,0,1)
    

    只有 NULL 才是 NULL.

    MariaDB [testdb]> SELECT IF( NULL IS NULL,"NULL","Not NULL");
    +-------------------------------------+
    | IF( NULL IS NULL,"NULL","Not NULL") |
    +-------------------------------------+
    | NULL                                |
    +-------------------------------------+
    1 row in set (0.000 sec)
    

    “NULL” (代表字串 ),0(數字) 也不是 NULL.

    MariaDB [testdb]> SELECT IF( "NULL" IS NULL,"NULL","Not NULL");
    +---------------------------------------+
    | IF( "NULL" IS NULL,"NULL","Not NULL") |
    +---------------------------------------+
    | Not NULL                              |
    +---------------------------------------+
    1 row in set (0.000 sec)
    
    MariaDB [testdb]> SELECT IF( 0 IS NULL,"NULL","Not NULL");
    +----------------------------------+
    | IF( 0 IS NULL,"NULL","Not NULL") |
    +----------------------------------+
    | Not NULL                         |
    +----------------------------------+
    1 row in set (0.000 sec)
    
    MariaDB [testdb]> SELECT IF( 1 IS NULL,"NULL","Not NULL");
    +----------------------------------+
    | IF( 1 IS NULL,"NULL","Not NULL") |
    +----------------------------------+
    | Not NULL                         |
    +----------------------------------+
    

數字處理函數

  • TRUNCATE
    取小數點,不計算四捨五入,需計算四捨五入請使用 ROUND.

    MariaDB [(none)]> SELECT TRUNCATE(100/3, 2); 
    +--------------------+
    | TRUNCATE(100/3, 2) |
    +--------------------+
    |              33.33 |
    +--------------------+
    1 row in set (0.001 sec)
    
    MariaDB [(none)]> SELECT TRUNCATE(-1.25,1);
    +-------------------+
    | TRUNCATE(-1.25,1) |
    +-------------------+
    |              -1.2 |
    +-------------------+
    1 row in set (0.000 sec)
    
    MariaDB [(none)]> SELECT ROUND(-1.25,1);
    +----------------+
    | ROUND(-1.25,1) |
    +----------------+
    |           -1.3 |
    +----------------+
    1 row in set (0.001 sec)
    
  • ROUND
    取小數點,四捨五入.

    MariaDB [(none)]> SELECT ROUND(-1.23,1);
    +----------------+
    | ROUND(-1.23,1) |
    +----------------+
    |           -1.2 |
    +----------------+
    1 row in set (0.00 sec)
    
    MariaDB [(none)]> SELECT ROUND(-1.25,1);
    +----------------+
    | ROUND(-1.25,1) |
    +----------------+
    |           -1.3 |
    +----------------+
    1 row in set (0.00 sec)
    
  • SUM
    數字加總. 通常搭配 GROUP BY 使用,可以依據指定欄位將數字欄位把數字加總,還可以搭背 WITH ROLLUP 可以把分類的資料做個總結(數字部分),詳細範例請參考 – http://benjr.tw/98988 (GROUP BY , HAVING , ORDER BY 以及 LIMIT).
  • MAX , MIN
    MAX() 函數會返回數值中最大的.

    MariaDB [testdb]> SELECT MAX(Salary) FROM employee;
    +-------------+
    | MAX(Salary) |
    +-------------+
    |       85000 |
    +-------------+
    1 row in set (0.001 sec)
    
    MariaDB [testdb]> SELECT Name , Dept , Salary FROM employee WHERE Salary = (SELECT MAX(Salary) FROM employee);
    +------+------+--------+
    | Name | Dept | Salary |
    +------+------+--------+
    | Ian  | PM   |  85000 |
    +------+------+--------+
    1 row in set (0.003 sec)
    

    MIN() 函數會返回數值中最小的.

    MariaDB [testdb]> SELECT MIN(Salary) FROM employee;
    +-------------+
    | MIN(Salary) |
    +-------------+
    |       45000 |
    +-------------+
    1 row in set (0.001 sec)
    
    MariaDB [testdb]> SELECT Name , Dept , Salary FROM employee WHERE Salary = (SELECT MIN(Salary) FROM employee);
    +------+---------+--------+
    | Name | Dept    | Salary |
    +------+---------+--------+
    | Ben  | Testing |  45000 |
    | JoJo | Testing |  45000 |
    +------+---------+--------+
    2 rows in set (0.001 sec)
    

時間處理函數

  • CURRENT_TIMESTAMP
    功能與 NOW 一樣,都是回傳目前系統的日期時間.

    MariaDB [(none)]> SELECT CURRENT_TIMESTAMP();
    +---------------------+
    | CURRENT_TIMESTAMP() |
    +---------------------+
    | 2019-09-06 17:16:18 |
    +---------------------+
    1 row in set (0.00 sec)
    
  • NOW
    功能與 CURRENT_TIMESTAMP 一樣,都是回傳目前系統的日期時間.

    MariaDB [(none)]> SELECT NOW();
    +---------------------+
    | NOW()               |
    +---------------------+
    | 2019-09-06 17:16:24 |
    +---------------------+
    1 row in set (0.00 sec)
    
  • CURDATE
    回傳目前日期.

    MariaDB [(none)]> SELECT CURDATE();
    +------------+
    | CURDATE()  |
    +------------+
    | 2019-09-06 |
    +------------+
    1 row in set (0.01 sec)
    
  • CURTIME
    回傳目前時間.

    MariaDB [(none)]> SELECT CURTIME();
    +-----------+
    | CURTIME() |
    +-----------+
    | 17:20:46  |
    +-----------+
    1 row in set (0.00 sec)
    
  • DATE
    回傳日期時間中的日期.

    MariaDB [testdb]> SELECT DATE(CURRENT_TIMESTAMP());
    +---------------------------+
    | DATE(CURRENT_TIMESTAMP()) |
    +---------------------------+
    | 2020-02-04                |
    +---------------------------+
    1 row in set (0.000 sec)
    
  • TIME
    回傳日期時間中的時間.

    MariaDB [testdb]> SELECT TIME(CURRENT_TIMESTAMP());
    +---------------------------+
    | TIME(CURRENT_TIMESTAMP()) |
    +---------------------------+
    | 20:21:43                  |
    +---------------------------+
    1 row in set (0.000 sec)
    
  • DATE_SUB
    將剪去指定的時間.INTERVAL 後面可以接的時間為 DAY, YEAR, SECOND, MINUTE_SECOND, DAY_SECOND, DAY_HOUR, SECOND_MICROSECOND.

    MariaDB [(none)]> SELECT DATE_SUB(CURRENT_TIMESTAMP() , INTERVAL 2 YEAR);
    -------------------------------------------------+
    | DATE_SUB(CURRENT_TIMESTAMP() , INTERVAL 2 YEAR) |
    +-------------------------------------------------+
    | 2017-09-06 17:22:36                             |
    +-------------------------------------------------+
    1 row in set (0.01 sec)
    

資料型別轉換函數

  • CAST
    資料型別作轉換.

    CAST(expr AS type)
    
    MariaDB [(none)]> SELECT CAST(1.09 AS SIGNED);
    +----------------------+
    | CAST(1.09 AS SIGNED) |
    +----------------------+
    |                    1 |
    +----------------------+
    1 row in set (0.00 sec)
    

    可轉換型別如下
    BINARY , CHAR , DATE , TIME , DATETIME , SIGNED [INTEGER] ,UNSIGNED [INTEGER]

  • CONVERT
    資料型別作轉換.其中 CONVERT(expr, type) 與 CAST(expr AS type) 使用上是一樣的.

    CONVERT(expr,type), CONVERT(expr USING transcoding_name)
    
    MariaDB [(none)]> SELECT CONVERT(1.09,SIGNED);
    +----------------------+
    | CONVERT(1.09,SIGNED) |
    +----------------------+
    |                    1 |
    +----------------------+
    1 row in set (0.00 sec)
    

    CONVERT(expr USING transcoding_name) 用在不同的字符集轉換上.

    MariaDB [(none)]> SELECT CONVERT('abc' USING utf8);
    +---------------------------+
    | CONVERT('abc' USING utf8) |
    +---------------------------+
    | abc                       |
    +---------------------------+
    1 row in set (0.00 sec)
    

檔案函數

  • LOAD_FILE
    LOAD_FILE 函數指定載入檔案位置,需注意權限問題,無法讀取時 LOAD_FILE 函數會回傳 NULL .得到 NULL 代表 mysql 使用者無法讀取該檔案,這時要注意存放檔案的目錄需要可以進入 (a+x) 的權限,檔案本身至少需要可供讀取 (a+r) 的權限.

    另外一個可能是當檔案大小超出 MYSQL 的預設時 max_allowed_packet 也無法存取.

    MariaDB [(none)]> SHOW VARIABLES LIKE 'max_allowed_packet';
    +--------------------+---------+
    | Variable_name      | Value   |
    +--------------------+---------+
    | max_allowed_packet | 1048576 |
    +--------------------+---------+
    1 row in set (0.00 sec)
    
    [root@localhost ~]# cat /var/lib/mysql/test.txt 
    ABC
    123
    [root@localhost ~]# mysql -u root -p
    Enter password: 
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MariaDB connection id is 3
    Server version: 5.5.60-MariaDB MariaDB Server
    
    Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    MariaDB [(none)]> SELECT LOAD_FILE('/var/lib/mysql/test.txt');
    +--------------------------------------+
    | LOAD_FILE('/var/lib/mysql/test.txt') |
    +--------------------------------------+
    | ABC
    123
                                 |
    +--------------------------------------+
    1 row in set (0.01 sec)
    

其他函數

  • LAST_INSERT_ID
    當資料欄位為 AUTO_INCREMENT 時,新增資料後才會知道該數值,透過 LAST_INSERT_ID 函數,可以得知剛剛新增資料 AUTO_INCREMENT 的 ID 數值是多少 (當有多筆資料新增時,並不會影響彼此讀取自己新增資料的 ID).

    MariaDB [testdb]> INSERT INTO employee (Name , Dept , JobTitle , Salary , email) VALUES ('Mat' , 'Testing' , 'Engineer' , '39000' , 'mat@benjr.tw') ;
    Query OK, 1 row affected (0.002 sec)
    
    MariaDB [testdb]> SELECT LAST_INSERT_ID();
    +------------------+
    | LAST_INSERT_ID() |
    +------------------+
    |                6 |
    +------------------+
    1 row in set (0.001 sec)
    
  • COUNT
    回傳 SELECT 的筆數,通常搭配 GROUP BY 使用.

    COUNT(expression)
    

    利用 COUNT(*) 來看回傳的資料筆數.

    MariaDB [testdb]> SELECT GROUP_CONCAT(Name SEPARATOR ',') AS "Member Name" , COUNT(*) COUNT FROM employee;
    +---------------------------+-------+
    | Member Name               | COUNT |
    +---------------------------+-------+
    | Ben,Afa,Boss,JoJo,Ian,Mat |     6 |
    +---------------------------+-------+
    1 row in set (0.000 sec)
    

    可以利用 COUNT(DISTINCT(column)) 或是 COUNT(DISTINCT column) 來計算不重覆的欄位有幾個.

    MariaDB [testdb]> SELECT GROUP_CONCAT(DISTINCT Dept SEPARATOR ',') AS "Dept Name" , COUNT(DISTINCT(Dept)) AS Dept FROM employee;
    +------------------+------+
    | Dept Name        | Dept |
    +------------------+------+
    | Testing,Power,PM |    3 |
    +------------------+------+
    1 row in set (0.001 sec)
    
沒有解決問題,試試搜尋本站其他內容

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

這個網站採用 Akismet 服務減少垃圾留言。進一步瞭解 Akismet 如何處理網站訪客的留言資料