在mySQL中排序规则的非法组合 [英] Illegal mix of collations in mySQL

问题描述

我需要将列从一个表传输到另一个表。源表具有与目标表不同的排序规则（latin1_general_ci和latin1_swedish_ci）。

I need to transfer a column from one table to another. The source table has a different collation than the target table (latin1_general_ci and latin1_swedish_ci).

我使用

UPDATE target 
LEFT JOIN source ON target.artnr = source.artnr 
SET target.barcode = source.barcode

I得到非法混合的整理。

I get an "illegal mix of collations".

什么是快速修复来获得这个工作，而不必更改任何表？我尝试CONVERT和COLLATE以UTF-8运行整个操作，但这没有帮助。 条形码只包含数字数据（即使它们都是VARCHAR），所以没有排序问题。

What is a quick fix to get this working without having to change either table? I tried CONVERT and COLLATE to run the whole operation in UTF-8, but that didn't help. "barcode" contains numeric data only (even though they all are VARCHARs), so there are no collation worries either way. I need to do this just once.

编辑：我使用 CAST（fieldname as unsigned） / code>在每个字段涉及。在这种情况下，我能够做到这一点，因为只有数字数据受到影响，但很高兴知道一个更一般的方法，这个问题，所以我打开这个问题。

I sorted it using a CAST(fieldname as unsigned) on every field involved. I was able to do that in this case because only numeric data was affected, but it would be nice to know a more general approach to this, so I am leaving the question open.

推荐答案

我尝试CONVERT和COLLATE以UTF-8运行整个操作，但是没有帮助。

I tried CONVERT and COLLATE to run the whole operation in UTF-8, but that didn't help.

它给我打了电话，所以今天我创建了这些表，并成功地运行了这个表：

It bugged me, so today I created those tables and successfully ran this one:

UPDATE target
LEFT JOIN  source ON( target.artnr = source.artnr COLLATE latin1_swedish_ci )
SET target.barcode = source.barcode

这篇关于在mySQL中排序规则的非法组合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！