文系プログラマによるTIPSブログ

文系プログラマ脳の私が開発現場で学んだ事やプログラミングのTIPSをまとめています。

MySQLで複数項目を一気にnull・空文字チェックする

力技ですが、こんな事もできるのです〜


f:id:treeapps:20180418131549p:plain

相当邪道で、いつできなくなるか解らないクエリの書き方になりますが、null・空文字チェックをする方法を考えてみました。正直かなり邪道だと思います。。

hogeとhigeカラムがnull・空文字でない事をチェックするクエリを普通に記述すると、こんな感じの冗長なクエリになるかと思います。

select if (hoge is not null and hoge != '' and hige is not null and hige != '', null, concat(hoge, hige)) from xxx;

邪道ですが、こんな風に書いたらどうでしょう。

select if (nullif (concat (hoge, hige), '') is null, null, concat(hoge, hige)) from xxx;

やっている事を解説すると、以下のようになります。

concat

これはnullチェック用。

concatは、引数に1つでもnullが入る場合、必ず結果がnullになります。これを利用します。つまり引数のカラム全てをnullチェックしなくてもよいのです。

nullif

これは空文字チェック用。

concatでnullチェックを突破された場合、例えばhoge・hige共に空文字の場合の対応です。空文字+空文字は空文字です。そこで、nullifで空文字をnullに変換してしまいます。

ifの条件句is null

concatとnullifでnullと空文字を強制的にnullにしたので、nullチェックするだけで済みます。

これで、以下の事が対応できます。

  • hoge=null、hige=null、nullが返る。
  • hoge=null、hige=''、nullが返る。
  • hoge=''、hige=null、nullが返る。
  • hoge=''、hige=''、nullが返る。
  • それ以外はconcat(hoge, hige)が返る。

一見すると何で空文字までチェックするの?と思いますが、load dataでcsv等を読み込んだ場合、空文字が混入し易いからです

load data時にnullをセットするには「\N」と書かなくてはいけませんが、全員が\Nと書いてくれる訳ではないので、空文字も考慮します。

処理速度は計測していないので解りませんが、同じカラム名を複数回記述する事は避けられました。こんなクエリ書いたら怒られるかな・・

ストアドファンクションを書いてもいいんですが、例によってテーブルロックがかかるので、ストアドファンクションはオフラインのテーブルの場合のみの使用に留めておいた方がいいです。