MySQL - Replace Statements

Consider you have the following table of data:

+----+--------------+------------+-------+
| id | registration | make       | model |
+----+--------------+------------+-------+
|  1 | LSO7VSC      | Volkswagen | Golf  |
+----+--------------+------------+-------+

Where id is an auto incrementing integer, and registration is a unique key.

What would you expect the following query to result in if MySQL is not set to strict mode.

REPLACE INTO `vehicle` (`registration`, `model`)
VALUES('LS07VSC', 'Polo');

You might expect it to find the row with LS07VSC in the registration and simply change the model from Golf to Polo. However the end result is actually as follows:

+----+--------------+------+-------+
| id | registration | make | model |
+----+--------------+------+-------+
|  8 | LS07VSC      |      | Polo  |
+----+--------------+------+-------+

This is because a replace statement always fully replaces an entire row and since we did not specify a value for make, it applied the implicit default value of an empty string.

If you had MySQL operating in strict mode then the replace statement would have thrown an error because in this table, make is defined as NOT NULL and has no default value, so the replace statement would have required a value be specified for make.