プログラムのコード量を減らす MySQL 関数

みなさん琉球朝顔ってご存知ですか？朝顔の中でもとてもたくましい事で有名な種類ですが今年の夏から我が家の庭に植えた所、未だに花が咲き誇っていて季節外れな事この上ありません、、、なんか雑草化すると駆除は困難だとか、、、

さて今日は知っておくと何かと便利な MySQL 組み込みの関数たちをご紹介しようと思います。プログラムサイドに記述すると数行に及ぶ処理が、SQL ベースで行うとほんの数文字で済んでしまいます。

DATE, DATE_FORMAT

日付や時刻関連の関数はとても充実していますが、中でもよく使うのはこの辺りでしょうか。こんなレコードがある時、、、

mysql> SELECT created FROM users;
+---------------------+
| created             |
+---------------------+
| 2009-06-05 13:33:26 |
| 2009-06-10 11:56:45 |
+---------------------+
2 rows in set (0.00 sec)

DATE 関数を使うと日付だけを取り出す事が出来ます。

mysql> SELECT DATE(created) AS created FROM users;
+-------------------+
| created           |
+-------------------+
| 2009-06-05        |
| 2009-06-10        |
+-------------------+
2 rows in set (0.00 sec)

DATE_FORMAT 関数は好きなように整形する事が可能です。

mysql> SELECT DATE_FORMAT(created,'%Y.%m.%d(%H:%i:%s)') AS created FROM users;
+----------------------+
| created              |
+----------------------+
| 2009.06.05(13:33:26) |
| 2009.06.10(11:56:45) |
+----------------------+
2 rows in set (0.00 sec)

CONCAT, CONCAT_WS

こちらもよく使います。文字列を結合する関数ですが、カラムとカラムを結合するのにも使えます。こんなレコードがあった時、、、

mysql> SELECT zip01, zip02 FROM users;
+-------+-------+
| zip01 | zip02 |
+-------+-------+
| 111   | 1111  |
| 222   | 2222  |
+-------+-------+
2 rows in set (0.00 sec)

CONCAT 関数を使うとあっさり文字列結合してくれます。

mysql> SELECT CONCAT(zip01,zip02) as zip FROM users;
+---------+
| zip     |
+---------+
| 1111111 |
| 2222222 |
+---------+
2 rows in set (0.00 sec)

CONCAT_WS 関数は区切り文字の指定が出来ます。

mysql> SELECT CONCAT_WS('-',zip01,zip02) as zip FROM users;
+----------+
| zip      |
+----------+
| 111-1111 |
| 222-2222 |
+----------+
2 rows in set (0.01 sec)

LOWER, UPPER

文字列を小文字、大文字に統一してくれます。メールアドレスの比較等の際に重宝しますね。

mysql> SELECT email FROM users WHERE email = LOWER('NAKAMURA@Example.COM');
+------------------------------+
| email                        |
+------------------------------+
| nakamura@example.com         |
+------------------------------+
1 row in set (0.01 sec)

REPLACE

文字列の置換も SQL で一発です。先程のレコードを例に取ると

mysql> SELECT REPLACE(email,'example.com','example.co.jp') AS email FROM users WHERE email = 'nakamura@example.com';
+---------------------------------+
| email                           |
+---------------------------------+
| nakamura@example.co.jp          |
+---------------------------------+
1 row in set (0.00 sec)

IF 文

以前に inoue も書いていましたが、SQL 中に条件分岐を記述する事が可能です。例えば NULL の場合はハイフンを表示したい！という時に役立ちます。こんなレコードがあったとすると、、、

mysql> SELECT birth FROM users;
+-------+
| birth |
+-------+
| NULL  |
| NULL  |
+-------+
2 rows in set (0.01 sec)

こうする事でプログラムで何もせずに表示する事が出来ますね。

mysql> SELECT IF(birth IS NULL,'-',birth) AS birth FROM users;
+-------+
| birth |
+-------+
| -     |
| -     |
+-------+
2 rows in set (0.00 sec)

終わりに

SQL 開発という言葉もあるぐらいデータベースというのは多機能です。アプリケーションを作る際は極力プログラムだけの力に頼らず SQL の機能も積極的に活用したいですね。

この辺りをガッツリ使っていこうと思うと cakePHP の find メソッドは使いづらかったりするんですよね、、、query メソッドがもう少し使いやすければなぁ、、、