Category Archives: SQL

MySQL5.1のboolean型を検証

Posted by 1000k on 2010年11月15日 No comments

MySQLにてBOOLEAN型のフィールドを作りたいと思ったのですが、Googleで調べると「MySQLのBOOLEAN型は実質tinyint(1)と同じ」といったことが書いてありました。また、「MySQLで厳密なBOOLEAN型を表現したければ、ENUM(‘TRUE’, ‘FALSE’) を使った方がいい」とも書いてありました。

個人的にはSQL文に「WHERE flag IS TRUE」と書いてSELECTできれば文句ありません。そういった挙動ができるのか確認しました。

実験した環境のMySQLバージョンは5.1.41です。

データはどのように格納されるのか？

以下のように、連番とブール値を対に持つテーブルを作りました。

CREATE TABLE boolean_test (
    `id` INT NOT NULL AUTO_INCREMENT,
    `flag` BOOLEAN,
    PRIMARY KEY (`id`)
);

テストデータは下記の通りです。ブーリアン（TRUE/FALSE）のほか、整数値やNULLでも実験します。

INSERT INTO boolean_test VALUES (1, TRUE);
INSERT INTO boolean_test VALUES (2, FALSE);
INSERT INTO boolean_test VALUES (3, 1);
INSERT INTO boolean_test VALUES (4, 0);
INSERT INTO boolean_test VALUES (5, NULL);
INSERT INTO boolean_test VALUES (6, 127);
INSERT INTO boolean_test VALUES (7, 128);
INSERT INTO boolean_test VALUES (8, 2147483647); -- 4 byteの最大値
INSERT INTO boolean_test VALUES (9, 2147483648); -- 4 byteの最大値+1

作成結果は下のようになりました。

mysql> SELECT * FROM boolean_test;
+----+------+
| id | flag |
+----+------+
|  1 |    1 |
|  2 |    0 |
|  3 |    1 |
|  4 |    0 |
|  5 | NULL |
|  6 |  127 |
|  7 |  127 |
|  8 |  127 |
|  9 |  127 |
+----+------+
9 rows in set (0.00 sec)

この時点で、TRUEは1として、FALSEは0として格納されています。また、どんなに大きな値を入れても127で止まっています。確かにMySQL内部でtinyint(1)として管理しているのがわかります。

実際、テーブルをDESCRIBEしてると、型はtinyint(1)と表示されます。

mysql> DESCRIBE boolean_test;
+-------+------------+------+-----+---------+----------------+
| Field | Type       | Null | Key | Default | Extra          |
+-------+------------+------+-----+---------+----------------+
| id    | int(11)    | NO   | PRI | NULL    | auto_increment |
| flag  | tinyint(1) | YES  |     | NULL    |                |
+-------+------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

BOOLEAN型としてSELECTできるか？

条件を「=」で指定してみる

SELECT * FROM boolean_test WHERE flag = TRUE;  -- id:1, 3 がヒット
SELECT * FROM boolean_test WHERE flag = FALSE; -- id:2, 4 がヒット
SELECT * FROM boolean_test WHERE flag = 1;     -- id:1, 3 がヒット
SELECT * FROM boolean_test WHERE flag = 0;     -- id:2, 4 がヒット

「TRUE = 1」「FALSE = 0」と扱っているようです。その証拠に、値が127のレコードは「flag = TRUE」でもヒットしません。

→「flag = TRUE」という条件は、「flag = 1」と同値。

条件を「IS」で指定してみる

SELECT * FROM boolean_test WHERE flag IS TRUE;  -- id:1, 3, 6, 7, 8, 9 がヒット
SELECT * FROM boolean_test WHERE flag IS FALSE; -- id:2, 4 がヒット

0より大きいものはTRUEとして判別される模様。

→「flag = TRUE」と「flag IS TRUE」は全く別の結果を返す。

UNKNOWNはどういう扱いになる？

SQL-99の定義によると、「BOOLEAN型はTRUE/FALSE/UNKNOWNの3値で表現される」ようです。 MySQLではどういう風に呼び出せるのか確かめてみました。

SELECT * FROM boolean_test WHERE flag IS UNKNOWN;  -- id: 5 がヒット
SELECT * FROM boolean_test WHERE flag = UNKNOWN;   -- NG: #1054 - Unknown column 'UNKNOWN' in 'where clause'
SELECT * FROM boolean_test WHERE flag = 'UNKNOWN'; -- ???: id:2, 4 がヒット

NULLのフィールドがUNKNOWNとして表現されているようです。また、「= UNKNOWN」と指定するとクエリが失敗しました。「IS UNKNOWN」が正しいようです。

よくわからないのが3つめの例。id:2, 4はFALSEのレコードなので、全くヒットしてほしくないのですが。このあたりはMySQLの仕様をよく確認しないとわかりませんが、まず入力しないだろうということで、この際無視します。

考察

やはりMySQLのBOOLEAN型はtinyint(1)と同じようです。 Boolean型と思ってクエリを投げると意図しない結果が返ってきてしまう危険性があるので、注意が必要です。

また、ENUM型は検証していませんが、SQL標準（SQL99）ではサポートされていないため、プログラムの互換性を考えると積極的に使う気が起きません。

MySQLでBoolean型を表したければ、少し気持ち悪いですがtinyint(1)で表現した方が良いようです。

参考

XAMPPのMySQLで正しくUTF-8を使えるようにする

Posted by 1000k on 2010年11月4日 No comments

はてなブックマーク - XAMPPのMySQLで正しくUTF-8を使えるようにする

XAMPP 1.7.3 に入っているMySQLは、デフォルト設定だと文字コード指定が適当なので、普通に使っていると文字化けすることがあります。

コマンドプロンプトから下記を実行すると現在の文字コード設定が確認できます。

