MySQL – 常用函數

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

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
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)]> CREATE DATABASE testdb;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> USE testdb;
Database changed

字串處理函數

  • 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]> CREATE TABLE employee (Name char(20),Dept char(20),JobTitle char(20),Salary int(11));
    Query OK, 0 rows affected (0.01 sec)
    
    MariaDB [testdb]> INSERT INTO employee VALUES ('Ben','Testing','Engineer','45000');
    Query OK, 1 row affected (0.00 sec)
    
    MariaDB [testdb]> INSERT INTO employee VALUES ('Afa','Power','Engineer','48000');
    Query OK, 1 row affected (0.00 sec)
    
    MariaDB [testdb]> INSERT INTO employee VALUES ('Boss','Testing','Manager','75000');
    Query OK, 1 row affected (0.01 sec)
    
    MariaDB [testdb]> INSERT INTO employee VALUES ('Cars','Testing','Senior Engineer','42000');
    Query OK, 1 row affected (0.00 sec)
    
    MariaDB [testdb]> SELECT Dept, GROUP_CONCAT(Name SEPARATOR ',') AS "Member Name" FROM employee GROUP BY Dept;
    +---------+---------------+
    | Dept    | Member Name   |
    +---------+---------------+
    | Power   | Afa           |
    | Testing | Ben,Boss,Cars |
    +---------+---------------+
    2 rows in set (0.00 sec)
    
  • LOCATE
    檢視字串裡面是否有要搜尋的字,substr (搜尋關鍵字), str (字串), pos(從字串的哪一個字元開始搜尋).

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

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

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

    MariaDB [(none)]> SELECT TRIM('  bar   ');
    +------------------+
    | TRIM('  bar   ') |
    +------------------+
    | bar              |
    +------------------+
    1 row in set (0.00 sec)
    
    MariaDB [(none)]> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
    +------------------------------------+
    | TRIM(LEADING 'x' FROM 'xxxbarxxx') |
    +------------------------------------+
    | barxxx                             |
    +------------------------------------+
    1 row in set (0.01 sec)
    
    MariaDB [(none)]> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
    +---------------------------------+
    | TRIM(BOTH 'x' FROM 'xxxbarxxx') |
    +---------------------------------+
    | bar                             |
    +---------------------------------+
    1 row in set (0.00 sec)
    
    MariaDB [(none)]> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
    +-------------------------------------+
    | TRIM(TRAILING 'xyz' FROM 'barxxyz') |
    +-------------------------------------+
    | barx                                |
    +-------------------------------------+
    1 row in set (0.00 sec)
    
  • REPLACE
    用 REPLACE 可以取代字串裡的特定字串.

    REPLACE(str, find_string, replace_with)
    

    \n 為 Linux 的換行字元.

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

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

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

數字處理函數

  • 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)
    

時間處理函數

  • 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_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 [(none)]> USE testdb;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    MariaDB [testdb]> CREATE TABLE staff (K1 int(11) NOT NULL auto_increment , name varchar(199) NOT NULL , dept varchar(199) NOT NULL, PRIMARY KEY (K1));
    Query OK, 0 rows affected (0.00 sec)
    
    MariaDB [testdb]> INSERT INTO staff (name , dept) VALUES('Ben','HR');
    Query OK, 1 row affected (0.00 sec)
    
    MariaDB [testdb]> SELECT LAST_INSERT_ID();
    +------------------+
    | LAST_INSERT_ID() |
    +------------------+
    |                1 |
    +------------------+
    1 row in set (0.00 sec)
    
  • COUNT
    回傳 SELECT 的筆數,通常搭配 GROUP BY 使用.

    COUNT(expression)
    
    MariaDB [testdb]> CREATE TABLE employee (Name char(20),Dept char(20),JobTitle char(20),Salary int(11));
    Query OK, 0 rows affected (0.01 sec)
    
    MariaDB [testdb]> INSERT INTO employee VALUES ('Ben','Testing','Engineer','45000');
    Query OK, 1 row affected (0.00 sec)
    
    MariaDB [testdb]> INSERT INTO employee VALUES ('Afa','Power','Engineer','48000');
    Query OK, 1 row affected (0.00 sec)
    
    MariaDB [testdb]> INSERT INTO employee VALUES ('Boss','Testing','Manager','75000');
    Query OK, 1 row affected (0.01 sec)
    
    MariaDB [testdb]> INSERT INTO employee VALUES ('Cars','Testing','Senior Engineer','42000');
    Query OK, 1 row affected (0.00 sec)
    
    MariaDB [testdb]> SELECT * FROM employee;
    +------+---------+-----------------+--------+
    | Name | Dept    | JobTitle        | Salary |
    +------+---------+-----------------+--------+
    | Ben  | Testing | Engineer        |  45000 |
    | Afa  | Power   | Engineer        |  48000 |
    | Boss | Testing | Manager         |  75000 |
    | Cars | Testing | Senior Engineer |  42000 |
    +------+---------+-----------------+--------+
    4 rows in set (0.00 sec)
    
    MariaDB [testdb]> SELECT Dept, GROUP_CONCAT(Name SEPARATOR ',') AS "Member Name" , COUNT(Dept) COUNT FROM employee;
    +---------+-------------------+-------+
    | Dept    | Member Name       | COUNT |
    +---------+-------------------+-------+
    | Testing | Ben,Afa,Boss,Cars |     4 |
    +---------+-------------------+-------+
    1 row in set (0.00 sec)
    
沒有解決問題,試試搜尋本站其他內容

發佈留言

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

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