MySQL Server にはサーバキャラクタセットとサーバ照合順序があり、いずれもヌルにすることはできません。

MySQL では、サーバキャラクタセットとサーバ照合順序が次のように決定されます。

このレベルでは、決定は簡単です。サーバのキャラクタセットと照合順序は、mysqld の起動時に使用するオプションに依存します。--default-character-set=character_set_name をキャラクタセットに対して使用でき、--default-collation=collation_name を照合順序に対して追加することもできます。文字コードを指定しないのは、--default-character-set=latin1 を指定した場合と同じです。キャラクタセット（たとえば latin1）のみを指定して照合順序を指定しないのは、--default-charset=latin1 --collation=latin1_swedish_ci を指定した場合と同じです。これは latin1 のデフォルト照合順序が latin1_swedish_ci であるためです。 したがって、以下の 3 つのコマンドを実行すると、いずれも同じ結果になります。

shell> mysqld
shell> mysqld --default-character-set=latin1
shell> mysqld --default-character-set=latin1
           --default-collation=latin1_swedish_ci

設定を変更する手段の 1 つは再コンパイルです。ソースからのビルド時にデフォルトのサーバキャラクタセットと照合順序を変更するには、--with-character-set と --with-collation を configure の引数として使用してください。例:

shell> ./configure --with-character-set=latin1

または

shell> ./configure --with-character-set=latin1
           --with-collation=latin1_german1_ci

mysqld と configure では、キャラクタセットと照合順序の有効性がチェックされます。組み合わせが有効でない場合、各プログラムによってエラーメッセージが表示され、強制終了されます。

各データベースにはデータベースキャラクタセットとデータベース照合順序があり、いずれもヌルにすることはできません。CREATE DATABASE および ALTER DATABASE コマンドには現在、データベースのキャラクタセットと照合順序を指定するためのオプション節があります。

CREATE DATABASE db_name
   [DEFAULT CHARACTER SET character_set_name [COLLATE collation_name]]

ALTER DATABASE db_name
    [DEFAULT CHARACTER SET character_set_name [COLLATE collation_name]]

例:

CREATE DATABASE db_name
   DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;

MySQL では、データベースキャラクタセットとデータベース照合順序が次のように選択されます。

MySQL の CREATE DATABASE ... DEFAULT CHARACTER SET ... 構文は標準 SQL CREATE SCHEMA ... CHARACTER SET ... 構文に類似しています。このため、キャラクタセットと照合順序が異なる複数のデータベースを同一の MySQL サーバ上に作成することができます。

テーブルのキャラクタセットと照合順序が CREATE TABLE ステートメントに指定されていない場合、データベースのキャラクタセットと照合順序はデフォルト値として使用されます。これらに他の用途はありません。

各テーブルにはテーブルキャラクタセットとテーブル照合順序があり、いずれもヌルにすることはできません。CREATE TABLE および ALTER TABLE ステートメントには現在、テーブルのキャラクタセットと照合順序を指定するためのオプション節があります。

CREATE TABLE table_name ( column_list )
   [DEFAULT CHARACTER SET character_set_name [COLLATE collation_name]]

ALTER TABLE table_name
   [DEFAULT CHARACTER SET character_set_name] [COLLATE collation_name]

例:

CREATE TABLE t1 ( ... ) DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci;

MySQL では、テーブルキャラクタセットとテーブル照合順序が次のように選択されます。

カラムのキャラクタセットと照合順序が個別のカラム定義に指定されていない場合、テーブルのキャラクタセットと照合順序はデフォルト値として使用されます。テーブルのキャラクタセットと照合順序は MySQL 拡張であり、同等の機能は標準 SQL に存在しません。

各 ``文字'' カラム（CHAR、VARCHAR または TEXT型）にはカラムキャラクタセットとカラム照合順序があり、いずれもヌルにすることはできません。カラム定義構文には現在、カラムキャラクタセットとカラム照合順序を指定するためのオプション節があります。

column_name {CHAR | VARCHAR | TEXT} (column_length)
    [CHARACTER SET character_set_name [COLLATE collation_name]]

