Why an ENUM("0", "1") is saved as an empty string in Mysql?

I have a MYSQL table with an ENUM field named "offset" and some other columns. The field is defined as:

ENUM(0,1), can be NULL, predefined value NULL

Now I have two server. A production server and a development server and the same PHP script used to create and to update the database.

First step: the application create the record witout passing the "offset" in the CREATE query.

Second step: the application ask to the user some data (not the "offset" value), read the row inserted in step one and make an array, update some field (not the "offset" field), create a query in an automated fashion and save the row again with the updated values.

The automated query builder simple read all the field passed in an array and create the UPDATE string.

In both systems I obtain this array:

$values = array(... 'offset' => null);

and convert it in this same query passing the values in the mysql_real_escape_string:

UPDATE MyTable SET values..., `offset` = '' WHERE id = '10';

Now there is the problem. When i launch the query in the production system, the row is saved, in the development system I got an error and the db says that the offset data is wrong without saving the row.

From phpmyadmin when I create the row with the first step, it shows NULL in the offset fiel开发者_JAVA百科d. After saving the field in the system which give no errors, it show me an empty string.

Both system are using MySQL 5 but the production uses 5.0.51 on Linux and development use 5.0.37 on Windows.

The questions:

Why one system give me an error an the other one save the field ? Is a configuration difference ?

Why when I save the field which is an enum "0" or "1" it saves "" and not NULL ?

---

Why one system give me an error an the other one save the field ? Is a configuration difference ?

Probably. See below.

Why when I save the field which is an enum "0" or "1" it saves "" and not NULL ?

According to the MySQL ENUM documentation:

The value may also be the empty string ('') or NULL under certain circumstances:

• If you insert an invalid value into an ENUM (that is, a string not present in the list of permitted values), the empty string is inserted instead as a special error value. This string can be distinguished from a "normal" empty string by the fact that this string has the numeric value 0. ...

  If strict SQL mode is enabled, attempts to insert invalid ENUM values result in an error.

(Emphasis added.)

---

strager's answer seems like a good explanation on why your code behaves differently on the 2 environments.

The problem lies elsewhere though. If you want to set a value to NULL in the query you shound use exactly NULL, but you are using mysql_real_escape_string() which result is always a string:

$ php -r 'var_dump(mysql_real_escape_string(null));'
string(0) ""

You should handle this differently. E.g:

$value = null
$escaped_value = is_null($value) ? "NULL" : mysql_real_escape_string($value);
var_dump($escaped_value);
// NULL

Some DB layers, like PDO, handle this just fine for you.

---

If you want it to be NULL, why don't you do this in the first place:

UPDATE MyTable SET values..., `offset` = NULL WHERE id = 10;