文字列を格納する列の定義
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間の互換性維持のために残されているのかもしれない。