MySQL がデフォルト値を持つ NULL 列の使用を推奨しない理由

MySQL がデフォルト値を持つ NULL 列の使用を推奨しない理由

よく聞かれる答えは、列に NULL 値を使用するとインデックスが無効になるというものですが、実際にテストすると、IS NULL はインデックスを使用することがわかります。つまり、上記のステートメントには抜け穴があります。

不安な人は下までスクロールして結論を​​見てください

序文

Null は列の特別な制約です。
明示的に「not null」キーワードで列を定義しない場合、テーブル内の列にnull制約が追加されます。
テーブルを作成するときに、多くのプログラマーはデフォルトで列を定義するのが好きです
利便性(無効性の判断コードを減らす)のために、結果として
クエリの不確実性とデータベースのパフォーマンスの低下を引き起こします。

NULL 値は列に対する特別な制約です。新しい列を作成するときに、データ列を宣言するためにキーワード not null を明示的に使用しないと、Mysql はデフォルトで NULL 制約を追加します。
一部の開発者は、怠惰のため、データ テーブルを作成するときに MySQL のデフォルトの推奨設定を直接使用します (つまり、フィールドで NULL 値の使用を許可します)。この悪い習慣により、NULL が使用されるシナリオで、不確実なクエリ結果が簡単に発生し、データベースのパフォーマンスが低下する可能性があります。

導入

Null は null であるということは、それが何でもないということであり、null が '' と等しいと考えることはできず、それらは完全に異なります。
MySQL には、null 値を処理するための 3 つの演算子、「IS NULL」、「IS NOT NULL」、「<=>」、および関数 ifnull() が用意されています。
IS NULL: 列の値が null の場合、true を返します。
IS NOT NULL: 列の値が null でない場合は true を返します。
<=>: 「=」に似た比較演算子ですが、同じではありません。2 つの null 値の場合でも true を返します。
(例: null <=> null は有効です)
IFNULL(): 2 つの入力パラメータを指定します。最初のパラメータが null 値の場合は、2 番目のパラメータを返します。
これは Oracle の NVL() 関数と似ています。

NULL は何も意味しないわけではありません。NULL と '' (null 値) はまったく異なる 2 つの値であることに注意してください。MySQL で NULL 値を操作できる主な演算子は 3 つあります。

  • NULLです
  • NULLではない
  • <=> 宇宙船演算子。この演算子は = と非常によく似ています。select NULL<=>NULL は true を返しますが、select NULL=NULL は false を返します。
  • IFNULL は関数です。使い方は自分で調べてください... とにかく、使い方はわかっています。

「<=>」で null 以外の値と比較した場合、null は true を返すことはありません。
任意の演算子を使用して NULL を他の値と比較すると、<=> を除いて NULL が生成されます。

(root@localhost mysql3306.sock)[zlm]>テーブルtest_null(を作成します。
    -> id int が null ではない、
    -> 名前varchar(10)
    -> );
クエリは正常、影響を受けた行は 0 行 (0.02 秒)

(root@localhost mysql3306.sock)[zlm]>test_null値に挿入(1,'zlm');
クエリは正常、1 行が影響を受けました (0.00 秒)

(root@localhost mysql3306.sock)[zlm]>test_null値(2,null)に挿入します。
クエリは正常、1 行が影響を受けました (0.00 秒)

(root@localhost mysql3306.sock)[zlm]>test_nullから*を選択します。
+----+------+
| ID | 名前 |
+----+------+
| 1 | ズルム |
| 2 | NULL |
+----+------+
セット内の 2 行 (0.00 秒)

(root@localhost mysql3306.sock)[zlm]>name=null の場合、test_null から * を選択します。
空のセット (0.00 秒)

(root@localhost mysql3306.sock)[zlm]>select * from test_null where name is null;
+----+------+
| ID | 名前 |
+----+------+
| 2 | NULL |
+----+------+
セット内の 1 行 (0.00 秒)

(root@localhost mysql3306.sock)[zlm]>nameがnullでないtest_nullから*を選択します。
+----+------+
| ID | 名前 |
+----+------+
| 1 | ズルム |
+----+------+
セット内の 1 行 (0.00 秒)