例:

CREATE TABLE Table1
(
   column1 VARCHAR(5) CHARACTER SET latin1 COLLATE latin1_german1_ci
);

MySQL では、カラムキャラクタセットとカラム照合順序が次のように選択されます。

CHARACTER SET および COLLATE 節は標準 SQL です。

MySQL でデフォルトのキャラクタセットと照合順序の値がどのように決定されるかを、以下の例で示します。

例 1:テーブル + カラム定義

CREATE TABLE t1
(
  c1 CHAR(10) CHARACTER SET latin1 COLLATE latin1_german1_ci
) DEFAULT CHARACTER SET latin2 COLLATE latin2_bin;

ここでは、latin1 キャラクタセットと latin1_german1_ci 照合順序がカラムに指定されています。定義は明示的なので、直接的と言えます。なお、latin1 カラムの保存先が latin2 テーブルになっていることに問題はありません。

例 2:テーブル + カラム定義

CREATE TABLE t1
(
   c1 CHAR(10) CHARACTER SET latin1
) DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci;

ここでは、latin1 キャラクタセットとデフォルト照合順序がカラムに指定されています。自然な指定に見えますが、デフォルト照合順序はテーブルレベルから取り込まれません。latin1 のデフォルト照合順序は常に latin1_swedish_ci です。したがって、カラム c1 には latin1_danish_ci の照合順序ではなく latin1_swedish_ci の照合順序が設定されます。

例 3:テーブル + カラム定義

CREATE TABLE t1
(
   c1 CHAR(10)
) DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci;

ここでは、デフォルトキャラクタセットとデフォルト照合順序がカラムに指定されています。この場合に MySQL では、テーブルレベルまで検索してカラムのキャラクタセットと照合順序が決定されます。したがって、カラム c1 のキャラクタセットは latin1、照合順序は latin1_danish_ci となります。

例 4:データベース + テーブル + カラム定義

CREATE DATABASE d1 DEFAULT CHARACTER SET latin2 COLLATE latin2_czech_ci;
USE d1;
CREATE TABLE t1
(
   c1 CHAR(10)
);

キャラクタセットと照合順序を指定せずにカラムを作成します。テーブルレベルのキャラクタセットと照合順序も指定しません。この場合に MySQL では、データベースレベルまでさかのぼって処理が決定されます。（データベースの設定はテーブルの設定になり、そしてカラムの設定になります。）したがって、カラム c1 のキャラクタセットは latin2、照合順序は latin2_czech_ci となります。

各接続には接続キャラクタセットと接続照合順序があり、いずれもヌルにすることはできません。実際には 2 つの接続キャラクタセットが存在するため、両者の区別が必要な場合は ``接続/リテラル'' および ``接続/結果'' の呼称を使い分けています。

``接続'' とは、サーバへの接続時に作成されるものです。クライアントは接続を介し、SQL ステートメント（クエリなど）をサーバに送信します。サーバでは接続を介し、応答（結果セットなど）をクライアントに返します。これによって、次のような疑問が生じます。(a) クライアントから送信される際にどのキャラクタセットでクエリが送られるのか。(b) サーバではクエリを受信した後にどのキャラクタセットに変換するのか。(c) サーバでは結果セットまたはエラーメッセージをクライアントに返送する前にどのキャラクタセットに変換するのか。これらは細かく調整することができますが、デフォルトを適用することもできます。デフォルトを適用する場合、このセクションをとばしてかまいません。

接続キャラクタセットに影響するステートメントが 2 つ存在します。

SET NAMES character_set_name
SET CHARACTER SET character_set_name

SET NAMES は、クライアントから送信される SQL ステートメントのキャラクタセットを示します。たとえば、SET NAMES cp1251 は 「このクライアントからの入力メッセージは今後、キャラクタセット cp1251 になります」 とサーバに通知します。サーバでは適宜、独自のキャラクタセットへと自由に変換することができます。

SET CHARACTER SET は、クライアントから送信される SQL ステートメントのキャラクタセットと、サーバからクライアントに返される結果セットのキャラクタセットを示します。そのため SET CHARACTER SET は、SET NAMES を含んでいるほか、たとえば SELECT ステートメントを使用する際にどのキャラクタセットでカラムに値が保持されるかを示します。

