真偽値の判定に、tinyint(1)を使うか、bool,boolean型を使うか、bit(1)を使うか、enumを使うか

Rails MySQL

MySQLレベルでの話。

最近SQLの細かい挙動とか、しっかり把握しきれてないなーとか、
一度覚えたことが年が経ってあやふやになってるところが多くある。
また勉強し直したい。


RDBMSとうか、KVSだのNoSQLだのDBのシステムによって大きく違うと思うけれど、
私はMySQLとちょっとPostgreSQLとかSQLiteとか触ったくらいなしょっぱい男です(先の言い訳)。

主に扱うのはMySQLで、そのくらいしかついていけません(わかるとは言いません)。


「MySQLってそうなんだ〜。Oracleだとこうなんだよー、きゃははー」とか言われても、しょんぼりとしかしません。


本題。


Rails使ってると、マイグレーションファイルでDBのテーブル定義して、
rake db:migrateでマイグレーションファイルを元にテーブルが作成されちゃう。
ここで使うDBによって、うまいこと作られる型に差異がある。

参考は

[Ruby on Rails: データ型一覧＆DB対応表 - kosuke-komiya.info/wiki]
http://kosuke-komiya.info/wiki/index.php?RubyOnRails_Types

[ActiveRecord と実際のDBの型の対応を確認する - @sugamasao.blog.title # => ”コードで世界を変えたい”]
http://d.hatena.ne.jp/seiunsky/20101220/1292813809

この辺とか。


しかし思う。

tinyint(1)って、MySQLのDriverとかActiveRecordがtrue/falseに変換してるだけで、
実際±127(符号無しなら0〜255)まで入れられるよなーと。
8ビット（1バイト）ですね。


確か記憶だと、
bool, booleanは、内部的にtinyint(1)になるエイリアスみたいなもんだった気がする。

[MySQL :: MySQL 4.1 リファレンスマニュアル :: 6.2 カラム型]
http://dev.mysql.com/doc/refman/4.1/ja/column-types.html



TINYINT[(M)] [UNSIGNED] [ZEROFILL]
非常に小さな整数。符号付きの範囲は -128 〜 127。符号なしの範囲は 0 〜 255。



BIT , BOOL , BOOLEAN
いずれも TINYINT(1) のシノニム。 シノニム BOOLEAN はバージョン 4.1.0 で追加された。
ブール型の完全な処理は SQL-99 に基づいて導入される。


[MySQL :: MySQL 5.1 リファレンスマニュアル :: 10.1.1 数値タイプの概要]
http://dev.mysql.com/doc/refman/5.1/ja/numeric-type-overview.html

こっちは5.1の数値リファレンス。



あれ？bit(1)もtinyint(1)と同じなの？
またまたー。


最近、bit(1)だったら正しく真偽値持ってるような意図になるんじゃないの？って疑問視してたらこれだよ・・・。


調べてったらbooleanの検証があったのでご紹介。

[MySQL5.1のboolean型を検証 | 1000g]
http://1000g.5qk.jp/2010/11/15/mysql5-1%E3%81%AEboolean%E5%9E%8B%E3%82%92%E6%A4%9C%E8%A8%BC/

tinyint(1)と同じになって、
・NULL
・0(where句でcolumn = falseで引っかかる)
・1(where句でcolumn = trueで引っかかる)
・2〜127(true,falseで引っかからない)
が挿入出来るみたい。
認識に違いは無かった。


さて、bitはどうなんよ？ってことで試すことにした。


そもそもRailsで非対応の型どうすんの？って場合はこちらも参考に。

[Rails の Migration で MySQL の型を指定する | METAREAL]
http://www.metareal.org/2008/02/06/using-mysql-data-types-in-rails-migration/



class CreatePepsi < ActiveRecord::Migration

  def self.up

    create_table :pepsies do |t|

      t.column :coke,       :"CHAR(64)"

      t.column :jolt,       :SMALLINT

...

こんな感じにすると非対応の型でも出来るみたい。




ちょっと単純にいきたいので、まずはMySQLで直接作る。



CREATE TABLE bit_test( 

  id int(11), 

  bit1 bit(1), 

  bit64 bit(64)

);

PRIMARY KEYは？とか、ストレージエンジンは？とかは簡単な例って事で省略。
どうでもいいけれど、全部小文字で昔は書いてたけれど、
「命令文の予約語は大文字にして、変数やテーブル名、列名を小文字にするとプログラム上で見てて区別が付きやすい」
と前職の人が言っていて、確かにと納得して区別するようになった。

続いてデータ挿入。



INSERT INTO bit_test VALUES(0,0,0);

INSERT INTO bit_test VALUES(1,1,1);

INSERT INTO bit_test VALUES(2,2,2);
ERROR 1406 (22001): Data too long for column 'bit1' at row 1

bit(1)だと、2を保存しようとしたらData too longになる。
これはカラムのデータ保存領域がちゃんと1bitになってるのかな？
そんなわけないか、バイト単位でしか保存されないのかな。

その辺内部構造までは理解が至らないものの、
tinyint(1)やbooleanと違って、
「2が保存できない」事を確認した。



SELECT * FROM bit_test WHERE bit1 = true;

SELECT * FROM bit_test WHERE bit1 = false;

SELECT * FROM bit_test WHERE bit64 = true;

SELECT * FROM bit_test WHERE bit64 = false;

これも思い通りの結果が返って、
真偽値で保存されていて、それ以外は保存出来ない手応えを得た。


適当にRailsのアプリケーションを新規作成。



mkdir bit_test_app

cd bit_test_app

rails bit_test

cd bit_test
ruby script/generate model BitTest

RAILS_ROOT/config/database.ymlを開いて編集



development:

  adapter: mysql

  database: bit_test_dev

  user: xxxxx

  password: xxxxx
rake db:create 実行。

続いてRAILS_ROOT/db/に出来たマイグレーションファイルを開いて編集



class CreateBitTests < ActiveRecord::Migration

  def self.up

    create_table :bit_tests do |t|

      t.column :bit1,       :"BIT(1)"

      t.column :bit64,      :"BIT(64)"

    end

  end
  def self.down

    drop_table :bit_tests

  end

end
と編集し、rake db:migrateを実行。

MySQLのクライアントで、SHOW CREATE TABLE bit_tests;を実行して、bit型になっていることを確認。



mysql -uxxxxx -pxxxxx

USE bit_test_dev;

SHOW TABLES;

SHOW CREATE TABLE bit_tests;
CREATE TABLE `bit_tests` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `bit1` bit(1) DEFAULT NULL,

  `bit64` bit(64) DEFAULT NULL,

  PRIMARY KEY(`id`)

)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
Railsのコンソールを立ち上げる。



