修改mysql触发redis_mysql触发器调用udf同步redis使用实例

﹏ヽ暗。殇╰゛Y 2023-01-11 08:17 67阅读 0赞

BEGIN

\#检测是否有sql异常

declare \_row,\_err,\_count int default 0;

DECLARE CONTINUE  HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND set \_err=1;

\#new.user\_token!=old.user\_token||  去掉改行，登陆修改token时，不调用触发器，当登陆后再调用注册设备时再调用触发器

\#判断以下字段修改时才同步数据

if new.user\_avatar!=old.user\_avatar||

new.user\_mobile!=old.user\_mobile||

\#new.user\_token!=old.user\_token||

new.near\_msg\_notice!=old.near\_msg\_notice||

new.duty\_msg\_notice!=old.duty\_msg\_notice||

new.system\_msg\_notice!=old.system\_msg\_notice||

new.passenger\_route\_notice!=old.passenger\_route\_notice||

new.passenger\_route\_notice!=old.passenger\_route\_notice then

set @json=(SELECT json\_object(userId,age,score,exp,level,bankBind,disturb,disturbTime,userAvatar,userMobile,userToken,nearMsgNotice,dutyMsgNotice,systemMsgNotice,passengerRouteNotice,routeMsgNotice,appversion,deviceToken,platform,deviceModel,status,identifer)

from (

SELECT u.user\_id as userId,ui.user\_age as age,ui.user\_score as score,ui.user\_exp as exp,ui.user\_level as level,ui.user\_bank\_bind as bankBind,ui.user\_disturb as disturb,ui.user\_disturb\_time as disturbTime,\`user\_avatar\` as userAvatar,user\_mobile as userMobile,user\_token as userToken,near\_msg\_notice as nearMsgNotice,duty\_msg\_notice as dutyMsgNotice,system\_msg\_notice as systemMsgNotice,passenger\_route\_notice as passengerRouteNotice,route\_msg\_notice as routeMsgNotice,d.\`appversion\`,d.\`devicetoken\` as deviceToken,d.\`platform\`,d.\`devicemodel\` as deviceModel,d.\`status\`,d.\`device\_identifier\` as identifer

FROM \`t\_user\` u,t\_user\_info ui,apns\_devices d

WHERE u.user\_id=ui.fk\_user\_id and u.user\_id=d.fk\_user\_id and u.user\_id=new.user\_id order by d.modified desc limit 1) x);

\#调用redis接口同步数据

set @rs = (SELECT http\_post('http://192.168.1.38:8082/user\_device/save',concat('value=',@json)));

\#错误日期记录

if \_err=1 then

insert into user\_tmp(trade\_id,fk\_user\_id) values(1,\_err);

\#SHOW ERRORS;

end if;

END