mysql 触发器 中文_MySql触发器中文乱码问题解决

在开发项目中遇到MySQL触发器导致的中文乱码问题,即使表和字段字符集为utf8。通过在触发器代码中指定中文变量字符集为utf8,成功解决了乱码问题。触发器涉及的内容包括在数据插入后,复制到其他表并更新相关统计信息。
摘要由CSDN通过智能技术生成

在开发XXXX项目过程中,需要用到触发器,该触发器会将部分数据复制到其它表中,但是复制后的数据,中文部分乱码,影响了正常的功能,所有表的默认字符集都是utf8,表中中文字段的字符集也已经显示指定为utf8,但是还是乱码!

解决方案就是指定中文变量所用的字符集为utf8,触发器代码如下:

DELIMITER $$

DROP TRIGGER /*!50032 IF EXISTS */ `antsoldier`.`event_ai`$$

create trigger `antsoldier`.`event_ai` AFTER INSERT on `antsoldier`.`event`

for each row BEGIN

declare threatName varchar(100) character set utf8;

declare threatType varchar(100) character set utf8;

declare threatProtocol varchar(24) character set utf8;

declare threatPid int;

declare threatCid int;

declare existNameCount int;

declare existTypeCount int;

declare existHostCount int;

declare existProtocolCount int;

/*获取威胁的pid和cid*/

select pid,cid into threatPid,threatCid from host where ip=inet_ntoa(new.dst_ip);

if threatPid > 0 and threatCid > 0 then

/*获取威胁的名称和类型*/

select t2.name,t3.name into threatName,threatType from plugin_sid t1,classic_eventbase t2,classic_type t3 where t1.plugin_id=new.plugin_id and t1.sid=new.plugin_sid and t1.class_id=t2.class_id and t1.class_sid=t2.sid and t3.class_id=t1.class_id;

select name into threatProtocol from protocol where id=new.protocol;

/*获取该名称的威胁存在的次数*/

select count(*) into existNameCount from threat_statistic_name where name=threatName and pid=threatPid and cid=threatCid limit 0,1;

/*获取该类型的威胁存在的次数*/

select count(*) into existTypeCount from threat_statistic_type where type=threatType and pid=threatPid and cid=threatCid limit 0,1;

/*获取威胁的主机ip信息*/

select count(*) into existHostCount from threat_statistic_host where hostip=new.dst_ip and pid=threatPid and cid=threatCid limit 0,1;

/*获取威胁的协议存在的次数*/

select count(*) into existProtocolCount from threat_statistic_protocol t1,protocol t2 where t2.id=new.protocol and t1.name=t2.name and pid=threatPid and cid=threatCid limit 0,1;

/*处理高危主机*/

if existHostCount>0 then

update threat_statistic_host set counts=counts+1 where hostip=new.dst_ip and pid=threatPid and cid=threatCid;

else

insert into threat_statistic_host(hostip,counts,pid,cid) values(new.dst_ip,1,threatPid,threatCid);

end if;

/*处理威胁名称*/

if existNameCount>0 then

update threat_statistic_name set counts=counts+1 where pid=threatPid and cid=threatCid and name=threatName;

else

insert into threat_statistic_name(name,counts,pid,cid) values(threatName,1,threatPid,threatCid);

end if;

/*处理威胁类型*/

if existTypeCount>0 then

update threat_statistic_type set counts=counts+1 where pid=threatPid and cid=threatCid and type=threatType;

else

insert into threat_statistic_type(type,counts,pid,cid) values(threatType,1,threatPid,threatCid);

end if;

/*处理威胁协议*/

if existProtocolCount>0 then

update threat_statistic_protocol set counts=counts+1 where name=threatProtocol and pid=threatPid and cid=threatCid;

else

insert into threat_statistic_protocol(name,counts,pid,cid) values(threatProtocol,1,threatPid,threatCid);

end if;

end if;

END;

$$

DELIMITER ;

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值