SQL 自訂函數 (單位字串轉數值)

Loading

測試環境為 Ubuntu16.04 x86_64 虛擬機.

當 SQL 函數 (Function) 沒有符合我們的需求時,可以自訂函數 User-Defined Function (UDF) 設定格式如下:

CREATE
    [DEFINER = { user | CURRENT_USER }]
    FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body

直接來看一個範例,在計算機系統關於容量,大小會透過簡寫的方式來表示,自訂一個函數 convert_bytes ,能將帶有單位的字串轉換成一般數值.

例 : 12.3TiB/s (TB/s) 自動轉換成為 12.3*1024*1024*1024*1024= 13523993021644.8 B/s ,其他單位參考如下.

  • KB (kilo bytes ,210)
  • MB (mega bytes ,220)
  • GB (giga bytes ,230)
  • TB (tera bytes ,240)
root@ubuntu:~# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 35
Server version: 10.0.31-MariaDB-0ubuntu0.16.04.2 Ubuntu 16.04

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

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

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

先確認之前沒有定義過 convert_bytes 這個 User-Defined Function (UDF) ,有的話移除.

MariaDB [testdb]> DELIMITER //
MariaDB [testdb]> CREATE FUNCTION convert_bytes (bytes TEXT)
    -> RETURNS DOUBLE
    -> BEGIN
    ->    IF bytes IS NULL THEN RETURN 0;
    ->    ELSEIF locate('K',bytes)!=0 THEN RETURN CONVERT(TRIM(TRAILING 'KB/s' FROM bytes)*1024 , DOUBLE);
    ->    ELSEIF locate('Ki',bytes)!=0 THEN RETURN CONVERT(TRIM(TRAILING 'KiB/s' FROM bytes)*1024 , DOUBLE);
    ->    ELSEIF locate('M',bytes)!=0 THEN RETURN CONVERT(TRIM(TRAILING 'MB/s' FROM bytes)*1048576 , DOUBLE);
    ->    ELSEIF locate('Mi',bytes)!=0 THEN RETURN CONVERT(TRIM(TRAILING 'MiB/s' FROM bytes)*1048576 , DOUBLE);
    ->    ELSEIF locate('G',bytes)!=0 THEN RETURN CONVERT(TRIM(TRAILING 'GB/s' FROM bytes)*1073741824 , DOUBLE);
    ->    ELSEIF locate('Gi',bytes)!=0 THEN RETURN CONVERT(TRIM(TRAILING 'GiB/s' FROM bytes)*1073741824 , DOUBLE);
    ->    ELSEIF locate('T',bytes)!=0 THEN RETURN CONVERT(TRIM(TRAILING 'TB/s' FROM bytes)*1099511627776 , DOUBLE);
    ->    ELSEIF locate('Ti',bytes)!=0 THEN RETURN CONVERT(TRIM(TRAILING 'TiB/s' FROM bytes)*1099511627776 , DOUBLE);
    ->    ELSE RETURN CONVERT(TRIM(TRAILING 'B/s' FROM bytes) , DOUBLE);
    -> END IF;
    -> END//
Query OK, 0 rows affected (0.00 sec)

MariaDB [testdb]> DELIMITER ;

建立好 User-Defined Function (UDF) 之後,可以透過下面指令來檢視.

MariaDB [testdb]> SHOW FUNCTION STATUS;
MariaDB [testdb]> SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE SPECIFIC_NAME='convert_bytes';
  • DELIMITER // //
    mysql 透過分號 “;” 來表示分隔(一個敘述完成),但函數裡面可能會有多個 分隔,這時候可以使用 DELIMITER // … // ,來表示裡面是一整個敘述.
  • DELIMITER ;
    把結束符號修改回為 分號 “;” .
  • CREATE FUNCTION convert_bytes (bytes TEXT)
    建立一個函數,須包含函數名稱與傳入變數與型態.
  • RETURNS TEXT
    回傳值的型態.
  • BEGIN END
    函數程式宣告必須包含在裡面.
  • IF ELSEIF END IF
    使用 IF ELSE 的判斷式,並 RETURN 回傳其結果.

    IF search_condition THEN statement_list
        [ELSEIF search_condition THEN statement_list] ...
        [ELSE statement_list]
    END IF
    

自訂 convert_bytes 函數裡面有用到兩個系統預設函數.

  • locate
    檢視字串裡面是否有要搜尋的字 https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_locate

    LOCATE(substr,str), LOCATE(substr,str,pos)
    
    mysql> SELECT LOCATE('bar', 'foobarbar');
            -> 4
    mysql> SELECT LOCATE('xbar', 'foobar');
            -> 0
    mysql> SELECT LOCATE('bar', 'foobarbar', 5);
            -> 7
    
  • trim
    移除特定字串 https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_trim

    TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM([remstr FROM] str)
    
    mysql> SELECT TRIM('  bar   ');
            -> 'bar'
    mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
            -> 'barxxx'
    mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
            -> 'bar'
    mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
            -> 'barx'
    
  • CONVERT
    資料型別作轉換 https://dev.mysql.com/doc/refman/5.7/en/cast-functions.html#function_convert

    CONVERT(expr,type), CONVERT(expr USING transcoding_name)
    

來試試看這個自訂的 convert_bytes 函數執行結果.

MariaDB [testdb]> SELECT convert_bytes ('12.3TiB/s') AS size;
+------------------+
| size             |
+------------------+
| 13523993021644.8 |
+------------------+
1 row in set (0.00 sec)

MariaDB [testdb]> SELECT convert_bytes ('12.3kB/s') AS size;
+---------+
| size    |
+---------+
| 12595.2 |
+---------+
1 row in set (0.00 sec)

遇過的問題


在建立 Stored Function 時卻得到以下的錯誤訊息.

This function has none of DETERMINISTIC, NO SQL,or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)  

請參考說明 – https://benjr.tw/102425

沒有解決問題,試試搜尋本站其他內容

發佈留言

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

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