Hatena::ブログ(Diary)

end0tknrのkipple - web写経開発 RSSフィード

2010-06-13

mysqlのcollateを使って大文字-小文字や全角-半角を無視した検索

mysqlでは、collate = utf8_unicode_ciを指定すると、大文字-小文字だけでなく、全角-半角を同一視できるそうですが、実際にどの文字が同一視されるのかを試してみました。

collateとは

http://tetlist.info/2009/01/mysql

↑このエントリでも分かりやすく紹介されていますが、collate(照合順序)とは、文字を比較(一致/不一致や表示順)する際のルールです。

utf8_unicode_ciで大文字-小文字だけでなく、全角-半角を同一視

mysqlデフォルトcollateであるutf8_general_ciでは、大文字-小文字を同一視しますが、utf8_unicode_ciでは、さらに半角-全角も同一視します。

ci とは Case Insensitive の略称のようです

tableやcolumn自体にcollateを設定する

tableやcolumn自体に対するcollate設定は、create tableやalter tableで行うことができます。

以下ではalter tableによるcollateの設定方法と、その確認方法を記載しています。

サンプルテーブル

create table test_collate (
id            int,
val	      varchar(4),
note          text,
primary key(id)
);
tableに対するcollate設定と確認
mysql> alter table test_collate  collate utf8_unicode_ci;
Query OK, 0 rows affected (0.36 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show table status like 'test_collate'\G              
*************************** 1. row ***************************
           Name: test_collate
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2010-06-13 19:49:37
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_unicode_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)
columnに対するcollate設定と確認
mysql> alter table test_collate
    ->  modify val varchar(4) CHARACTER SET utf8
    ->  collate utf8_unicode_ci;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table  test_collate\G
*************************** 1. row ***************************
       Table: test_collate
