有没有遇到这种场景呢,当使用mysql做复杂字符串处理的时候总感觉mysql内置的函数不够用呢。比如做字符串切割,解析xml等。而这个时候你恰恰对编写mysql函数或者存储过程缺乏一定的经验积累、而你擅长的可能是c/c++,java、c#或者其它编程语言。当然,你也可以把数据库中的数据加载到应用程序中去处理,然后利用应用程序灵活复杂的字符串处理函数实现功能。当然这也是可以的的,只不过注意力需要做切换,因为有的时候可能你想的是只对数据库操作就实现你的目标。再比如我想把现有mysql数据库中的数据同步到其它数据源如oracle。当然这种需求通过应用程序都是可以实现的 只不过始终要把数据库中的数据复制一份到应用程序，这时候应用程序其实扮演的也是临时数据库的角色,只不过这个临时数据库的存储介质是内存。数据库和应用程序其实没有本质上的区别。只不过数据库是相对固化的、当然如果你有兴趣去修改数据库的源码那也是可以的。数据库设计者也考虑到了拓展性的问题,所以有了用户定义函数(UDF)用来补充内置函数的不足。编写MYSQL UDF需要一定的c/c++基础。其它数据库也是充满了UDF的影子,比如oracle 因为它内置了java虚拟机所以可以直接利用java的语法去创建函数 这样的话明显在极大的程度上提高了数据库的数据处理能力。还有h2嵌入式数据库,因为它本身就是使用java来写的 所以它的UDF函数当然可以直接利用java了。如果我们脑洞够大实力够强的话,我们在Mysql里面其实可以集成一个java虚拟机然后用java语法去编写自定义函数。当然这里我们只简单对c/c++实现的http接口做个分析。http协议早已经成为一种标准,因为它可以跨语言跨平台。这样理论上我们使用任何语言编写的http接口都可以被mysql所利用了，当然执行效率又是另一方面的事情。
以下示例程序中使用mysql8作为测试对象,示例程序包含1个头文件和1个源代码文件,项目中需要引用curl的头文件和mysql8的头文件
mysql-udf-http.h
#include <curl/curl.h>
/* Common definitions for all functions */
#define CURL_UDF_MAX_SIZE 1024*1024
#define VERSION_STRING "1.0\n"
#define VERSION_STRING_LENGTH 4
typedef struct st_curl_results st_curl_results;
struct st_curl_results {
?? ?char *result;
?? ?size_t size;
};
#pragma once
?
main.c
#define _CRT_SECURE_NO_WARNINGS
#include <mysql.h>
#include <string.h>
#include <stdio.h>
#include <stdlib.h>
#include <curl/curl.h>
#include "mysql-udf-http.h"
#pragma comment(lib,"legacy_stdio_definitions.lib")
/*
?* 当libjpeg-turbo为vs2010编译时，vs2015下静态链接libjpeg-turbo会链接出错:找不到__iob_func,
?* 增加__iob_func到__acrt_iob_func的转换函数解决此问题,
?* 当libjpeg-turbo用vs2015编译时，不需要此补丁文件
?*/
#if _MSC_VER>=1900
#include "stdio.h"?
_ACRTIMP_ALT FILE* __cdecl __acrt_iob_func(unsigned);
#ifdef __cplusplus?
extern "C"
#endif?
FILE* __cdecl __iob_func(unsigned i) {
?? ?return __acrt_iob_func(i);
}
#endif /* _MSC_VER>=1900 */
_declspec(dllexport) BOOL http_get_init(UDF_INIT *initid, UDF_ARGS *args, char *message);
_declspec(dllexport) char *http_get(UDF_INIT *initid, UDF_ARGS *args, char *result, unsigned long *length, char *is_null, char *error);
_declspec(dllexport) void http_get_deinit(UDF_INIT *initid);
_declspec(dllexport) BOOL http_post_init(UDF_INIT *initid, UDF_ARGS *args, char *message);
_declspec(dllexport) char *http_post(UDF_INIT *initid, UDF_ARGS *args, char *result, unsigned long *length, char *is_null, char *error);
_declspec(dllexport) void http_post_deinit(UDF_INIT *initid);
_declspec(dllexport) BOOL http_put_init(UDF_INIT *initid, UDF_ARGS *args, char *message);
_declspec(dllexport) char *http_put(UDF_INIT *initid, UDF_ARGS *args, char *result, unsigned long *length, char *is_null, char *error);
_declspec(dllexport) void http_put_deinit(UDF_INIT *initid);
_declspec(dllexport) BOOL http_delete_init(UDF_INIT *initid, UDF_ARGS *args, char *message);
_declspec(dllexport) char *http_delete(UDF_INIT *initid, UDF_ARGS *args, char *result, unsigned long *length, char *is_null, char *error);
_declspec(dllexport) void http_delete_deinit(UDF_INIT *initid);
static void *myrealloc(void *ptr, size_t size)
{
?? ?/* There might be a realloc() out there that doesn't like reallocing
?? ? ? NULL pointers, so we take care of it here */
?? ?if (ptr)
?? ??? ?return realloc(ptr, size);
?? ?else
?? ??? ?return malloc(size);
}
static size_t
result_cb(void *ptr, size_t size, size_t nmemb, void *data)
{
?? ?size_t realsize = size * nmemb;
?? ?struct st_curl_results *res = (struct st_curl_results *)data;
?? ?res->result = (char *)myrealloc(res->result, res->size + realsize + 1);
?? ?if (res->result)
?? ?{
?? ??? ?memcpy(&(res->result[res->size]), ptr, realsize);
?? ??? ?res->size += realsize;
?? ??? ?res->result[res->size] = 0;
?? ?}
?? ?return realsize;
}
/* ------------------------HTTP GET----------------------------- */
BOOL http_get_init(UDF_INIT *initid, UDF_ARGS *args, char *message)
{
?? ?st_curl_results *container;
?? ?if (args->arg_count != 1)
?? ?{
?? ??? ?strncpy(message,
?? ??? ??? ?"one argument must be supplied: http_get('<url>').",
?? ??? ??? ?MYSQL_ERRMSG_SIZE);
?? ??? ?return 1;
?? ?}
?? ?args->arg_type[0] = STRING_RESULT;
?? ?initid->max_length = CURL_UDF_MAX_SIZE;
?? ?container = (st_curl_results *)malloc(sizeof(st_curl_results));
?? ?initid->ptr = (char *)container;
?? ?return 0;
}
char *http_get(UDF_INIT *initid, UDF_ARGS *args,
?? ?char *result,
?? ?unsigned long *length,
?? ?char *is_null,
?? ?char *error)
{
?? ?CURLcode retref;
?? ?CURL *curl;
?? ?st_curl_results *res = (st_curl_results *)initid->ptr;
?? ?curl_global_init(CURL_GLOBAL_ALL);
?? ?curl = curl_easy_init();
?? ?res->result = NULL;
?? ?res->size = 0;
?? ?if (curl)
?? ?{
?? ??? ?curl_easy_setopt(curl, CURLOPT_URL, args->args[0]);
?? ??? ?curl_easy_setopt(curl, CURLOPT_WRITEFUNCTION, result_cb);
?? ??? ?curl_easy_setopt(curl, CURLOPT_WRITEDATA, (void *)res);
?? ??? ?curl_easy_setopt(curl, CURLOPT_USERAGENT, "mysql-udf-http/1.0");
?? ??? ?retref = curl_easy_perform(curl);
?? ??? ?if (retref) {
?? ??? ??? ?fprintf(stderr, "error\n");
?? ??? ??? ?if (res->result)
?? ??? ??? ??? ?strcpy(res->result, "");
?? ??? ??? ?res->size = 0;
?? ??? ?}
?? ?}
?? ?curl_easy_cleanup(curl);
?? ?*length = res->size;
?? ?return ((char *)res->result);
}
void http_get_deinit(UDF_INIT *initid)
{
?? ?/* if we allocated initid->ptr, free it here */
?? ?st_curl_results *res = (st_curl_results *)initid->ptr;
?? ?if (res->result)
?? ??? ?free(res->result);
?? ?free(res);
?? ?return;
}
/* ------------------------HTTP POST----------------------------- */
BOOL http_post_init(UDF_INIT *initid, UDF_ARGS *args, char *message)
{
?? ?st_curl_results *container;
?? ?if (args->arg_count != 2)
?? ?{
?? ??? ?strncpy(message,
?? ??? ??? ?"two arguments must be supplied: http_post('<url>','<data>').",
?? ??? ??? ?MYSQL_ERRMSG_SIZE);
?? ??? ?return 1;
?? ?}
?? ?args->arg_type[0] = STRING_RESULT;
?? ?initid->max_length = CURL_UDF_MAX_SIZE;
?? ?container = (st_curl_results *)malloc(sizeof(st_curl_results));
?? ?initid->ptr = (char *)container;
?? ?return 0;
}
char *http_post(UDF_INIT *initid, UDF_ARGS *args,
?? ?char *result,
?? ?unsigned long *length,
?? ? char *is_null,
?? ?char *error)
{
?? ?CURLcode retref;
?? ?CURL *curl;
?? ?st_curl_results *res = (st_curl_results *)initid->ptr;
?? ?curl_global_init(CURL_GLOBAL_ALL);
?? ?curl = curl_easy_init();
?? ?res->result = NULL;
?? ?res->size = 0;
?? ?if (curl)
?? ?{
?? ??? ?struct curl_slist *chunk = NULL;
?? ??? ?chunk = curl_slist_append(chunk, "Expect:");
?? ??? ?curl_easy_setopt(curl, CURLOPT_URL, args->args[0]);
?? ??? ?curl_easy_setopt(curl, CURLOPT_WRITEFUNCTION, result_cb);
?? ??? ?curl_easy_setopt(curl, CURLOPT_WRITEDATA, (void *)res);
?? ??? ?curl_easy_setopt(curl, CURLOPT_USERAGENT, "mysql-udf-http/1.0");
?? ??? ?curl_easy_setopt(curl, CURLOPT_HTTPHEADER, chunk);
?? ??? ?curl_easy_setopt(curl, CURLOPT_POSTFIELDS, args->args[1]);
?? ??? ?retref = curl_easy_perform(curl);
?? ??? ?if (retref) {
?? ??? ??? ?fprintf(stderr, "error\n");
?? ??? ??? ?if (res->result)
?? ??? ??? ??? ?strcpy(res->result, "");
?? ??? ??? ?res->size = 0;
?? ??? ?}
?? ?}
?? ?curl_easy_cleanup(curl);
?? ?*length = res->size;
?? ?return ((char *)res->result);
}
void http_post_deinit(UDF_INIT *initid)
{
?? ?/* if we allocated initid->ptr, free it here */
?? ?st_curl_results *res = (st_curl_results *)initid->ptr;
?? ?if (res->result)
?? ??? ?free(res->result);
?? ?free(res);
?? ?return;
}
/* ------------------------HTTP PUT----------------------------- */
BOOL http_put_init(UDF_INIT *initid, UDF_ARGS *args, char *message)
{
?? ?st_curl_results *container;
?? ?if (args->arg_count != 2)
?? ?{
?? ??? ?strncpy(message,
?? ??? ??? ?"two arguments must be supplied: http_put('<url>','<data>').",
?? ??? ??? ?MYSQL_ERRMSG_SIZE);
?? ??? ?return 1;
?? ?}
?? ?args->arg_type[0] = STRING_RESULT;
?? ?initid->max_length = CURL_UDF_MAX_SIZE;
?? ?container = (st_curl_results *)malloc(sizeof(st_curl_results));
?? ?initid->ptr = (char *)container;
?? ?return 0;
}
char *http_put(UDF_INIT *initid, UDF_ARGS *args,
?? ?char *result,
?? ?unsigned long *length,
?? ?char *is_null,
?? ?char *error)
{
?? ?CURLcode retref;
?? ?CURL *curl;
?? ?st_curl_results *res = (st_curl_results *)initid->ptr;
?? ?curl_global_init(CURL_GLOBAL_ALL);
?? ?curl = curl_easy_init();
?? ?res->result = NULL;
?? ?res->size = 0;
?? ?if (curl)
?? ?{
?? ??? ?struct curl_slist *chunk = NULL;
?? ??? ?chunk = curl_slist_append(chunk, "Expect:");
?? ??? ?curl_easy_setopt(curl, CURLOPT_URL, args->args[0]);
?? ??? ?curl_easy_setopt(curl, CURLOPT_WRITEFUNCTION, result_cb);
?? ??? ?curl_easy_setopt(curl, CURLOPT_WRITEDATA, (void *)res);
?? ??? ?curl_easy_setopt(curl, CURLOPT_USERAGENT, "mysql-udf-http/1.0");
?? ??? ?curl_easy_setopt(curl, CURLOPT_HTTPHEADER, chunk);
?? ??? ?curl_easy_setopt(curl, CURLOPT_CUSTOMREQUEST, "PUT");
?? ??? ?curl_easy_setopt(curl, CURLOPT_POSTFIELDS, args->args[1]);
?? ??? ?retref = curl_easy_perform(curl);
?? ??? ?if (retref) {
?? ??? ??? ?fprintf(stderr, "error\n");
?? ??? ??? ?if (res->result)
?? ??? ??? ??? ?strcpy(res->result, "");
?? ??? ??? ?res->size = 0;
?? ??? ?}
?? ?}
?? ?curl_easy_cleanup(curl);
?? ?*length = res->size;
?? ?return ((char *)res->result);
}
void http_put_deinit(UDF_INIT *initid)
{
?? ?/* if we allocated initid->ptr, free it here */
?? ?st_curl_results *res = (st_curl_results *)initid->ptr;
?? ?if (res->result)
?? ??? ?free(res->result);
?? ?free(res);
?? ?return;
}
/* ------------------------HTTP DELETE----------------------------- */
BOOL http_delete_init(UDF_INIT *initid, UDF_ARGS *args, char *message)
{
?? ?st_curl_results *container;
?? ?if (args->arg_count != 1)
?? ?{
?? ??? ?strncpy(message,
?? ??? ??? ?"one arguments must be supplied: http_delete('<url>').",
?? ??? ??? ?MYSQL_ERRMSG_SIZE);
?? ??? ?return 1;
?? ?}
?? ?args->arg_type[0] = STRING_RESULT;
?? ?initid->max_length = CURL_UDF_MAX_SIZE;
?? ?container = (st_curl_results *)malloc(sizeof(st_curl_results));
?? ?initid->ptr = (char *)container;
?? ?return 0;
}
char *http_delete(UDF_INIT *initid, UDF_ARGS *args,
?? ?char *result,
?? ?unsigned long *length,
?? ?char *is_null,
?? ?char *error)
{
?? ?CURLcode retref;
?? ?CURL *curl;
?? ?st_curl_results *res = (st_curl_results *)initid->ptr;
?? ?curl_global_init(CURL_GLOBAL_ALL);
?? ?curl = curl_easy_init();
?? ?res->result = NULL;
?? ?res->size = 0;
?? ?if (curl)
?? ?{
?? ??? ?curl_easy_setopt(curl, CURLOPT_URL, args->args[0]);
?? ??? ?curl_easy_setopt(curl, CURLOPT_WRITEFUNCTION, result_cb);
?? ??? ?curl_easy_setopt(curl, CURLOPT_WRITEDATA, (void *)res);
?? ??? ?curl_easy_setopt(curl, CURLOPT_USERAGENT, "mysql-udf-http/1.0");
?? ??? ?curl_easy_setopt(curl, CURLOPT_CUSTOMREQUEST, "DELETE");
?? ??? ?retref = curl_easy_perform(curl);
?? ??? ?if (retref) {
?? ??? ??? ?fprintf(stderr, "error\n");
?? ??? ??? ?if (res->result)
?? ??? ??? ??? ?strcpy(res->result, "");
?? ??? ??? ?res->size = 0;
?? ??? ?}
?? ?}
?? ?curl_easy_cleanup(curl);
?? ?*length = res->size;
?? ?return ((char *)res->result);
}
void http_delete_deinit(UDF_INIT *initid)
{
?? ?/* if we allocated initid->ptr, free it here */
?? ?st_curl_results *res = (st_curl_results *)initid->ptr;
?? ?if (res->result)
?? ??? ?free(res->result);
?? ?free(res);
?? ?return;
}
使用vs编译过程中可能出现以下错误
需要引用的库文件有
??编译成功之后将?MySQLUdfHttpWin.dll 拷到mysql8 插件目录?
然后在navicat中进行注册 自定义函数
注册之后运行结果如下
?HTTP_GET
?HTTP_POST
