amari3のはてなダイアリー このページをアンテナに追加 RSSフィード

2012-06-27

[]MySQL でシーケンスをエミュレートしてみた

Oracle とか PostgreSQL 等にあるシーケンスのエミュレートを、MySQLの公式ドキュメント で紹介されているので試してみた。

シーケンス用のテーブルの作成と初期値

数値型のカラムが一つあるテーブルが必要。

下記例では 1 から採番される。途中から採番したい場合は、初期値に 0 以外を入れればよい。

mysql> CREATE TABLE sequence (id INT NOT NULL);
mysql> INSERT INTO sequence VALUES (0);

シーケンス値の取得方法

下記要領で取得する

mysql> UPDATE sequence SET id = LAST_INSERT_ID(id + 1);
mysql> SELECT LAST_INSERT_ID();

これだけで、シーケンスのエミュレートができる。

上記の事が実現できるメカニズムは、LAST_INSERT_ID(expr)にある。LAST_INSERT_ID(expr)に引数が渡されると、そのコネクションにおける LAST_INSERT_ID() で取得できる値が設定される。

実際に試してみるのがわかりやすいと思う。

mysql> SELECT LAST_INSERT_ID(1000);
mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|             1000 |
+------------------+

これらを踏まえて説明をすると、UPDATE 文で現在の値を +1 する。その値を LAST_INSERT_ID(expr)に渡して、LAST_INSERT_ID()(引数なし)で取得できるようにしている。

意外と簡単に実現できたが、実戦投入するには色々調査不足なので、この辺は慎重にやろう。

2012-01-14

[]MySQLにおけるBOOLEAN型の扱いについて調べてみた

PostgreSQLで定義されているテーブルからMySQLに移植する必要があって、作業をしてるとBOOLEAN型で定義されてるカラムが見つかった。単純にTINYINTとかで置き換えてもよかったんだけど、MySQLにもあるんだろうかと興味本位で調べてみた。

MySQLのBOOLEAN型はTINYINT(1)と等価

以下のようなテーブル定義をしてみた。

CREATE TABLE IF NOT EXISTS bool_test (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    flg BOOLEAN,
    updated_at TIMESTAMP
) ENGINE=InnoDB CHARSET=utf8;

作成されたテーブルはこちら。

mysql> desc bool_test;
+------------+------------------+------+-----+-------------------+----------------+
| Field      | Type             | Null | Key | Default           | Extra          |
+------------+------------------+------+-----+-------------------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL              | auto_increment |
| flg        | tinyint(1)       | YES  |     | NULL              |                |
| updated_at | timestamp        | NO   |     | CURRENT_TIMESTAMP |                |
+------------+------------------+------+-----+-------------------+----------------+
3 rows in set (0.00 sec)

BOOLEAN型がTINYINT(1)と等価なのが分かると思う。つまり、真偽値だけを格納できる訳ではない。

実際に値を入れてみる

以下のテストデータを入れてみた。

INSERT INTO bool_test (flg) VALUES
 (TRUE), (FALSE),
 (1), (0), (100), (-100),
 (NULL), (TRUE), (FALSE);

作成されたデータはこちら。

mysql> SELECT * FROM bool_test ORDER BY id;
+----+------+---------------------+
| id | flg  | updated_at          |
+----+------+---------------------+
|  1 |    1 | 2012-01-15 00:35:08 |
|  2 |    0 | 2012-01-15 00:35:08 |
|  3 |    1 | 2012-01-15 00:35:08 |
|  4 |    0 | 2012-01-15 00:35:08 |
|  5 |  100 | 2012-01-15 00:35:08 |
|  6 | -100 | 2012-01-15 00:35:08 |
|  7 | NULL | 2012-01-15 00:35:08 |
|  8 |    1 | 2012-01-15 00:35:08 |
|  9 |    0 | 2012-01-15 00:35:08 |
+----+------+---------------------+
9 rows in set (0.00 sec)

TRUE=1FALSE=0にそれぞれ変換されて格納されているのが分かる。あと、TINYINT型なんで当然だけど、-128〜127までの整数値なら入れる事が出来る。

BOOLEAN型のカラムをSELECTしてみる

ここまでで、テーブルの定義結果とデータの格納結果が分かったので、flgカラムを色々なやり方でSELECTしてみた。


TRUE/FALSEで比較してSELECT

まずは一番単純にWHERE句条件にTRUE/FALSEを指定してみる。

mysql> SELECT * FROM bool_test WHERE flg = TRUE ORDER BY id;
+----+------+---------------------+
| id | flg  | updated_at          |
+----+------+---------------------+
|  1 |    1 | 2012-01-15 00:35:08 |
|  3 |    1 | 2012-01-15 00:35:08 |
|  8 |    1 | 2012-01-15 00:35:08 |
+----+------+---------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM bool_test WHERE flg = FALSE ORDER BY id;
+----+------+---------------------+
| id | flg  | updated_at          |
+----+------+---------------------+
|  2 |    0 | 2012-01-15 00:35:08 |
|  4 |    0 | 2012-01-15 00:35:08 |
|  9 |    0 | 2012-01-15 00:35:08 |
+----+------+---------------------+
3 rows in set (0.00 sec)

