MySQLの複合UNIQUEインデックスと文字列型のはなし

Firebird 徹底入門

Firebird 徹底入門

この本の著者の木村さんのブログを読んでいて気になった記事のメモ。
Firebird SQLの人だそうだが、MySQLや他のデータベースにも詳しく、ブログで様々なデータベースの話が取り上げられている。

MySQLの複合UNIQUE制約をNULLはすり抜けるが空文字列はすり抜けない

キムラデービーブログ MySQLでの一意インデックス(Unique index)の実装について

つまり、Firebird(Oracle, PostgreSQL)からMySQLに移行する場合、
エラーとしてはじかれていた(1,null)がはじかれなくなります。
逆に、MySQLからFirebird(Oracle, PostgreSQL)に移行する場合は、
エラーでなかったものが、はじかれる可能性がありますので、ご注意ください。

MySQLって、複合UNIQUEインデックスを作ってもNULLはその制約をすり抜けてしまうという罠があるんだよね〜
例えば、foo(INT), bar(INT), baz(VARCHAR)っていう3つの列の複合UNIQUEインデックスを作ったとして、

foo, bar, baz
1, 1, NULL
1, 1, NULL <- これは一意性約に違反しない!
1, 1, "(空文字列)"
1, 1, "(空文字列)" <- これは一意制約に違反する。

こういう風に、NULLだと一意制約をすり抜けるけど、空文字列だと一意性約に引っかかるっていう仕様があります。
今までMySQLを使っていてこの仕様は知っていたんだけど、
MySQLから他のデータベースに移行することを考えたことがなかったので記事を読んでなるほどなぁと思いました。

データベース移行をしないとしても、テーブル設計で注意しなければならない点ですね。

MySQLの文字列は後ろのスペースを見ない

キムラデービーブログ MySQLの文字列比較セマンティクスは「空白埋め」
http://blog.kimuradb.com/?eid=877166

Oracleとは違いMySQLの場合は、
CHARもVARCHARも空白埋め比較セマンティクスで比較されるということ。
そのため、以下のような後続の半角スペースの個数が違うだけの文字列は、
CHAR, VARCHAR、文字リテラルで同一視されます。

これは知りませんでした。

以降、僕が知っているMySQLの文字列型の注意点を書いておきますね☆

MySQLの複合UNIQUEインデックスに255文字を超えるVARCHAR型のキーを入れることができない

なぜかよくわかりませんが、そういう縛りがあります。
ぐぐっても根拠がわかりませんでした。

MySQL5.1で試した例ですが、
確かに255文字を超えるVARCHARの列を含む複合UNIQUEインデックスを定義しようとするとエラーになります。

DROP TABLE IF EXISTS muluni
;
-- 256文字の列として定義
CREATE TABLE muluni (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(256) NOT NULL
) DEFAULT CHARSET=utf8 ENGINE=InnoDB
;

ALTER TABLE muluni ADD CONSTRAINT UNIQUE (id, name)
;
-- これはエラーになる。
-- ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

DROP TABLE IF EXISTS muluni
;

-- 255文字の列として定義
CREATE TABLE muluni (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL
) DEFAULT CHARSET=utf8 ENGINE=InnoDB
;

ALTER TABLE muluni ADD CONSTRAINT UNIQUE (id, name)
;
-- エラーにならない。
-- Query OK, 0 rows affected (0.03 sec)

僕はさっきまで「合計で767バイトを超える定義はエラー」と思っていたのですが、
確認したらそうではなく、767バイトを超える列を含めるとエラーのようです。
これも要注意ですね。

この問題の原因と対策は以下の記事が詳しいです!
tanamonの日記 MySQLのUNIQUEなINDEXには長さ767byteまでしか使えない件と対策
http://d.hatena.ne.jp/tanamon/20090930/1254332746

MySQLのVARCHAR型は桁長オーバーでも定義長以降を切り捨てて挿入・更新される

これも要注意の仕様です。
MySQLのVARCHAR型は定義長よりも長いデータを入れようとしたときにエラーにならず、
桁長を超えた部分を切り捨てて入れます。

INSERT INTO muluni VALUES (1, REPEAT('A', 256))
;
SELECT LENGTH(NAME) FROM muluni
;
/*
+--------------+
| LENGTH(NAME) |
+--------------+
|          255 |
+--------------+
1 row in set (0.00 sec)
*/
UPDATE muluni SET name = REPEAT('B', 256)
;
SELECT LENGTH(NAME) FROM muluni
;
/*
+--------------+
| LENGTH(NAME) |
+--------------+
|          255 |
+--------------+
1 row in set (0.00 sec)
*/

結構文字列型って気をつけないといけないことがあるんですね。