MySQL 5.6 リファレンスマニュアル  /  ...  /  SQL ステートメントでの COLLATE の使用

10.1.7.2 SQL ステートメントでの COLLATE の使用

COLLATE 句では、比較に対するデフォルト照合順序が何であれ、オーバーライドできます。SQL ステートメントのさまざまな個所で COLLATE を使用できます。次にいくつかの例を示します。

  • ORDER BY を指定した場合

    SELECT k
    FROM t1
    ORDER BY k COLLATE latin1_german2_ci;
    
  • AS を指定した場合

    SELECT k COLLATE latin1_german2_ci AS k1
    FROM t1
    ORDER BY k1;
    
  • GROUP BY を指定した場合

    SELECT k
    FROM t1
    GROUP BY k COLLATE latin1_german2_ci;
    
  • 集計関数を指定した場合

    SELECT MAX(k COLLATE latin1_german2_ci)
    FROM t1;
    
  • DISTINCT を指定した場合

    SELECT DISTINCT k COLLATE latin1_german2_ci
    FROM t1;
    
  • 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;
    
  • 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.