Documentation Home
MySQL 5.6 リファレンスマニュアル
Download this Manual
PDF (US Ltr) - 26.8Mb
PDF (A4) - 26.9Mb
HTML Download (TGZ) - 7.1Mb
HTML Download (Zip) - 7.2Mb


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
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.