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

最新推荐文章于 2021-02-04 23:56:43 发布

南京周润发

最新推荐文章于 2021-02-04 23:56:43 发布

阅读量140

点赞数

文章标签： mysql udf json

本文链接：https://blog.csdn.net/weixin_30610771/article/details/113280437

版权

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

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 安装

mysql> show variables like '%plugin%';

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

| Variable_name | Value |

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

| plugin_dir | /usr/lib64/mysql/plugin |

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

[root@phicomm-sd-2 plugin]# pwd

/usr/lib64/mysql/plugin

[root@phicomm-sd-2 plugin]# cp/home/wenjuan.zhang/lib_mysqludf_json-x86_64/lib_mysqludf_json.so .

[root@phicomm-sd-2 plugin]# mysql -uroot –p

mysql> create function json_array returns string soname'lib_mysqludf_json.so';

Query OK, 0 rows affected (0.00 sec)

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

mysql> create function lib_mysqludf_json_info returns string soname'lib_mysqludf_json.so';

ERROR 1126 (HY000): Can't open shared library'lib_mysqludf_json.so' (errno: 22 /usr/lib64/mysql/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 |

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

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

1 row in set (0.00 sec)

卸载该函数：

mysql> DROP FUNCTION json_array;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from mysql.func;

Empty set (0.00 sec)

4.1.4实例

创建一张表：

