文字列を格納する列の定義

MySQLユーザー(何)には常識なのだろうが、ほかのDBMSに慣れてからMySQLを使い始めた人がはまりそうなポイント。(そもそも自分がこれに気づいたのは、とあるSQL文を見たから(謎))

それは次の2つのCREATE TABLE文の違い。

CREATE TABLE hoge1(v VARCHAR(8)) ;
CREATE TABLE hoge2(v VARCHAR(8) BINARY) ;

これに、

INSERT INTO hoge1(v) VALUES('Hoge'),('hoge'),('HOGE'),('hOge') ;
INSERT INTO hoge2(v) VALUES('Hoge'),('hoge'),('HOGE'),('hOge') ;

して、

SELECT v AS v1 FROM hoge1 WHERE v = 'hoge' ;
SELECT v AS v2 FROM hoge2 WHERE v = 'hoge' ;

すると、次のような結果が返ってくる。

hoge1
+------+
| v1   |
+------+
| Hoge |
| hoge |
| HOGE |
| hOge |
+------+

hoge2
+------+
| v2   |
+------+
| hoge |
+------+

怖いですねぇ、恐ろしいですねぇ(誰)。

ところで、MySQLのマニュアル(http://dev.mysql.com/doc/refman/5.1/ja/create-table.html)を見ても、「VARCHAR BINARY」なんてものはない。
そこで、各テーブルの定義を見てみる。('mysql>'はMySQL Command Line Clientのプロンプト＠Windows Vista＋MySQL v5.1)

mysql> SELECT table_name, column_name, character_set_name, collation_name, column_type
    -> FROM information_schema.columns
    -> WHERE table_name like 'hoge_' ;
+------------+-------------+--------------------+-----------------+-------------+
| table_name | column_name | character_set_name | collation_name  | column_type |
+------------+-------------+--------------------+-----------------+-------------+
| hoge1      | v           | utf8               | utf8_general_ci | varchar(8)  |
| hoge2      | v           | utf8               | utf8_bin        | varchar(8)  |
+------------+-------------+--------------------+-----------------+-------------+
2 rows in set (0.01 sec)

mysql> SHOW CREATE TABLE hoge1 ;
+-------+-------------------------------------------------------------------------------------------+
| Table | Create Table                                                                              |
+-------+-------------------------------------------------------------------------------------------+
| hoge1 | CREATE TABLE `hoge1` (
  `v` varchar(8) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE hoge2 ;
+-------+---------------------------------------------------------------------------------------------------------------
----------------+
| Table | Create Table
                |
+-------+---------------------------------------------------------------------------------------------------------------
----------------+
| hoge2 | CREATE TABLE `hoge2` (
  `v` varchar(8) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------
----------------+
1 row in set (0.00 sec)

mysql>

で、改めて先のマニュアルを見てみると、

data_type:
  ：
| VARCHAR(length)
      [CHARACTER SET charset_name] [COLLATE collation_name]

というのがある。
で、MySQL v4.1のマニュアル(http://dev.mysql.com/doc/refman/4.1/ja/create-table.html)を見てみると、

type:
   ：
 | VARCHAR(length) [BINARY]

とあったが、列に対してCHARACTER SETやCOLLATEを指定することはできないらしい。
つまり、「VARCHAR(n) BINARY」という書き方は、v4、v5間の互換性維持のために残されているのかもしれない。