(root@localhost mysql3306.sock)[zlm]>test_nullから*を選択します。ここで、nullはnullです。
空のセット (0.00 秒)

(root@localhost mysql3306.sock)[zlm]>test_nullから*を選択します。ここで、null<>null;
空のセット (0.00 秒)

(root@localhost mysql3306.sock)[zlm]>test_nullから*を選択します。ここで、null<=>null;
+----+------+
| ID | 名前 |
+----+------+
| 1 | ズルム |
| 2 | NULL |
+----+------+
セット内の 2 行 (0.00 秒)

//null<=>null は常に true を返します。これは「1=1 の場合」と同じです。

Null は「欠落した不明な値」を意味します。詳細は以下をご覧ください。
NULL は不確定な値を表します。2 つの NULL があっても、必ずしも等しいとは限りません。(C の初期化されていないローカル変数のようなものです。)

(root@localhost mysql3306.sock)[zlm]>0 は NULL、0 は NULL ではありません、'' は NULL、'' は NULL ではありません;
+-----------+--------------+------------+----------------+
| 0 は NULL です | 0 は NULL ではありません | '' は NULL です | '' は NULL ではありません |
+-----------+--------------+------------+----------------+
| 0 | 1 | 0 | 1 |
+-----------+--------------+------------+----------------+
セット内の 1 行 (0.00 秒)

//ゼロ数値または空の文字列と等しくありません。
//MySQL では、0 は false、1 は true を意味します。

(root@localhost mysql3306.sock)[zlm]>SELECT 1 = NULL、1 <> NULL、1 < NULL、1 > NULL;
+----------+-----------+-----------+----------+
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |
+----------+-----------+-----------+----------+
| NULL | NULL | NULL | NULL |
+----------+-----------+-----------+----------+
セット内の 1 行 (0.00 秒)

//数値と比較することはできません。
//MySQL では null も false を意味します。

式に null 値が含まれている場合は、結果として null が返されます。
NULL を含む値を返す式は、別の NULL 値を返します。

(root@localhost mysql3306.sock)[zlm]>ifnull(null,'First is null'),ifnull(null+10,'First is null'),ifnull(concat('abc',null),'First is null'); を選択します。
+------------------------------+---------------------------------+--------------------------------------------+
| ifnull(null,'First は null') | ifnull(null+10,'First は null') | ifnull(concat('abc',null),'First は null') |
+------------------------------+---------------------------------+--------------------------------------------+
| 最初は null です | 最初は null です | 最初は null です |
+------------------------------+---------------------------------+--------------------------------------------+
セット内の 1 行 (0.00 秒)

//null 値は ifnull() 関数で破棄する必要があり、これにより通常、SQL ステートメントがより複雑になります。
// ご存知のとおり、MySQL は関数インデックスをサポートしていません。そのため、列のインデックスは使用できない可能性があります。これは本当に悪いことです。

count(*) と count(null 列) を使用する場合は異なります。
count(*) または count(null column) を使用した場合の結果は異なり、count(null column)<=count(*) となります。

(root@localhost mysql3306.sock)[zlm]>test_nullからcount(*),count(name)を選択します。
+----------+--------------+
| count(*) | count(名前) |
+----------+--------------+
| 2 | 1 |
+----------+--------------+
セット内の 1 行 (0.00 秒)

//count(*) は null を無視してすべての行を返しますが、count(name) は列 "name" 内の null 以外の行を返します。
//上記の詳細を知らない人がいた場合、これも不確実性につながります。

ユーザーが NULL 属性に精通していない場合、間違った統計結果が得られやすくなります。

distinctive、group by、order by を使用する場合、すべての null 値は同じ値として扱われます。
select NULL=NULL の結果は false ですが、distinct、group by、order by を使用すると、NULL は同じ値とみなされます。

(root@localhost mysql3306.sock)[zlm]>test_null値(3,null)に挿入します。
クエリは正常、1 行が影響を受けました (0.00 秒)

