10.1.7.6 _bin および binary 照合順序

このセクションでは、非バイナリ文字列の _bin 照合順序が、バイナリ文字列の binary 「照合順序」とどのように異なるかについて説明します。

非バイナリ文字列 (CHAR、VARCHAR、および TEXT データ型に格納) には、文字セットと照合順序があります。特定の文字セットには複数の照合順序が対応していることがあり、そのそれぞれが、セット内の文字の具体的なソートおよび比較順序を定義しています。これらの中の 1 つが文字セットのバイナリ照合順序であり、その照合順序名の中には _bin サフィクスがあります。たとえば、latin1 と utf8 には、latin1_bin および utf8_bin という名前のバイナリ照合順序があります。

バイナリ文字列 (BINARY、VARBINARY、および BLOB データ型に格納) には、非バイナリ文字列にあるような文字セットや照合順序はありません。(CHARSET() と COLLATION() 関数はどちらも、バイナリ文字列に適用されると binary の値を返します。)バイナリ文字列はバイトのシーケンスであり、これらのバイトの数値がソート順序を決定します。

_bin 照合順序は、いくつかの点で binary 照合順序と異なります。

ソートおよび比較の単位。バイナリ文字列は、バイトのシーケンスです。ソートおよび比較は、常に数値のバイト値に基づきます。非バイナリ文字列は文字のシーケンスであり、これはマルチバイトであることもあります。非バイナリ文字列の照合順序は、文字値のソートおよび比較の順序を定義します。_bin 照合順序の場合、この順序は文字のバイナリコード値にのみ基づきます (これは、_bin 照合順序では複数バイトの文字の可能性を考慮に入れる必要がある点を除き、バイナリ文字列の順序に似ています)。ほかの照合順序の場合、文字の順序は、大文字と小文字の区別などのその他の要素を考慮に入れることがあります。

文字セットの変換。非バイナリ文字列には文字セットがあり、多くの場合、文字列に _bin 照合順序があるときでも、別の文字セットに変換されます。

• 別の文字セットを持つほかのカラムから、カラム値を割り当てる場合:

  UPDATE t1 SET utf8_bin_column=latin1_column;
  INSERT INTO t1 (latin1_column) SELECT utf8_bin_column FROM t2;

• 文字列リテラルを使用して、INSERT または UPDATE のカラム値を割り当てる場合:

  SET NAMES latin1;
  INSERT INTO t1 (utf8_bin_column) VALUES ('string-in-latin1');

• サーバーからクライアントに結果を送信する場合:

  SET NAMES latin1;
  SELECT utf8_bin_column FROM t2;

バイナリ文字列カラムの場合、変換は行われません。前述の場合、文字列値はバイトのようにコピーされます。

大文字と小文字の変換。照合順序は文字の大文字と小文字に関する情報をもたらすので、非バイナリ文字列内の文字は大文字と小文字を変換できます。これは、順序で大文字と小文字の区別を無視する _bin 照合順序の場合でも該当します。

mysql> SET NAMES latin1 COLLATE latin1_bin;
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT LOWER('aA'), UPPER('zZ');
+-------------+-------------+
| LOWER('aA') | UPPER('zZ') |
+-------------+-------------+
| aa          | ZZ          |
+-------------+-------------+
1 row in set (0.13 sec)

大文字と小文字という概念は、バイナリ文字列内のバイトには適用されません。大文字と小文字の変換を行うには、文字列を非バイナリ文字列に変換する必要があります。

mysql> SET NAMES binary;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT LOWER('aA'), LOWER(CONVERT('aA' USING latin1));
+-------------+-----------------------------------+
| LOWER('aA') | LOWER(CONVERT('aA' USING latin1)) |
+-------------+-----------------------------------+
| aA          | aa                                |
+-------------+-----------------------------------+
1 row in set (0.00 sec)

比較での後続スペースの処理。非バイナリ文字列には、_bin 照合順序を含むすべての照合順序に対する PADSPACE 動作があります。後続スペースは比較で意味がありません。

mysql> SET NAMES utf8 COLLATE utf8_bin;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT 'a ' = 'a';
+------------+
| 'a ' = 'a' |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)

バイナリ文字列では、後続スペースを含むすべての文字が比較で重要になります。

mysql> SET NAMES binary;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT 'a ' = 'a';
+------------+
| 'a ' = 'a' |
+------------+
|          0 |
+------------+
1 row in set (0.00 sec)

挿入および取り出しでの後続スペースの処理。CHAR(N) 列には非バイナリ文字列が格納されます。N 文字より短い値は、挿入時にスペースで拡張されます。取り出しの場合、後続スペースは削除されます。

BINARY(N) 列にはバイナリ文字列が格納されます。N バイトより短い値は、挿入時に 0x00 バイトで拡張されます。取り出しの場合、何も削除されず、宣言された長さの値が常に返されます。

mysql> CREATE TABLE t1 (
    ->   a CHAR(10) CHARACTER SET utf8 COLLATE utf8_bin,
    ->   b BINARY(10)
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO t1 VALUES ('a','a');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT HEX(a), HEX(b) FROM t1;
+--------+----------------------+
| HEX(a) | HEX(b)               |
+--------+----------------------+
| 61     | 61000000000000000000 |
+--------+----------------------+
1 row in set (0.04 sec)