こんにちは。最近ガスを止められ温もりの無い生活を送っている松田です。
今回は最近自分が知ったMySQLの小技をいくつか書いてみます。
んなもん常識だろ！ってネタがあっても優しく見守ってあげてください。
まず今回の実行サンプルには以下のテーブルを使ってます。
mysql> SELECT * FROM user_m;
+---------+----------+---------------------+
| user_id | name | create_datetime |
+---------+----------+---------------------+
| 1 | atsushi | 2007-05-17 21:53:40 |
| 2 | joe | 2007-05-17 21:53:59 |
| 3 | masahiro | 2007-05-18 21:54:02 |
| 4 | saito | 2007-05-18 21:54:05 |
| 5 | yuka | 2007-05-18 21:54:10 |
+---------+----------+---------------------+
1. 実行結果をファイルに書き出す / INTO OUTFILE|DUMPFILE
SELECTした結果を直接ファイルに出力する方法です。SELECT文の後ろに INTO OUTFILEとファイル名を指定することで実行できます。
SELECT * FROM user_m INTO OUTFILE "/tmp/output.txt" ;
出力されるテキストは以下のようになります。
/tmp/output.txt
1 atsushi 2007-05-17 21:53:40
2 joe 2007-05-17 21:53:59
3 masahiro 2007-05-18 21:54:02
4 saito 2007-05-18 21:54:05
5 yuka 2007-05-18 21:54:10
通常は半角スペース区切りで出力されますが、ファイル名の後ろに FIELDS TERMINATED BY ',' を付けるとカンマ区切りにすることができます。これを使えばCSVファイルも楽に作れそうです。
INTO DUMPFILE は出力するレコードが一つだけの時のみ使用できます。
これは画像などのデータを入れているBLOB型のレコードから画像ファイルを作成する場合などに使えます。
2. GROUP BY のついでにソートしたい / GROUP BY 条件 ASC|DESC
GROUP BY でグループ化し、その結果でソートするには通常以下のようなSQLを書きます。
SELECT DATE(create_datetime), COUNT(*) FROM user_m GROUP BY DATE(create_datetime) ORDER BY DATE(create_datetime) DESC;
これをもっと簡単に書く方法が存在します。
ORDER BYを省略し、GROUP BYの後ろにASCかDESCを付けちゃうのです。
これだけでGROUP BYの値をソートに使用することができます。
SELECT DATE(create_datetime), COUNT(*) FROM user_m GROUP BY DATE(create_datetime) DESC;
GROUP BY に指定する条件が複雑なSQLの場合は地味に便利です。
3. GROUP BY した結果の合計出したいんだけど･･･ / GROUP BY 条件 WITH ROLLUP
まずは日付ごとに登録された人数を出してみます。
mysql> SELECT DATE(create_datetime), COUNT(*) FROM user_m GROUP BY DATE(create_datetime);
+-----------------------+----------+
| DATE(create_datetime) | COUNT(*) |
+-----------------------+----------+
| 2007-05-17 | 2 |
| 2007-05-18 | 3 |
+-----------------------+----------+
5月17日に作られたユーザーが2人、5月18日に作られたユーザーが3人います。
こういったGROUP BYを使った出力結果の合計を出すことができるのが WITH ROLLUP です。
mysql> SELECT DATE(create_datetime), COUNT(*) FROM user_m GROUP BY DATE(create_datetime) WITH ROLLUP;
+-----------------------+----------+
| DATE(create_datetime) | COUNT(*) |
+-----------------------+----------+
| 2007-05-17 | 2 |
| 2007-05-18 | 3 |
| NULL | 5 |
+-----------------------+----------+
これで合計数「5」が得られました。合計数と無関係なカラムにはNULLが入ります。
ただしこの検索SQLにLIMITを付ける場合、合計前の表にLIMITがかけられるわけではなく、合計後の上記のような表にLIMITがかけられるだけなので注意しましょう。
4. 複数行の結果を一行で出したい / GROUP_CONCAT
複数の要素を一行にまとめて返します。
要するにPHPの join や implode と同じ機能ですね。
mysql> SELECT GROUP_CONCAT(name SEPARATOR '/') FROM user_m;
+----------------------------------+
| GROUP_CONCAT(name SEPARATOR '/') |
+----------------------------------+
| atsushi/joe/masahiro/saito/yuka/ |
+----------------------------------+
SEPARATORを省略すると、自動的にカンマ「,」で区切られるようです。
5. DELETEしてからINSERTするのメンドクサイよね / REPLACE
テーブルに値をINSERTするとき、特定のフィールドに同じ値を入れたくないことが良くあります。そんなときはフィールドにUNIQUE属性を付け、さらにDELETEを一度発行してからINSERTしたりします（よね？）。
このDELETEとINSERTをまとめてやってくれるのが REPLACE です。
REPLACE はPRIMARY KEYやUNIQUE属性が付いているフィールドに同じ値を入力しようとすると、自動的にDELETE、INSERTを行ってくれます。
ここでは name にUNIQUE属性を付けて試してみました。
mysql> SELECT * FROM user_m;
+---------+----------+---------------------+
| user_id | name | create_datetime |
+---------+----------+---------------------+
| 1 | atsushi | 2007-05-17 21:53:40 |
| 2 | joe | 2007-05-17 21:53:59 |
| 3 | masahiro | 2007-05-18 21:54:02 |
| 4 | saito | 2007-05-18 21:54:05 |
| 5 | yuka | 2007-05-18 21:54:10 |
+---------+----------+---------------------+
5 rows in set (0.00 sec)
mysql> REPLACE INTO user_m (name, create_datetime) VALUES ('atsushi', NOW());
Query OK, 2 rows affected (0.03 sec)
mysql> SELECT * FROM user_m;
+---------+----------+---------------------+
| user_id | name | create_datetime |
+---------+----------+---------------------+
| 6 | atsushi | 2007-05-18 22:10:11 |
| 2 | joe | 2007-05-17 21:53:59 |
| 3 | masahiro | 2007-05-18 21:54:02 |
| 4 | saito | 2007-05-18 21:54:05 |
| 5 | yuka | 2007-05-18 21:54:10 |
+---------+----------+---------------------+
5 rows in set (0.00 sec)
user_id=1の'atsushi'が削除され、新たにuser_id=7の'atsushi'がINSERTされました。
REPLACEを行った直後、「2 rows affected」になっているのは、DELETEとINSERTの2レコードに対して操作を行ったからのようです。
でも'atsushi'の表示位置が元の位置と同じなのはなぜなんだろう。
通常のINSERTとは違う処理ということでしょうか。
6. 複数のテーブルにまとめてINSERTしたい！
そんな方法があるようです。マルチテーブル・インサートと呼ぶらしいです。
サンプルを作ろうかと思ったのですが参考先ページ以上の例が出せそうにないのでリンクのみで。
http://www.atmarkit.co.jp/fdb/rensai/sqlclinic12/sqlclinic12_1.html
うーん、こりゃすごい。全然知らなかった。
すごい！とは思うものの使う場面が思い浮かびません。。
まだまだ奥が深いMySQL・・・
もっと勉強が必要なことを思い知らされました。