MySQLでBOOLEAN型のカラムに'true'をINSERTすると、その値はfalseになります。
問題
次のような構造のテーブルがあり、レコードは空の状態だとします。
CREATE TABLE `users` (
`id` INT NOT NULL,
`name` TEXT NOT NULL,
`status` TEXT NOT NULL,
`active` BOOLEAN NOT NULL,
PRIMARY KEY (`id`)
);
このテーブルに対して、レコードを追加してみましょう。
INSERT INTO `users` (`id`, `name`, `status`, `active`)
VALUES (1, 'Alice', 'true', 'true');
その後、次のようなSQL文を実行するとどうなるでしょうか。
SELECT count(*) FROM users WHERE active = true;
結果は、次のようになります。
| count(*) |
|---|
| 0 |
それでは、次のようなSQL文を実行するとどうなるでしょうか。
SELECT count(*) FROM users WHERE active = false;
結果は、次のようになります。
| count(*) |
|---|
| 1 |
これはいったい、なぜでしょうか。
原因
レコードを追加したあとのテーブルは、次のような状態になっています。
| id | name | status | active |
|---|---|---|---|
| 1 | Alice | true | 0 |
MySQLのBOOLEAN型は実際にはTINYINT(1)型のシノニムで、0がfalse、0以外がtrueを表しています。1
trueという文字列はTINYINT(1)型としては無効な値なので、デフォルト値である0になってしまったというわけです。
解決策
BOOLEAN型のカラムでは、文字列ではなくbooleanリテラルで値を書きましょう。2
INSERT INTO `users` (`id`, `name`, `status`, `active`)
VALUES (1, 'Alice', 'true', true);
INSERT INTO `users` (`id`, `name`, `status`, `active`)
VALUES (2, 'Bob', 'true', false);
あるいは、対応する数値を使ってもいいでしょう。
INSERT INTO `users` (`id`, `name`, `status`, `active`)
VALUES (1, 'Alice', 'true', 1);
INSERT INTO `users` (`id`, `name`, `status`, `active`)
VALUES (2, 'Bob', 'true', 0);
CSVファイルをインポートする場合
LOAD DATA INFILE構文を用いて次のようなCSVファイルをインポートする場合も、同様の問題が発生します。
"id","name","status","active"
"1","Alice","true","true"
"2","Bob","true","false"
この問題を解決するには、インポート時にBOOLEAN型のカラムの値をbooleanリテラルに変換します。
LOAD DATA LOCAL INFILE 'users.csv'
REPLACE
INTO TABLE users
FIELDS
TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
IGNORE 1 LINES
(id, name, status, @active)
SET active = IF(@active='true', true, false);