(root@localhost mysql3306.sock)[zlm]>test_nullから別の名前を選択します。
+------+
| 名前 |
+------+
| 日本語
| NULL |
+------+
セット内の 2 行 (0.00 秒)

// 2 行の null 値が 1 を返したため、結果は 2 になりました。

(root@localhost mysql3306.sock)[zlm]>名前でtest_nullグループから名前を選択します。
+------+
| 名前 |
+------+
| NULL |
| 日本語
+------+
セット内の 2 行 (0.00 秒)

// 2 行の null 値が同じグループに配置されました。
// デフォルトでは、group by によって結果も並べ替えられます (null 行が最初に表示されます)。

(root@localhost mysql3306.sock)[zlm]>test_nullからidとnameをnameで選択します。
+----+------+
| ID | 名前 |
+----+------+
| 2 | NULL |
| 3 | NULL |
| 1 | ズルム |
+----+------+
セット内の 3 行 (0.00 秒)

// 3 行がソートされました (2 つの null 行が最初に表示されました)。

MySQL は、null 値を含む列でのインデックスの使用をサポートしています (Oracle との違い)。
MySQL は NULL 値を含む列でのインデックスの使用をサポートしていますが、Oracle はサポートしていません。これは通常、列に NULL が含まれている場合、インデックスが無効になると言われることです。
厳密に言えば、この記述は MySQL には当てはまりません。

(root@localhost mysql3306.sock)[sysbench]>テーブルを表示します。
+--------------------+
| sysbench のテーブル |
+--------------------+
|sbtest1|
|sbtest10|
|sbテスト2|
|sbtest3|
|sbテスト4|
|sbtest5|
|sbtest6|
|sbtest7|
|sbtest8|
|sbtest9|
+--------------------+
セット内の行数は 10 です (0.00 秒)

(root@localhost mysql3306.sock)[sysbench]>show create table sbtest1\G
************************** 1. 行 ****************************
       テーブル: sbtest1
テーブルの作成: CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL デフォルト '0',
  `c` char(120) NOT NULL デフォルト ''
  `pad` char(60) NOT NULL DEFAULT ''
  主キー (`id`)、
  キー `k_1` (`k`)
) エンジン=InnoDB AUTO_INCREMENT=100001 デフォルト文字セット=utf8
セット内の 1 行 (0.00 秒)

(root@localhost mysql3306.sock)[sysbench]>alter table sbtest1、k int nullを変更、c char(120) nullを変更、pad char(60) nullを変更;
クエリは正常、影響を受けた行は 0 行 (4.14 秒)
レコード: 0 重複: 0 警告: 0

(root@localhost mysql3306.sock)[sysbench]>sbtest1に値(100001,null,null,null)を挿入します。
クエリは正常、1 行が影響を受けました (0.00 秒)

(root@localhost mysql3306.sock)[sysbench]>explain select id,k from sbtest1 where id=100001;
+----+-------------+----------+-----------+--------+---------------+----------+-------+-------+------+------+------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+----------+-----------+--------+---------------+----------+-------+-------+------+------+------+
| 1 | SIMPLE | sbtest1 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+----------+-----------+--------+---------------+----------+-------+-------+------+------+------+
セットに 1 行、警告 1 件 (0.00 秒)

(root@localhost mysql3306.sock)[sysbench]>explain select id,k from sbtest1 where k is null;
+----+-------------+----------+-----------+---------+---------------+------+--------+--------+----------+----------------------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+----------+-----------+---------+---------------+------+--------+--------+----------+----------------------------+
| 1 | SIMPLE | sbtest1 | NULL | ref | k_1 | k_1 | 5 | const | 1 | 100.00 | where の使用; index の使用 |
+----+-------------+----------+-----------+---------+---------------+------+--------+--------+----------+----------------------------+
セットに 1 行、警告 1 件 (0.00 秒)

//最初のクエリでは、新しく追加された行が主キーによって取得されます。
//2番目のクエリでは、新しく追加された行がセカンダリキー「k_1」によって取得されます
// NULL 値を含む列でもインデックスを使用できることが証明されています。
//列「k」は 4 バイトを占める int データ型ですが、「key_len」の値は 5 になります。何がうれしいのでしょうか? null 値は行に null フラグを格納するために 1 バイトを必要とするためです。

