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);
参考リンク
- MySQL :: MySQL 8.0 Reference Manual :: 11.1.1 Numeric Data Type Syntax
- MySQL :: MySQL 8.0 Reference Manual :: 9.1.6 Boolean Literals
- MySQL :: MySQL 8.0 Reference Manual :: 13.2.7 LOAD DATA Statement
- MySQL :: MySQL 8.0 Reference Manual :: 12.5 Control Flow Functions