错误如下:
1267 Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' (SQL: update `usersinfos` set `updated_at` = 2021-04-12 16:36:48, `open_at` = 2021-04-12 16:36:48 where `id` = 17)
原因:
因为我这个表中有触发器,代码如下:
CREATE TRIGGER `trig_usersinfos_update` BEFORE UPDATE ON `usersinfos` FOR EACH ROW begin
if(new.condition_code>=440100) and (new.condition_code<440200) then
select 'lawyer_auth',score into @item_code,@item_score from score_rules where score_item_code='lawyer_auth';
select score into @star5_score from star_rules where star_level=5;
select score,star into @curr_score,@curr_star from users where id=new.id;
if(@curr_star>=5) then
set @limit_score=@star5_score * 2;
else
set @limit_score=@star5_score;
end if;
select count(*) into @once from user_score_his where score_item_code=@item_code and userid=new.id;
if (@once=0) and (@curr_score+@item_score<@limit_score) then
insert into user_score_his(userid, score_before, score_item_code, add_score, created_at, obj_type, obj_id)
select id as userid, score as score_before, @item_code as score_item_code, @item_score as add_score, now() as created_at, 'usersinfos' as obj_type, id as obj_id
from users
where id=new.id;
update users set score=score+@item_score
where id=new.id;
end if;
end if;
if(new.profile is not null) and (trim(new.profile) != '') then
select 'self_intro',score into @item_code,@item_score from score_rules where score_item_code='self_intro';
select score into @star5_score from star_rules where star_level=5;
select score,star into @curr_score,@curr_star from users where id=new.id;
if(@curr_star>=5) then
set @limit_score=@star5_score * 2;
else
set @limit_score=@star5_score;
end if;
select count(*) into @once from user_score_his where score_item_code=@item_code and userid=new.id;
if (@once=0) and (@curr_score+@item_score<@limit_score) then
insert into user_score_his(userid, score_before, score_item_code, add_score, created_at, obj_type, obj_id)
select id as userid, score as score_before, @item_code as score_item_code, @item_score as add_score, now() as created_at, 'usersinfos' as obj_type, id as obj_id
from users
where id=new.id;
update users set score=score+@item_score
where id=new.id;
end if;
end if;
end;
这两个sql中存在查询字符(select 'lawyer_auth',score into @item_code,@item_score from score_rules where score_item_code='lawyer_auth';
select 'self_intro',score into @item_code,@item_score from score_rules where score_item_code='self_intro';),就会因为字符排序规则不一样而报1267错误;
修改如下:
CREATE TRIGGER `trig_usersinfos_update` BEFORE UPDATE ON `usersinfos` FOR EACH ROW begin
if(new.condition_code>=440100) and (new.condition_code<440200) then
select CONVERT('lawyer_auth' USING utf8) COLLATE utf8_unicode_ci,score into @item_code,@item_score from score_rules where score_item_code=CONVERT('lawyer_auth' USING utf8) COLLATE utf8_unicode_ci;
select score into @star5_score from star_rules where star_level=5;
select score,star into @curr_score,@curr_star from users where id=new.id;
if(@curr_star>=5) then
set @limit_score=@star5_score * 2;
else
set @limit_score=@star5_score;
end if;
select count(*) into @once from user_score_his where score_item_code=@item_code and userid=new.id;
if (@once=0) and (@curr_score+@item_score<@limit_score) then
insert into user_score_his(userid, score_before, score_item_code, add_score, created_at, obj_type, obj_id)
select id as userid, score as score_before, @item_code as score_item_code, @item_score as add_score, now() as created_at, 'usersinfos' as obj_type, id as obj_id
from users
where id=new.id;
update users set score=score+@item_score
where id=new.id;
end if;
end if;
if(new.profile is not null) and (trim(new.profile) != '') then
select CONVERT('self_intro' USING utf8) COLLATE utf8_unicode_ci,score into @item_code,@item_score from score_rules where score_item_code=CONVERT('self_intro' USING utf8) COLLATE utf8_unicode_ci;
select score into @star5_score from star_rules where star_level=5;
select score,star into @curr_score,@curr_star from users where id=new.id;
if(@curr_star>=5) then
set @limit_score=@star5_score * 2;
else
set @limit_score=@star5_score;
end if;
select count(*) into @once from user_score_his where score_item_code=@item_code and userid=new.id;
if (@once=0) and (@curr_score+@item_score<@limit_score) then
insert into user_score_his(userid, score_before, score_item_code, add_score, created_at, obj_type, obj_id)
select id as userid, score as score_before, @item_code as score_item_code, @item_score as add_score, now() as created_at, 'usersinfos' as obj_type, id as obj_id
from users
where id=new.id;
update users set score=score+@item_score
where id=new.id;
end if;
end if;
end;
即修改该两句sql就规避了该错误
select CONVERT('lawyer_auth' USING utf8) COLLATE utf8_unicode_ci,score into @item_code,@item_score from score_rules where score_item_code=CONVERT('lawyer_auth' USING utf8) COLLATE utf8_unicode_ci;
select CONVERT('self_intro' USING utf8) COLLATE utf8_unicode_ci,score into @item_code,@item_score from score_rules where score_item_code=CONVERT('self_intro' USING utf8) COLLATE utf8_unicode_ci;