質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.46%
MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

意見交換

クローズ

20回答

4081閲覧

MySQL カラムで真偽値の扱いについて、BIT(1) か BOOLEAN か?

origa3

総合スコア22

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

1グッド

0クリップ

投稿2023/04/05 18:10

1

0

テーマ、知りたいこと

MySQL で真偽値を格納するカラムを設定することはままあるかと思いますが、そのカラム型はどうされていらっしゃいますか?
調べると下記のような、BIT(1)派とBOOLEAN派に分かれそうです。

背景、状況

こちらはBIT(1)派の方のご意見の記事です。

MySQLに真偽値を格納する場合はbit(1)型のフィールドが最適
https://qiita.com/ka215/items/379c4d46d0c04b7fdb46

こちらはBOOLEAN派の方のご意見(ご回答)です。

Bit vs. Boolean columns
https://dba.stackexchange.com/questions/15801/bit-vs-boolean-columns

私としては、「BIT(1)は格納される値が2つしかない点で優れているが、SELECT時に1(0)true(false)に変換しないといけない手間がかかる点が扱いにくい」と感じており、甲乙つけがたく悩んでいます。

特に求めているご意見

「私が取り組んだケースではこちらの方がこういう点で良かった・悪かった」など具体ケースでのご意見ですと特に嬉しく思います。

よろしくお願い致します。

試したこと

尚、下記SQLで50万行ほどのレコードに対し確認したところ実行計画は同じ結果になり、優位性はこの件数と方法では見られないようでした。

SQL

1-- テストテーブル 2CREATE TABLE booltest ( 3 id INT UNSIGNED NOT NULL AUTO_INCREMENT, 4 bit1 BIT(1) NOT NULL, 5 tis TINYINT NOT NULL, 6 tiu TINYINT UNSIGNED NOT NULL, 7 bol BOOLEAN NOT NULL, 8 PRIMARY KEY (id), 9 INDEX (bit1), 10 INDEX (tis), 11 INDEX (tiu), 12 INDEX (bol) 13);

SQL

1-- 各パターンをINSERT 2INSERT INTO booltest (bit1, tis, tiu, bol) VALUES (0,0,0,0); -- 1行 3INSERT INTO booltest (bit1, tis, tiu, bol) VALUES (0,0,0,1); 4INSERT INTO booltest (bit1, tis, tiu, bol) VALUES (0,0,1,0); 5INSERT INTO booltest (bit1, tis, tiu, bol) VALUES (0,0,1,1); 6INSERT INTO booltest (bit1, tis, tiu, bol) VALUES (0,1,0,0); 7INSERT INTO booltest (bit1, tis, tiu, bol) VALUES (0,1,0,1); 8INSERT INTO booltest (bit1, tis, tiu, bol) VALUES (0,1,1,0); 9INSERT INTO booltest (bit1, tis, tiu, bol) VALUES (0,1,1,1); 10INSERT INTO booltest (bit1, tis, tiu, bol) VALUES (1,0,0,0); 11INSERT INTO booltest (bit1, tis, tiu, bol) VALUES (1,0,0,1); 12INSERT INTO booltest (bit1, tis, tiu, bol) VALUES (1,0,1,0); 13INSERT INTO booltest (bit1, tis, tiu, bol) VALUES (1,0,1,1); 14INSERT INTO booltest (bit1, tis, tiu, bol) VALUES (1,1,0,0); 15INSERT INTO booltest (bit1, tis, tiu, bol) VALUES (1,1,0,1); 16INSERT INTO booltest (bit1, tis, tiu, bol) VALUES (1,1,1,0); 17INSERT INTO booltest (bit1, tis, tiu, bol) VALUES (1,1,1,1); -- 16行 18 19-- 倍々に増やす 20INSERT INTO booltest (bit1, tis, tiu, bol) SELECT bit1, tis, tiu, bol FROM booltest; -- 32行 21INSERT INTO booltest (bit1, tis, tiu, bol) SELECT bit1, tis, tiu, bol FROM booltest; -- 64行 22INSERT INTO booltest (bit1, tis, tiu, bol) SELECT bit1, tis, tiu, bol FROM booltest; 23INSERT INTO booltest (bit1, tis, tiu, bol) SELECT bit1, tis, tiu, bol FROM booltest; 24INSERT INTO booltest (bit1, tis, tiu, bol) SELECT bit1, tis, tiu, bol FROM booltest; 25INSERT INTO booltest (bit1, tis, tiu, bol) SELECT bit1, tis, tiu, bol FROM booltest; 26INSERT INTO booltest (bit1, tis, tiu, bol) SELECT bit1, tis, tiu, bol FROM booltest; 27INSERT INTO booltest (bit1, tis, tiu, bol) SELECT bit1, tis, tiu, bol FROM booltest; 28INSERT INTO booltest (bit1, tis, tiu, bol) SELECT bit1, tis, tiu, bol FROM booltest; 29INSERT INTO booltest (bit1, tis, tiu, bol) SELECT bit1, tis, tiu, bol FROM booltest; -- 16384行 30INSERT INTO booltest (bit1, tis, tiu, bol) SELECT bit1, tis, tiu, bol FROM booltest; 31INSERT INTO booltest (bit1, tis, tiu, bol) SELECT bit1, tis, tiu, bol FROM booltest; 32INSERT INTO booltest (bit1, tis, tiu, bol) SELECT bit1, tis, tiu, bol FROM booltest; 33INSERT INTO booltest (bit1, tis, tiu, bol) SELECT bit1, tis, tiu, bol FROM booltest; 34INSERT INTO booltest (bit1, tis, tiu, bol) SELECT bit1, tis, tiu, bol FROM booltest; -- 524228行

