mysql udf json_mysqludf_json将关系数据以JSON编码

sphinx2.1版本引入了json属性，该如何构建json格式数据呢？mysql触发器与gearman结合应用时，只能向gearman worker传递单一的字符串参数，如果要将mysql表中的某些列的数据传递给worker处理呢？这些问题，在我工作当中遇到过的，后面再一一整理出来与大家分享。那么，这样将数据以json格式编码呢，下面先介绍下lib_mysqludf_json，后续会使用到这个UDF。

lib_mysqludf_json UDF库函数将关系数据映射为JSON格式。

通常，数据库中的数据映射为JSON格式，是通过程序来转换的。比如，PHP的json扩展，将PHP数据结构转换为JSON，这通常是需要遍历整个结果集后来创建JSON格式。使用UDF来进行JSON编码更快。

1. 通常用法说明

JSON UDFs在设计时考虑到在大部分情况下，只有一个简单和直接的映射是必须的。因此，JSON UDFs尽量简单的映射，尽量少的开销。不过，也能产生更复杂的映射。

1.1 lib_mysqludf_json提供的功能有：

1). 可变长度的参数列表允许直接映射

在很多情况下， 需要映射行级别数据为JSON数组或JSON对象。可变参数列表允许单一方法调用来接受一个完整的项目，随后被映射为JSON结构。

2). 字符串数据的正确转义

字符串值映射到JSON字符串时，自动转义以符合JSON语法。必要时，绕过自动专业行为是非常简单的。

3). 一致的NULL处理

根据数据类型对数据库NULL值进行处理。字符串的NULL值映射到javascript null，数字的NULL值映射到javascript NaN。

4). 表达式自动映射成JSON对象成员

通常情况下， 从关系型数据创建JSON对象时， 很完美的使用列名来作为对象成员名称。 在这种情况下， 不需要明确提供成员名称。 该函数简单的复用参数表达式的文本。同时，很容易的将成员映射到一个自定义的名称。

5). JSON成员名称基本的有效性检查

常量对象成员的名称检查，是否是有效的JSON成员名称。

6). 支持嵌套的JSON结构

某些情况下，需要一个嵌套的JSON对象和/或数组。通过情况下，由一个内部函数返回的一个字符串结果会被外部函数转义。当使用UDF嵌套的JSON时，并非如此。

2. 字符串数据的转义和引用

参数字符串通常自动的映射到JSON双引号的字符串。字符串值转义成一个有效的JSON值。下面的字符需要转义：

“转义成\"

换行转义成\n

回车转义成\r

\转义成\

3. 表达式映射到对象成员

为了尽可能容易的将行数据映射成JSON对象，json_object函数将每个参数映射成一个成员。表达式文本作为成员名称，值作为成员值。这可以毫不费力的处理许多常见的情况。

然而，这并不是说json_object函数盲目的使用表达式文本。除了少数例外，需要对表达式进行检查，是否是有效的JSON成员名称。在有些情况下，使用修改后表达式文本。如aTableAlias.columnName改为columnName.

总是可以覆盖自动成员名称映射，这是通过内部函数调用一个包含别名的表达式。

4. 函数

lib_mysqludf_json库的目的是提供了一系列将关系数据映射到JSON格式函数。当前支持的函数有：

1). json_array：映射可变数目参数到JSON数组

2). json_members：映射可变数目的name-value对到JSON对象成员

3). json_object：映射可变数目的参数到JSON对象

4). json_values：映射可变数目的参数到JSON值

使用lib_mysqludf_json_info()来获取当前安装的lib_mysqludf_json信息。

下面分别介绍这四个函数：

4.1 json_array([arg1,..,argN])

json_array接受任何类型的参数，返回一个字符串。

4.1.1 [arg1,..,argN]

可以是任何数据类型，包含空。

1. 数值类型映射到十进制数

2. 字符串、位、时间类型映射到双引号的字符串

3. 对数字类型NULL值，映射为NaN。对字符串类型NULL值，映射为null

4. 参数列表可以为空，这种情况下，返回一个空字符串，也就是一个空数组

4.1.2 返回

求javascript表达式的数组值

4.1.3 安装

# wget https://github.com/mysqludf/lib_mysqludf_json/archive/master.zip

# unzip master.zip

# cd lib_mysqludf_json-master

# mv lib_mysqludf_json.so  lib_mysqludf_json.so_bak

# gcc $(/usr/local/mysql5.6/bin/mysql_config --cflags) -shared -fPIC -o lib_mysqludf_json.so lib_mysqludf_json.c

# cp lib_mysqludf_json.so /usr/local/mysql5.6/lib/plugin/

# mysql -uroot -p -h127.0.0.1

mysql> CREATE FUNCTION json_array RETURNS STRING SONAME 'lib_mysqludf_json.so';

Query OK, 0 rows affected (0.00 sec)

1

2

3

4

5

6

7

8

9

# wget https://github.com/mysqludf/lib_mysqludf_json/archive/master.zip

# unzip master.zip

# cd lib_mysqludf_json-master

# mv lib_mysqludf_json.so  lib_mysqludf_json.so_bak

# gcc $(/usr/local/mysql5.6/bin/mysql_config --cflags) -shared -fPIC -o lib_mysqludf_json.so lib_mysqludf_json.c

# cp lib_mysqludf_json.so /usr/local/mysql5.6/lib/plugin/

# mysql -uroot -p -h127.0.0.1

mysql>CREATEFUNCTIONjson_arrayRETURNSSTRINGSONAME'lib_mysqludf_json.so';

QueryOK,0rowsaffected(0.00sec)

lib_mysqludf_json位数一定要和mysql一致。否则会报下面错误：

ERROR 1126 (HY000): Can't open shared library 'lib_mysqludf_json.so' (errno: 11 /usr/local/mysql5.6/lib/plugin/lib_mysqludf_json.so: wrong ELF class: ELFCLASS32)

https://github.com/mysqludf/lib_mysqludf_json提供的是32位的。

查看是否安装成功：

mysql> select * from mysql.func;

+------------+-----+----------------------+----------+

| name | ret | dl | type |

+------------+-----+----------------------+----------+

| json_array | 0 | lib_mysqludf_json.so | function |

+------------+-----+----------------------+----------+

1 row in set (0.00 sec)

1

2

3

4

5

6

7

mysql>select *frommysql.func;

+------------+-----+----------------------+----------+

|name|ret|dl|type|

+------------+-----+----------------------+----------+

|json_array|0|lib_mysqludf_json.so|function|

+------------+-----+----------------------+----------+

1rowinset(0.00sec)

卸载该函数：

mysql> DROP FUNCTION json_array;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from mysql.func;

Empty set (0.00 sec)

1

2

3

4

5

mysql>DROPFUNCTIONjson_array;

QueryOK,0rowsaffected(0.00sec)

mysql>select *fr