Specify MySQL column collation in a view to overcome mismatched collations in comparison

In my budget changes history table, my key is using collation utf8mb4_unicode_ci

show full columns from budgetchangeshistory

rsskey            varchar(500)  utf8mb4_unicode_ci YES      NULL                      select,insert,update,references 

However, when I compare my budget changes view to history, I get a collation mismatch

select importdate, previmportdate, propno, awardnumber, awardtype, rectype, changedescription, rsskey
from budgetchanges_vw
where rsskey not in (select rsskey from budgetchangeshistory)
;

Illegal mix of collations (utf8mb4_bin,NONE) and (utf8mb4_unicode_ci,IMPLICIT) for operation '='

The key in the budget changes view has collation utf8mb4_bin

show full columns from budgetchanges_vw

rsskey            varchar(67)  utf8mb4_bin        YES      NULL          select,insert,update,references 

Solution

Force the column in the view to match the collation in the history table

    ,convert(concat_ws('-'
    ,lower(b.propno)
    ,lower(cast(b.awardnumber as char))
    ,lower(b.awardtypecode)
    ,lower(cast(b.rectypeid as char))
    ,replace(cast(b.importdate as char),' ','-')
   ) using utf8mb4) collate utf8mb4_unicode_ci rsskey