これは私が自分でテストした例です。

mysql> test_1 から * を選択します。
+-----------+------+------+
| 名前 | コード | ID |
+-----------+------+------+
| gaoyi | を | 1 |
| ガオイ | w | 2 |
| chuzhong | を | 3 |
| chuzhong | w | 4 |
| シャオシュエ | dd | 5 |
| xiaoxue | dfdf | 6 |
| 蘇江匯 | su | 99 |
| 蘇江匯 | NULL | 99 |
+-----------+------+------+
セット内の行数は 8 です (0.00 秒)

mysql> explain select * from test_1 where code is NULL;
+----+-------------+---------+-----------+---------+---------------+------------+--------+--------+---------+------------------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+---------+-----------+---------+---------------+------------+--------+--------+---------+------------------------+
| 1 | SIMPLE | test_1 | NULL | ref | index_code | index_code | 161 | const | 1 | 100.00 | インデックス条件の使用 |
+----+-------------+---------+-----------+---------+---------------+------------+--------+--------+---------+------------------------+
セットに 1 行、警告 1 件 (0.00 秒)

mysql> explain select * from test_1 where code is not NULL;
+----+-------------+----------+-----------+---------+---------------+------------+-------+-------+---------+-----------------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+----------+-----------+---------+---------------+------------+-------+-------+---------+-----------------------+
| 1 | SIMPLE | test_1 | NULL | 範囲 | index_code | index_code | 161 | NULL | 7 | 100.00 | インデックス条件を使用 |
+----+-------------+----------+-----------+---------+---------------+------------+-------+-------+---------+-----------------------+
セットに 1 行、警告 1 件 (0.00 秒)

mysql> 説明: test_1 から select * を実行し、 code='dd' と入力します。
+----+-------------+---------+-----------+---------+---------------+------------+--------+--------+---------+------------------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+---------+-----------+---------+---------------+------------+--------+--------+---------+------------------------+
| 1 | SIMPLE | test_1 | NULL | ref | index_code | index_code | 161 | const | 1 | 100.00 | インデックス条件の使用 |
+----+-------------+---------+-----------+---------+---------------+------------+--------+--------+---------+------------------------+
セットに 1 行、警告 1 件 (0.00 秒)

mysql> explain select * from test_1 where code like "dd%";
+----+-------------+----------+-----------+---------+---------------+------------+-------+-------+---------+-----------------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+----------+-----------+---------+---------------+------------+-------+-------+---------+-----------------------+
| 1 | SIMPLE | test_1 | NULL | 範囲 | index_code | index_code | 161 | NULL | 1 | 100.00 | インデックス条件を使用 |
+----+-------------+----------+-----------+---------+---------------+------------+-------+-------+---------+-----------------------+
セットに 1 行、警告 1 件 (0.00 秒)

まとめ

NULL 値は、SQL ステートメントを処理するときに常に多くの不確実性をもたらします。誤ってパフォーマンスが低下する可能性があります。

列に NULL 値を使用すると、制御不能な動作が発生しやすくなり、システム パフォーマンスが大幅に低下することがあります。

例えば:

集計関数() では null 値が推定されないため、不正確な結果が発生する可能性があります。
count()、max()、min() などの NULL 値を含む列に対して統計計算を実行すると、結果が期待どおりになりません。

null 値は、「distinct」、「group by」、「order by」などの操作の動作に影響し、誤った並べ替えの原因になります。
並べ替え、グループ化、重複排除の結果を妨害します。

null 値の判定には ifnull() 関数が必要であり、プログラム コードがより複雑になります。
場合によっては、NULL によって生じる技術的負債を解消するために、SQL で IFNULL() を使用して結果が制御可能であることを確認する必要がありますが、これによりプログラムが複雑になります。
NULL 値の場合、行に NULL 情報を格納するために追加の 1 バイトが必要です。