SQL

1-- BIT(1) に対する実行計画を確認 2EXPLAIN SELECT * FROM booltest WHERE bit1 = 1; 3EXPLAIN SELECT * FROM booltest WHERE bit1 = true; 4 5-- BOOLEAN に対する実行計画を確認 6EXPLAIN SELECT * FROM booltest WHERE bol = 1; 7EXPLAIN SELECT * FROM booltest WHERE bol = true;
John_Doe👍を押しています

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

回答20

#1

maisumakun

総合スコア145208

投稿2023/04/05 22:14

調べると下記のような、BIT(1)派とBOOLEAN派に分かれそうです。

MySQLでBOOLEAN指定すると、実際にはTINYINTとと解釈されます(MySQL 8.0 リファレンス)。RailsでもTINYINT(1)を使っています。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

#2

Zuishin

総合スコア28662

投稿2023/04/05 23:28

BOOLEAN に 0 と 1 以外の値が入ることが問題になっているようですが、それは「入れなければいい」ということでもあり、また「入れても特に問題はない」ということでもあるので、好きな方を使えばいいのでは?
他の言語でも Truthy と Falsy が一意でないことなど珍しくありません。
重要なのは True False に一意に変換できることでしょう。

Qiita の記事って有用性より先に「トレンドに乗りたい」という動機で書かれることが多いので、有用性が認められないのであれば、そんなに振り回されなくて大丈夫だと思います。

もし自分で決められないのであれば、どっちを使うか決めてあげましょうか?

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

#3

sazi

総合スコア25206

投稿2023/04/06 00:45

編集2023/04/06 00:49

3値理論とかデータの連携、今後の拡張など考えると素直にBoolean型を使う方が良いと考えます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

#4

pippi19

総合スコア679

投稿2023/04/06 01:38

編集2023/04/06 01:39

下記SQLで50万行ほどのレコードに対し確認したところ実行計画は同じ結果になり、優位性はこの件数と方法では見られないようでした。

性能差がない以上は、採用基準が直感的なわかりやすさ、
扱いやすさに限定されるので、BOOLEANに一票。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

#5

