☰

#1267 - 操作 'UNION' 中排序规则 (utf8mb4_0900_ai_ci,COERCIBLE) 和 (latin1_swedish_ci,IMPLICIT) 混合非法

tag-icon • tag-icon mysql
#1267 - 操作 'UNION' 中排序规则 (utf8mb4_0900_ai_ci,COERCIBLE) 和 (latin1_swedish_ci,IMPLICIT) 混合非法

我已将 MySQL 从版本 5.7 升级到 8。我已经从旧版 MySQL 5.7 导出了数据库，现在我正尝试将数据库导入 MySQL 8。但是，其中一个数据库视图具有 union 子句，因此出现错误。

错误是 -

#1267 - Illegal mix of collations (utf8mb4_0900_ai_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'UNION'

观点是 -

CREATE VIEW VIEW_ALL_ORDER_ITEMS_REF AS
  select * from VIEW_ORDER_ITEM_EQ_REF
  union 
  select * from VIEW_ORDER_ITEM_SO_REF
  union 
  select * from VIEW_ORDER_ITEM_OA_REF
  union 
  select * from VIEW_ORDER_ITEM_WO_REF
  union 
  select * from VIEW_ORDER_ITEM_DO_REF

当我尝试在两个以上的视图之间应用联合时遇到了这个错误。

这个视图很好用。

drop view if exists VIEW_ALL_ORDER_ITEMS_REF;
CREATE VIEW VIEW_ALL_ORDER_ITEMS_REF AS
  select * from VIEW_ORDER_ITEM_EQ_REF
  union 
  select * from VIEW_ORDER_ITEM_SO_REF

有人可以帮忙吗？

答案1

当您从 MySQL 转储中恢复数据库时，您可以通过更改表、函数和过程的字符集和排序规则来解决问题，同时确保数据库的未来安全。MySQL 5.7 和 8.x 之间有许多变化，所以现在确实是这样做的最佳时机。

以下是您可以对每个语句执行的操作CREATE：

  1. 替换DEFAULT CHARSET为utf8mb4

  2. 替换COLLATE为utf8mb4_unicode_ci

  3. （对于表格）确保设置ENGINE为InnoDB

    笔记：您不想再使用 MyISAM，也不想将ENGINE类型与查询混合，因为这会对性能造成相当大的影响。

使用您喜欢的文本编辑器完成所有这些操作，然后将导入过程运行到新的 MySQL 数据库中。确保将数据库DEFAULT CHARSET和COLLATE值设置为与表、函数和过程相同的值。

建议理由：

MySQL 8.0 与 5.x 系列有很大不同，大量在 5.2 之前弃用的项目被完全删除。这包括某些列类型以及排序规则。导入过程将尝试自动将弃用的元素调整为现代元素，但通常会弄乱字符集和排序规则。

事实证明，这utf8mb4_unicode_ci是处理多字节字符（例如表情符号和非英语语言中使用的字符）时最可靠的排序规则。虽然它会占用更多磁盘空间，但这将确保您的应用程序可以处理任何字符。末尾_ci的位确保在连接和查找时将值视为不区分大小写。

替换所有CHARSET和COLLATE值将确保您不会Illegal mix of collations再次收到错误......除非......

存储过程和触发器需要考虑的事项

MySQL 8.0 似乎对在存储过程中创建临时表的要求更高一些。如果使用临时表，请务必在代码中预定义它们，就像对普通表进行预定义一样。语法几乎相同，只是您要添加一个额外的单词：

DROP TEMPORARY TABLE IF EXISTS `YearlySums`;
CREATE TEMPORARY TABLE IF NOT EXISTS `YearlySums ` (
    ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Illegal mix of collations这将确保您在使用存储过程时不会遇到错误。

如果您的表有BEFORE INSERT或BEFORE UPDATE触发器，并且这些表是通过存储过程填充的，那么在将数据库投入生产设置之前，您需要进行大量测试。Oracle 在 8.0.25 中引入了一个相当严重的错误，当触发器BEFORE作为数据验证的一部分处理行时，在某些情况下可能会导致 MySQL 服务器引擎崩溃，但只有当该数据由存储过程提供时才会发生这种情况。这个问题已经存在了一年多，而 Oracle 似乎并不在意。

不要让这个漏洞毁了你的新年假期，就像去年毁了我一样

相关内容