MySQL の null (IFNULL、COALESCE、NULLIF) に関する知識ポイントのまとめ

この記事では、MySQL の null (IFNULL、COALESCE、NULLIF) に関連する知識ポイントを例を使用して説明します。ご参考までに、詳細は以下の通りです。

MySQL では、NULL 値は 0 または空の文字列 '' と異なり、それ自体と等しくない不明な値を表します。

NULL 値を別の NULL 値または他の値と比較すると、結果は NULL になります。これは、不明な値 (NULL 値) が別の不明な値 (NULL 値) と比較され、その値も当然不明な値 (NULL 値) であるためです。

ただし、通常、NULL 値は、データが欠落している、不明である、または該当しないことを示すために使用されます。たとえば、見込み客の電話番号が NULL であっても、後で追加することができます。したがって、テーブルを作成するときに、NOT NULL 制約を使用して、列が NULL 値を受け入れるかどうかを指定できます。次に、リードテーブルを作成し、それを詳細な理解の基礎として使用します。

CREATE TABLEリード（
  id INT AUTO_INCREMENT 主キー、
  first_name VARCHAR(50) NOT NULL、
  last_name VARCHAR(50) NOT NULL、
  ソース VARCHAR(255) NOT NULL、
  電子メール VARCHAR(100)、
  電話番号 VARCHAR(25)
);

id は主キー列であり、NULL 値を受け入れないこと、first_name、last_name、および source 列は NOT NULL 制約を使用するため、これらの列に NULL 値を挿入できないことがわかります。一方、email 列と phone 列は NULL 値を受け入れることができます。

したがって、挿入ステートメントで NULL 値を使用して、欠落しているデータを指定できます。たとえば、次のステートメントは、leads テーブルに行を挿入します。電話番号が欠落しているため、NULL 値が使用されます。

リードに INSERT INTO(first_name,last_name,source,email,phone) を挿入します
VALUE('John','Doe','Web 検索','[email protected]',NULL);

電子メール列のデフォルト値は NULL なので、次のように INSERT ステートメントで電子メールを省略できます。

リードに INSERT INTO(first_name,last_name,source,phone)
VALUES('リリー','ブッシュ','コールドコール','(408)-555-1234'),
('デビッド'、'ウィリアム'、'ウェブ検索'、'(408)-888-6789');

列の値を NULL に設定する場合は、代入演算子 (=) を使用できます。たとえば、David William の電話番号を NULL に更新するには、次の UPDATE ステートメントを使用します。

アップデートリード 
セット 
  電話番号 = NULL
どこ
  id = 3;

しかし、order by 句を使用して結果セットを昇順に並べ替えると、MySQL は NULL 値を他の値よりも低いと見なすため、NULL 値を最初に表示します。次のクエリは電話番号を昇順に並べ替えます。

選択 
  *
から
  リード
電話で注文する;

上記のクエリステートメントを実行すると、結果は次のようになります。

+----+------------+-----------+--------------+---------------------+----------------+
| ID | 名 | 姓 | ソース | 電子メール | 電話 |
+----+------------+-----------+--------------+---------------------+----------------+
| 1 | John | Doe | Web 検索 | [email protected] | NULL |
| 3 | デビッド | ウィリアム | Web 検索 | NULL | NULL |
| 2 | リリー | ブッシュ | コールドコール | NULL | (408)-555-1234 |
+----+------------+-----------+--------------+---------------------+----------------+

ORDER BY DESC を使用すると、結果セットの最後に NULL 値が表示されます。

選択 
  *
から
  リード
電話番号DESCで注文する

上記のクエリステートメントを実行すると、結果は次のようになります。

+----+------------+-----------+--------------+---------------------+----------------+
| ID | 名 | 姓 | ソース | 電子メール | 電話 |
+----+------------+-----------+--------------+---------------------+----------------+
| 2 | リリー | ブッシュ | コールドコール | NULL | (408)-555-1234 |
| 1 | John | Doe | Web 検索 | [email protected] | NULL |
| 3 | デビッド | ウィリアム | Web 検索 | NULL | NULL |
+----+------------+-----------+--------------+---------------------+----------------+
3行セット

クエリで NULL をテストする場合は、where 句で IS NULL または IS NOT NULL 演算子を使用できます。たとえば、電話番号を提供していないリードを取得するには、次のように IS NULL 演算子を使用します。

選択 
  *
から
  リード
どこ
  電話番号がNULLです。

上記のクエリステートメントを実行すると、結果は次のようになります。

+----+-------------+------------+-----------+----------------------+-------+
| ID | 名 | 姓 | ソース | 電子メール | 電話 |
+----+-------------+------------+-----------+----------------------+-------+
| 1 | John | Doe | Web 検索 | [email protected] | NULL |
| 3 | デビッド | ウィリアム | Web 検索 | NULL | NULL |
+----+-------------+------------+-----------+----------------------+-------+
2行セット

IS NOT 演算子を使用して、電子メールアドレスを提供したすべてのリードを取得することもできます。

選択 
  *
から
  リード
どこ
  電子メールはNULLではありません。

上記のクエリステートメントを実行すると、結果は次のようになります。

+----+-------------+------------+-----------+----------------------+-------+
| ID | 名 | 姓 | ソース | 電子メール | 電話 |
+----+-------------+------------+-----------+----------------------+-------+
| 1 | John | Doe | Web 検索 | [email protected] | NULL |
+----+-------------+------------+-----------+----------------------+-------+
セット内の1行

ただし、NULL が NULL と等しくない場合でも、GROUP BY 句では 2 つの NULL 値は等しいとみなされます。SQL の例を見てみましょう。

