- 2005-03-25 (金)
- MySQL
MySQL標準のクライアントプログラム - mysql に関するTIPS集です。便利オプションとかコマンドってすぐ忘れちゃうし、psql (PostgreSQL) とごっちゃになる可能性が非常に高いので、自分への備忘録目的でまとめてみました。情報参照元はページ最下部に記述してありますが、9割方 O'REILLY社の「MySQLクックブック vol.1」 の１章からの参照です。はやい話が同章のまとめ／要約＋αって事です（汗）。なお、対象バージョンは 3.22 〜 4.1.10 (現時点最新版）、各TIPSごとに詳細バージョンを示してあります。
TIPS一覧
- 入力途中のクエリ発行をやめたい／キャンセルしたい: \c コマンド
- SQL変数（テンポラリ変数）の使用
- 外部ファイルからのクエリ読み込み
- コマンドラインにてクエリ発行したい: -e オプション
- クエリ結果表示にページング機能をつける: PAGER 環境変数
- クエリ出力形式変更-カラム縦列挙 (vertical) 表示に: -E (--vertical) オプション
- クエリ出力形式変更-タブ区切り形式に: \B (--batch) オプション
- タブ文字以外の区切り文字を使いたい
- クエリ出力形式変更-HTML形式に: -H (--html) オプション
- クエリ出力形式変更-XML形式に: -X (--xml) オプション
- 列見出し（カラム名ヘッダ）を非表示に: -N (--skip-column-names) オプション
- クエリ出力行に番号をつける : SQL変数を利用する
- mysqlセッション記録をログに残したい: --tee オプション
- 参考情報
入力途中のクエリ発行をやめたい／キャンセルしたい: \c コマンド
mysql> SELECT * FROM hoge
-> WHERE \c
mysql>
使い始め当初は、何も考えずに Ctrl + C を入力→mysqlクライアントプログラム自体が終了してしまって困ってました (postgreSQLのpsqlは Ctrl+C でクエリキャンセル出来てた）。メモメモ。
SQL変数（テンポラリ変数）の使用
@var_name := value
でクエリ中の任意の値を変数に格納する事が可能。
- 変数への代入 mysql> SELECT @id := id FROM hoge WHERE name = 'bashi'; - 変数格納値の表示 mysql> SELECT @id; - クエリ内での利用 mysql> DELETE FROM hoge WHERE id = @id;
注意すべきは、変数に格納される値は１つのみである事。クエリ結果が複数あった場合は最後に取得した値がイキになります。
外部ファイルからのクエリ読み込み
mysql> SOURCE [file_path];
コマンドラインにてクエリ発行したい: -e オプション
% mysql -e "SELECT * FROM hoge" [dbname]
※複数クエリもセミコロンで区切る事で1行記述が可能:
% mysql -e "SELECT * FROM hoge; INSERT INTO fuga values(1,'xxx')" [dbname]
クエリ結果表示にページング機能をつける: PAGER 環境変数
PAGER環境変数に任意のページングプログラムのパスを設定しておく事で、mysqlクライアント上でのクエリ結果をページ送り機能付で見れます。.bashrc とかに設定する他に、my.cnf ファイルに以下のように記述してもOK:
[mysql]
pager=/bin/more
ページング機能はデフォルトで ON になっていますが、mysqlクライアント上で \P / \n コマンドにて任意に制御する事が可能です:
- ページング機能 ON
mysql> \P PAGER set to /bin/more - ページング機能 OFF
mysql> \n
PAGER set to stdout
クエリ出力形式変更-カラム縦列挙 (vertical) 表示に: -E (--vertical) オプション
% mysql -E [dbname]
カラムがやたら多い時、格納文字列が長い時等に便利そう。mysql上では delimiter を ";" の変わりに \G を使用する事でそのクエリのみ vertical 表示になります。
mysql> SELECT * FROM hoge\G
実行結果:
*************************** 1. row *************************** id: 1 name: seiya class: pegasus *************************** 2. row *************************** id: 2 name: shiryu class: dragon *************************** 3. row *************************** id: 3 name: hyoga class: cygnus *************************** 4. row *************************** id: 4 name: shun class: andromeda *************************** 5. row *************************** id: 5 name: ikki class: phoenix 5 rows in set (0.00 sec)
クエリ出力形式変更-タブ区切り形式に: -B (--batch) オプション
% mysql -B -e "SELECT * FROM hoge" [dbname]
実行結果:
id name class 1 seiya pegasus 2 shiryu dragon 3 hyoga cygnus 4 shun andromeda 5 ikki phoenix
-B = バッチモードなのでプロンプト等の対話形式まわりの情報は一切表示されなくなる。おまけ：通常の表形式の指定は -t (--table) オプション。
タブ文字以外の区切り文字を使いたい
そんなオプションは無いらしいです（残念）。パイプ使って sed やら tr かますべし。
% mysql -B -e "SELECT * FROM hoge" [dbname] | tr "\t" ","
クエリ出力形式変更-HTML形式に: -H (--html) オプション
% mysql -H -e "SELECT * FROM hoge" [dbname]
実行結果(見やすいように改行整形してます):
<TABLE BORDER=1>
<TR><TH>id</TH><TH>name</TH><TH>class</TH></TR>
<TR><TD>1</TD><TD>seiya</TD><TD>pegasus</TD></TR>
<TR><TD>2</TD><TD>shiryu</TD><TD>dragon</TD></TR>
<TR><TD>3</TD><TD>hyoga</TD><TD>cygnus</TD></TR>
<TR><TD>4</TD><TD>shun</TD><TD>andromeda</TD></TR>
<TR><TD>5</TD><TD>ikki</TD><TD>phoenix</TD></TR>
</TABLE>
※対応バージョン:ver3.22.26〜
クエリ出力形式変更-XML形式に: -X (--xml) オプション
% mysql -X -e "SELECT * FROM hoge" [dbname]
実行結果:
<?xml version="1.0"?>
<resultset statement="SELECT * FROM hoge
">
<row>
<id>1</id>
<name>seiya</name>
<class>pegasus</class>
</row>
<row>
<id>2</id>
<name>shiryu</name>
<class>dragon</class>
</row>
<row>
<id>3</id>
<name>hyoga</name>
<class>cygnus</class>
</row>
<row>
<id>4</id>
<name>shun</name>
<class>andromeda</class>
</row>
<row>
<id>5</id>
<name>ikki</name>
<class>phoenix</class>
</row>
</resultset>
※対応バージョン:ver.4.0〜
※ver4.1.10現在、同オプションは <>&'" 等の文字に対してエスケープ処理はしてくれない模様（これら文字がクエリ結果に含まれていると Invalid な XML になる）。-X オプションを使用する際は後処理としてHTMLエスケープを行うことを忘れずに。情報源: MySQLDocumentationのこの記事のコメント欄
列見出し（カラム名ヘッダ）を非表示に: -N (--skip-column-names) オプション
バッチモード -B と組み合わせて使う感じ。
% mysql -N -B -e "SELECT * FROM hoge" [dbname]
実行結果:
1 seiya pegasus 2 shiryu dragon 3 hyoga cygnus 4 shun andromeda 5 ikki phoenix
※対応バージョン: ver.3.22.20〜。それより以前のバージョンでは -ss オプションを使う事により同様の結果にする事が可能。
クエリ出力行に番号をつける : SQL変数を利用する
mysql> SET @n = 0;
mysql> SELECT *, @n := @n+1 AS rownum FROM hoge;
実行結果:
+------+--------+-----------+--------+ | id | name | class | rownum | +------+--------+-----------+--------+ | 1 | seiya | pegasus | 1 | | 2 | shiryu | dragon | 2 | | 3 | hyoga | cygnus | 3 | | 4 | shun | andromeda | 4 | | 5 | ikki | phoenix | 5 | +------+--------+-----------+--------+ 5 rows in set (0.00 sec)
mysqlセッション記録をログに残したい: --tee オプション
% mysql --tee=hoge.txt [dbname]
上記のように、mysqlコマンド実行時に指定せずとも、mysql上から \T コマンドを使う事により任意のタイミングでのロギングが可能。
- ロギング開始
mysql> \T hoge.txt
Logging to file 'hoge.txt' - ロギング停止
mysql> \t
Outfile disabled.
参考情報
MySQL.com の MySQL Documentation
O'REILLY社 - MySQLクックブック vol.1 - Paul Dubois著、赤井誠+他 訳