给MySQL增加mysql-udf-http和mysql-udf-json自定义函数,让MySQL有调用http接口和查询直接回JSON的能力
1、安装mysql-udf-http
yum install -y libcurl*
下载地址:http://pan.baidu.com/s/1nuYZqR3
tar zxvf mysql-udf-http-1.0.tar.gz
cd mysql-udf-http-1.0
./configure --prefix=/usr/local/mysql-udf-http --with-mysql=/usr/local/mysql/bin/mysql_config
make && make install
ln -s /usr/local/mysql-udf-http/lib/mysql-udf-http.so.0.0.0 /usr/local/mysql/lib/plugin/mysql-udf-http.so
service mysql restart
#删除
DROP FUNCTION IF EXISTS http_get;
DROP FUNCTION IF EXISTS http_post;
DROP FUNCTION IF EXISTS http_put;
DROP FUNCTION IF EXISTS http_delete;
#创建
create function http_get returns string soname 'mysql-udf-http.so';
create function http_post returns string soname 'mysql-udf-http.so';
create function http_put returns string soname 'mysql-udf-http.so';
create function http_delete returns string soname 'mysql-udf-http.so';
实例:
select http_get('http://10.10.3.199/dsideal_yy/kgdxpr') as res;
select http_post('http://10.10.3.199/dsideal_yy/kgdxpr','id=1&type=a') as res;
2、安装mysql-udf-json
下载地址:http://pan.baidu.com/s/1pLtnXDT
unzip lib_mysqludf_json-master.zip
cd lib_mysqludf_json-master
gcc $(mysql_config --cflags) -shared -fPIC -o lib_mysqludf_json.so lib_mysqludf_json.c
cp lib_mysqludf_json.so /usr/local/mysql/lib/plugin/
#删除
DROP FUNCTION IF EXISTS lib_mysqludf_json_info;
DROP FUNCTION IF EXISTS json_array;
DROP FUNCTION IF EXISTS json_members;
DROP FUNCTION IF EXISTS json_object;
DROP FUNCTION IF EXISTS json_values;
#创建
create function lib_mysqludf_json_info returns string soname 'lib_mysqludf_json.so';
create function json_array returns string soname 'lib_mysqludf_json.so';
create function json_members returns string soname 'lib_mysqludf_json.so';
create function json_object returns string soname 'lib_mysqludf_json.so';
create function json_values returns string soname 'lib_mysqludf_json.so';
实例:
1、select json_object(login_name as user,login_password as pwd) as user from t_sys_loginperson;
返回:
{"user":"admin","pwd":"8aff8dd13eeecabe03830865bfa1b2b8"}
{"user":"sch000001","pwd":"e10adc3949ba59abbe56e057f20f883e"}
{"user":"stu000001","pwd":"e10adc3949ba59abbe56e057f20f883e"}
2、select json_array(login_name,login_password) as user from t_sys_loginperson;
返回:
["admin","8aff8dd13eeecabe03830865bfa1b2b8"]
["sch000001","e10adc3949ba59abbe56e057f20f883e"]
["stu000001","e10adc3949ba59abbe56e057f20f883e"]