MySQL NULL 値の処理

MySQL が使用することをすでに知っていますSELECTコマンドおよびWHERE句を使用してデータテーブル内のデータを読み取りますが、指定したクエリ条件フィールドが NULL の場合、そのコマンドは正常に動作しない可能性があります。

MySQL では、NULL は欠落したデータや不明なデータを表すために使用されます。NULL 値の処理には特別な注意が必要です。データベース内で予期しない結果を引き起こす可能性があるためです。

このような場合に対処するために、MySQL には次の3つの主要な演算子が用意されています。

  • IS NULL:列の値が NULL の場合、この演算子は true を返します。
  • IS NOT NULL:列の値が NULL でない場合、演算子は true を返します。
  • <=>:比較演算子(= 演算子とは異なります)。比較する2つの値が等しい場合、または両方が NULL の場合に true を返します。

NULL に関する条件比較演算は特殊です。列内で NULL 値を検索するために = NULL や != NULL を使用することはできません。

MySQL では、NULL 値と他の任意の値(NULL であっても)との比較は常に NULL を返します。つまり、NULL = NULL は NULL を返します。

MySQL で NULL を処理するには、IS NULL および IS NOT NULL 演算子を使用します。

注意:

select * , columnName1+ifnull(columnName2,0) from tableName;

columnName1、columnName2 は int 型です。columnName2 に null の値がある場合、columnName1+columnName2=null になります。 ifnull(columnName2,0) は columnName2 の null 値を 0 に変換します。

MySQL で NULL 値を扱う際の一般的な注意点とテクニック

1. NULL かどうかの確認:

列が NULL かどうかを確認するには、IS NULL または IS NOT NULL 条件を使用できます。

SELECT * FROM employees WHERE department_id IS NULL;
SELECT * FROM employees WHERE department_id IS NOT NULL;

2. COALESCE 関数を使用して NULL を処理する:

COALESCE 関数は NULL の値を置き換えるために使用できます。複数の引数を受け取り、引数リスト内の最初の非 NULL 値を返します。

SELECT product_name, COALESCE(stock_quantity, 0) AS actual_quantity
FROM products;

上記の SQL 文では、stock_quantity 列が NULL の場合、COALESCE は 0 を返します。

3. IFNULL 関数を使用して NULL を処理する:

IFNULL 関数は MySQL 固有の COALESCE バージョンであり、2つの引数を受け取ります。最初の引数が NULL の場合は、2番目の引数を返します。

SELECT product_name, IFNULL(stock_quantity, 0) AS actual_quantity
FROM products;

4. NULL の並べ替え:

ORDER BY 句で並べ替える場合、NULL 値はデフォルトで並べ替えの最後に配置されます。NULL 値を先頭に配置したい場合は、ORDER BY column_name ASC NULLS FIRST を使用し、逆に最後に配置したい場合は ORDER BY column_name DESC NULLS LAST を使用します。

SELECT product_name, price
FROM products
ORDER BY price ASC NULLS FIRST;

5. <=> 演算子を使用した NULL 比較:

<=> 演算子は、MySQL で2つの式が等しいかどうかを比較するための特別な演算子であり、NULL 値の比較に対しても TRUE を返します。NULL 値の等値比較を処理するために使用できます。

SELECT * FROM employees WHERE commission <=> NULL;

6. 集計関数による NULL の処理に注意する:

集計関数(COUNT、SUM、AVG など)を使用する場合、これらは NULL 値を無視するため、予期しない結果になる可能性があります。NULL を 0 として扱いたい場合は、COALESCE または IFNULL を使用できます。

SELECT AVG(COALESCE(salary, 0)) AS avg_salary FROM employees;

これにより、salary が NULL であっても、集計関数はそれを 0 として扱います。

NULL 値を処理する際は、クエリと操作のセマンティクスが期待どおりであることを確認するために特別な注意を払う必要があります。テーブル構造を設計する際にも、NULL 値の使用シナリオと妥当性を考慮する必要があります。


コマンドプロンプトで NULL 値を使用する