mysql> create table `ttlsa_users` (

-> `uid` int(11) unsigned,

-> `username` varchar(40) NOT NULL,

-> `password` varchar(40) NOT NULL,

-> `createtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

-> PRIMARY KEY (`uid`)

-> );

插入数据：

mysql> insert into `ttlsa_users` values (888,'ttlsa_admin',md5('websiteadmin'),null);

Query OK, 1 row affected (0.00 sec)

创建数组：

mysql> select json_array(uid,username,password,createtime) as userinfo from ttlsa_users;

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

| userinfo |

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

| [888,"ttlsa_admin","6a6e41c9b741f740cfa5f266b249d452","2013-08-10 11:04:00"] |

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

1 row in set (0.01 sec)

创建嵌套数组：

mysql> select json_array(uid,json_array(username,password),createtime) as userinfo from ttlsa_users;

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

| userinfo |

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

| [888,["ttlsa_admin","6a6e41c9b741f740cfa5f266b249d452"],"2013-08-10 11:04:00"] |

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

1 row in set (0.00 sec)

遇到特殊字符会自动转义：

mysql> select json_array('\"\"');

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

| json_array('\"\"') |

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

| ["\"\""] |

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

1 row in set (0.00 sec)

mysql> select json_array('""');

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

| json_array('""') |

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

| ["\"\""] |

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

1 row in set (0.00 sec)

有时候，需要避免这种自动转义，直接将原数据存入到数据库，可以使用json_前缀来避免，如下所示：

mysql> select json_array('""'json_);

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

| json_array('""'json_) |

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

| [""] |

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

1 row in set (0.00 sec)

mysql> select json_array('\"\"' json_);

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

| json_array('\"\"' json_) |

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

| [""] |

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

1 row in set (0.00 sec)

当表达式自身已经是以json_开头的名字，那么json_别名是不需要的。如，如果有一个列名为json_data的表，那么该列的值将不会转义。如果真希望它转义，可以使用一个除json_外的任何列名。如下所示：

mysql>update ttlsa_users set json_data='\\website\\ - "http://www.ttlsa.com"' where uid=888;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from ttlsa_users;

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

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

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

1 row in set (0.00 sec)

mysql> select json_array(json_data) as userinfo from ttlsa_users;

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

| userinfo |

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

| [\website\ - "http://www.ttlsa.com"] |

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

1 row in set (0.00 sec)

可以看到”和\没有转义。

mysql> select json_array(json_data xuhh) as userinfo from ttlsa_users;

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

| userinfo |

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

| ["\\website\\ - \"http://www.ttlsa.com\""] |

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

1 row in set (0.00 sec)

可以看到“和\都转义了。直接的说就是定义别名。

4.2 json_object([arg1,..,argN])

json_object接受可变数目的参数，并将它们映射到JSON值，并返回一个关联数组。

4.2.1 [arg1,..,argN]值可以是任何类型包括空。表达式文本作为成员名称，也就是键。目的是很容易的将纯表中的数据转换成JSON对象。表达式会被检查以确认产生的是一个有效的javascript标识符。

有时，使用表达式作为成员名称并不恰当，可以使用别名。

4.2.2返回

求javascript表达式的javascript值(关联数组)

4.2.3 安装

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

Query OK, 0 rows affected (0.00 sec)

查看：

mysql> select * from mysql.func;

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

| name | ret | dl | type |

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

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

2 rows in set (0.00 sec)

卸载：

mysql> DROP FUNCTION json_object;

4.2.4 实例

mysql> select json_object(uid, username as name, password as pw, createtime) from ttlsa_users;

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

| json_object(uid, username as name, password as pw, createtime) |

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

| {"uid":888,"name":"ttlsa_admin","pw":"6a6e41c9b741f740cfa5f266b249d452","createtime":"2013-08-10 11:27:01"} |

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

1 row in set (0.00 sec)

mysql> select json_object(uid as "uid",username as "name",password as "pw",createtime) as user from ttlsa_users;

ERROR 1123 (HY000): Can't initialize function 'json_object'; Invalid json member name - name cannot be empty

以上错误这样解决，给每个成员名称使用别名即可：

mysql> select json_object(uid as uid,username as name,password as pw,createtime as createtime) as user from ttlsa_users;

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

| user |

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

| {"uid":888,"name":"ttlsa_admin","pw":"6a6e41c9b741f740cfa5f266b249d452","createtime":"2013-09-10 09:58:23"} |

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

1 row in set (0.00 sec)

json_object可以与json_members结合使用。

4.3 json_members(name1,value1[,..,..,nameN,valueN])该函数可以用来对任意的名称设置name-value对的JSON对象成员列表。

自动映射不会处理嵌套的对象，说白了就是关联数组的关联数组，如array(‘website’=>array(‘网址’=>’www.ttlsa.com’,'QQ群’=>39514058)),json_members相当于’website’，json_object相当于array(‘网址’=>’www.ttlsa.com’,'QQ群’=>39514058)。

json_members函数的目的是方便创建嵌套的JSON对象。

4.3.1 name1,value1[,..,..,nameN,valueN]

一系列的name-value对。name作为JSON对象成员的成员名称，value是该成员的值。

4.3.2返回

以逗号分割的JSON关联值的JSON成员名称。

如果名称是一个常量表达式，需要检查是否是有效的JSON成员名称。如果名称不是常量，不执行检查。

4.3.3 安装

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

Query OK, 0 rows affected (0.00 sec)

查看：

mysql> select * from mysql.func;

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

| name | ret | dl | type |

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

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

3 rows in set (0.00 sec)

卸载：

mysql> DROP FUNCTION json_members;

4.3.4 实例

mysql> select json_object(uid,json_members('user_info',json_object(username as name)),json_data) from ttlsa_users;

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

| json_object(uid,json_members('user_info',json_object(username as name)),json_data) |

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

| {"uid":888,"user_info":{"name":"ttlsa_admin"},\website\ - "http://www.ttlsa.com"} |

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

1 row in set (0.00 sec)

再给大家附上lib_mysqludf_json——64bit和32bit的类库。

lib_mysqludf_json-x86_64.rar

(4.9 KB, 下载次数: 1, 售价: 2 pub币)

2013-9-10 11:01 上传

点击文件名下载附件

lib_mysqludf_json

售价: 2 pub币 [记录]

lib_mysqludf_json-master_32.zip

(22.92 KB, 下载次数: 0, 售价: 2 pub币)

2013-9-10 10:59 上传

点击文件名下载附件

mysqludf_json

售价: 2 pub币 [记录]

[购买]

南京周润发

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql udf json_mysqludf_json将关系数据以JSON编码(一)

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

扫一扫