最近用到MySQL的UDF, 查了一下相關(guān)文獻(xiàn), 對(duì)用戶(hù)用戶(hù)實(shí)現(xiàn)function和Aggregate function的方法做個(gè)介紹.

快速編寫(xiě)一個(gè)MySQL UDF

為了能夠快速了解UDF(user-defined function)是什么, 我們首先構(gòu)建一個(gè)最簡(jiǎn)單的UDF, 然后再介紹更細(xì)節(jié)的內(nèi)容. 本文在Ubuntu16.04下測(cè)試, 開(kāi)始之前需要先安裝MySQL以及相關(guān)的庫(kù), 在Ubuntu下是:

sudo apt-get install libmysqlclient-dev

Step1: 編寫(xiě)c++代碼并生成動(dòng)態(tài)鏈接庫(kù)

示例c++代碼如下:

extern "c"{
long long myadd(UDF_INIT *initid, UDF_ARGS *args,
                char *is_null, char *error);
my_bool myadd_init(UDF_INIT *initid, UDF_ARGS *args,
                  char *message);
}

long long myadd(UDF_INIT *initid, UDF_ARGS *args,
                char *is_null, char *error) {
    int a = *((long long *)args->args[0]);
    int b = *((long long *)args->args[1]);
    return a + b;
}

my_bool myadd_init(UDF_INIT *initid, UDF_ARGS *args,
                  char *message){
    return 0;
}

完成以后, 將文件保存為udf.cpp 然后使用如下的命令編譯動(dòng)態(tài)鏈接庫(kù):

g++ -shared -fPIC -I /usr/include/mysql -o udf.so udf.cpp

獲得udf.so文件.

Step2: 在MySQL中添加函數(shù)

完成上述的編寫(xiě)以后, 將udf.so文件拷貝到MySQL的plugin目錄下, 在Ubuntu16.04中默認(rèn)是:

/usr/lib/mysql/plugin/

然后使用如下的命令在MySQL中安裝動(dòng)態(tài)鏈接庫(kù).

CREATE FUNCTION myadd RETURNS INTEGER SONAME 'udf.so'

Step3: 調(diào)用函數(shù)以及相關(guān)查詢(xún)

• 調(diào)用函數(shù)

select myadd(1,2);

可以獲得計(jì)算結(jié)果3

• 查詢(xún)安裝列表

select * from mysql.func;

可以查看數(shù)據(jù)庫(kù)當(dāng)前被安裝的.so的庫(kù)列表.

• 用drop function來(lái)刪除函數(shù):

DROP FUNCTION myadd;

UDF編寫(xiě)過(guò)程解釋

可以看到, 我們?cè)谏厦嫣砑恿俗约旱暮瘮?shù)myadd, 這個(gè)函數(shù)被安裝以后可以被MySQL執(zhí)行. 我們同時(shí)定義了myadd_init, 這是系統(tǒng)規(guī)定的必須使用的初始化函數(shù). 在編寫(xiě)MySQL的UDF的時(shí)候, 一方面我們要定義自己需要的函數(shù), 另一方面, 我們要同時(shí)編寫(xiě)配套的一系列其他函數(shù), 這些函數(shù)的命名有固定規(guī)則. 比如用戶(hù)自定義的函數(shù)名為xxx, 則配套的函數(shù)為xxx_init, xxx_deinit等, 其參數(shù)列表也是固定的,下面進(jìn)行介紹.

編寫(xiě)用戶(hù)主函數(shù)

首先是用戶(hù)函數(shù)的定義, 我們假設(shè)需要定義的函數(shù)名字為為xxx, 則我們的函數(shù)需要有參數(shù)列表和返回值, 這不能由用戶(hù)隨意指定, 是有固定規(guī)則的.

其中返回類(lèi)型支持5種:

enum Item_result {STRING_RESULT=0, REAL_RESULT, INT_RESULT, ROW_RESULT,  DECIMAL_RESULT}; 

對(duì)于這5種返回值, 定義的函數(shù)頭分別如下:

• 返回值是STRING 類(lèi)型或DECIMAL類(lèi)型

char *xxx(UDF_INIT *initid, UDF_ARGS *args,
          char *result, unsigned long *length,
          char *is_null, char *error);

對(duì)于這種定義, 返回值可以指向result, 把需要的內(nèi)容拷貝進(jìn)去, 并設(shè)置長(zhǎng)度, 如下:

memcpy(result, "result string", 13);
*length = 13;

• INTEGER類(lèi)型

long long xxx(UDF_INIT *initid, UDF_ARGS *args,
              char *is_null, char *error);

• REAL類(lèi)型