ruby script/console
>> BitTest.create

=> #
>> BitTest.create(:bit1 => 1)

=> #
>> BitTest.create(:bit1 => 0)

=> #
>> BitTest.create(:bit1 => 3)

=> 激しくエラー。bit(1)に3を保存して、Data too longだと思う。
>> BitTest.create(:bit64 => 3)

=> # #bit(64)なら3は保存可能
>> BitTest.create(:bit1 => true)

=> #
>> BitTest.create(:bit1 => false)

=> #



>> require "pp"

=> []
>> pp Bittest.find(:all, :conditions => ["bit1 = ?" true])

[#,

 #]

=> nil
>> pp Bittest.find(:all, :conditions => ["bit1 = ?" 1])

[#,

 #]

=> nil



>> pp Bittest.find(:all, :conditions => ["bit1 = ?" false])

[#,

 #]

=> nil

いい感じ。でもStringが返ってるね。



>> Bittest.find(:first, :conditions => ["bit1 = ?" true]).bit1

"\001"

=> nil
>> Bittest.find(:first, :conditions => ["bit1 = ?" true]).bit1.class

String

=> nil

非常にマッチする記事を見つけた。


[MySQLのBIT型 - LazyLoadLife]
http://d.hatena.ne.jp/babie/20080717/1216286789


unpackとかpackすれば、intに出来るとのこと。

bit(1)なら、true･falseに使えて2以上保存出来ない(エラーが起きる)し、
ちゃんと0/1,true/falseで検索も出来るので問題ないといえばないけれど、ややこしい。

仮に1000万レコードあったとして、
内部構造的に、tinyint(1)より容量が少なかったり、selectのパフォーマンスが良いなら使うのもいい気がした。



タイトルに入れたけどenumは普段使う事が無くて、最近目にしてるだけで書いただけでした。
正確にやりたければそれがいいと記事では見たけれど、普段使わないのでわからず！



以上、Railsにおける非対応カラムの作成と、
MySQL・Railsにおけるtinyint(1)、bool、boolean、bit(1)についてのまとめでした。