概要
- COLLATE文は文字の照合順序を指定する
- 照合順序は文字の大小比較をする際の基準
- 検索時のWHEREや並べ替えORDER BYなどで適用される
書き方
WHERE
WHERE colname COLLATE collateion = 'expression';
WHERE colname COLLATE collateion LIKE 'expression';
ORDER BY
ORDER BY colname COLLATE collateion;
Collation
utf8mb4_unicode_ciutf8mb4_general_ciutf8mb4_binutf8mb4_0900-ai_ci（デフォルト）
デフォルトの照合順序
UTF-8のデフォルトはutf8_general_ciだが、utf8mb4の場合utf8mb4_0900-ai_ciと照合順序が異なる。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
mysql> SHOW CHARACTER SET; +----------+---------------------------------+---------------------+--------+ | Charset | Description | Default collation | Maxlen | +----------+---------------------------------+---------------------+--------+ | armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 | | ascii | US ASCII | ascii_general_ci | 1 | | big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 | | binary | Binary pseudo charset | binary | 1 | | cp1250 | Windows Central European | cp1250_general_ci | 1 | | cp1251 | Windows Cyrillic | cp1251_general_ci | 1 | | cp1256 | Windows Arabic | cp1256_general_ci | 1 | | cp1257 | Windows Baltic | cp1257_general_ci | 1 | | cp850 | DOS West European | cp850_general_ci | 1 | | cp852 | DOS Central European | cp852_general_ci | 1 | | cp866 | DOS Russian | cp866_general_ci | 1 | | cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 | | dec8 | DEC West European | dec8_swedish_ci | 1 | | eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 | | euckr | EUC-KR Korean | euckr_korean_ci | 2 | | gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 | | gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 | | gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 | | geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 | | greek | ISO 8859-7 Greek | greek_general_ci | 1 | | hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 | | hp8 | HP West European | hp8_english_ci | 1 | | keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 | | koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 | | koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 | | latin1 | cp1252 West European | latin1_swedish_ci | 1 | | latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 | | latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 | | latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 | | macce | Mac Central European | macce_general_ci | 1 | | macroman | Mac West European | macroman_general_ci | 1 | | sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 | | swe7 | 7bit Swedish | swe7_swedish_ci | 1 | | tis620 | TIS620 Thai | tis620_thai_ci | 1 | | ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 | | ujis | EUC-JP Japanese | ujis_japanese_ci | 3 | | utf16 | UTF-16 Unicode | utf16_general_ci | 4 | | utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 | | utf32 | UTF-32 Unicode | utf32_general_ci | 4 | | utf8 | UTF-8 Unicode | utf8_general_ci | 3 | | utf8mb4 | UTF-8 Unicode | utf8mb4_0900_ai_ci | 4 | +----------+---------------------------------+---------------------+--------+ |
確認
以下のテーブルを使う。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
mysql> SELECT text FROM testtable; +--------------+ | text | +--------------+ | a | | b | | c | | A | | B | | C | | まま | | ママ | | ﾏﾏ | | はは | | ハハ | | ﾊﾊ | | ぱぱ | | パパ | | ﾊﾟﾊﾟ | | ばば | | ババ | | ﾊﾞﾊﾞ | +--------------+ |
区別
utf8mb4_0900_ai_ci
- 大文字と小文字は区別される
- 全角と半角は区別される
- 平仮名と片仮名は区別される
- 濁点の有無は区別される
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
mysql> SELECT text FROM testtable WHERE text COLLATE utf8mb4_0900_ai_ci IN('a', 'はは'); +--------------+ | text | +--------------+ | a | | A | | はは | | ハハ | | ﾊﾊ | | ぱぱ | | パパ | | ﾊﾟﾊﾟ | | ばば | | ババ | | ﾊﾞﾊﾞ | +--------------+ |
utf8mb4_general_ci
- 大文字と小文字は区別されない
- 全角と半角は区別される
- 平仮名と片仮名は区別される
- 濁点の有無は区別される
|
1 2 3 4 5 6 7 8 |
mysql> SELECT text FROM testtable WHERE text COLLATE utf8mb4_general_ci IN('a', 'はは'); +--------+ | text | +--------+ | a | | A | | はは | +--------+ |
utf8mb4_unicode_ci
- 大文字と小文字は区別されない
- 全角と半角は区別されない
- 平仮名と片仮名は区別されない
- 濁点の有無は区別されない
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
mysql> SELECT text FROM testtable WHERE text COLLATE utf8mb4_unicode_ci IN('a', 'はは'); +--------------+ | text | +--------------+ | a | | A | | はは | | ハハ | | ﾊﾊ | | ぱぱ | | パパ | | ﾊﾟﾊﾟ | | ばば | | ババ | | ﾊﾞﾊﾞ | +--------------+ |
utf8mb4_bin
- 大文字と小文字は区別される
- 全角と半角は区別される
- 平仮名と片仮名は区別される
- 濁点の有無は区別される
|
1 2 3 4 5 6 7 |
mysql> SELECT text FROM testtable WHERE text COLLATE utf8mb4_bin IN('a', 'はは'); +--------+ | text | +--------+ | a | | はは | +--------+ |
順序
utf8mb4_0900_ai_ci
utf8mb4_unicode_ciと同じ。
- アルファベットは小文字・大文字順→アルファベット順
- 仮名は平仮名・片仮名・半角が優先→清音・濁点・半濁点の順
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
mysql> SELECT text FROM testtable ORDER BY text COLLATE utf8mb4_0900_ai_ci; +--------------+ | text | +--------------+ | a | | A | | b | | B | | c | | C | | はは | | ハハ | | ﾊﾊ | | ぱぱ | | パパ | | ﾊﾟﾊﾟ | | ばば | | ババ | | ﾊﾞﾊﾞ | | まま | | ママ | | ﾏﾏ | +--------------+ |
utf8mb4_general_ci
- アルファベットは小文字・大文字順→アルファベット順
- 仮名はユニコード文字順
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
mysql> SELECT text FROM testtable ORDER BY text COLLATE utf8mb4_general_ci; +--------------+ | text | +--------------+ | a | | A | | b | | B | | c | | C | | はは | | ばば | | ぱぱ | | まま | | ハハ | | ババ | | パパ | | ママ | | ﾊﾊ | | ﾊﾞﾊﾞ | | ﾊﾟﾊﾟ | | ﾏﾏ | +--------------+ |
utf8mb4_unicode_ci
Unicodeの文字順と異なる。
- アルファベットは小文字・大文字順→アルファベット順
- 仮名は平仮名・片仮名・半角が優先→清音・濁点・半濁点の順
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
mysql> SELECT text FROM testtable ORDER BY text COLLATE utf8mb4_unicode_ci; +--------------+ | text | +--------------+ | a | | A | | b | | B | | c | | C | | はは | | ハハ | | ﾊﾊ | | ぱぱ | | パパ | | ﾊﾟﾊﾟ | | ばば | | ババ | | ﾊﾞﾊﾞ | | まま | | ママ | | ﾏﾏ | +--------------+ |
utf8mb4_bin
- アルファベット・仮名ともUnicode文字順
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
mysql> SELECT text FROM testtable ORDER BY text COLLATE utf8mb4_bin; +--------------+ | text | +--------------+ | A | | B | | C | | a | | b | | c | | はは | | ばば | | ぱぱ | | まま | | ハハ | | ババ | | パパ | | ママ | | ﾊﾊ | | ﾊﾞﾊﾞ | | ﾊﾟﾊﾟ | | ﾏﾏ | +--------------+ |
こんにちは、とても良い感じでまとめていただいたブログを拝見したのでコメントします
例えば utf8mb4_unicode_ci で
* 全角と半角は区別されない
* 濁点の有無は区別されない
と並列で書かれておりますが
半角濁点と全角濁点は区別することになると思います。
SELECT text FROM testtable WHERE text COLLATE utf8mb4_unicode_ci IN(‘a’, ‘パパ’);
と検索したら 半角の「ﾊﾟﾊﾟ」は引っかからないと思います。
コメントをいただいてありがとうございます。
私の方の環境で今一度、お示しいただいたSELECT文を試してみました。
utf8mb4_unicode_ciの場合：
INの中がはは,ハハ,ババ,パパの全角,半角いずれか
-> いずれの場合も、はは,ハハ,パパ,ババの全角と半角が全てヒット
utf8mb4_general_ciの場合：
INの中がはは,ハハ,ババ,パパの全角,半角いずれか
-> INで指定したものだけヒット
環境あるいは設定で変わってくるのでしょうか。
おかしな点があったらご指摘ください。