「NOT NULL」指定フィールドに値を指定せずにレコードを挿入した時、NULLが登録されず、「空文字」や「0」が登録されるケースについて。

ケース１

「NOT NULL」指定なし、「DEFAULT NULL」指定なしフィールドをもつテーブルを作成。

CREATE TABLE test1 ( 
  id INT AUTO_INCREMENT PRIMARY KEY,
  name CHAR(10),
  number INT
);

テーブル構成確認

name：「Null」が「YES」、「Default」が「NULL」
number：「Null」が「YES」、「Default」が「NULL」

MariaDB [lesson]> DESC test1;
+--------+----------+------+-----+---------+----------------+
| Field  | Type     | Null | Key | Default | Extra          |
+--------+----------+------+-----+---------+----------------+
| id     | int(11)  | NO   | PRI | NULL    | auto_increment |
| name   | char(10) | YES  |     | NULL    |                |
| number | int(11)  | YES  |     | NULL    |                |
+--------+----------+------+-----+---------+----------------+
3 rows in set (0.056 sec)

レコード挿入

下記3パターンでレコード挿入

1. nameフィールドもnumberフィールドも値を指定してレコード挿入
2. nameフィールドのみ値を指定してレコード挿入
3. numberフィールドのみ値を指定してレコード挿入

INSERT INTO test1 SET name='田中', number=10;
INSERT INTO test1 SET name='田中';
INSERT INTO test1 SET number=30;

テーブル確認

MariaDB [lesson]> SELECT * FROM test1;
+----+------+--------+
| id | name | number |
+----+------+--------+
|  1 | 田中 |     10 |
|  2 | 田中 |   NULL |
|  3 | NULL |     30 |
+----+------+--------+
3 rows in set (0.013 sec)

1. nameフィールドもnumberフィールドも値を指定してレコード挿入
   各フィールドに指定した値を登録
2. nameフィールドのみ値を指定してレコード挿入
   値を指定しなかったnumberフィールドにNULLが登録される
3. numberフィールドのみ値を指定してレコード挿入
   値を指定しなかったnameフィールドにNULLが登録される

「NOT NULL」指定なし、「DEFAULT NULL」指定なしフィールドは、値を指定せずにレコードを挿入すると「NULL」が登録される。

ケース２

「NOT NULL」を指定あり、「DEFAULT NULL」指定なしフィールドをもつテーブルを作成。

CREATE TABLE test2 ( 
  id INT AUTO_INCREMENT PRIMARY KEY,
  name CHAR(10) NOT NULL,
  number INT NOT NULL
);

テーブル構成確認

name：「Null」が「NO」、「Default」が「NULL」
number：「Null」が「NO」、「Default」が「NULL」

MariaDB [lesson]> DESC test2;
+--------+----------+------+-----+---------+----------------+
| Field  | Type     | Null | Key | Default | Extra          |
+--------+----------+------+-----+---------+----------------+
| id     | int(11)  | NO   | PRI | NULL    | auto_increment |
| name   | char(10) | NO   |     | NULL    |                |
| number | int(11)  | NO   |     | NULL    |                |
+--------+----------+------+-----+---------+----------------+
3 rows in set (0.014 sec)

レコード挿入

下記3パターンでレコード挿入

1. nameフィールドもnumberフィールドも値を指定してレコード挿入
2. nameフィールドのみ値を指定してレコード挿入
3. numberフィールドのみ値を指定してレコード挿入

INSERT INTO test2 SET name='田中', number=10;
INSERT INTO test2 SET name='田中';
INSERT INTO test2 SET number=30;

テーブル確認

MariaDB [lesson]> SELECT * FROM test2;
+----+------+--------+
| id | name | number |
+----+------+--------+
|  1 | 田中 |     10 |
|  2 | 田中 |      0 |
|  3 |      |     30 |
+----+------+--------+
3 rows in set (0.000 sec)

1. nameフィールドもnumberフィールドも値を指定してレコード挿入
   各フィールドに指定した値を登録
2. nameフィールドのみ値を指定してレコード挿入
   値を指定しなかったnumberフィールドに「0」が登録される
3. numberフィールドのみ値を指定してレコード挿入
   値を指定しなかったnameフィールドに「空文字」が登録される

「NOT NULL」指定あり、「DEFAULT NULL」指定なしフィールドは、値を指定せずにレコードを挿入すると「NULL」を登録することができないため、フィールドがもつデータ型の初期値が登録される。

そのため数字型のフィールドには「0」、文字列型のフィールドには「空文字」が登録される。

ケース３

「NOT NULL」を指定あり、「DEFAULT NULL」指定ありフィールドをもつテーブルを作成。

CREATE TABLE test3 ( 
  id INT AUTO_INCREMENT PRIMARY KEY,
  name CHAR(10) NOT NULL DEFAULT NULL,
  number INT NOT NULL DEFAULT NULL
);

値を指定せずにレコードを挿入すると矛盾するのでテーブル作成時にエラーになる。

MariaDB [lesson]> CREATE TABLE test3 (
    ->   id INT AUTO_INCREMENT PRIMARY KEY,
    ->   name CHAR(10) NOT NULL DEFAULT NULL,
    ->   number INT NOT NULL DEFAULT NULL
    -> );
ERROR 1067 (42000): Invalid default value for 'name'

「NOT NULL」を指定あり、「DEFAULT NULL」指定ありのフィールドを作成することはできない。

まとめ

上記の結果から、DESCで表示されるテーブル構成「Default」の「NULL」は初期値が指定されていないという意味の「NULL（何も指定していない）」であり、「Null（NOT NULL）」の「Yes」「No」で登録される値が変化する。

MariaDB [lesson]> DESC test2;
+--------+----------+------+-----+---------+----------------+
| Field  | Type     | Null | Key | Default | Extra          |
+--------+----------+------+-----+---------+----------------+
| id     | int(11)  | NO   | PRI | NULL    | auto_increment |
| name   | char(10) | NO   |     | NULL    |                |
| number | int(11)  | NO   |     | NULL    |                |
+--------+----------+------+-----+---------+----------------+
3 rows in set (0.014 sec)

使用したコード

#ケース１
CREATE TABLE test1 ( 
  id INT AUTO_INCREMENT PRIMARY KEY,
  name CHAR(10),
  number INT
);

DESC test1;

INSERT INTO test1 SET name='田中', number=10;
INSERT INTO test1 SET name='田中';
INSERT INTO test1 SET number=30;

SELECT * FROM test1;

#ケース２
CREATE TABLE test2 ( 
  id INT AUTO_INCREMENT PRIMARY KEY,
  name CHAR(10) NOT NULL,
  number INT NOT NULL
);

DESC test2;

INSERT INTO test2 SET name='田中', number=10;
INSERT INTO test2 SET name='田中';
INSERT INTO test2 SET number=30;

SELECT * FROM test2;

#ケース３
CREATE TABLE test3 ( 
  id INT AUTO_INCREMENT PRIMARY KEY,
  name CHAR(10) DEFAULT NULL,
  number INT DEFAULT NULL
);

DESC test3;

INSERT INTO test3 SET name='田中', number=10;
INSERT INTO test3 SET name='田中';
INSERT INTO test3 SET number=30;

SELECT * FROM test3;