以下の例では、データベース サンプル 内のテーブル サンプル_test_tbl に サンプル_author と サンプル_count の2つの列があり、サンプル_count には NULL 値を挿入する設定であると仮定します。

例

次の例を試してください:

データテーブル サンプル_test_tbl を作成する

root@host# mysql -u root -p password; Enter password:******* mysql> use サンプル; Database changed mysql> create table サンプル_test_tbl -> ( -> サンプル_author varchar(40) NOT NULL, -> サンプル_count INT -> ); Query OK, 0 rows affected (0.05 sec) mysql> INSERT INTO サンプル_test_tbl (サンプル_author, サンプル_count) values ('サンプル', 20); mysql> INSERT INTO サンプル_test_tbl (サンプル_author, サンプル_count) values ('サンプル', NULL); mysql> INSERT INTO サンプル_test_tbl (サンプル_author, サンプル_count) values ('Google', NULL); mysql> INSERT INTO サンプル_test_tbl (サンプル_author, サンプル_count) values ('FK', 20); mysql> SELECT * from サンプル_test_tbl; +---------------+--------------+ | サンプル_author | サンプル_count | +---------------+--------------+ | サンプル | 20| | サンプル |NULL | | Google | NULL | | FK | 20 | +---------------+--------------+ 4 rows in set (0.01 sec)

次の例では、= および != 演算子が機能しないことを確認できます。

mysql> SELECT * FROM サンプル_test_tbl WHERE サンプル_count = NULL; Empty set (0.00 sec) mysql> SELECT * FROM サンプル_test_tbl WHERE サンプル_count != NULL; Empty set (0.01 sec)

データテーブルの サンプル_test_tbl 列が NULL かどうかを検索するには、次を使用する必要がありますIS NULLとIS NOT NULL、次の例のとおりです。

mysql> SELECT * FROM サンプル_test_tbl WHERE サンプル_count IS NULL; +---------------+--------------+ | サンプル_author | サンプル_count| +---------------+--------------+ | サンプル |NULL | | Google | NULL | +---------------+--------------+ 2 rows in set (0.01 sec) mysql> SELECT * from サンプル_test_tbl WHERE サンプル_count IS NOT NULL; +---------------+--------------+ | サンプル_author | サンプル_count | +---------------+--------------+ | サンプル | 20 | | FK | 20 | +---------------+--------------+ 2 rows in set (0.01 sec)

PHP スクリプトを使用して NULL 値を処理する

PHP スクリプトでは、if...else ステートメントを使用して変数が空かどうかを処理し、対応する条件ステートメントを生成できます。

以下の例では、PHP が $サンプル_count 変数を設定し、その変数をデータテーブルの サンプル_count フィールドと比較します。

MySQL ORDER BY テスト:

<?php $dbhost = 'localhost'; //mysql サーバーのホストアドレス $dbuser = 'root'; //mysql ユーザー名 $dbpass = '123456'; //mysql ユーザー名のパスワード $conn = mysqli_connect($dbhost, $dbuser, $dbpass); if(! $conn ) { die('接続失敗:' . mysqli_error($conn)); } //エンコーディングを設定して、中国語の文字化けを防ぐ mysqli_query($conn , "set names utf8"); if( isset($サンプル_count )) { $sql = "SELECT サンプル_author, サンプル_count FROM サンプル_test_tbl WHERE サンプル_count = $サンプル_count"; } else { $sql = "SELECT サンプル_author, サンプル_count FROM サンプル_test_tbl WHERE サンプル_count IS NULL"; } mysqli_select_db( $conn, 'サンプル' ); $retval = mysqli_query( $conn, $sql ); if(! $retval ) { die('データを読み取れません:' . mysqli_error($conn)); } echo '<h2>サンプル IS NULL テスト<h2>'; echo '<table border="1"><tr><td>著者</td><td>ログイン回数</td></tr>'; while($row = mysqli_fetch_array($retval, MYSQL_ASSOC)) { echo "<tr>". "<td>{$row['サンプル_author']} </td> ". "<td>{$row['サンプル_count']} </td> ". "</tr>"; } echo '</table>'; mysqli_close($conn); ?>

出力結果は次の図のとおりです。

その他の拡張