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位的。
查看是否安装成功:
1
2
3
4
5
6
7
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
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 实例
创建一张表:
1
2
3
4
5
6
7
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`)
-> );
插入数据:
1
2
mysql> insert into `ttlsa_users` values (888,'ttlsa_admin',md5('websiteadmin'),null);
Query OK, 1 row affected (0.00 sec)
创建数组:
1
2
3
4
5
6
7
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)
创建嵌套数组:
1
2
3
4
5
6
7
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)
遇到特殊字符会自动转义:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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_前缀来避免,如下所示:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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)