NULL 値は、保存のために元のフィールド スペースを占有しませんが、マーク付けのために追加のバイトが適用されます。このフィールドは、NULL 制約を追加します。(追加のフラグ ビットと同様)
上記の欠点があるため、デフォルトで null を持つ列を定義することはお勧めしません。
すべての列に「not null」を定義し、ゼロ数値と空の文字列を使用して、関連するデータ型の null を置き換えることをお勧めします。

上記の欠点を踏まえると、列のデフォルト値として NULL を設定することはお勧めしません。NOT NULL を使用してデフォルト設定を排除し、NULL の代わりに 0 または '' 空文字列を使用することができます。

参考文献

https://www.cnblogs.com/aaron8219/p/9259379.html

これで、MySQL がデフォルト値が null の列の使用を推奨しない理由についての説明は終わりです。MySQL のデフォルト値が null であることの詳細については、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

以下もご興味があるかもしれません:
  • Springbootバックエンドで複数のデータソースとMySQLデータベースを構成する便利な方法
  • MySQL DEFINER の使用方法の詳細な説明
  • MySQLの分離レベルとロックメカニズムの詳細な説明
  • Django 本番環境構築 (uWSGI+django+nginx+python+MySQL)
  • MySQL の主キーがクエリを高速化するために数値を使用するか UUID を使用するかについての簡単な分析
  • MySQL 権限とデータベース設計のケーススタディ
  • Node-Redを使用してMySQLデータベースに接続する方法
  • MySQL の group by と having の詳細な説明

<<:  Nginx 設定ファイルの詳細な説明と最適化の提案ガイド

>>:  CSS3 で複数のカスタムフォントを導入する

推薦する

CentOS7でXShellとネットワーク設定を接続する方法

1. Linuxネットワーク構成ネットワークを構成する前に、まずローカル IPv4 アドレスやデフォ...

Dockerが新しいイメージをロードした後にリポジトリとタグ名が両方ともnoneになる問題を解決する

次のコマンドを使用できます: docker tag [イメージID] [名前]:[バージョン]例えば...

VueはTeleportをベースにModalコンポーネントを実装します

目次1. テレポートについて知る2. テレポートの基本的な使い方3. 最初のステップの最適化4. 第...

ランダムな文字を生成する Java サンプルコード

サンプルコード: java.util.Random をインポートします。 java.util.UUI...

Vueバスの簡単な使い方

Vueバスの簡単な使い方シナリオの説明:コンポーネント A にはコンポーネント B と C が含まれ...

js で継承を実装する 5 つの方法

コンストラクタの借用この手法の基本的な考え方は単純です。サブタイプ コンストラクター内からスーパータ...

iframeノードの初期化の問題に関する議論

今日、ふとリッチテキストエディタの制作原理を見直してみようと思いました。それで、彼は何も言わずにそれ...

IE6では画像要素imgに余分な空白スペースがある

ページの DIV+CSS レイアウトを行う際、IE6 で画像要素 img の下に余分なスペースができ...

CocosCreatorメッセージ配信メカニズムの詳細な説明

概要この記事は、ゲームビジネスアーキテクチャに関連するコンテンツの紹介から始まります。ゲームビジネス...

ZabbixはPSK共有キーを使用してサーバーとエージェント間の通信を暗号化します。

Zabbix バージョン 3.0 以降、Zabbix サーバー、Zabbix プロキシ、Zabbi...

overflow:autoの使い方の詳しい説明

本文に入る前に、オーバーフローとフレックスレイアウトの使い方をいくつか紹介します。 overflow...

VUE+Canvasはデスクトップピンボールブロック破壊ゲームのサンプルコードを実装します

誰もがピンボールやレンガ崩しのゲームをプレイしたことがあるでしょう。左と右のキーを使用して、下にある...

Linux での chmod コマンドの使用方法の詳細な説明

chmod コマンド構文chmod コマンドを使用する場合の正しい構文は次のとおりです。 chmod...

Linux でユーザーを完全に削除する 2 つの方法

Linux 操作実験環境: Centos7 仮想マシンまず、共通ユーザーgubeiqingを作成しま...

Vue2.x における双方向バインディングの原理と実装

目次1. 実施プロセス2. オブザーバーを表示する3. ウォッチャーを実装する4. コンパイルを実装...