collation(照合順序)の違いにより、「大文字小文字」「全角半角」を区別するかどうか挙動が異なります。ここでは、「デフォルトのcollationの確認方法」と「collationの違いによる影響」をとりあげます。
目次
デフォルトのcollationを確認
utf8mb4 のデフォルトcollationを確認してみます。
mysql> SHOW COLLATION LIKE 'utf8mb4%';
+------------------------+---------+-----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+------------------------+---------+-----+---------+----------+---------+
| utf8mb4_general_ci | utf8mb4 | 45 | Yes | Yes | 1 |
| utf8mb4_bin | utf8mb4 | 46 | | Yes | 1 |
| utf8mb4_unicode_ci | utf8mb4 | 224 | | Yes | 8 |
| utf8mb4_icelandic_ci | utf8mb4 | 225 | | Yes | 8 |
| utf8mb4_latvian_ci | utf8mb4 | 226 | | Yes | 8 |
| utf8mb4_romanian_ci | utf8mb4 | 227 | | Yes | 8 |
| utf8mb4_slovenian_ci | utf8mb4 | 228 | | Yes | 8 |
| utf8mb4_polish_ci | utf8mb4 | 229 | | Yes | 8 |
| utf8mb4_estonian_ci | utf8mb4 | 230 | | Yes | 8 |
| utf8mb4_spanish_ci | utf8mb4 | 231 | | Yes | 8 |
| utf8mb4_swedish_ci | utf8mb4 | 232 | | Yes | 8 |
| utf8mb4_turkish_ci | utf8mb4 | 233 | | Yes | 8 |
| utf8mb4_czech_ci | utf8mb4 | 234 | | Yes | 8 |
| utf8mb4_danish_ci | utf8mb4 | 235 | | Yes | 8 |
| utf8mb4_lithuanian_ci | utf8mb4 | 236 | | Yes | 8 |
| utf8mb4_slovak_ci | utf8mb4 | 237 | | Yes | 8 |
| utf8mb4_spanish2_ci | utf8mb4 | 238 | | Yes | 8 |
| utf8mb4_roman_ci | utf8mb4 | 239 | | Yes | 8 |
| utf8mb4_persian_ci | utf8mb4 | 240 | | Yes | 8 |
| utf8mb4_esperanto_ci | utf8mb4 | 241 | | Yes | 8 |
| utf8mb4_hungarian_ci | utf8mb4 | 242 | | Yes | 8 |
| utf8mb4_sinhala_ci | utf8mb4 | 243 | | Yes | 8 |
| utf8mb4_german2_ci | utf8mb4 | 244 | | Yes | 8 |
| utf8mb4_croatian_ci | utf8mb4 | 245 | | Yes | 8 |
| utf8mb4_unicode_520_ci | utf8mb4 | 246 | | Yes | 8 |
| utf8mb4_vietnamese_ci | utf8mb4 | 247 | | Yes | 8 |
+------------------------+---------+-----+---------+----------+---------+
26 rows in set (0.00 sec)utf8mb4 のデフォルトcollationは、utf8mb4_general_ci になります。
ciはCase Insensitive(大文字と小文字を区別しない)の略です。
collationの違いによる影響
カラム定義でcollationを指定
下記3つのcollationの挙動を確認します。
- utf8mb4_general_ci (デフォルト)
- utf8mb4_unicode_ci
- utf8mb4_bin
下記のように、カラムにcollationを指定できます。
CREATE TABLE `test` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`text_utf8mb4_general_ci` varchar(255) DEFAULT NULL,
`text_utf8mb4_bin` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
`text_utf8mb4_unicode_ci` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`memo` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4;補足になりますが、以下のようにするとテーブルのデフォルトのcollationが utf8mb4_bin になります。
CREATE TABLE `test` (
省略
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;検証データ
検証用にレコードを登録しました。
mysql> SELECT * FROM `test`;
+----+-------------------------+------------------+-------------------------+--------------------+
| id | text_utf8mb4_general_ci | text_utf8mb4_bin | text_utf8mb4_unicode_ci | memo |
+----+-------------------------+------------------+-------------------------+--------------------+
| 1 | あ | あ | あ | 全角ひらがな |
| 2 | ア | ア | ア | 全角カタカナ |
| 3 | ｱ | ｱ | ｱ | 半角カタカナ |
| 4 | A | A | A | 半角大文字 |
| 5 | a | a | a | 半角小文字 |
| 6 | Ａ | Ａ | Ａ | 全角大文字 |
| 7 | ａ | ａ | ａ | 全角小文字 |
+----+-------------------------+------------------+-------------------------+--------------------+
7 rows in set (0.01 sec)utf8mb4_bin
( 大文字小文字区別する )
( 半角全角区別する )
mysql> SELECT * FROM `test` WHERE `text_utf8mb4_bin` = "a";
+----+-------------------------+------------------+-------------------------+-----------------+
| id | text_utf8mb4_general_ci | text_utf8mb4_bin | text_utf8mb4_unicode_ci | memo |
+----+-------------------------+------------------+-------------------------+-----------------+
| 5 | a | a | a | 半角小文字 |
+----+-------------------------+------------------+-------------------------+-----------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM `test` WHERE `text_utf8mb4_bin` = "あ";
+----+-------------------------+------------------+-------------------------+--------------------+
| id | text_utf8mb4_general_ci | text_utf8mb4_bin | text_utf8mb4_unicode_ci | memo |
+----+-------------------------+------------------+-------------------------+--------------------+
| 1 | あ | あ | あ | 全角ひらがな |
+----+-------------------------+------------------+-------------------------+--------------------+
1 row in set (0.00 sec)utf8mb4_general_ci
( 大文字小文字区別しない )
( 半角全角区別する )
mysql> SELECT * FROM `test` WHERE `text_utf8mb4_general_ci` = "a";
+----+-------------------------+------------------+-------------------------+-----------------+
| id | text_utf8mb4_general_ci | text_utf8mb4_bin | text_utf8mb4_unicode_ci | memo |
+----+-------------------------+------------------+-------------------------+-----------------+
| 4 | A | A | A | 半角大文字 |
| 5 | a | a | a | 半角小文字 |
+----+-------------------------+------------------+-------------------------+-----------------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM `test` WHERE `text_utf8mb4_general_ci` = "あ";
+----+-------------------------+------------------+-------------------------+--------------------+
| id | text_utf8mb4_general_ci | text_utf8mb4_bin | text_utf8mb4_unicode_ci | memo |
+----+-------------------------+------------------+-------------------------+--------------------+
| 1 | あ | あ | あ | 全角ひらがな |
+----+-------------------------+------------------+-------------------------+--------------------+
1 row in set (0.00 sec)utf8mb4_unicode_ci
( 大文字小文字区別しない )
( 半角全角区別しない )
mysql> SELECT * FROM `test` WHERE `text_utf8mb4_unicode_ci` = "a";
+----+-------------------------+------------------+-------------------------+-----------------+
| id | text_utf8mb4_general_ci | text_utf8mb4_bin | text_utf8mb4_unicode_ci | memo |
+----+-------------------------+------------------+-------------------------+-----------------+
| 4 | A | A | A | 半角大文字 |
| 5 | a | a | a | 半角小文字 |
| 6 | Ａ | Ａ | Ａ | 全角大文字 |
| 7 | ａ | ａ | ａ | 全角小文字 |
+----+-------------------------+------------------+-------------------------+-----------------+
4 rows in set (0.01 sec)
mysql> SELECT * FROM `test` WHERE `text_utf8mb4_unicode_ci` = "あ";
+----+-------------------------+------------------+-------------------------+--------------------+
| id | text_utf8mb4_general_ci | text_utf8mb4_bin | text_utf8mb4_unicode_ci | memo |
+----+-------------------------+------------------+-------------------------+--------------------+
| 1 | あ | あ | あ | 全角ひらがな |
| 2 | ア | ア | ア | 全角カタカナ |
| 3 | ｱ | ｱ | ｱ | 半角カタカナ |
+----+-------------------------+------------------+-------------------------+--------------------+
3 rows in set (0.00 sec)SQL実行時にCOLLATEを指定
SQL実行時にCOLLATEを指定することもできます。
mysql> SELECT * FROM `test` WHERE `text_utf8mb4_general_ci` = "a" COLLATE utf8mb4_unicode_ci;
+----+-------------------------+------------------+-------------------------+-----------------+
| id | text_utf8mb4_general_ci | text_utf8mb4_bin | text_utf8mb4_unicode_ci | memo |
+----+-------------------------+------------------+-------------------------+-----------------+
| 4 | A | A | A | 半角大文字 |
| 5 | a | a | a | 半角小文字 |
| 6 | Ａ | Ａ | Ａ | 全角大文字 |
| 7 | ａ | ａ | ａ | 全角小文字 |
+----+-------------------------+------------------+-------------------------+-----------------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM `test` WHERE `text_utf8mb4_bin` = "a" COLLATE utf8mb4_unicode_ci;
+----+-------------------------+------------------+-------------------------+-----------------+
| id | text_utf8mb4_general_ci | text_utf8mb4_bin | text_utf8mb4_unicode_ci | memo |
+----+-------------------------+------------------+-------------------------+-----------------+
| 4 | A | A | A | 半角大文字 |
| 5 | a | a | a | 半角小文字 |
| 6 | Ａ | Ａ | Ａ | 全角大文字 |
| 7 | ａ | ａ | ａ | 全角小文字 |
+----+-------------------------+------------------+-------------------------+-----------------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM `test` WHERE `text_utf8mb4_unicode_ci` = "a" COLLATE utf8mb4_bin;
+----+-------------------------+------------------+-------------------------+-----------------+
| id | text_utf8mb4_general_ci | text_utf8mb4_bin | text_utf8mb4_unicode_ci | memo |
+----+-------------------------+------------------+-------------------------+-----------------+
| 5 | a | a | a | 半角小文字 |
+----+-------------------------+------------------+-------------------------+-----------------+
1 row in set (0.00 sec)
