为什么ENUM(“0”,“1”)在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 field. 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 ?

3个回答




为什么一个系统会给我一个错误而另一个系统会保存该字段? 是否有配置差异?</ p>
</ blockquote>

可能。 请参阅下文。</ p>


为什么当我保存枚举“0”或“1”的字段时,它会保存“”而不是NULL?</ p>
</ blockquote>

根据 MySQL ENUM文档 :</ p>


在某些情况下,该值也可能是空字符串('')或NULL:</ p>



  • 如果在ENUM中插入无效值(即允许值列表中不存在的字符串),则将插入空字符串作为特殊错误值</ strong >。 此字符串可以与“普通”空字符串区分开来,因为此字符串的数值为0. ... </ p>

    如果启用了严格的SQL模式,则尝试 插入无效的ENUM值会导致错误。</ strong> </ p> </ li>
    </ ul>
    </ blockquote>

    (强调添加。)</ p> \ n </ div>

展开原文

原文

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的答案似乎很好地解释了为什么你的代码在2种环境中表现不同。</ p>

问题出在其他地方。 如果要在查询中将值设置为NULL,则使用完全为NULL,但是您使用的是mysql_real_escape_string(),其结果始终是字符串:</ p>

  $ php -r  'var_dump(mysql_real_escape_string(null));'
string(0)“”
</ code> </ pre>

您应该以不同方式处理此问题。 例如:</ p>

  $ value = null 
$ escaped_value = is_null($ value)? “NULL”:mysql_real_escape_string($ value);
var_dump($ escaped_value);
// NULL
</ code> </ pre>

某些数据库层,如PDO,处理这个就好了 为你。</ p>
</ div>

展开原文

原文

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.

duanchuang1935
duanchuang1935 Torromeo它正在准备一份声明。 我已经完成了在枚举类型字段中存储NULL的任务。 感谢分享这个“像PDO这样的一些数据库层,处理这个问题对你来说很好。” 声明。 我从这个声明中得到了答案。 你救了我的一天。
2 年多之前 回复
duanque19820925
duanque19820925 +1因为这正是关于转义空值的问题的答案,我会在解决“环境”差异后要求并完全避免错误生成的查询
接近 10 年之前 回复



如果您希望它是 NULL </ code>,为什么不首先执行此操作:< / p>

  UPDATE MyTable SET values ...,offset = NULL WHERE id = 10; 
</ code> </ pre>
</ div>

展开原文

原文

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;

立即提问
相关内容推荐