double xxx(UDF_INIT *initid, UDF_ARGS *args,
              char *is_null, char *error);

• ROW類(lèi)型

未實(shí)現(xiàn)

編寫(xiě)系統(tǒng)內(nèi)置函數(shù)

在完成了用戶(hù)定義的主函數(shù)以后, 還需要編寫(xiě)配套的系統(tǒng)內(nèi)置函數(shù). 其相關(guān)說(shuō)明如下:

xxx_init

這個(gè)函數(shù)會(huì)在自定義的xxx函數(shù)調(diào)用前被調(diào)用, 進(jìn)行基本的初始化工作, 其完整定義如下:

my_bool xxx_init(UDF_INIT *initid, UDF_ARGS *args, char *message);

• 返回值: 1代表出錯(cuò), 可以在message中給出錯(cuò)誤信息并且返回給客戶(hù)端, 0表示正確執(zhí)行.信息長(zhǎng)度不能大于MYSQL_ERRMSG_SIZE
• 函數(shù)功能: 該函數(shù)的主要功能一般是分配空間, 函數(shù)參數(shù)檢查的等. 如果不需要做任何操作, 直接返回0即可.

xxx_deinit

該函數(shù)用于釋放申請(qǐng)的空間, 其完整定義如下:

void xxx_deinit(UDF_INIT *initid);

• 函數(shù)功能: 該函數(shù)的功能主要是釋放資源, 如果在xxx_init中申請(qǐng)了內(nèi)存, 可以在此處釋放, 該函數(shù)在用戶(hù)函數(shù)xxx執(zhí)行以后執(zhí)行

對(duì)于普通的UDF, 上面兩個(gè)內(nèi)置函數(shù)足夠了, 但是對(duì)于Aggregate函數(shù), 像sum, count函數(shù), 必須額外給出如下的函數(shù):

xxx_clear

其完整定義如下:

void xxx_clear(UDF_INIT *initid, char *is_null, char *error);

xxx_add

void xxx_add(UDF_INIT *initid, UDF_ARGS *args,
             char *is_null, char *error);

兩種UDF的執(zhí)行流程介紹

這兩種UDF非別有如下的執(zhí)行流程:

普通函數(shù)執(zhí)行流程

• 調(diào)用xxx_init來(lái)初始化, 并申請(qǐng)內(nèi)存空間用于存儲(chǔ)結(jié)果
• 調(diào)用xxx
• 調(diào)用xxx_deinit釋放空間

對(duì)于普通函數(shù)的執(zhí)行流程, 可以參照一開(kāi)始給出的myadd函數(shù).

Aggregate函數(shù)執(zhí)行流程

• 調(diào)用xxx_init來(lái)初始化, 并申請(qǐng)內(nèi)存空間用于存儲(chǔ)結(jié)果
• 對(duì)表使用group by 排序, 形成多個(gè)或一個(gè)group
• xxx_clear調(diào)用, 對(duì)每個(gè)新的group, 調(diào)用之
• 對(duì)每個(gè)group的每一行,調(diào)用xxx_add
• 調(diào)用xxx
• 重復(fù)3-5, 直到處理完所有的group
• 調(diào)用xxx_deinit釋放空間

對(duì)于Aggregate函數(shù)的執(zhí)行流程, 可以參照后面給出的mysum函數(shù).

函數(shù)參數(shù)列表介紹

對(duì)于上面介紹的函數(shù), 其參數(shù)列表和返回值是我們所關(guān)注的, 對(duì)于某些函數(shù)特有的參數(shù), 在上面介紹函數(shù)的同時(shí)已經(jīng)做了介紹, 現(xiàn)在介紹其公有的參數(shù)部分:

UDF_INIT

該結(jié)構(gòu)主要用于用戶(hù)函數(shù)與系統(tǒng)內(nèi)置函數(shù)的通信, 其結(jié)構(gòu)如下:

在本文例子中, 我們只用到ptr, 其余均使用默認(rèn)值. 所謂的通信, 是指我們?cè)趚xx_init中就有這個(gè)參數(shù)了, 后后續(xù)的xxx與xxx_deinit中, 我們依然可以獲取這個(gè)類(lèi)型的指針, 這樣, 我們就可以在xxx_init函數(shù)中申請(qǐng)一塊空間, 并令ptr指向這塊空間, 在xxx函數(shù)中使用這塊空間, 然后在xxx_deinit中釋放空間, 這是MySQL的UDF的基本用法.

UDF_ARGS

該結(jié)構(gòu)主要用于傳參數(shù), 參數(shù)由MySQL提供, 對(duì)于表而言, 就是一行一行的表數(shù)據(jù), 其介紹如下:

