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

測試環境為 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

直接來看一個範例,在計算機系統關於容量,大小會透過簡寫的方式來表示,自訂一個函數 format_bytes ,能將一般數值轉換成帶有單位的字串.
參考 https://github.com/mysql/mysql-sys/blob/master/functions/format_bytes.sql 範例.

例 : 24593485034234 B 轉換成為 22.37 TiB ,其他單位參考如下.

• KB (kilo bytes ,2¹⁰)
• MB (mega bytes ,2²⁰)
• GB (giga bytes ,2³⁰)
• TB (tera bytes ,2⁴⁰)

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)]> CREATE DATABASE testdb;
Query OK, 0 rows affected (0.00 sec)

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

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

MariaDB [testdb]> DROP FUNCTION IF EXISTS format_bytes;
Query OK, 0 rows affected (0.01 sec)

自訂函數 format_bytes 主程式.

MariaDB [testdb]> DELIMITER //
MariaDB [testdb]> CREATE FUNCTION format_bytes (bytes TEXT)
    -> RETURNS TEXT
    -> BEGIN
    ->  IF bytes IS NULL THEN RETURN NULL;
    ->   ELSEIF bytes >= 1099511627776 THEN RETURN CONCAT(ROUND(bytes / 1099511627776, 2), ' TB');
    ->   ELSEIF bytes >= 1073741824 THEN RETURN CONCAT(ROUND(bytes / 1073741824, 2), ' GB');
    ->   ELSEIF bytes >= 1048576 THEN RETURN CONCAT(ROUND(bytes / 1048576, 2), ' MB');
    ->   ELSEIF bytes >= 1024 THEN RETURN CONCAT(ROUND(bytes / 1024, 2), ' KB');
    ->   ELSE RETURN CONCAT(ROUND(bytes, 0), ' bytes');
    -> 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='format_bytes';

• DELIMITER // //
  mysql 透過分號 “;” 來表示分隔(一個敘述完成),但函數裡面可能會有多個 分隔,這時候可以使用 DELIMITER // … // ,來表示裡面是一整個敘述.
• DELIMITER ;
  把結束符號修改回為 分號 “;” .
• CREATE FUNCTION format_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
  

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

• CONCAT
  將兩個或更多個表達式連接在一起.
• 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)
  

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

MariaDB [testdb]> SELECT format_bytes(24593485034234) AS size;
+-----------+
| size      |
+-----------+
| 22.37 TiB |
+-----------+
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