所以我创建了一个接受 2 个字符串并连接它们的 UDF。

我的 UDF://concat_kv.c

#include <my_global.h>
#include <my_sys.h>
#include <mysql.h>
#include <ctype.h>
#include <string.h>
#include <stdlib.h>

typedef unsigned long ulong;

my_bool concat_kv_init(UDF_INIT *initid, UDF_ARGS *args, char *message) {
    if(args->arg_count != 2 || args->arg_type[0] != STRING_RESULT || args->arg_type[1] != STRING_RESULT) {
        strcpy(message, "concat_kv(): Requires 2 string parameters: Key - Value.");
        return 1;
    }
    return 0;
}

char *concat_kv(UDF_INIT *initid, UDF_ARGS *args, char *result, ulong *length, char *is_null, char *error) {
    char *key = (char*)calloc(strlen(args->args[0]), sizeof(char));
    char *value = (char*)calloc(strlen(args->args[1]), sizeof(char));
    char *res = (char *)calloc(strlen(args->args[0]) + strlen(args->args[1]) + 2, sizeof(char));
    int len = strlen(args->args[0]) + strlen(args->args[1]) + 2;
    key = args->args[0];
    value = args->args[1];
    strcat(res, key);
    strcat(res, " ");
    strcat(res, value);
    res[len-1] = '\0'; // Terminating character...
    return res;
}

void concat_kv_deinit(UDF_INIT *initid) {
}

编译文件为:

gcc $(mysql_config --cflags) -shared concat_kv.c -o concat_kv.so  

将 concat_kv.so 文件移动到 /usr/lib/mysql/plugins/。

在 mysql 中创建函数为:

CREATE FUNCTION concat_kv RETURNS STRING SONAME 'concat_kv.so';  

然后做:

SELECT concat_kv("Aditya", "Singh") as conc;

预期输出:

| conc |
--------
| "Aditya Singh" |

但是得到了意想不到的输出:

mysql> SELECT concat_kv("Aditya", "Singh") as conc;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| conc 
|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Aditya Singh            �T 
|+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

我在连接字符串后得到一些不可打印的东西。一些垃圾值附加在字符串之后。

最佳答案

您的 concat_kv 函数应该看起来更像这样:

char *concat_kv(UDF_INIT *initid, UDF_ARGS *args, char *result, ulong *length, char *is_null, char *error) {
    char *key = args->args[0];
    char *value = args->args[1];
    strcpy(result, key);
    strcat(result, " ");
    strcat(result, value);
    *length = strlen(result);
    return result;
}

一些说明:

• 结果参数应该用于结果

• 应返回长度

• 长度应该是没有终止空字符，见这里:https://dev.mysql.com/doc/refman/8.0/en/udf-return-values.html

• key 和 value 只是从 args 中使用并通过 strcpy/strcat 复制到结果中

演示

动态内存分配

正如评论中已经指出的那样，如果超出预分配缓冲区的大小，则存在缓冲区溢出的危险。

内存泄漏

如果我们要在 concat_kv() 中动态分配内存，我们会引入内存泄漏，因为每次调用用户定义的函数时都会请求内存，并且永远不会再次释放。

解决方案是使用concat_kv_init()和concat_kv_deinit()函数，MySQL在concat_kv()调用前后直接调用这两个函数。

这里引用 MySQL 文档:

MySQL passes a buffer to the xxx() function using the result parameter. This buffer is sufficiently long to hold 255 characters, which can be multibyte characters. The xxx() function can store the result in this buffer if it fits, in which case the return value should be a pointer to the buffer. If the function stores the result in a different buffer, it should return a pointer to that buffer.

If your string function does not use the supplied buffer (for example, if it needs to return a string longer than 255 characters), you must allocate the space for your own buffer with malloc() in your xxx_init() function or your xxx() function and free it in your xxx_deinit() function.

参见 https://dev.mysql.com/doc/refman/8.0/en/udf-return-values.html

动态内存分配示例

所以如果我们不能保证结果的大小大于预分配的缓冲区，我们需要在 concat_kv_init() 中分配内存并在 concat_kv_deinit() 中释放它。

my_bool concat_kv_init(UDF_INIT *initid, UDF_ARGS *args, char *message) {
    if(args->arg_count != 2 || args->arg_type[0] != STRING_RESULT || args->arg_type[1] != STRING_RESULT) {
        strcpy(message, "concat_kv(): Requires 2 string parameters: Key - Value.");
        return 1;
    }
    ulong length = strlen(args->args[0]) + strlen(args->args[1]) + 2;
    initid->ptr = (char *)malloc(length);
    return 0;
}

char *concat_kv(UDF_INIT *initid, UDF_ARGS *args, char *result, ulong *length, char *is_null, char *error) {
    char *key = args->args[0];
    char *value = args->args[1];
    strcpy(initid->ptr, key);
    strcat(initid->ptr, " ");
    strcat(initid->ptr, value);
    *length = strlen(initid->ptr);
    return initid->ptr;
}

void concat_kv_deinit(UDF_INIT *initid) {
    free(initid->ptr);
    initid->ptr = NULL;
}

关于MySQL UDF 使用附加到返回字符串的垃圾字符进行响应，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/56030546/