CODEWITHSUNDEEP
mysqlnulldefault-value
Nate Pinchot
Nate Pinchot

Reputation: 3318

Is it possible to disable automatic empty value insertion for fields without default value in MySQL?

Is there any configuration setting to stop MySQL from inserting an empty value for fields which don't have a default value and weren't specified in the insert statement? I'd much prefer it rejected an invalid insert statement.

Example - I have the following table in MySQL:

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `val1` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `val2` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

If you run this insert statement:

INSERT INTO test (val2) VALUES('a');

You get this response:

Query OK, 1 row affected, 1 warning (0.00 sec)

Response from show warnings:

+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1364 | Field 'val1' doesn't have a default value |
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)

Response from SELECT * FROM test:

+----+------+------+
| id | val1 | val2 |
+----+------+------+
|  1 |      | asdf |
+----+------+------+
1 row in set (0.00 sec)

Upvotes: 1

Views: 985

Answers (1)

Marc B
Marc B

Reputation: 360772

You can enable 'strict' mode, which'd cause MySQL to fail any inserts that don't provide values for default-less fields.

Upvotes: 5

Related Questions