如何修改MySQL视图的排序规则？解决排序规则冲突报错

首先，你遇到的1347报错是因为视图不是基础物理表，所以不能用ALTER TABLE命令修改它的字符集或排序规则。想要解决这个Illegal mix of collations的问题，我们需要从视图的本质出发——视图的字段属性是继承底层表的，所以可以通过以下几种方式处理：

方法1：重新创建视图并显式指定排序规则 #

既然视图的排序规则依赖于底层表，我们可以在重新创建视图时，对字符串类型的字段强制指定utf8_unicode_ci排序规则，让它和你的其他表保持一致。

执行下面的语句替换原视图：

CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=root@% SQL SECURITY DEFINER VIEW vw_loan_full AS 
select 
    a.id AS loan_id,
    a.client_id AS client_id,
    b.display_name COLLATE utf8_unicode_ci AS client_name,
    b.account_no COLLATE utf8_unicode_ci AS cif_no,
    a.product_id AS product_id,
    c.short_name COLLATE utf8_unicode_ci AS product_name,
    a.currency_code AS ccy_code,
    a.annual_nominal_interest_rate AS rate,
    a.term_frequency AS term,
    a.approved_principal AS approved_principal,
    a.principal_amount AS disbursed_amount,
    a.principal_outstanding_derived AS pos,
    a.loan_status_id AS status,
    a.submittedon_date AS created_date,
    a.submittedon_userid AS created_by,
    a.expected_disbursedon_date AS value_date,
    a.maturedon_date AS maturity_date,
    a.approvedon_date AS approvedon_date,
    a.disbursedon_date AS disbursedon_date,
    a.expected_maturedon_date AS expected_maturedon_date,
    a.loanpurpose_cv_id AS loan_purpose,
    a.account_no COLLATE utf8_unicode_ci AS account_no
from ((mifostenant-default.m_loan a 
left join mifostenant-default.m_client b on((a.client_id = b.id))) 
left join mifostenant-default.m_product_loan c on((a.product_id = c.id)))

这里我们给所有字符串类型的字段（比如display_name、account_no、short_name）加上了COLLATE utf8_unicode_ci，确保视图返回的字段排序规则和你的xxff_client、xxff_message等表完全匹配，这样后续的replace操作就不会再出现排序规则冲突了。

方法2：修改视图依赖的底层表排序规则（彻底解决） #

如果你的mifostenant-default库下的m_client、m_product_loan、m_loan这几个底层表本身就是用utf8_general_ci的话，更彻底的方法是直接修改这些底层表的字符集和排序规则，这样视图会自动继承正确的规则：

-- 依次修改底层表的排序规则
ALTER TABLE mifostenant-default.m_client CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE mifostenant-default.m_product_loan CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE mifostenant-default.m_loan CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

修改完成后，你可以重新创建一次视图（或者直接刷新视图），后续所有依赖这个视图的查询都不会再遇到排序规则问题。注意：修改底层表前建议先备份数据，避免影响其他依赖这些表的业务逻辑。

临时应急方法：在查询中临时指定排序规则 #

如果你暂时不想修改视图或底层表，可以在原查询语句中，对视图返回的字段临时转换排序规则，也能解决冲突：

select 
    b.email, 
    concat('[AAA]: Xin chao QK ', a.client_name COLLATE utf8_unicode_ci, ' HD ',a.account_no COLLATE utf8_unicode_ci, ' vua khoi tao') as subject, 
    replace( (select email_content from xxff_message where refid=1 and status='O'),'#LOAN_ID#', a.account_no COLLATE utf8_unicode_ci) as message, 
    a.loan_id
from interface.vw_loan_full a
left join interface.xxff_client b on a.client_id=b.client_id
where 1=1
and a.status=100
and (a.loan_id, email) not in ( select loan_id, receiver from xxff_loan_message_sent where stage='INIT100_MSG_EMAIL')
and date(a.value_date) between DATE_ADD(CURDATE(), INTERVAL -30 day) and date(now());

不过这种方法需要每次查询都手动添加COLLATE，适合临时应急，长期来看还是推荐前两种方法。

内容的提问来源于stack exchange，提问作者Satomi Satoh