yambejp

総合スコア115025

投稿2023/04/06 03:19

mysqlはtrueを1、falseを0と解釈しますしtinyintで十分だと思います。
あとは運用の問題で、tinyintカラムは2以上の値が指定可能ですから変なコードを書くと
想定と違う結果になる可能性はあります。
どうせキャストされるというのであればset('0','1')のnot nullという文字列型も
場合によっては有効かもしれません

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

#6

xebme

総合スコア1085

投稿2023/04/06 09:26

編集2023/04/06 20:39

手元に、Fedora 37/MariaDBしかない(他にMySQLがインストールされたPCがない)ので、試した結果を書きます。

値の範囲

  • bit(1) 0,1
  • boolean / tinyint -128 〜 127

DML
where の条件次第では、booleanやtinyintは問題が起きます。

= は問題が起きません。

sql

1where <> = true ' = 1 2where <列> = false ' = 0

is を使うと条件判定が異なります。<列> is true<列> is not false<列>は、<> 0という意味なので、booleanに0,1以外の値が格納されているなら、0以外の値がすべて選択されます。

sql

1where <> is true ' <> 0 2where <列> ' <> 0

<列> is false<列> is not truenot <列>は、= 0 と同じです。

使い方

is条件判定が使われることを想定すると次のようになります。

  • bit(1) は0,1しか格納できないので好ましい
    ただしbit(1)はSELECTで表示されない。cast(<列> as unsigned)する
  • boolean/tinyintは、制約を付加して0,1限定で使う
    booleanの解釈に注意が必要

整数型

他の整数型でも、= true | false<> true | falseis true | falseis not true | false は使えるようです。

三値論理

boolean列がnullableなら三値論理は適用されると思います。独自に判定したければ以下のようにします。

  • false : 0
  • unknown : 0.5
  • true : 1

sql

1a or b = max(a,b) 2a and b = min(a,b) 3not a = 1 - a

is null/is true

最後に、is nullの存在理由は明らかですがis true | falseの存在理由は不明です。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

#7

xebme

総合スコア1085

投稿2023/04/12 20:58

編集2023/04/12 22:46

is null/is true
最後に、is nullの存在理由は明らかですがis true | falseの存在理由は不明です。

訂正します。MySQLのbooleantruefalseunknownのいずれかです(三値論理)。isはこの3つの値をそれぞれ判定できます。

https://dev.mysql.com/doc/refman/8.0/ja/comparison-operators.html#operator_is

=を使用するとnullがある場合、三値論理が適用されて結果はnull。
isを使用するとnullがある場合、三値論理の値を判定し結果(true/false)を返します。

teratailの過去の記事に詳しい説明が載っていました

https://teratail.com/questions/29384

3値論理

基本は「ウカシェヴィッチの3値論理」です。SQLは「クリーネの3値論理」を採用しています。両者の違いは、U -> U をTとするかUとするかです。

意見

数値を真理値とみるダブルスタンダードがあること、真理値unknownに該当する明示的な値がないこと(nullに変換)、などが、問題をややこしくしているようです。not nullと値制約(0,1)を課して単純な2値論理の世界で使うのが良さそうです。(BIT(1) NOT NULLが最も現実的でしょう)

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

#8

sazi

総合スコア25206

投稿2023/04/13 00:18

3値理論について、仮にNot Null制約を施したとしても、リレーションに於いて外部結合した場合にはNullは発生し、その場合は結局Null=Unkownとして扱わざるを得ないという事になります。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

#9

xebme

総合スコア1085

投稿2023/04/13 00:51

#8

リレーションに於いて外部結合した場合にはNullは発生し、その場合は結局Null=Unkownとして扱わざるを得ないという事になります。

ありがとうございます。やっと意見交換らしくなってきました。
unknownisで論理演算を行うときに利用する。is判定は全て否定できないということは理解しました。そのunknownをbooleanなどに格納する場合に問題が出そうですね。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