Create Table: CREATE TABLE `test_collate` (
  `id` int(11) NOT NULL DEFAULT '0',
  `val` varchar(4) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `note` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

select時にもcollateを指定可能

多くの方がtableやcolumnに対するcollate指定を紹介していますが、

select時のwhere句等でもcollateを指定できます。

http://dev.mysql.com/doc/refman/5.1/ja/charset-collate.html

例: where句におけるcollate指定

select * from test_collate
where val = 'a' COLLATE utf8_unicode_ci;

select時にもcollateを指定して、大文字-小文字や全角-半角の同一視を確認

今回は、次のテストデータを使用して、collate=utf8_unicode_ci時の大文字-小文字や全角-半角の同一視を確認してみます。

insert into test_collate values(11,'A','半角大文字');
insert into test_collate values(12,'a','半角小文字');
insert into test_collate values(13,'A','全角大文字');
insert into test_collate values(14,'a','全角小文字');

insert into test_collate values(21,'1','半角');
insert into test_collate values(22,'1','全角');
insert into test_collate values(23,'一','漢数字');
insert into test_collate values(24,'壱','漢数字?');
insert into test_collate values(25,'','機種依存文字 丸数字');
insert into test_collate values(26,'','機種依存文字 ローマ数字 大');
insert into test_collate values(27,'','機種依存文字 ローマ数字 小');

insert into test_collate values(31,'*','半角');
insert into test_collate values(32,'*','全角');

insert into test_collate values(41,'け','全角ひらがな');
insert into test_collate values(42,'ケ','全角カタカナ');
insert into test_collate values(43,'げ','全角ひらがな 濁音');
insert into test_collate values(44,'ゲ','全角かたかな 濁音');
insert into test_collate values(45,'ケ','半角カタカナ');
insert into test_collate values(46,'ゲ','半角カタカナ 濁音');
insert into test_collate values(47,'ヶ','全角小書き');

insert into test_collate values(51,'は','全角ひらがな');
insert into test_collate values(52,'ハ','全角カタカナ');
insert into test_collate values(53,'ば','全角ひらがな 濁音');
insert into test_collate values(54,'バ','全角カタカナ 濁音');
insert into test_collate values(55,'ぱ','全角ひらがな 半濁音');
insert into test_collate values(56,'パ','全角カタカナ 半濁音');
insert into test_collate values(57,'ハ','半角カタカナ');
insert into test_collate values(58,'バ','半角カタカナ 濁音');
insert into test_collate values(59,'パ','半角カタカナ 半濁音');

以下では、特に気になったselect結果のみを記載しています

数字の場合、丸数字も同一視

ローマ数字の気糶颪脇碓貉襪任ないと予想していましたが、丸数字が同一視されるとは。

mysql> select * from test_collate
    -> where val = '1' COLLATE utf8_unicode_ci
    -> order by id;
+----+------+------------------------------+
| id | val  | note                         |
+----+------+------------------------------+
| 21 | 1    | 半角                         |
| 22 | 1   | 全角                         |
| 25 |     | 機種依存文字 丸数字          |
+----+------+------------------------------+
3 rows in set (0.00 sec)
「け」の場合「〜ヶ所」のような小書きも同一視

「け」で「〜ヶ所」のような小書きが同一視されるのは予想していませんでした。

mysql> select * from test_collate
    -> where val = 'け' COLLATE utf8_unicode_ci
    -> order by id;
+----+--------+---------------------------+
| id | val    | note                      |
+----+--------+---------------------------+
| 41 | け     | 全角ひらがな              |
| 42 | ケ     | 全角カタカナ              |
| 43 | げ     | 全角ひらがな 濁音         |
| 44 | ゲ     | 全角かたかな 濁音         |
| 45 | ケ      | 半角カタカナ              |
| 46 | ゲ     | 半角カタカナ 濁音         |
| 47 | ヶ     | 全角小書き                |
+----+--------+---------------------------+
7 rows in set (0.00 sec)
半角の濁音や半濁音も同一視

半角カナの濁音等は怪しいかと思いましたが、問題なく同一視できているようです。

mysql> select * from test_collate
    -> where val = 'パ' COLLATE utf8_unicode_ci
    -> order by id;
+----+--------+------------------------------+
| id | val    | note                         |
+----+--------+------------------------------+
| 51 | は     | 全角ひらがな                 |
| 52 | ハ     | 全角カタカナ                 |
| 53 | ば     | 全角ひらがな 濁音            |
| 54 | バ     | 全角カタカナ 濁音            |
| 55 | ぱ     | 全角ひらがな 半濁音          |
| 56 | パ     | 全角カタカナ 半濁音          |
| 57 | ハ      | 半角カタカナ                 |
| 58 | バ     | 半角カタカナ 濁音            |
| 59 | パ     | 半角カタカナ 半濁音          |
+----+--------+------------------------------+
9 rows in set (0.00 sec)

漢字に対してのutf8_unicode_ciは効果がなさそう

おまけとして、齊藤,斉藤,斎藤,齋藤 や 渡邊,渡辺,渡邉 をutf8_unicode_ciで比較してみましたが、いずれも同一視されませんでした。


今回の実験で、utf8_unicode_ciによる大文字-小文字や全角-半角の同一視に

関する動作はなんとなく分かりましたが、どの文字が同一視されるのかを記載した資料ってあるのだろうか?


と、思っていたら...

utf8_unicode_ciの同一視文字は、strings/ctype-uca.c で定義

※2011/4/17追記

MySQLマニュアルによると UCA というアルゴリズムを使用しているようです。

<略>

UCA はちゃんと読んでませんが、文字毎に weight という値が割り当てられていて、同じ weight であれば同じ文字とみなすという感じみたいです。

MySQL のソース中では、strings/ctype-uca.c で処理されています。このファイルから、どの文字が同一視されるかを調べてみました。

結果はこちら→ MySQL 5.5.11 unicode_ci で同一視される文字

これを作成するためのスクリプトhttps://gist.github.com/922928

日本語の範囲で関係あるのは、英数字記号ひらがなカタカナくらいみたいです。

http://d.hatena.ne.jp/tmtms/20110416/mysql_unicode_collation

ありがとうございました。 > id:tmtmsさん

ということは、strings/ctype-uca.c に定義を加えれば、齊藤,斉藤,斎藤,齋藤 や 渡邊,渡辺,渡邉 で同一視できる気がします。

実際のstrings/ctype-uca.cへの変更は、本当に必要になったときに考えます。

はてなユーザーのみコメントできます。はてなへログインもしくは新規登録をおこなってください。