Menu
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).
I use
UPDATE target
LEFT JOIN source ON target.artnr = source.artnr
SET target.barcode = source.barcode
I get an "illegal mix of collations".
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.
Edit: 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.
469
So what is an "illegal mix of collations"? An "illegal mix of collations" occurs when an expression compares two strings of different collations but of equal coercibility and the coercibility rules cannot help to resolve the conflict.
A collation is a set of rules that defines how to compare and sort character strings. Each collation in MySQL belongs to a single character set. Every character set has at least one collation, and most have two or more collations. A collation orders characters based on weights.
In general, utf8mb4 is the “safest” character set as it also supports 4-byte unicode while utf8 only supports up to 3.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
