MySQL 5.0 Reference Manual  /  ...  /  Using COLLATE in SQL Statements

10.1.7.2 Using COLLATE in SQL Statements

With the COLLATE clause, you can override whatever the default collation is for a comparison. COLLATE may be used in various parts of SQL statements. Here are some examples:

  • With ORDER BY:

    SELECT k
    FROM t1
    ORDER BY k COLLATE latin1_german2_ci;
    
  • With AS:

    SELECT k COLLATE latin1_german2_ci AS k1
    FROM t1
    ORDER BY k1;
    
  • With GROUP BY:

    SELECT k
    FROM t1
    GROUP BY k COLLATE latin1_german2_ci;
    
  • With aggregate functions:

    SELECT MAX(k COLLATE latin1_german2_ci)
    FROM t1;
    
  • With DISTINCT:

    SELECT DISTINCT k COLLATE latin1_german2_ci
    FROM t1;
    
  • With WHERE:

         SELECT *
         FROM t1
         WHERE _latin1 'Müller' COLLATE latin1_german2_ci = k;
    
         SELECT *
         FROM t1
         WHERE k LIKE _latin1 'Müller' COLLATE latin1_german2_ci;
    
  • With HAVING:

    SELECT k
    FROM t1
    GROUP BY k
    HAVING k = _latin1 'Müller' COLLATE latin1_german2_ci;
    

User Comments
  Posted by Meffy Ellis on September 6, 2015
Without "COLLATE utf8_unicode_ci", running the following queries together in MySQL Workbench resulted in the error shown below.

SET @MyStyle = 'SHIRT12345';
SELECT [...] WHERE Style = @MyStyle blah blah;

Error Code: 1267. Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='

Adding "COLLATE utf8_unicode_ci" as shown below forced the variable's collation to be the same one I'm using in the database, and the query ran successfully.

SET @MyStyle = 'SHIRT12345' COLLATE utf8_unicode_ci;
SELECT [...] WHERE Style = @MyStyle blah blah;
Sign Up Login You must be logged in to post a comment.