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

最新推荐文章于 2021-12-07 09:54:59 发布

麓鸯

最新推荐文章于 2021-12-07 09:54:59 发布

阅读量419

点赞数

文章标签： mysql 触发器中文

本文链接：https://blog.csdn.net/weixin_31016735/article/details/113897993

版权

在开发项目中遇到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;