mysql触发器+mysql_udf_http

注意--libdir  和 --with-mysql 必须要写正确
--libdir 如果不知道，可以直接登陆mysql 直接去创建mysql自定义函数,会报错，错误信息里面有路径信息,copy过来就可以了


1.安装curl和mysql-udf-http


yum install libcurl
yum install curl-devel
./configure --prefix=/usr/local/mysqludf --with-mysql=/usr/local/mysql/bin/mysql_config --libdir=/usr/local/mysql/lib/mysql/plugin/
make  && make install
2.通过命令行登陆进入MySQL
/usr/local/webserver/mysql/bin/mysql -S /tmp/mysql.sock


3. 创建MySQL自定义函数
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';  


4. 使用方法
I. 函数描述：


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


II. 示例 A：
/* 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:your_password@api.t.sina.com.cn/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');




TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2) //去除指定位置的指定字符
UCASE (string2 ) //转换成大写
RIGHT(string2,length) //取string2最后length个字符
SPACE(count) //生成count个空格


二、数值类型
TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2) //去除指定位置的指定字符
UCASE (string2 ) //转换成大写
RIGHT(string2,length) //取string2最后length个字符
SPACE(count) //生成count个空格


ABS (number2 ) //绝对值
BIN (decimal_number ) //十进制转二进制
CEILING (number2 ) //向上取整
CONV(number2,from_base,to_base) //进制转换
FLOOR (number2 ) //向下取整
FORMAT (number,decimal_places ) //保留小数位数
HEX (DecimalNumber ) //转十六进制
注：HEX()中可传入字符串，则返回其ASC-11码，如HEX(’DEF’)返回4142143
也可以传入十进制整数，返回其十六进制编码，如HEX(25)返回19
LEAST (number , number2 [,..]) //求最小值
MOD (numerator ,denominator ) //求余
POWER (number ,power ) //求指数
RAND([seed]) //随机数
ROUND (number [,decimals ]) //四舍五入,decimals为小数位数]


select FROM_UNIXTIME(1463456236); 显示当前时间
select UNIX_TIMESTAMP('2016-01-01'); 显示当前时间






触发器脚本  51


DELIMITER $$;


DROP TRIGGER IF EXISTS `insert`;CREATE DEFINER=`root`@`localhost` TRIGGER `insert` BEFORE INSERT ON `CHATLOG` FOR EACH ROW BEGIN


DECLARE info varchar(5012) DEFAULT '';
DECLARE dtime int(11) DEFAULT 0;
DECLARE flag varchar(64)  DEFAULT '';
DECLARE param varchar(10240)  DEFAULT '';
DECLARE result varchar(32)  DEFAULT '';


set info = concat(51,'|',NEW.CHARID,'|',NEW.ROLENAME,'|',NEW.ACCNAME,'|',1,'|',1,'|',NEW.IP,'|',NEW.CHATTIME,'|','私聊','|',NEW.CONTENT   );
set dtime = unix_timestamp( now());
set flag = UCASE( md5( concat('gamewszzl','info',info,'site51game','time',   dtime   ,'5DpCa7tUMY' )));
set param = concat('game=wszzl&site=51game&time=',dtime,'&info=',info,'&flag=',flag );
set result = http_post('http://gameapi.51.com/chatlog/monitor',param);
 
END
$$;