[c:\]$ c:\xampp\mysql\bin\mysql -uroot

mysql> SHOW variables LIKE '%char%';
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | latin1                         |
| character_set_connection | latin1                         |
| character_set_database   | latin1                         |
| character_set_filesystem | binary                         |
| character_set_results    | latin1                         |
| character_set_server     | latin1                         |
| character_set_system     | utf8                           |
| character_sets_dir       | C:\xampp\mysql\share\charsets\ |
+--------------------------+--------------------------------+
8 rows in set (0.00 sec)

これをどうにかするため、c:\xampp\mysql\bin\my.ini を下記のように変更します。

# [client]ブロックに下記の行を追加する
default-character-set=utf8

# [mysqld]ブロックに下記の行を追加する
default-character-set=utf8
skip-character-set-client-handshake

これでMySQLを再起動すると、文字コード設定がUTF-8になります。

[c:\]$ c:\xampp\mysql\bin\mysql -uroot

mysql> SHOW variables LIKE '%char%';
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | utf8                           |
| character_set_connection | utf8                           |
| character_set_database   | utf8                           |
| character_set_filesystem | binary                         |
| character_set_results    | utf8                           |
| character_set_server     | utf8                           |
| character_set_system     | utf8                           |
| character_sets_dir       | C:\xampp\mysql\share\charsets\ |
+--------------------------+--------------------------------+
8 rows in set (0.00 sec)

参考

tracのレポートでカラムの表示・非表示を切り替える

Posted by 1000k on 2010年10月19日 No comments

業務上tracを使っています。

チケットを抽出するSQLをいじったとき、SELECTしたいカラムが画面に入らないことがよくありました。ヘルプを見てもよくわからなかったので、メモしておきます。

項目の表示・非表示を切替え方

SELECT句のASで指定する別名の先頭に「_ (アンダーバー)」をつけると、画面では非表示になります。
レポート最下部の「異なるフォーマットでダウンロード:」からRSSやCSVでダウンロードすると、中に含まれていることがわかります。

サンプル

SELECT
    p.value     AS __color__
  , version     AS __group__
  , id          AS ticket
  , summary     AS 概要
  , component   AS コンポーネント
  , milestone   AS マイルストーン
  , t.type      AS 分類
  , owner       AS 担当者
  , status      AS ステータス
  , time        AS 登録日付
  , changetime  AS 更新日付
  , description AS _説明        -- 画面には非表示
  , reporter    AS _報告者      -- 画面には非表示
FROM
    ticket t
    LEFT JOIN enum p 
    ON p.name = t.priority AND p.type = 'priority'
WHERE
    status <> 'closed'
    AND version = '2.0'
ORDER BY
    (version IS NULL)
  , version
  , CAST(p.value AS int)
  , t.type
  , time

ビュー作成時、同名のビューが存在してもエラーを出さないようにする

Posted by 1000k on 2010年8月19日 No comments

はてなブックマーク - ビュー作成時、同名のビューが存在してもエラーを出さないようにする

「ビュー作成時、同名のビューがすでに存在するときは無視したい」というケースがありました。

直感的には「CREATE VIEW IF NOT EXISTS {ビュー名} AS {SELECT文}」と書けそうですが、このSQLは解釈されず、エラーが出ます。正しくは下記の用に「CREATE OR REPLACE VIEW」を使えば良いようです。

CREATE OR REPLACE VIEW view_uso AS
    SELECT
        id
       ,name
    FROM
        hige
    WHERE
        type = 'uso'
;

参考

SQLite3で現在時刻をINSERTする

Posted by 1000k on 2010年7月5日 No comments

datetime(‘now’, ‘localtime’)を使う。

INSERT INTO posts (body, created, modified)
VALUES ('ウソ文章', datetime('now', 'localtime'), datetime('now', 'localtime'));

なお、この値はスキーマ定義時にDEFAULT値に設定することなどもできるようです。

参考

SQLite3 での日付処理

[SQL]SELECT文の処理の順番

Posted by 1000k on 2010年5月25日 No comments

SELECT文をよく使うわりに、内部でどういう順で処理されているのか知らなかったので調べました。メモしておきます。

FROM
WHERE
GROUP BY
HAVING
SELECT
UNION等の集合演算
ORDER BY
DISTINCT

1000g

技術メモ

Category Archives: SQL

MySQL5.1のboolean型を検証

データはどのように格納されるのか？

BOOLEAN型としてSELECTできるか？

条件を「=」で指定してみる

条件を「IS」で指定してみる

UNKNOWNはどういう扱いになる？

考察

参考

XAMPPのMySQLで正しくUTF-8を使えるようにする

参考

tracのレポートでカラムの表示・非表示を切り替える

項目の表示・非表示を切替え方

サンプル

ビュー作成時、同名のビューが存在してもエラーを出さないようにする

参考

SQLite3で現在時刻をINSERTする

参考

[SQL]SELECT文の処理の順番

参考

アーカイブ

カテゴリー

最近のコメント

1000g

技術メモ

Category Archives: SQL

MySQL5.1のboolean型を検証

データはどのように格納されるのか？

BOOLEAN型としてSELECTできるか？

条件を「=」で指定してみる

条件を「IS」で指定してみる

UNKNOWNはどういう扱いになる？

考察

参考

XAMPPのMySQLで正しくUTF-8を使えるようにする

参考

tracのレポートでカラムの表示・非表示を切り替える

項目の表示・非表示を切替え方

サンプル

ビュー作成時、同名のビューが存在してもエラーを出さないようにする

参考

SQLite3で現在時刻をINSERTする

参考

[SQL]SELECT文の処理の順番

参考

アーカイブ

カテゴリー

タグ

最近のコメント