Collation(デフォルト照合順序, 大文字と小文字, 半角と全角)

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 | A                      | A               | A                      | 全角大文字         |
|  7 | a                      | a               | a                      | 全角小文字         |
+----+-------------------------+------------------+-------------------------+--------------------+
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 | A                      | A               | A                      | 全角大文字      |
|  7 | a                      | a               | a                      | 全角小文字      |
+----+-------------------------+------------------+-------------------------+-----------------+
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 | A                      | A               | A                      | 全角大文字      |
|  7 | a                      | a               | a                      | 全角小文字      |
+----+-------------------------+------------------+-------------------------+-----------------+
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 | A                      | A               | A                      | 全角大文字      |
|  7 | a                      | a               | a                      | 全角小文字      |
+----+-------------------------+------------------+-------------------------+-----------------+
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)

参考