例:column1 が CHAR(5) CHARACTER SET latin2 として定義されているとします。SET CHARACTER SET が指定されていない場合、SELECT column1 FROM t に対しサーバは、キャラクタセット latin2 を使用して column1 の値をすべて返します。一方、SET CHARACTER SET latin1 が指定されている場合、サーバは送信前に latin2 の値を latin1 に変換します。そのような変換は低速であり、損失につながることもあります。

SET NAMES または SET CHARACTER SET の実行時には、``接続照合順序'' も変更していることになります。ただし、接続照合順序は整合性の維持のみを目的として存在しています。通常、その値は重要ではありません。

mysql クライアントでは、起動するたびに SET NAMES を実行する必要はありません。--default-character-set-name オプション設定を mysql のコマンドラインか、オプションファイルに追加することができます。 たとえば、以下のオプション設定ファイルの設定では、mysql を実行するたびに接続キャラクタセットが指定されます。

[mysql]
default-character-set-name=character_set_name

各文字列リテラルにはキャラクタセットと照合順序があり、いずれもヌルにすることはできません。

文字列リテラルでは、オプションとしてキャラクタセットイントロデューサと COLLATE 節を指定することができます。

[_character_set_name]'string' [COLLATE collation_name]

例:

SELECT 'string';
SELECT _latin1'string';
SELECT _latin1'string' COLLATE latin1_danish_ci;

単純なステートメント SELECT 'string' では、接続/リテラルキャラクタセットが使用されます。

_character_set_name は以前、イントロデューサと呼ばれていました。指定すると、``キャラクタセット X の文字列が後続する'' ことがパーサに通知されます。 上記はユーザの混乱を招いていたため、ここで強調しておきますが、イントロデューサは変換の原因にはならず、文字列の値が変更されないことを示すにすぎません。標準的な 16 進リテラルおよび数値 16 進リテラルの表記（x'literal' および 0xnnnn）の前と ?（用意されたステートメントをプログラミング言語インタフェースで使用する際のパラメータ代替）の前でも、イントロデューサは有効です。

例:

SELECT _latin1 x'AABBCC';
SELECT _latin1 0xAABBCC;
SELECT _latin1 ?;

MySQL では、リテラルのキャラクタセットおよび照合順序が次のように決定されます。

例:

キャラクタセットイントロデューサと COLLATE 節は、標準 SQL の指定に基づき実装されます。

COLLATE 節では、比較に対するデフォルト照合順序が何であれ、無効にすることができます。SQL クエリのさまざまな個所で COLLATE を使用することができます。以下に例を示します。

COLLATE 節は優先順位が高い（|| よりも上）ので、式

x || y COLLATE z

は以下と同じ意味になります。

x || (y COLLATE z)

BINARY 演算子は、COLLATE 節の省略形です。たとえば、BINARY 'x' は 'x' COLLATE y と同じであり、この場合に y は該当するバイナリ照合順序の名称を表します。たとえば、カラム a のキャラクタセットが latin1 であると仮定すると、以下のクエリはどちらも同じ結果をもたらします。

SELECT * FROM t1 ORDER BY BINARY a;
SELECT * FROM t1 ORDER BY a COLLATE latin1_bin;

注意:どのキャラクタセットにもバイナリ照合順序があります。

大多数のクエリでは、MySQL がどの照合順序により比較演算を行うかは明確になっています。たとえば以下の場合、照合順序が ``カラム x のカラム照合順序'' になることは明らかです。

SELECT x FROM T ORDER BY x;
SELECT x FROM T WHERE x = x;
SELECT DISTINCT x FROM T;

ただし、複数のオペランドが使用されていると、あいまいになることがあります。たとえば、以下のような場合です。

SELECT x FROM T WHERE x = 'Y';

x の照合順序と文字列リテラル 'Y' の照合順序のどちらがこのクエリで使用されるのでしょうか。

標準 SQL では、``強制可能'' ルールと呼ばれていた方法で上記の問題を解決しました。これについては、以下の発想が基本となっています: x と 'Y' の両方に照合順序があるので、どちらの照合順序を優先するかを判断しなければならない。複雑な問題だが、これらのルールでほとんどの状況に対応できる。

これらのルールによって、あいまいさは次のように解消されます。

例:

各キャラクタセットには 1 つ以上の照合順序があり、各照合順序は 1 つのキャラクタセットに関連付けられていることを思い出してください。したがって、次のステートメントはエラーになります。latin2_bin 照合順序は latin1 キャラクタセットに対して有効でないからです。

mysql> SELECT _latin1 'x' COLLATE latin2_bin;
ERROR 1251: COLLATION 'latin2_bin' is not valid
for CHARACTER SET 'latin1'

テーブル T のカラム X に以下の latin1 カラムの値が設定されているとします。

Muffler
Muller
MX Systems
MySQL

さらに、以下のステートメントを使用してカラムの値を取得したとします。

SELECT X FROM T ORDER BY X COLLATE collation_name;

異なった照合順序に対する値の順序は以下のようになります。

この表は、複数の異なった照合順序を ORDER BY 節で使用した結果の一例です。この例では、2 個の点が上に付いている U が問題の原因です。この文字をドイツではウムラウトと呼んでいますが、私たちは分音記号付きの U と呼んでいます。

最初のカラムには、スウェーデン語/フィンランド語の照合ルールを使用したSELECT の結果が示されています。この照合ルールによると、分音記号付きの U は Y と一致します。

2 番目のカラムには、ドイツ語の DIN-1 ルールを使用した SELECT の結果が示されています。このルールによると、分音記号付きの U は U と一致します。

3 番目のカラムには、ドイツ語の DIN-2 ルールを使用したSELECT の結果が示されています。このルールによると、分音記号付きの U は UE と一致します。

3 つの異なった照合順序が、3 つの異なった結果をもたらしています。これこそ正に MySQL で対処すべき状況です。適切な照合順序を使用すると、任意のソート順序を選択できます。

MySQL には、文字列を返す多数の演算子と関数があります。 このセクションでは、そのような文字列のキャラクタセットと照合順序について解説しています。

文字列の入力を取得して文字列の結果を出力として返す単純な関数では、出力のキャラクタセットおよび照合順序は主要な入力のキャラクタセットおよび照合順序と同じです。たとえば UPPER(X) は、キャラクタセットおよび照合が X と一致する文字列を返します。 同じことは以下についても当てはまります。 INSTR(), LCASE(), LOWER(), LTRIM(), MID(), REPEAT(), REPLACE(), REVERSE(), RIGHT(), RPAD(), RTRIM(), SOUNDEX(), SUBSTRING(), TRIM(), UCASE(), UPPER()。 （注意: REPLACE() 関数は他のすべての関数とは異なり、文字列入力の照合順序を無視し、大文字と小文字が区別されない比較を毎回実行します。）

複数の文字列入力を組み合わせて単一の文字列出力を返す操作には、SQL-99 の ``集約ルール'' が適用されます（以下を参照）。

たとえば、CASE ... WHEN a THEN b WHEN b THEN c COLLATE X END と指定されている場合、照合順序は X になります。同じことは以下についても当てはまります。 CONCAT(), GREATEST(), IF(), LEAST(), CASE, UNION, ||, ELT()。

文字データに変換する操作のため、結果文字列のキャラクタセットと照合順序は 接続/リテラルキャラクタセットに含まれており、接続/リテラル照合順序を持っています。 このことは以下についても当てはまります。 CHAR(), CAST(), CONV(), FORMAT()。 HEX(), SPACE()。

CONVERT() を使用すると、異なるキャラクタセット間でデータを変換することができます。構文は以下のとおりです。

CONVERT(expr USING transcoding_name)

MySQL では、トランスコーディング名は対応するキャラクタセット名と同じです。

例:

SELECT CONVERT(_latin1'Muller' USING utf8);
INSERT INTO utf8table (utf8column)
   SELECT CONVERT(latin1field USING utf8) FROM latin1table;

