Hatena::ブログ(Diary)

xmallocのプログラミングノート

2010年07月29日

MySQLにインストールされたCHARACTER SETとCOLLATIONの確認方法とカラム別の設定方法

MySQLはVARCHAR型のカラムに対して、(CREATE DATABASE等で指定された)デフォルトのキャラクタセットと照合順序を使用して文字列検索を行います。

CREATE DATABASE mydb DEFAULT CHARACTER SET utf8;

上のように定義したデータベースのキャラクタセットはutf8となり、照合順序はutf8_general_ciとなります。

噛み砕いて言うと、このデータベースではデフォルトでVARCHAR等のカラムのキャラクタセットはutf8となり、utf8なので1文字のサイズは3バイトとなり、文字列型カラムの比較では大文字小文字を区別しなくなります。(_ciはcase insensitiveの略です。)



だいたいの場合上の設定で問題無いですが、たまにカラムごとにキャラクタセットと照合順序を変更したくなります。

例えばVARCHARカラムにエスケープ済みURLを保存したい場合、1文字3バイトは冗長です。インデクスを使用する場合MySQLの制限でインデクス列の合計サイズが1000バイト以下でないといけないので、utf8のままですとVARCHAR(333)あたりが上限となり、URLを保存するカラムとしては微妙に短い気がします。また大文字小文字を区別しないのもURLとしては問題ありです。

で、カラムごとにキャラクタセットと照合順序を変更するにはCHARACTER SETとCOLLATE構文を使用します。例の続きで、URL用カラムならこんな感じにしてます。

CREATE TABLE url_data
(url VARCHAR(1000) CHARACTER SET ascii COLLATE ascii_bin);

ちなみにテーブル単位でキャラクタセットと照合順序を指定することもできますが使ったことないです。

CREATE TABLE url_data
(url VARCHAR(1000))
CHARACTER SET ascii COLLATE ascii_bin;


キャラクタセットと照合順序を変更するには、それぞれサーバで指定可能な値を調べる必要があります。

ログインしているMySQLデータベースでどういったキャラクタセットがサポートされているかは、SHOW CHARACTER SET命令で確認できます。Default collationがそのキャラクタセットのデフォルト照合順序です。COLLATEを指定せずにCHARACTER SETだけ指定するとDefault collationが照合順序となります。

mysql> SHOW CHARACTER SET;
+----------+-----------------------------+---------------------+--------+
| Charset  | Description                 | Default collation   | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese    | big5_chinese_ci     |      2 | 
| dec8     | DEC West European           | dec8_swedish_ci     |      1 | 
| cp850    | DOS West European           | cp850_general_ci    |      1 | 
| hp8      | HP West European            | hp8_english_ci      |      1 | 
| koi8r    | KOI8-R Relcom Russian       | koi8r_general_ci    |      1 | 
| latin1   | cp1252 West European        | latin1_swedish_ci   |      1 | 
| latin2   | ISO 8859-2 Central European | latin2_general_ci   |      1 | 
| swe7     | 7bit Swedish                | swe7_swedish_ci     |      1 | 
| ascii    | US ASCII                    | ascii_general_ci    |      1 | 
| ujis     | EUC-JP Japanese             | ujis_japanese_ci    |      3 | 
| sjis     | Shift-JIS Japanese          | sjis_japanese_ci    |      2 | 
| hebrew   | ISO 8859-8 Hebrew           | hebrew_general_ci   |      1 | 
| tis620   | TIS620 Thai                 | tis620_thai_ci      |      1 | 
| euckr    | EUC-KR Korean               | euckr_korean_ci     |      2 | 
| koi8u    | KOI8-U Ukrainian            | koi8u_general_ci    |      1 | 
| gb2312   | GB2312 Simplified Chinese   | gb2312_chinese_ci   |      2 | 
| greek    | ISO 8859-7 Greek            | greek_general_ci    |      1 | 
| cp1250   | Windows Central European    | cp1250_general_ci   |      1 | 
| gbk      | GBK Simplified Chinese      | gbk_chinese_ci      |      2 | 
| latin5   | ISO 8859-9 Turkish          | latin5_turkish_ci   |      1 | 
| armscii8 | ARMSCII-8 Armenian          | armscii8_general_ci |      1 | 
| utf8     | UTF-8 Unicode               | utf8_general_ci     |      3 | 
| ucs2     | UCS-2 Unicode               | ucs2_general_ci     |      2 | 
| cp866    | DOS Russian                 | cp866_general_ci    |      1 | 
| keybcs2  | DOS Kamenicky Czech-Slovak  | keybcs2_general_ci  |      1 | 
| macce    | Mac Central European        | macce_general_ci    |      1 | 
| macroman | Mac West European           | macroman_general_ci |      1 | 
| cp852    | DOS Central European        | cp852_general_ci    |      1 | 
| latin7   | ISO 8859-13 Baltic          | latin7_general_ci   |      1 | 
| cp1251   | Windows Cyrillic            | cp1251_general_ci   |      1 | 
| cp1256   | Windows Arabic              | cp1256_general_ci   |      1 | 
| cp1257   | Windows Baltic              | cp1257_general_ci   |      1 | 
| binary   | Binary pseudo charset       | binary              |      1 | 
| geostd8  | GEOSTD8 Georgian            | geostd8_general_ci  |      1 | 
| cp932    | SJIS for Windows Japanese   | cp932_japanese_ci   |      2 | 
| eucjpms  | UJIS for Windows Japanese   | eucjpms_japanese_ci |      3 | 
+----------+-----------------------------+---------------------+--------+
36 rows in set (0.00 sec)