#10

sazi

総合スコア25206

投稿2023/04/13 04:07

#9

unknownをbooleanなどに格納する場合に問題が出そうですね。

Null≠Unkownと定義してしまうと、それは4値になってしまうので、UnkownはNullに含まれる(即ちUnkown=Null)として扱うのが良いかと思います。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

#11

xebme

総合スコア1085

投稿2023/04/13 21:11

#10

nullを、値が欠損している状態とみるか、null値とみるかで考えが変わると思います。

  • nullを値の欠損状態とみなす

 true,false,unknownの真理値リテラルと欠損値状態(null)

  • nullが値として存在する

 true,false,unknown,nullの4値が併存する

理論的には3値+欠損状態だと思うのですが、現実はunknown値をnull値で代用する実装になっています。

Boolean data typeを調べてみました。情報を少し引用します。

引用

述語を評価するboolean演算( IS (NOT) TRUE, IS (NOT) FALSE, IS (NOT) UNKNOWN )は[SQL:1999](https://en.wikipedia.org/wiki/SQL:1999)で導入された。 BOOLEANデータタイプはオプション機能(T031)である。BOOLEAN型は、TRUE、FALSE、UNKNOWNの真理値リテラルからなるが、NULL BOOLEANとUNKNOWNは互換性がある。 しかしNULLと同等とするとUNKNOWN=UNKNOWNはTRUEにならずUNKNOWN/NULLとなり矛盾が生じる。

Null - SQLにNULLの解説があり、上のBooleanについての記述も含まれています。

理解

SQL仕様ではunknownをnullとみなすことができる。mysqlはunknown値を持たない。代わりにnull値を使っている。is (not) unknown演算をサポートするが、これはis (not) nullのエイリアスである。boolean型もtinyintのエイリアスである。

結局、整数型とnull、true/falseで全てを賄えると思います。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

#12

sazi

総合スコア25206

投稿2023/04/14 00:10

#11
本題に戻ると、BIT(1)でのNullは有り得ますが、それはBIT単位では使用できませんから、本来のBIT型の意図と違うBIT(1)をBooleanとして使用するのは、イレギュラーな使用方法だと考えます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

#13

xebme

総合スコア1085

投稿2023/04/14 00:38

#12

見解の相違ですね。私は本来の意味でbooleanが存在しないと思います。booleanとintの互換性を維持するなら、3つの真理値リテラルを正しく実装できないのではないか。また、SQL標準がNULLとUNKNOWNの互換性を唱えていることもbooleanの実装を困難にしていると思います。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

#14

Zuishin

総合スコア28662

投稿2023/04/14 00:43

編集2023/04/14 00:58

そもそも NULL や UNKNOWN は値をもたないことや使用できないことを示すもので、BOOLEAN とは無関係でしょう。
他のデータ型でも NULL や UNKOWN になり得ます。

二値が NULL に変換されるのは外部結合して NULL を排除しなかったという「演算の結果」もたらされたもので、「演算に使用されたデータ」とは本質的に無関係です。
どのような型でも、演算の結果、別の型に変換することができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

#15

xebme

総合スコア1085

投稿2023/04/14 00:59

編集2023/04/14 21:47

#14

演算を2つに分けて考えます。

  • 数値演算、比較演算

 いったんNULLになるとどんな演算を実行してもNULLのまま

  • 論理演算(and/or)

 データベースの中(SQL世界)は3値論理で動いています。論理演算の結果は、TRUE/FALSE/UNKNOWNです。この3つの真理値を表現できるのがboolean型

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

#16

Zuishin

総合スコア28662

投稿2023/04/14 01:00

編集2023/04/14 01:02

数値演算、比較演算
いったんNULLになるとどんな演算を実行してもNULLのまま

NULL を 0 に変換することが可能です。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

#17

sazi

総合スコア25206

投稿2023/04/14 01:26

#14

BOOLEAN が NULL に変換されるのは外部結合して NULL を排除しなかったという「演算の結果」もたらされたもので、「演算に使用されたデータ」とは本質的に無関係です。

データベース設計に於いては、結合状態まで考慮する訳で、無関係とは考えておりません。
boolean型をDB上のどの属性に落とし込むのが扱いやすいのかという観点で意見を述べていて、属性そのものの扱いがどうかというところにはスコープしていないつもりです。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

#18

Zuishin

総合スコア28662

投稿2023/04/14 01:46

編集2023/04/14 01:57

結局三値理論というのが「単なる理解のしかた」にすぎなくて、本質は値があるかないかの二種類、UNKNOWN を別物とするなら三種類と考えています。
これは BOOLEAN であろうが BIT(1) であろうが STRING であろうが同じで、BOOLEAN のみ三値という特別視はしない方が話は簡単になります。
論理演算する際に NULL を FALSE や TRUE に変換すれば良いのです。

どのみち変換が生じるのであれば、BOOLEAN が TINYINT のエイリアスであろうが BIT(1) のエイリアスであろうが本質的な差は無く、速度や利便性と言ったそのデータベースやプロジェクトに依存する要素を元に好きな方を選べば良いと思います。
速度的な差は無く、BOOLEAN というエイリアスを持つことで TINYINT に特別なセマンティクスが生じるのであれば、私はそれを使う方が好ましいと思います。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

#19

xebme

総合スコア1085

投稿2023/04/14 06:56

編集2023/04/14 10:46

#17

データベース設計に於いては、結合状態まで考慮する訳で、無関係とは考えておりません。

基本表だけでなく導出表にもbooleanが登場する。ありがとうございます。

boolean型をDB上のどの属性に落とし込むのが扱いやすいのかという観点で意見を述べていて、属性そのものの扱いがどうかというところにはスコープしていないつもりです。

booleanレイヤーで一元的に扱えれば良いと思いますが問題がありそうです。こちらの理解が追いつかず調べながらの発言となってしまい申し訳ありません。

#18

速度的な差は無く、BOOLEAN というエイリアスを持つことで TINYINT に特別なセマンティクスが生じるのであれば、私はそれを使う方が好ましいと思います。

同じことをこれまでに理解した私の言葉で表現します。

  • 整数型、true/falseの2値論理のレイヤーがある(NULLを含めて3値)
  • その上にbooleanレイヤー(3値論理セマンティクス)が載っている

booleanレイヤーで考えた方が良い。私もそう思います。しかしUNKNOWN値を持たないため(なぜかunknownがNULLのエイリアスではない)、次のSQLはUNKNOWNを理解できません。(unknownをnullに置き換えるとOK)

SQL

1select unknown is unknown; 2select unknown = unknown; 3insert into <テーブル>(<boolean項目>)values(unknown);

unknown値を解しない部分的な3値論理レイヤーだと思います。またunknownがNULLのエイリアスではないのはなぜかという疑問は残ったままです。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

#20

RUNA

総合スコア1

投稿2023/05/14 02:37

編集2023/05/16 05:01

他システムとのデータ交換を考えた場合、booleanの方がいいのかなぁって思います。
リモートDB接続とか、CSV等でテキスト出力の場合とかも含めてですがね。
基本は、「0」か「0以外」なんですが、

false と true について、C++の bool 型について規格書に書いてありました。
A prvalue of type bool can be converted to a prvalue of type int, with false becoming zero and true becoming one.
Working Draft, Standard for Programming Language C++ N4659 7.6.6

拙訳:bool 型の純粋右辺値は、int 型の純粋右辺値に変換されうる。false ならば int 型の 0 に、true ならば int 型の 1 に変換される。

なので、int型とのことなので、bitではなく、boolの方が いいかもです。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

最新の回答から1ヶ月経過したため この意見交換はクローズされました

意見をやりとりしたい話題がある場合は質問してみましょう!

質問する

関連した質問