CONVERT(... USING ...) は、SQL-99 の仕様に基づき実装されています。

CAST() を使用し、文字列を別のキャラクタセットに変換することもできます。新しい書式は以下のとおりです。

CAST ( character_string AS character_data_type
    CHARACTER SET character_set_name )

例:

SELECT CAST(_latin1'test' AS CHAR CHARACTER SET utf8);

COLLATE 節を CAST() の内部で使用することはできませんが、外部では使用することができます。したがって、CAST(... COLLATE ...) は無効ですが、CAST(...) COLLATE ... は有効です。

例:

SELECT CAST(_latin1'test' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin;

CAST() を CHARACTER SET の指定なしで使用した場合、キャラクタセットと照合順序は接続/リテラルキャラクタセットとそのデフォルト照合順序になります。CAST() を CHARACTER SET X の指定ありで使用した場合、キャラクタセットは X、照合順序は X のデフォルト照合順序になります。

SHOW CHARACTER SET コマンドを実行すると、使用可能なキャラクタセットがすべて表示されます。 オプション節 LIKE を指定すると、条件にマッチするキャラクタセットを表示することができます。

例:

mysql> SHOW CHARACTER SET LIKE 'latin%';
+---------+-----------------------------+-------------------+--------+
| Charset | Description                 | Default collation | Maxlen |
+---------+-----------------------------+-------------------+--------+
| latin1  | ISO 8859-1 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 |
+---------+-----------------------------+-------------------+--------+
4 rows in set (0.00 sec)

上の一覧に関する注意:

SHOW COLLATION コマンドを実行すると、使用可能なキャラクタセットがすべて表示されます。 オプション節 LIKE を指定すると、条件にマッチする照合順序名が表示されます。

mysql> SHOW COLLATION LIKE 'latin1%';
+-------------------+---------+----+---------+----------+---------+
| Collation         | Charset | Id | Default | Compiled | Sortlen |
+-------------------+---------+----+---------+----------+---------+
| latin1_german1_ci | latin1  |  5 |         |          |       0 |
| latin1_swedish_ci | latin1  |  8 | Yes     | Yes      |       0 |
| latin1_danish_ci  | latin1  | 15 |         |          |       0 |
| latin1_german2_ci | latin1  | 31 |         | Yes      |       2 |
| latin1_bin        | latin1  | 47 |         | Yes      |       0 |
| latin1_general_ci | latin1  | 48 |         |          |       0 |
| latin1_general_cs | latin1  | 49 |         |          |       0 |
+-------------------+---------+----+---------+----------+---------+
7 rows in set (0.00 sec)

Default カラムは、照合順序がそのキャラクタセットのデフォルトであるかどうかを示します。 Compiled は、キャラクタセットがサーバにコンパイルされているかどうかを示します。 Sortlen は、キャラクタセットでソート文字列を表現するために必要なメモリの容量に関連します。

以下のクエリは、指定されたデータベースを作成する CREATE DATABASE ステートメントを示します。実行結果には、データベースオプションがすべて含まれています。DEFAULT CHARACTER SET と COLLATE がサポートされています。データベースオプションがすべて保存されている単一のテキストファイルがデータベースディレクトリにあります。

mysql> SHOW CREATE DATABASE a;
+----------+-----------------------------------------------------------+
| Database | Create Database
|
+----------+-----------------------------------------------------------+
| a        | CREATE DATABASE `a` /*!40100 DEFAULT CHARACTER SET macce
COLLATE macce_ci_ai */ |
+----------+-----------------------------------------------------------+
1 row in set (0.00 sec)

SHOW COLUMNS ステートメントを SHOW FULL COLUMNS として呼び出したときは、テーブルのカラムの照合順序を示します。 データ型が CHAR、VARCHAR、TEXT のいずれかであるカラムは、照合順序が NULL になっていません。数値型と文字型以外のカラムは、照合順序が NULL になっています。例:

mysql> SHOW FULL COLUMNS FROM a;
+-------+---------+-------------------+------+-----+---------+-------+
| Field | Type    | Collation         | Null | Key | Default | Extra |
+-------+---------+-------------------+------+-----+---------+-------+
| a     | char(1) | latin1_swedish_ci | YES  |     | NULL    |       |
| b     | int(11) | NULL              | YES  |     | NULL    |       |
+-------+---------+-------------------+------+-----+---------+-------+
2 rows in set (0.02 sec)

キャラクタセットは表示対象に含まれていません。

Unicode キャラクタセットが 2 つ存在することは言うまでもありません。これらのキャラクタセットを使用し、約 650 の言語でテキストを保存することができます。2 つの新しいキャラクタセットに対応する多くの照合順序は追加されていませんが、間もなく追加される予定です。現在、大文字と小文字およびアクセントが区別されないデフォルトの照合順序のほか、バイナリ照合順序があります。

+---------+-----------------+-------------------+--------+
| Charset | Description     | Default collation | Maxlen |
+---------+-----------------+-------------------+--------+
| utf8    | UTF-8 Unicode   | utf8_general_ci   |      3 |
| ucs2    | UCS-2 Unicode   | ucs2_general_ci   |      2 |
+---------+-----------------+-------------------+--------+

+----------+-----------------------------+---------------------+--------+
| Charset  | Description                 | Default collation   | Maxlen |
+----------+-----------------------------+---------------------+--------+
| dec8     | DEC West European           | dec8_swedish_ci     |      1 |
| hp8      | HP West European            | hp8_english_ci      |      1 |
+----------+-----------------------------+---------------------+--------+

+----------+-----------------------------+---------------------+--------+
| Charset  | Description                 | Default collation   | Maxlen |
+----------+-----------------------------+---------------------+--------+
| armscii8 | ARMSCII-8 Armenian          | armscii8_general_ci |      1 |
| cp1256   | Windows Arabic              | cp1256_general_ci   |      1 |
| hebrew   | ISO 8859-8 Hebrew           | hebrew_general_ci   |      1 |
| greek    | ISO 8859-7 Greek            | greek_general_ci    |      1 |
| latin5   | ISO 8859-9 Turkish          | latin5_turkish_ci   |      1 |
| geostd8  | Georgian                    | geostd8_general_ci  |      1 |
+----------+-----------------------------+---------------------+--------+

サポートされているアジアのキャラクタセットには、中国語、日本語、韓国語、タイ語が含まれています。これらは複雑な場合があります。たとえば、中国語のキャラクタセットは数千種類の文字に対応していなければなりません。

+----------+-----------------------------+---------------------+--------+
| Charset  | Description                 | Default collation   | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese    | big5_chinese_ci     |      2 |
| gb2312   | GB2312 Simplified Chinese   | gb2312_chinese_ci   |      2 |
| gbk      | GBK Simplified Chinese      | gbk_chinese_ci      |      2 |
| euckr    | EUC-KR Korean               | euckr_korean_ci     |      2 |
| ujis     | EUC-JP Japanese             | ujis_japanese_ci    |      3 |
| sjis     | Shift-JIS Japanese          | sjis_japanese_ci    |      2 |
| tis620   | TIS620 Thai                 | tis620_thai_ci      |      1 |
+----------+-----------------------------+---------------------+--------+

バルト語キャラクタセットには、エストニア語、ラトビア語、リトアニア語が含まれています。現在サポートされているバルト語キャラクタセットは以下の 2 つです:

ベラルーシ語、ブルガリア語、ロシア語、ウクライナ語と共に使用するキリル語のキャラクタセットおよび照合順序を以下に示します。

チェコ、スロバキア、ハンガリー、ルーマニア、スロベニア、クロアチア、ポーランドで使用されるキャラクタセットも一部サポートされています。

西ヨーロッパのキャラクタセットには、大部分の西ヨーロッパ言語（フランス語、スペイン語、カタロニア語、バスク語、ポルトガル語、イタリア語、アルバニア語、オランダ語、ドイツ語、デンマーク語、スウェーデン語、ノルウェー語、フィンランド語、フェロー語、アイスランド語、アイルランド語、スコットランド語、英語など）が含まれています。