Entries


上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。

mysqlでNULLを判定して0に置換する


以前と同じ場所,NULL問題ではまったのでブログに書いておきます.

sqlでupdate文を流すとき,中身がNULLだとうまく加算ができませんでした.
実はこれはかなり前にハマった記憶があります.しっかりしていないとすぐに忘れてしまいますね.

確認のためにまず練習用のテーブルを作ります.
CREATE TABLE niceboat (
id int(2) NOT NULL auto_increment,
num int(3) ,
point int(3),
PRIMARY KEY (id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

次にINSERT文を流します.テーブルに10とNULLを入れます.
INSERT INTO niceboat (num, point) VALUES (10,10);
INSERT INTO niceboat (num,point) VALUES (10,NULL);

SELECT文を流すとこんな感じです.
mysql> SELECT * FROM niceboat;
+----+------+-------+
| id | num | point |
+----+------+-------+
| 1 | 10 | 10 |
| 2 | 10 | NULL |
+----+------+-------+
2 rows in set (0.00 sec)

次にNULLが入ったカラムにupdate文を流します.
numカラムに1を足し,pointカラムに2を足すUPDATE文です.
UPDATE niceboat SET num=num+1, point=point+2;

では結果を見てみましょう.
mysql> SELECT * FROM niceboat;
+----+------+-------+
| id | num | point |
+----+------+-------+
| 1 | 11 | 12 |
| 2 | 11 | NULL |    ←NULLのままです
+----+------+-------+
2 rows in set (0.00 sec)

結果を見るとid=1のpointには2が足され12になっていますが,id=2のpointは2は加算されずNULLのままになっています.数字ではないNULLは演算できないからです.ここではNULLを判定して0に置き換え演算をしてNULLのカラムに2を表示させます.先ほどのUPDATE文を書き換えます.
UPDATE niceboat SET num=num+1,point= COALESCE(point,0)+2;

結果を見てみます.
mysql> SELECT * FROM niceboat;
+----+------+-------+
| id | num | point |
+----+------+-------+
| 1 | 12 | 14 |
| 2 | 12 | 2 |
+----+------+-------+
2 rows in set (0.00 sec)

今度はNULLのカラムに2が加算されました.COALESCE(コウアレス)はNULLを判定し中身を指定したこの場合0に置き換えます.便利ですね.

COALESCEはSELECT文でも便利です.練習のためにもう一度INSERT文を流します.
INSERT INTO niceboat (num,point) VALUES (10,NULL);


SELECT結果のNULLを0に置き換えたい場合,この例ではpointカラムのNULLを0に置き換えます.
SELECT COALESCE(point,0) FROM niceboat;

結果を見るとNULLが0に置き換わっています.
mysql> SELECT COALESCE(point,0) FROM niceboat;
+-------------------+
| COALESCE(point,0) |
+-------------------+
| 14 |
| 2 |
| 0 |
+-------------------+
3 rows in set (0.00 sec)

これで十分満足なのですが,結果がpointカラムでなくCOALESCE(point,0)となっているので,直したい場合は後ろにas pointを付ければいいでしょうか.
SELECT COALESCE(point,0) AS point FROM niceboat;

結果を見てみます.
mysql> SELECT COALESCE(point,0) AS point FROM niceboat;
+-------+
| point |
+-------+
| 14 |
| 2 |
| 0 |
+-------+
3 rows in set (0.00 sec)

だいぶ長くなってしまったけれど,今度こそNULLの判定で詰まらなくなることを祈っています.
【改訂第3版】 SQLポケットリファレンス (POCKET REFERENCE)【改訂第3版】 SQLポケットリファレンス (POCKET REFERENCE)
(2009/04/29)
朝井 淳

商品詳細を見る

Appendix

プロフィール

川崎修

  • Author:川崎修
  • インターネット大好き!
    調布周辺で自宅警備してます



    メール:pugiemonn@gmail.com
    skype:pugiemonn
    twilog:Twilog
    mixi:mixi
    facebook:facebook
My Profile by iddy
FC2ブログ

カレンダー

03 | 2017/04 | 05
- - - - - - 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 - - - - - -

最近の記事

Twitter

コミュニティ

ブログ内検索

ブロとも申請フォーム

この人とブロともになる

Adsense

月別アーカイブ

はてぶ数

上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。