照合順序の確認はSHOW COLLATIONです。_ciで終わるCollationはcase insensitive比較、_binで終わるCollationがバイナリとして比較です。MySQL4とかだと_csで終わる照合順序もあったと思いますが、5.1だと無いっぽいです。

mysql> SHOW COLLATION;
+----------------------+----------+-----+---------+----------+---------+
| Collation            | Charset  | Id  | Default | Compiled | Sortlen |
+----------------------+----------+-----+---------+----------+---------+
| big5_chinese_ci      | big5     |   1 | Yes     | Yes      |       1 | 
| big5_bin             | big5     |  84 |         | Yes      |       1 | 
| dec8_swedish_ci      | dec8     |   3 | Yes     | Yes      |       1 | 
| dec8_bin             | dec8     |  69 |         | Yes      |       1 | 
| cp850_general_ci     | cp850    |   4 | Yes     | Yes      |       1 | 
| cp850_bin            | cp850    |  80 |         | Yes      |       1 | 
| hp8_english_ci       | hp8      |   6 | Yes     | Yes      |       1 | 
| hp8_bin              | hp8      |  72 |         | Yes      |       1 | 
| koi8r_general_ci     | koi8r    |   7 | Yes     | Yes      |       1 | 
| koi8r_bin            | koi8r    |  74 |         | Yes      |       1 | 
| latin1_german1_ci    | latin1   |   5 |         | Yes      |       1 | 
| latin1_swedish_ci    | latin1   |   8 | Yes     | Yes      |       1 | 
| latin1_danish_ci     | latin1   |  15 |         | Yes      |       1 | 
| latin1_german2_ci    | latin1   |  31 |         | Yes      |       2 | 
| latin1_bin           | latin1   |  47 |         | Yes      |       1 | 
| latin1_general_ci    | latin1   |  48 |         | Yes      |       1 | 
| latin1_general_cs    | latin1   |  49 |         | Yes      |       1 | 
| latin1_spanish_ci    | latin1   |  94 |         | Yes      |       1 | 
| latin2_czech_cs      | latin2   |   2 |         | Yes      |       4 | 
| latin2_general_ci    | latin2   |   9 | Yes     | Yes      |       1 | 
| latin2_hungarian_ci  | latin2   |  21 |         | Yes      |       1 | 
| latin2_croatian_ci   | latin2   |  27 |         | Yes      |       1 | 
| latin2_bin           | latin2   |  77 |         | Yes      |       1 | 
| swe7_swedish_ci      | swe7     |  10 | Yes     | Yes      |       1 | 
| swe7_bin             | swe7     |  82 |         | Yes      |       1 | 
| ascii_general_ci     | ascii    |  11 | Yes     | Yes      |       1 | 
| ascii_bin            | ascii    |  65 |         | Yes      |       1 | 
| ujis_japanese_ci     | ujis     |  12 | Yes     | Yes      |       1 | 
| ujis_bin             | ujis     |  91 |         | Yes      |       1 | 
| sjis_japanese_ci     | sjis     |  13 | Yes     | Yes      |       1 | 
| sjis_bin             | sjis     |  88 |         | Yes      |       1 | 
| hebrew_general_ci    | hebrew   |  16 | Yes     | Yes      |       1 | 
| hebrew_bin           | hebrew   |  71 |         | Yes      |       1 | 
| tis620_thai_ci       | tis620   |  18 | Yes     | Yes      |       4 | 
| tis620_bin           | tis620   |  89 |         | Yes      |       1 | 
| euckr_korean_ci      | euckr    |  19 | Yes     | Yes      |       1 | 
| euckr_bin            | euckr    |  85 |         | Yes      |       1 | 
| koi8u_general_ci     | koi8u    |  22 | Yes     | Yes      |       1 | 
| koi8u_bin            | koi8u    |  75 |         | Yes      |       1 | 
| gb2312_chinese_ci    | gb2312   |  24 | Yes     | Yes      |       1 | 
| gb2312_bin           | gb2312   |  86 |         | Yes      |       1 | 
| greek_general_ci     | greek    |  25 | Yes     | Yes      |       1 | 
| greek_bin            | greek    |  70 |         | Yes      |       1 | 
| cp1250_general_ci    | cp1250   |  26 | Yes     | Yes      |       1 | 
| cp1250_czech_cs      | cp1250   |  34 |         | Yes      |       2 | 
| cp1250_croatian_ci   | cp1250   |  44 |         | Yes      |       1 | 
| cp1250_bin           | cp1250   |  66 |         | Yes      |       1 | 
| gbk_chinese_ci       | gbk      |  28 | Yes     | Yes      |       1 | 
| gbk_bin              | gbk      |  87 |         | Yes      |       1 | 
| latin5_turkish_ci    | latin5   |  30 | Yes     | Yes      |       1 | 
| latin5_bin           | latin5   |  78 |         | Yes      |       1 | 
| armscii8_general_ci  | armscii8 |  32 | Yes     | Yes      |       1 | 
| armscii8_bin         | armscii8 |  64 |         | Yes      |       1 | 
| utf8_general_ci      | utf8     |  33 | Yes     | Yes      |       1 | 
| utf8_bin             | utf8     |  83 |         | Yes      |       1 | 
| utf8_unicode_ci      | utf8     | 192 |         | Yes      |       8 | 
| utf8_icelandic_ci    | utf8     | 193 |         | Yes      |       8 | 
| utf8_latvian_ci      | utf8     | 194 |         | Yes      |       8 | 
| utf8_romanian_ci     | utf8     | 195 |         | Yes      |       8 | 
| utf8_slovenian_ci    | utf8     | 196 |         | Yes      |       8 | 
| utf8_polish_ci       | utf8     | 197 |         | Yes      |       8 | 
| utf8_estonian_ci     | utf8     | 198 |         | Yes      |       8 | 
| utf8_spanish_ci      | utf8     | 199 |         | Yes      |       8 | 
| utf8_swedish_ci      | utf8     | 200 |         | Yes      |       8 | 
| utf8_turkish_ci      | utf8     | 201 |         | Yes      |       8 | 
| utf8_czech_ci        | utf8     | 202 |         | Yes      |       8 | 
| utf8_danish_ci       | utf8     | 203 |         | Yes      |       8 | 
| utf8_lithuanian_ci   | utf8     | 204 |         | Yes      |       8 | 
| utf8_slovak_ci       | utf8     | 205 |         | Yes      |       8 | 
| utf8_spanish2_ci     | utf8     | 206 |         | Yes      |       8 | 
| utf8_roman_ci        | utf8     | 207 |         | Yes      |       8 | 
| utf8_persian_ci      | utf8     | 208 |         | Yes      |       8 | 
| utf8_esperanto_ci    | utf8     | 209 |         | Yes      |       8 | 
| utf8_hungarian_ci    | utf8     | 210 |         | Yes      |       8 | 
| ucs2_general_ci      | ucs2     |  35 | Yes     | Yes      |       1 | 
| ucs2_bin             | ucs2     |  90 |         | Yes      |       1 | 
| ucs2_unicode_ci      | ucs2     | 128 |         | Yes      |       8 | 
| ucs2_icelandic_ci    | ucs2     | 129 |         | Yes      |       8 | 
| ucs2_latvian_ci      | ucs2     | 130 |         | Yes      |       8 | 
| ucs2_romanian_ci     | ucs2     | 131 |         | Yes      |       8 | 
| ucs2_slovenian_ci    | ucs2     | 132 |         | Yes      |       8 | 
| ucs2_polish_ci       | ucs2     | 133 |         | Yes      |       8 | 
| ucs2_estonian_ci     | ucs2     | 134 |         | Yes      |       8 | 
| ucs2_spanish_ci      | ucs2     | 135 |         | Yes      |       8 | 
| ucs2_swedish_ci      | ucs2     | 136 |         | Yes      |       8 | 
| ucs2_turkish_ci      | ucs2     | 137 |         | Yes      |       8 | 
| ucs2_czech_ci        | ucs2     | 138 |         | Yes      |       8 | 
| ucs2_danish_ci       | ucs2     | 139 |         | Yes      |       8 | 
| ucs2_lithuanian_ci   | ucs2     | 140 |         | Yes      |       8 | 
| ucs2_slovak_ci       | ucs2     | 141 |         | Yes      |       8 | 
| ucs2_spanish2_ci     | ucs2     | 142 |         | Yes      |       8 | 
| ucs2_roman_ci        | ucs2     | 143 |         | Yes      |       8 | 
| ucs2_persian_ci      | ucs2     | 144 |         | Yes      |       8 | 
| ucs2_esperanto_ci    | ucs2     | 145 |         | Yes      |       8 | 
| ucs2_hungarian_ci    | ucs2     | 146 |         | Yes      |       8 | 
| cp866_general_ci     | cp866    |  36 | Yes     | Yes      |       1 | 
| cp866_bin            | cp866    |  68 |         | Yes      |       1 | 
| keybcs2_general_ci   | keybcs2  |  37 | Yes     | Yes      |       1 | 
| keybcs2_bin          | keybcs2  |  73 |         | Yes      |       1 | 
| macce_general_ci     | macce    |  38 | Yes     | Yes      |       1 | 
| macce_bin            | macce    |  43 |         | Yes      |       1 | 
| macroman_general_ci  | macroman |  39 | Yes     | Yes      |       1 | 
| macroman_bin         | macroman |  53 |         | Yes      |       1 | 
| cp852_general_ci     | cp852    |  40 | Yes     | Yes      |       1 | 
| cp852_bin            | cp852    |  81 |         | Yes      |       1 | 
| latin7_estonian_cs   | latin7   |  20 |         | Yes      |       1 | 
| latin7_general_ci    | latin7   |  41 | Yes     | Yes      |       1 | 
| latin7_general_cs    | latin7   |  42 |         | Yes      |       1 | 
| latin7_bin           | latin7   |  79 |         | Yes      |       1 | 
| cp1251_bulgarian_ci  | cp1251   |  14 |         | Yes      |       1 | 
| cp1251_ukrainian_ci  | cp1251   |  23 |         | Yes      |       1 | 
| cp1251_bin           | cp1251   |  50 |         | Yes      |       1 | 
| cp1251_general_ci    | cp1251   |  51 | Yes     | Yes      |       1 | 
| cp1251_general_cs    | cp1251   |  52 |         | Yes      |       1 | 
| cp1256_general_ci    | cp1256   |  57 | Yes     | Yes      |       1 | 
| cp1256_bin           | cp1256   |  67 |         | Yes      |       1 | 
| cp1257_lithuanian_ci | cp1257   |  29 |         | Yes      |       1 | 
| cp1257_bin           | cp1257   |  58 |         | Yes      |       1 | 
| cp1257_general_ci    | cp1257   |  59 | Yes     | Yes      |       1 | 
| binary               | binary   |  63 | Yes     | Yes      |       1 | 
| geostd8_general_ci   | geostd8  |  92 | Yes     | Yes      |       1 | 
| geostd8_bin          | geostd8  |  93 |         | Yes      |       1 | 
| cp932_japanese_ci    | cp932    |  95 | Yes     | Yes      |       1 | 
| cp932_bin            | cp932    |  96 |         | Yes      |       1 | 
| eucjpms_japanese_ci  | eucjpms  |  97 | Yes     | Yes      |       1 | 
| eucjpms_bin          | eucjpms  |  98 |         | Yes      |       1 | 
+----------------------+----------+-----+---------+----------+---------+
126 rows in set (0.00 sec)


以上、使用頻度が少なくてよく忘れるのでブログにメモでした。

より詳しくはMySQL :: MySQL 5.1 リファレンスマニュアル :: 9.3 デフォルトのキャラクタセットおよび照合順序の指定をどうぞ。