SQLでNULLではなく、0が格納されてしまう。

Question

ユーザーからの入力がなかった場合にはNULLを設定したいのですが、0になってしまいます。
"%s"あたりが怪しいとは思うのですが、どう実装すれば良いか悩んでいます。
現実装は以下に抜粋します。(実際は6カラムありますが、省略しています)
※DBでデータ型はINT、デフォルト値はNULLに設定しています。

$sql = sprintf('INSERT INTO hogeTable SET hoge="%s"',
    mysqli_real_escape_string($db, $_POST['hoge'])
);

<td>
    <select name="hoge">
        <option value="">-</option>
        <option value=1>1</option>
        <option value=2>2</option>
    </select>
</td>

「-」を選択して、echo print_r($_POST);で中身を確認すると、空文字が格納されていることが確認できたのですが、
SQL文を生成した際に0に変換されてしまっているようです。

以下のようにしても結果は同じでした。

<td>
    <select name="hoge">
        <option value=NULL>-</option>
        <option value=1>1</option>
        <option value=2>2</option>
    </select>
</td>

取得した値が空文字だった場合、以下のようなSQL文を呼ぶよう分岐することで一応動作上は問題なくなったのですが、かなり強引なやり方なのが気に入りません。

$sql = sprintf('INSERT INTO hogeTable SET hoge=NULL');

何か良い方法はありますでしょうか？

Answer 1

MySQLでは「INSERT INTO 〜 SET」という構文が使えるんですね・・・ベンダ拡張恐るべし。

前置きはさて置き、
今回のケースではMySQLの自動変換にやられてるくさいですね。

以下、PHPのソースからSQL部分を切り出してみました。

INSERT INTO hogeTable SET hoge="%s"

更新しようとしているカラムが数値型ということなので、
上記のSQLに違和感を感じてもらいたい所です。

今回のケースで空文字にした場合も、
value値をNULLに変更してもどちらも0が設定されてしまうのはそれぞれ下記のようなSQLが作成されてしまうためです。

-- 空文字の場合
INSERT INTO hogeTable SET hoge=""

-- valueをNULLにした場合
INSERT INTO hogeTable SET hoge="NULL" -- ← 文字列「NULL」を設定しようとしてるSQLになる

これにMySQLの無効な文字列は変換できる所までキャストするという動きが相まって「0」が設定されているものと思われます。
(少し旧い記事ですが参考までにこちらをどうぞ)

そもそも数値型データに値を設定する場合は、文字列と異なり引用符は不要です。

なのでvalueをNULLとして、
POST変数にもNULLが文字列として渡っているのであれば、
以下のように引用符を取り外せばNULLとして設定されるはずです。

INSERT INTO hogeTable SET hoge= %s

またNULLについては、
扱いが難しいので避けれるのであれば避けてもらうのが無難ですが、
実入力としての「0」は集計対象とする場合は区別化しないと結果が狂うので、
入力内容不明という用途としてNULLを扱う分には頭ごなしには否定しません。

ただし、NULLの特性について熟知した上での利用をお勧めします。
集計関数では原則NULLは除外されるので問題ないですが、
検索条件としての参照や値の比較・計算が発生する場合は要注意です。

蛇足ですがNULLを利用しない代替案としては、
例えばあえて業務上設定されえない値をNULL代わりに利用したり、
入力有無判定ではそれを表すフラグやステータスの列を用意するといった回避策はしばしば使われますね。

Answer 2

普通にバリデートして場合分けすればよいような気がしますが・・・

