Documentation Home
MySQL Globalization
Related Documentation Download this Excerpt
PDF (US Ltr) - 436.7Kb
PDF (A4) - 436.1Kb
HTML Download (TGZ) - 86.9Kb
HTML Download (Zip) - 89.6Kb


MySQL Globalization  /  ...  /  Using COLLATE in SQL Statements

1.8.1 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
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
  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.