2007年5月18日

ちょっと使えるMySQLの小技5つ+1

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

今回は最近自分が知った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)