mysql-udf安装

• 一、mysql-udf-http
  • 安装cur
  • 安装mysql-udf-http
  • 创建
  • 删除
  • 验证是否安装成功
  • Description
  • 实例
• 二、mysql-udf-json
  • 创建
  • 删除
  • 验证是否安装成功
  • 实例
  • 创建触发器

一、mysql-udf-http

安装cur

yum install -y libcurl*

• 或者

yum install -y curl*

安装mysql-udf-http

项目网址：http://code.google.com/p/mysql-udf-http/

中文说明：http://blog.zyan.cc/mysql-udf-http/

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

• 如果提示configure: error: no acceptable C compiler found in $PATH需要安装GCC编译器

yum install -y gcc

• 如果没有mysql_config请执行以下命令

yum install -y mysql-devel

• 搜索mysql-udf-http.so文件位置

find / -name mysql-udf-http.so

由于mysql-udf-http.so不在mysql/lib/plugin/目录下，所以需要创建软连接或者复制过去

• 创建软连接

ln -s /usr/local/mysql-udf-http/lib/mysql-udf-http.so /usr/local/mysql/lib/plugin/mysql-udf-http.so

• 或者复制

cp /usr/local/mysql-udf-http/lib/mysql-udf* /usr/local/mysql/lib/plugin/

• 重启mysql

service mysqld restart

• 或者

systemctl restart mysqld.service

创建

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';

删除

DROP FUNCTION IF EXISTS http_get;
DROP FUNCTION IF EXISTS http_post;
DROP FUNCTION IF EXISTS http_put;
DROP FUNCTION IF EXISTS http_delete;

验证是否安装成功

select * from mysql.func; 

Description

SELECT http_get('<url>');
SELECT http_post('<url>', '<data>');
SELECT http_put('<url>', '<data>');
SELECT http_delete('<url>');

实例

/* HTTP GET、POST方式提交关键词“xoyo”到百度移动搜索 */  
SELECT http_get('http://m.baidu.com/s?word=xoyo&pn=0');  
SELECT http_post('http://m.baidu.com/s','word=xoyo&pn=0');  
  
/* 新浪微博开放平台：获取新浪用户ID为103500的最近一条微博内容 */  
SELECT http_get('http://api.t.sina.com.cn/statuses/user_timeline/103500.json?count=1&source=1561596835') AS data;  
/* 新浪微博开放平台：发表一条微博 */  
SELECT http_post('http://your_sina_uid:[email protected]/statuses/update.xml?source=1561596835', 'status=Thins is sina weibo test information');  
  
/* Tokyo Tyrant 写入、读取、删除操作 */  
SELECT http_put('http://192.168.8.34:1978/key', 'This is value');  
SELECT http_get('http://192.168.8.34:1978/key');  
SELECT http_delete('http://192.168.8.34:1978/key');  

二、mysql-udf-json

wget https://github.com/claer-ding/lib_mysqludf_json/archive/2013.zip
unzip 2013.zip
cd lib_mysqludf_json-2013
gcc $(/usr/local/mysql/bin/mysql_config --cflags) -shared -fPIC -o lib_mysqludf_json.so lib_mysqludf_json.c
cp lib_mysqludf_json.so /usr/local/mysql/lib/plugin/

创建

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';

删除

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;

验证是否安装成功

select * from mysql.func; 

实例

• 返回json对象

select json_object(login_name as user,login_password as pwd) as user from t_sys_loginperson;

• 返回json数组

select json_array(login_name,login_password) as user from t_sys_loginperson;

创建触发器

/* INSERT插入操作的触发器 */  
/*开头将结束符号定义为|*/
DELIMITER |  
DROP TRIGGER IF EXISTS mytable_insert;  
CREATE TRIGGER mytable_insert  
AFTER INSERT ON mytable  
FOR EACH ROW BEGIN  
    SET @tt_json = (SELECT json_object(id,addtime,title) FROM mytable WHERE id = NEW.id LIMIT 1);  
    SET @tt_resu = (SELECT http_put(CONCAT('http://192.168.8.34:1978/', NEW.id), @tt_json));  
/*使用|结束*/
END |  
/*最后使用DELIMITER ; 将结束符号还原*/
DELIMITER ;  

/* UPDATE更新操作的触发器 */  
DELIMITER |  
DROP TRIGGER IF EXISTS mytable_update;  
CREATE TRIGGER mytable_update  
AFTER UPDATE ON mytable  
FOR EACH ROW BEGIN
	/*判断表中字段新数据与旧数据变化*/
	if new.status<>old.status then
    SET @tt_json = (SELECT json_object(id,addtime,title) FROM mytable WHERE id = OLD.id LIMIT 1);  
    SET @tt_resu = (SELECT http_get(CONCAT('http://192.168.8.34:1978/', OLD.id), @tt_json));  
	/*结束判断*/
	end if;
END |  
DELIMITER ;  

/* DELETE删除操作的触发器 */  
DELIMITER |  
DROP TRIGGER IF EXISTS mytable_delete;  
CREATE TRIGGER mytable_delete  
AFTER DELETE ON mytable  
FOR EACH ROW BEGIN  
    SET @tt_resu = (SELECT http_delete(CONCAT('http://192.168.8.34:1978/', OLD.id)));  
END |  
DELIMITER ;