I have a table with the character set latin1 (checked by show variables like "character_set_database";) and a default collation of latin1_swedish_ci (checked by SHOW TABLE STATUS;).
I'd like to run a query using the collation latin1_general_cs, which is compiled on my system (checked by Show collation LIKE "%_cs";):
select * from myTab WHERE col RLIKE '[[:upper:]]' COLLATE 'latin1_general_cs' LIMIT 10;
which gives an error:
ERROR 1253 (42000): COLLATION 'latin1_general_cs' is not valid for CHARACTER SET 'utf8'
Even the default collation does not work:
select * from myTab WHERE col RLIKE '[[:upper:]]' COLLATE 'latin1_swedish_ci' LIMIT 10;
error:
ERROR 1253 (42000): COLLATION 'latin1_swedish_ci' is not valid for CHARACTER SET 'utf8'
Whats wrong? is it because my terminal sends data in UTF-8? (Is the "Connection in UTF-8"?) How can I anyway use case sensitive collation? I need it, otherwise it seems impossible to check for uppercase letters.
解决方案
You may find this interesting.
I can't say I'm an expert in this area, but as far as I can see, UTF8 and Latin1 encode characters differently. So trying to collate UTF8 using Latin1 doesn't make sense.
Whereas:
CREATE DATABASE mydb
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_general_ci;
is consistent.
Update
Sorry, I just re-read your question and you say that the table is using latin1. Odd. Have you tried using the utf8 collations?