実行結果を見ると、TRUE=1、FALSE=0として評価されているのが分かると思う。


IS TRUE/IS FALSEで比較してSELECT

色々調べてみるとIS TRUEIS FALSEという比較方法があることが分かったので試してみた。

mysql> SELECT * FROM bool_test WHERE flg IS TRUE ORDER BY id;
+----+------+---------------------+
| id | flg  | updated_at          |
+----+------+---------------------+
|  1 |    1 | 2012-01-15 00:35:08 |
|  3 |    1 | 2012-01-15 00:35:08 |
|  5 |  100 | 2012-01-15 00:35:08 |
|  6 | -100 | 2012-01-15 00:35:08 |
|  8 |    1 | 2012-01-15 00:35:08 |
+----+------+---------------------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM bool_test WHERE flg IS FALSE ORDER BY id;
+----+------+---------------------+
| id | flg  | updated_at          |
+----+------+---------------------+
|  2 |    0 | 2012-01-15 00:35:08 |
|  4 |    0 | 2012-01-15 00:35:08 |
|  9 |    0 | 2012-01-15 00:35:08 |
+----+------+---------------------+
3 rows in set (0.01 sec)

実行結果を見ると、IS TRUEはflg=0以外の行を返してくる。つまり、TRUEという値とIS TRUEは直接の関係は無さそうである。そして、IS FALSEはflg=0の行を返してくる。IS FALSEではNULL値を含む行は返らないことも併せて分かった。

最後に

BOOLEAN型やTRUE/FALSE、IS TRUE/IS FALSEというのがあるというのも調べるまで知らなかったし、MySQLは調べる度に発見があってすごく楽しい。

実際のテーブル設計では、一般的な型や値を使う方が他のRDBMSへの移植性も高くなるし、使わない方がいいんだろうと考えられる。

2011-01-18

[][]MySQL Cluster の ndb_size.pl を使うための準備

MySQL のストレージエンジン、NDB Cluster のテーブルサイズの見積をするのに便利なコマンド、ndb_size.pl*1は、依存モジュール等がありそのままでは使えないんで、その準備の仕方をメモしておく。

依存しているモジュール

  • HTML::Template
  • Class::MethodMaker
  • DBD::mysql

インストール方法

CPAN モジュールを入れるときは、cpanm が便利なのでこれを使う。

# cpanm HTML::Template
# cpanm Class::MethodMaker

DBD::mysql は MySQL をソースコンパイルで入れた場合、パスが通っていないとテストでこけるのでその辺の設定をする。

# export PATH=/usr/local/mysql/bin:$PATH
# vim /etc/ld.so.conf
/usr/local/mysql/lib/mysql

# ldconfig

最後に DBD::mysql を入れて終わり。

# cpanm DBD::mysql

使い方

使い始めたばかりで詳しくないけどコマンドの紹介。大体こんな感じで使う。

ちなみにコマンドは、/usr/local/mysql/bin にある。

% perl ndb_size.pl --database=testdb --hostname=localhost --user=test --password=hoge --format=html > size.html

色々調べてみて、ブログに書けそうなら続編として書くつもりです。

*1:MySQLをインスコするとついてくる

2010-03-31

[][]MySQL のバイナリログの削除方法

MySQL をマスタ/スレーブ構成で運用していると、バイナリログがハードディスク容量を圧迫してくるのでその削除手順をメモ。

1.レプリケーションの進行状態を確認

スレーブ側がどこまでバイナリログを読み込んでいるか確認する。Master_Log_File に表示されているファイル(mysql-bin.006223)まで読み込まれている。つまり、その直前のファイルまでは削除しても問題ないと言うことになる。

mysql> show slave status \G
Master_Log_File: mysql-bin.006223

2.マスタ側のバイナリログ一覧を確認

マスタ側のバイナリログ一覧を確認する。レプリケーションが正常かつ順調に行われている場合、1のMaster_Log_File と同じものになっているはずである。

mysql> show master logs;
| mysql-bin.005987 | 104957654 |
| mysql-bin.005988 | 104949424 |
<略>
| mysql-bin.006222 | 105051618 |
| mysql-bin.006223 | 22691624 |
+------------------+-----------+

3.バイナリログを削除

1で確認したバイナリログの直前まで削除する。以下のように、mysql-bin.006223 を指定すると、その直前までのバイナリログを削除してくれる。

mysql> purge master logs to 'mysql-bin.006223';

おしまい。

2009-05-22

[][]MySQL(5.0)のテーブル名変更

数億レコードあるテーブルに、無謀にもカラム追加しようとして痛い目を見たのだが、ある程度は過去データを捨ててもいいというようになったので、テーブル名の変更をしてバックアップを取って、あらたにテーブル定義をしなおした。

テーブル名の変更の仕方は非常に簡単。

mysql> RENAME TABLE <既存のテーブル名> TO <変更後のテーブル名>;

テーブル名の前に DB 名をつければ、異なる DB 間でも変更が可能みたいである。

実行してみて思ったことが、テーブル名変更にかかるコストがほぼ 0 に近いことである。自分のタイミングでテーブル名を変更できるので、テーブルデータが巨大になりそうなときの逃げ道として使えそうだ。