<?PHP
if(isset($_POST['hoge'])){
  $sql = "INSERT INTO hogeTable (hoge) VALUES(%s)";
  $hoge=filter_input(INPUT_POST,'hoge',FILTER_VALIDATE_INT,["options"=>["default"=>""]]);
  $sql=sprintf($sql,$hoge===""?"NULL":"{$hoge}");
  print $sql;
}else{
  print "postデータはありません";
};
?>
<form method="post">
<input type="text" name="hoge" value="">
<input type="submit" value="go">:空データ
</form>
<form method="post">
<input type="text" name="hoge" value="-100">
<input type="submit" value="go">:マイナス
</form>
<form method="post">
<input type="text" name="hoge" value="0">
<input type="submit" value="go">:ゼロ
</form>
<form method="post">
<input type="text" name="hoge" value="012">
<input type="submit" value="go">:ゼロで始まる数字
</form>
<form method="post">
<input type="text" name="hoge" value="100">
<input type="submit" value="go">:普通の数値
</form>
<form method="post">
<input type="text" name="hoge" value="12.34">
<input type="submit" value="go">：小数点
</form>
<form method="post">
<input type="text" name="hoge" value="9,999">
<input type="submit" value="go">：カンマ区切り文字
</form>
<form method="post">
<input type="text" name="hoge" value="aaa">
<input type="submit" value="go">:文字列
</form>
<form method="post">
<input type="text" name="hoge" value="123xxx">
<input type="submit" value="go">:数字入り文字列
</form>

Answer 3

MySQLでは、文字列を数値にキャストする際、数字から始まっていない文字列は全て 0 に変換されます。

mysql> SELECT CAST('' AS SIGNED), CAST('NULL' AS SIGNED), CAST('3hoge' AS SIGNED);
+--------------------+------------------------+-------------------------+
| CAST('' AS SIGNED) | CAST('NULL' AS SIGNED) | CAST('3hoge' AS SIGNED) |
+--------------------+------------------------+-------------------------+
|                  0 |                      0 |                       3 |
+--------------------+------------------------+-------------------------+
1 row in set, 3 warnings (0.00 sec)

PHP の mysqli モジュールから MySQL に NULL を格納したい場合、
以下のようにバインド変数に（PHPの）NULL をセットしてください。

// ユーザーからの入力がなかった場合にはNULLを代入
if (count($_POST['hoge']) > 0) {
    $value = (int) $_POST['hoge'];  
} else {
    $value = null;
}

$stmt = mysqli_prepare($db, 'INSERT INTO hogeTable SET hoge = ?');
mysqli_stmt_bind_param($stmt, 'i', $value);

mysqli_stmt_execute($stmt);

http://php.net/manual/ja/mysqli-stmt.bind-param.php

You can bind to variables with NULL values, and on update and insert queries, the corresponding field will be updated to NULL no matter what bind string type you associated it with.

Answer 4

$sql = sprintf('INSERT INTO hogeTable SET hoge=IF("%s"="", NULL, "%s")',
    mysqli_real_escape_string($db, $_POST['hoge']),
    mysqli_real_escape_string($db, $_POST['hoge'])
);

我ながら…なんとも場当たり的な対処。

Answer 5

そもそも論ですが、NULLってバッドプラクティスなのでは？
NULL撲滅委員会

数値の0ではダメで、NULLをわざわざ入れたい理由は何でしょう？

Comments

[DaiAoki]

NULLを格納したい理由は、hogeのAVGを取得して平均値を出す際に、未入力の項目はNULLとして除外したいからです。
0が格納されてしまいますと、平均値を正しく取得できないという問題があり、NULLを格納したいのです。

[退会済みユーザー]

集計とかするなら、なおさらNULLは排除しといたほうが余計なバグが出ない気がしますが・・・。まあMySQLの場合はNULLを無視してAVG計算してくれるようですが。

でも「0」を使った場合でも0を除外して平均値を出す工夫はいくらでも出来そうですよ。
↓
null - Mysql AVG to ignore zero - Stack Overflow
http://stackoverflow.com/questions/5340883/mysql-avg-to-ignore-zero

Answer 6

回答していただいた皆さん、ありがとうございました。
検討の結果、valueをNULLとし、SQL文のダブルクォーテーョンを外す方法が一番しっくりきたため、ベストアンサーに選ばせていただきました。
特にNULLの扱い方には注意が必要ということで、今後機能を増やしていった時に、今回の変更が悪影響を与えないように気をつけたいと思いました。

ありがとうございました。