MySql中文乱码

解决方案 »

免费领取超大流量手机卡，每月29元包185G流量+100分钟通话, 中国电信官方发货

插入数据是通过C++代码调用存储过程实现的，存储过程如下：CREATE DEFINER=`root`@`%` PROCEDURE `sp_insert_entities`(
p_increment int,
p_sn varchar(100),
p_portnum int,
p_vlanid int,
p_ip bigint(22),p_mac char(24),
p_state int,
p_statetime bigint(22),
p_hostname varchar(50),
p_groupname varchar(50),p_actions varchar(200)
)
begin
declare tb_name varchar(40);
declare v_portid int;
declare v_flag int;declare p_flag int;
declare v_probeid int;
declare v_probename varchar(30);
declare v_parentid int;
declare v_portname varchar(30);
declare v_devicegroupname varchar(30);
declare v_mode int;
declare v_command1 varchar(300);
declare v_command2 varchar(300);
declare v_command3 varchar(300);
declare v_target1 varchar(100);
declare v_target2 varchar(100);select count(1) into @p_flag from t_ports a, t_probes b
where a.probe_id=b.id and b.sn=p_sn and a.port_num=p_portnum and a.vlan_id=p_vlanid;
set p_flag=@p_flag;if p_flag = 0 then
    select id into @v_probeid from t_probes where sn=p_sn;
    set v_probeid=@v_probeid;
    select name into @v_probename from t_probes where sn=p_sn;
    set v_probename=@v_probename;
    set v_portname=concat("[",v_probename,"]","port_",p_portnum,".",p_vlanid);
    insert into t_ports(port_num,vlan_id,mode,name,probe_id) values(p_portnum,
                p_vlanid,0,v_portname,v_probeid);

    select a.id into @v_portid from t_ports a,t_probes b
    where a.probe_id=b.id and b.sn=p_sn and a.port_num=p_portnum and vlan_id=p_vlanid;
    set v_portid=@v_portid;
    set v_parentid=0;
    set v_devicegroupname=concat("[",v_probename,"]","port_",p_portnum,".",p_vlanid);
    insert into t_devicegroups(parent_id,type,name,port_id) values(v_parentid,2,v_devicegroupname,v_portid);        select id into @v_parentid from t_devicegroups where type=2 and port_id=v_portid;
    set v_parentid=@v_parentid;
    set v_devicegroupname="访客";
    insert into t_devicegroups(parent_id,type,name,port_id) values(v_parentid,3,v_devicegroupname,v_portid);        set v_command1=concat("opcode=add_port#target=probe#port_id=",p_portnum,"#vlan_id=",p_vlanid,"#");
    insert into t_commands(probe_id,command_type,command_state,command) values(v_probeid,
                101,0,v_command1);
    set v_target1="";
    if p_vlanid = 0 then
       set v_target1=concat("port_",p_portnum);
    else
       set v_target1=concat("port_",p_portnum,".",p_vlanid);
    end if;
    set v_command2=concat("opcode=set_alias#target=",v_target1,"#alias=",v_portname,"#");
    insert into t_commands(probe_id,command_type,command_state,command) values(v_probeid,
    101,0,v_command2);
    set v_target2=concat(v_target1,"/","guest");
    set v_command3=concat("opcode=set_alias#target=",v_target2,"#alias=访客#");
    insert into t_commands(probe_id,command_type,command_state,command) values(v_probeid,
    101,0,v_command3);
end if;
select mode into @v_mode from t_probes where sn=p_sn;
set v_mode=@v_mode;
select a.id into @v_portid from t_ports a,t_probes b
where a.probe_id=b.id and b.sn=p_sn and a.port_num=p_portnum and vlan_id=p_vlanid;
set v_portid=@v_portid;
set tb_name='t_entities';
set @insert_sql=concat("insert into `",tb_name,"` (ip,mac,state_time,state,actions,
                       host_name,work_group,port_id)
                       values(?,?,?,?,?,  ?,?,?)");
set @update_sql=concat("update `",tb_name,"` set state_time=?,state=?,actions=?,host_name=?,
                       work_group=?,port_id=? where ip=? and mac=?");
if p_mac !="" then
    if p_increment = 0 then
        prepare s1 from @insert_sql;
        set @a1 = p_ip;
        set @a2 = p_mac;
        set @a3 = p_statetime;
        set @a4 = p_state;
        set @a5 = p_actions;

        set @a6 = p_hostname;
        set @a7 = p_groupname;
        set @a8 = v_portid;

        execute s1 using @a1,@a2,@a3,@a4,@a5,@a6,@a7,@a8;
        deallocate prepare s1;
    else
        select count(1) into @v_flag from t_entities where ip=p_ip and mac=p_mac;
        set v_flag=@v_flag;
        if v_flag > 0 then
           if p_state = 5 then
              if v_mode =1 then
                  prepare s2 from @update_sql;
                  set @b1 = P_statetime;
                  set @b2 = p_state;
                  set @b3 = p_actions;
                  set @b4 = p_hostname;
                  set @b5 = p_groupname;

                  set @b6 = v_portid;
                  set @b7 = p_ip;
                  set @b8 = p_mac;

                  execute s2 using @b1,@b2,@b3,@b4,@b5,@b6,@b7,@b8;
                  deallocate prepare s2;
              else
                  delete from t_entities where ip=p_ip and mac=p_mac;
              end if;
           else
              prepare s2 from @update_sql;
              set @b1 = P_statetime;
              set @b2 = p_state;
              set @b3 = p_actions;
              set @b4 = p_hostname;
              set @b5 = p_groupname;

              set @b6 = v_portid;
              set @b7 = p_ip;
              set @b8 = p_mac;

              execute s2 using @b1,@b2,@b3,@b4,@b5,@b6,@b7,@b8;
              deallocate prepare s2;
           end if;
        else
           if p_state =5 then
              if v_mode =1 then
                  prepare s3 from @insert_sql;
                  set @c1 = p_ip;
                  set @c2 = p_mac;
                  set @c3 = p_statetime;
                  set @c4 = p_state;
                  set @c5 = p_actions;

                  set @c6 = p_hostname;
                  set @c7 = p_groupname;
                  set @c8 = v_portid;

                  execute s3 using @c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8;
                  deallocate prepare s3;
              else
                  delete from t_entities where ip=p_ip and mac=p_mac;
              end if;
           else
              prepare s3 from @insert_sql;
              set @c1 = p_ip;
              set @c2 = p_mac;
              set @c3 = p_statetime;
              set @c4 = p_state;
              set @c5 = p_actions;

              set @c6 = p_hostname;
              set @c7 = p_groupname;
              set @c8 = v_portid;

              execute s3 using @c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8;
              deallocate prepare s3;
           end if;
        end if;
    end if;
end if;
end;
怎么连接MYSQL的？在统一字符集的下重新插入记录
http://blog.csdn.net/ACMAIN_CHM/archive/2009/05/12/4174186.aspx
MySQL 中文显示乱码
统一字符集下插入记录没问题（我是在mysql-front下直接调用）；
C++连接mysql是通过mysqlclient库实现的
加上这个试试。gbk 改成你自己的字符集 utf89.  存储过程参数乱码
create procedure t ( aa char(10) charset 'gbk')另外C代码中，也设置一下字符集 set names 'utf8', 或者 set name 'gbk'  这个要看你的C程序中支持的字符集是什么。
搞定了，问题出在C++程序的连接上，C++程序的连接没指定连接时的字符集，导致client,connection,result的字符集和server的字符集不统一，所以造成乱码