こんにちは。最近ガスを止められ温もりの無い生活を送っている松田です。

今回は最近自分が知ったMySQLの小技をいくつか書いてみます。
んなもん常識だろ！ってネタがあっても優しく見守ってあげてください。

まず今回の実行サンプルには以下のテーブルを使ってます。

1. mysql> SELECT * FROM user_m;
2. +---------+----------+---------------------+
3. | user_id | name     | create_datetime     |
4. +---------+----------+---------------------+
5. |       1 | atsushi  | 2007-05-17 21:53:40 | 
6. |       2 | joe      | 2007-05-17 21:53:59 | 
7. |       3 | masahiro | 2007-05-18 21:54:02 | 
8. |       4 | saito    | 2007-05-18 21:54:05 | 
9. |       5 | yuka     | 2007-05-18 21:54:10 | 
10. +---------+----------+---------------------+

1. 実行結果をファイルに書き出す / INTO OUTFILE|DUMPFILE
SELECTした結果を直接ファイルに出力する方法です。SELECT文の後ろに INTO OUTFILEとファイル名を指定することで実行できます。

1.  SELECT * FROM user_m INTO OUTFILE "/tmp/output.txt" ;

出力されるテキストは以下のようになります。
/tmp/output.txt

1.  1 atsushi 2007-05-17 21:53:40
2.  2 joe 2007-05-17 21:53:59
3.  3 masahiro  2007-05-18 21:54:02
4.  4 saito 2007-05-18 21:54:05
5.  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を書きます。

1. 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の値をソートに使用することができます。

1. SELECT DATE(create_datetime), COUNT(*) FROM user_m GROUP BY DATE(create_datetime) DESC;

GROUP BY に指定する条件が複雑なSQLの場合は地味に便利です。



3. GROUP BY した結果の合計出したいんだけど･･･ / GROUP BY 条件 WITH ROLLUP
まずは日付ごとに登録された人数を出してみます。

1. mysql> SELECT DATE(create_datetime), COUNT(*) FROM user_m GROUP BY DATE(create_datetime);
2. +-----------------------+----------+
3. | DATE(create_datetime) | COUNT(*) |
4. +-----------------------+----------+
5. | 2007-05-17            |        2 | 
6. | 2007-05-18            |        3 | 
7. +-----------------------+----------+

5月17日に作られたユーザーが2人、5月18日に作られたユーザーが3人います。
こういったGROUP BYを使った出力結果の合計を出すことができるのが WITH ROLLUP です。

1. mysql> SELECT DATE(create_datetime), COUNT(*) FROM user_m GROUP BY DATE(create_datetime) WITH ROLLUP;
2. +-----------------------+----------+
3. | DATE(create_datetime) | COUNT(*) |
4. +-----------------------+----------+
5. | 2007-05-17            |        2 | 
6. | 2007-05-18            |        3 | 
7. | NULL                  |        5 | 
8. +-----------------------+----------+

これで合計数「5」が得られました。合計数と無関係なカラムにはNULLが入ります。
ただしこの検索SQLにLIMITを付ける場合、合計前の表にLIMITがかけられるわけではなく、合計後の上記のような表にLIMITがかけられるだけなので注意しましょう。



4. 複数行の結果を一行で出したい / GROUP_CONCAT
複数の要素を一行にまとめて返します。
要するにPHPの join や implode と同じ機能ですね。

1. mysql> SELECT GROUP_CONCAT(name SEPARATOR '/') FROM user_m;
2. +----------------------------------+
3. | GROUP_CONCAT(name SEPARATOR '/') |
4. +----------------------------------+
5. | atsushi/joe/masahiro/saito/yuka/ | 
6. +----------------------------------+

SEPARATORを省略すると、自動的にカンマ「,」で区切られるようです。



5. DELETEしてからINSERTするのメンドクサイよね / REPLACE
テーブルに値をINSERTするとき、特定のフィールドに同じ値を入れたくないことが良くあります。そんなときはフィールドにUNIQUE属性を付け、さらにDELETEを一度発行してからINSERTしたりします（よね？）。
このDELETEとINSERTをまとめてやってくれるのが REPLACE です。
REPLACE はPRIMARY KEYやUNIQUE属性が付いているフィールドに同じ値を入力しようとすると、自動的にDELETE、INSERTを行ってくれます。

ここでは name にUNIQUE属性を付けて試してみました。

1. mysql> SELECT * FROM user_m;
2. +---------+----------+---------------------+
3. | user_id | name     | create_datetime     |
4. +---------+----------+---------------------+
5. |       1 | atsushi  | 2007-05-17 21:53:40 | 
6. |       2 | joe      | 2007-05-17 21:53:59 | 
7. |       3 | masahiro | 2007-05-18 21:54:02 | 
8. |       4 | saito    | 2007-05-18 21:54:05 | 
9. |       5 | yuka     | 2007-05-18 21:54:10 | 
10. +---------+----------+---------------------+
11. 5 rows in set (0.00 sec)
12.  
13. mysql> REPLACE INTO user_m (name, create_datetime) VALUES ('atsushi', NOW());
14. Query OK, 2 rows affected (0.03 sec)
15.  
16. mysql> SELECT * FROM user_m;
17. +---------+----------+---------------------+
18. | user_id | name     | create_datetime     |
19. +---------+----------+---------------------+
20. |       6 | atsushi  | 2007-05-18 22:10:11 | 
21. |       2 | joe      | 2007-05-17 21:53:59 | 
22. |       3 | masahiro | 2007-05-18 21:54:02 | 
23. |       4 | saito    | 2007-05-18 21:54:05 | 
24. |       5 | yuka     | 2007-05-18 21:54:10 | 
25. +---------+----------+---------------------+
26. 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・・・
もっと勉強が必要なことを思い知らされました。