我需要将列从一个表传输到另一个表。源表具有与目标表不同的排序规则(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.barcodeI得到非法混合的整理。
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中排序规则的非法组合
发布评论