可以看到, 這個(gè)參數(shù)結(jié)構(gòu)提供了很多功能, 本文只關(guān)注通過(guò)args成員來(lái)獲得具體的參數(shù)內(nèi)容.

一個(gè)Aggregate 函數(shù)的例子

有了上面的基礎(chǔ), 我們就可以自己實(shí)現(xiàn)一個(gè)sum函數(shù)mysum, 其作用和內(nèi)置的sum有一樣的功能, 下面給出代碼和解釋:

#include <mysql/mysql.h>

extern "C" {
my_bool   mysum_init(UDF_INIT *const initid, UDF_ARGS *const args,
                           char *const message);
void mysum_deinit(UDF_INIT *const initid);
void mysum_clear(UDF_INIT *const initid, char *const is_null,
                            char *const error);
void mysum_add(UDF_INIT *const initid, UDF_ARGS *const args,
                          char *const is_null, char *const error);
long long mysum(UDF_INIT *const initid, UDF_ARGS *const args,
                      char *const result, unsigned long *const length,
                      char *const is_null, char *const error);
}



//執(zhí)行前先進(jìn)行初始化,分配空間
my_bool mysum_init(UDF_INIT *const initid, UDF_ARGS *const args,
                           char *const message){
    long long * i = new long long;
    initid->ptr = (char*)i;
    return 0;
}

//在執(zhí)行該函數(shù)前,先執(zhí)行g(shù)roup by, 然后遇到每個(gè)新的group, 先調(diào)用該函數(shù).如果沒(méi)有g(shù)roup by, 則所有的都是一個(gè)group.
void mysum_clear(UDF_INIT *const initid, char *const is_null,
                            char *const error) {
    *((long long *)(initid->ptr)) = 0;
}

//每一行數(shù)據(jù)都經(jīng)過(guò)add函數(shù)處理
void   mysum_add(UDF_INIT *const initid, UDF_ARGS *const args,
                          char *const is_null, char *const error) {
    *((long long *)(initid->ptr)) =  *((long long *)(initid->ptr)) +
                                    *((long long *)args->args[0]);
}

//所有數(shù)據(jù)處理完成, 調(diào)用用戶(hù)定義的mysum函數(shù)返回結(jié)果;遇到下一個(gè)group, 重新從clear開(kāi)始執(zhí)行.
long long mysum(UDF_INIT *const initid, UDF_ARGS *const args,
                      char *const result, unsigned long *const length,
                      char *const is_null, char *const error) {
    return *((long long *)(initid->ptr));
}

//執(zhí)行結(jié)束, 釋放空間
void mysum_deinit(UDF_INIT *const initid){
    
    delete initid->ptr;
}

使用上述的方法編譯并復(fù)制到對(duì)應(yīng)的plugin目錄以后, 可以用如下的命令添加函數(shù), 注意這里和添加普通的函數(shù)方法不一樣.

CREATE AGGREGATE FUNCTION mysum RETURNS INTEGER SONAME 'udf.so';

函數(shù)執(zhí)行結(jié)果如下:

Database changed
mysql> select * from student;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | zhangfei  |
|    2 | zhangfei  |
|    3 | zhangfei  |
|    4 | zhangliao |
|    5 | zhangliao |
|    6 | zhangliao |
|    7 | shaoyiwen |
+------+-----------+
7 rows in set (0.00 sec)

mysql> select mysum(id) from student;
+-----------+
| mysum(id) |
+-----------+
|        28 |
+-----------+
1 row in set (0.00 sec)

總結(jié)

可以看到, MySQL的UDF可以用于處理MySQL表中的數(shù)據(jù), 其對(duì)外提供了普通函數(shù)與Aggregate函數(shù)接口, 普通函數(shù)處理一行的數(shù)據(jù), Aggregate函數(shù)處理一個(gè)group的數(shù)據(jù). 其函數(shù)頭是固定的, 對(duì)外提供了5種數(shù)據(jù)類(lèi)型.需要注意的是, 我們編寫(xiě)的MySQL UDF必須保證是線程安全的.

相關(guān)資料

[1] http:/dev.mysql.com/doc/refman/5.7/en/adding-udf.html
[2] http:/blog.csdn.net/luoqiya/article/details/12888553
[3] http:/www.codeproject.com/Articles/15643/MySQL-User-Defined-Functions

原始鏈接:yiwenshao.github.io/2016/11/20/MySQL的UDF/
文章作者:Yiwen Shao
許可協(xié)議:** Attribution-NonCommercial 4.0
轉(zhuǎn)載請(qǐng)保留以上信息, 謝謝!