選択 
  メール、カウント(*)
から
  リード
電子メールでグループ化;

このクエリは、電子メール列が NULL である行が 1 行にグループ化されているため、2 行のみを返します。結果は次のようになります。

+---------------------+----------+
| メール | カウント(*) |
+---------------------+----------+
| NULL | 2 |
| [email protected] | 1 |
+---------------------+----------+
2行セット

列にユニーク制約または UNIQUE インデックスを使用する場合、その列に複数の NULL 値を挿入できることを知っておく必要があります。この場合、MySQL は NULL 値を個別の値と見なします。次に、phone 列に UNIQUE インデックスを作成してこれを確認します。

leads(phone) に idx_phone という一意のインデックスを作成します。

ここで注意すべき点は、BDB ストレージエンジンを使用する場合、MySQL は NULL 値を等しいと見なすため、一意制約のある列に複数の NULL 値を挿入できないことです。

null のメリットとデメリットがわかったので、MySQL で null を処理する方法を見てみましょう。 MySQL には、IFNULL、COALESCE、NULLIF という合計 3 つの関数が用意されています。

それぞれを個別に見てみましょう。まず、IFNULL 関数は 2 つのパラメータを受け入れます。 IFNULL 関数は、最初の引数が NULL でない場合は最初の引数を返し、そうでない場合は 2 番目の引数を返します。たとえば、次のステートメントは、電話番号 (phone) が NULL でない場合はそれを返し、それ以外の場合は NULL ではなく N/A を返します。例を見てみましょう:

選択 
  id、first_name、last_name、IFNULL(phone、'N/A') 電話
から
  リード;

上記のクエリステートメントを実行すると、次の結果が得られます。

+----+------------+-----------+----------------+
| ID | 名 | 姓 | 電話 |
+----+------------+-----------+----------------+
| 1 | ジョン | ドウ | N/A |
| 2 | リリー | ブッシュ | (408)-555-1234 |
| 3 | デビッド | ウィリアム | N/A |
+----+------------+-----------+----------------+
3行セット

COALESCE 関数は引数のリストを受け取り、最初の NULL 以外の引数を返します。たとえば、COALESCE 関数を使用すると、情報の優先度に基づいて、電話、電子メール、N/A の順序でリードの連絡先情報を表示できます。以下にいくつか例を挙げます。

選択 
  id、
  ファーストネーム、
  苗字、
  COALESCE(電話、メール、'N/A') 連絡先
から
  リード;

上記のクエリステートメントを実行すると、次のコードが取得されます。

+----+------------+-----------+---------------------+
| ID | 名 | 姓 | 連絡先 |
+----+------------+-----------+---------------------+
| 1 | ジョン | ドウ | [email protected] |
| 2 | リリー | ブッシュ | (408)-555-1234 |
| 3 | デビッド | ウィリアム | N/A |
+----+------------+-----------+---------------------+
3行セット

最後に、2 つのパラメータを受け入れる NULLIF 関数があります。 2 つの引数が等しい場合、NULLIF 関数は NULL を返します。それ以外の場合は、最初の引数を返します。 NULLIF 関数は、列に NULL 値と空の文字列値の両方がある場合に便利です。たとえば、次の行を誤ってリードテーブルに挿入しました。

リードに INSERT INTO(first_name,last_name,source,email,phone) を挿入します
VALUE('Thierry','Henry','Web 検索','[email protected]','');

電話は空の文字列であるため: ''、NULL ではありません。したがって、潜在顧客の連絡先情報を取得したい場合は、次のように、電子メールアドレスではなく、null 電話番号になります。

選択 
  id、
  ファーストネーム、
  苗字、
  COALESCE(電話、メール、'N/A') 連絡先
から
  リード;

上記のクエリステートメントを実行すると、次のコードが取得されます。

+----+------------+-----------+---------------------+
| ID | 名 | 姓 | 連絡先 |
+----+------------+-----------+---------------------+
| 1 | ジョン | ドウ | [email protected] |
| 2 | リリー | ブッシュ | (408)-555-1234 |
| 3 | デビッド | ウィリアム | N/A |
| 4 | ティエリ | アンリ | |
+----+------------+-----------+---------------------+

この問題を解決するには、NULLIF 関数を使用して電話番号を空の文字列 ('') と比較する必要があります。等しい場合は NULL を返し、そうでない場合は電話番号を返します。

選択 
  id、
  ファーストネーム、
  苗字、
  COALESCE(NULLIF(電話, ''), 電子メール, 'N/A') 連絡先
から
  リード;

上記のクエリステートメントを実行すると、次のコードが取得されます。

+----+------------+-----------+---------------------------+
| ID | 名 | 姓 | 連絡先 |
+----+------------+-----------+---------------------------+
| 1 | ジョン | ドウ | [email protected] |
| 2 | リリー | ブッシュ | (408)-555-1234 |
| 3 | デビッド | ウィリアム | N/A |
| 4 | ティエリー | ヘンリー | [email protected] |
+----+------------+-----------+---------------------------+
4行セット

さて、この記録についてはこれですべてです。

MySQL 関連のコンテンツに興味のある読者は、このサイトの次のトピックをチェックしてください: 「MySQL クエリスキル」、「MySQL トランザクション操作スキル」、「MySQL ストアドプロシージャスキル」、「MySQL データベースロック関連スキルの概要」、および「MySQL 共通関数の概要」

この記事が皆様のMySQLデータベース設計に役立つことを願っています。

以下もご興味があるかもしれません: