Learning site for website creation

MySQLで「NOT NULL」指定フィールドに値を指定しなかった時、NULLが登録されないケース

  • 投稿日:2020年09月24日

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

ケース1

「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」が登録される。

ケース2

「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」、文字列型のフィールドには「空文字」が登録される。

ケース3

「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)

使用したコード

#ケース1
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;

#ケース2
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;

#ケース3
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;