MYSQL case sensitive search for utf8_bin field

case-sensitivecharacter-encodingmysqlmysql-error-1267sql

I created a table and set the collation to utf8 in order to be able to add a unique index to a field. Now I need to do case insensitive searches, but when I performed some queries with the collate keyword and I got:

mysql> select * from page where pageTitle="Something" Collate utf8_general_ci;

ERROR 1253 (42000): COLLATION 'utf8_general_ci' is not valid for
CHARACTER SET 'latin1'

mysql> select * from page where pageTitle="Something" Collate latin1_general_ci;

ERROR 1267 (HY000): Illegal mix of collations (utf8_bin,IMPLICIT) and
(latin1_general_ci,EXPLICIT) for operation '='

I am pretty new to SQL, so I was wondering if anyone could help.

Best Solution

A string in MySQL has a character set and a collation. Utf8 is the character set, and utf8_bin is one of its collations. To compare your string literal to an utf8 column, convert it to utf8 by prefixing it with the _charset notation:

_utf8 'Something'

Now a collation is only valid for some character sets. The case-sensitive collation for utf8 appears to be utf8_bin, which you can specify like:

_utf8 'Something' collate utf8_bin

With these conversions, the query should work:

select * from page where pageTitle = _utf8 'Something' collate utf8_bin

The _charset prefix works with string literals. To change the character set of a field, there is CONVERT ... USING. This is useful when you'd like to convert the pageTitle field to another character set, as in:

select * from page 
where convert(pageTitle using latin1) collate latin1_general_cs = 'Something'

To see the character and collation for a column named 'col' in a table called 'TAB', try:

select distinct collation(col), charset(col) from TAB

A list of all character sets and collations can be found with:

show character set
show collation

And all valid collations for utf8 can be found with:

show collation where charset = 'utf8'

Related Solutions

Mysql – concatenate multiple MySQL rows into one field

You can use GROUP_CONCAT:

SELECT person_id,
   GROUP_CONCAT(hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Ludwig stated in his comment, you can add the DISTINCT operator to avoid duplicates:

SELECT person_id,
   GROUP_CONCAT(DISTINCT hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Jan stated in their comment, you can also sort the values before imploding it using ORDER BY:

SELECT person_id, 
       GROUP_CONCAT(hobbies ORDER BY hobbies ASC SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Dag stated in his comment, there is a 1024 byte limit on the result. To solve this, run this query before your query:

SET group_concat_max_len = 2048;

Of course, you can change 2048 according to your needs. To calculate and assign the value:

SET group_concat_max_len = CAST(
                     (SELECT SUM(LENGTH(hobbies)) + COUNT(*) * LENGTH(', ')
                           FROM peoples_hobbies
                           GROUP BY person_id) AS UNSIGNED);

Mysql – Troubleshooting “Illegal mix of collations” error in thesql

This is generally caused by comparing two strings of incompatible collation or by attempting to select data of different collation into a combined column.

The clause COLLATE allows you to specify the collation used in the query.

For example, the following WHERE clause will always give the error you posted:

WHERE 'A' COLLATE latin1_general_ci = 'A' COLLATE latin1_general_cs

Your solution is to specify a shared collation for the two columns within the query. Here is an example that uses the COLLATE clause:

SELECT * FROM table ORDER BY key COLLATE latin1_general_ci;

Another option is to use the BINARY operator:

BINARY str is the shorthand for CAST(str AS BINARY).

Your solution might look something like this:

SELECT * FROM table WHERE BINARY a = BINARY b;

or,

SELECT * FROM table ORDER BY BINARY a;

Best Solution

Related Solutions

Mysql – concatenate multiple MySQL rows into one field

Mysql – Troubleshooting “Illegal mix of collations” error in thesql

Related Question