插入零的空字符串，而不是null

My insert statement looks like this:

我的insert语句如下所示:

INSERT INTO foo (bar) VALUES ('');

The bar field was created like so:

bar字段的创建方式如下:

bar INT(11)
    COLLATION: (NULL)
    NULL: YES
    DEFAULT: (NULL)

MySQL version: 5.1.

MySQL版本:5.1。

Shouldn’t an empty string insert a NULL? I’m not sure why I’m seeing a zero (0) being stored in the table.

不应该空字符串插入NULL吗?我不确定为什么我看到表中存储了零(0)。

4 个解决方案

#1

MySQL by default attempts to coerce invalid values for a column to the correct type. Here, the empty string '' is of type string, which is neither an integer nor NULL. I suggest taking the following steps:

默认情况下,MySQL会尝试将列的无效值强制转换为正确的类型。这里,空字符串''的类型为string,既不是整数也不是NULL。我建议采取以下步骤:

Change the query to the following: INSERT INTO foo (bar) VALUES (NULL);

将查询更改为以下内容:INSERT INTO foo(bar)VALUES(NULL);

Enable strict mode in MySQL. This prevents as many unexpected type and value conversions from occurring. You will see more error messages when you try to do something MySQL doesn't expect, which helps you to spot problems more quickly.

在MySQL中启用严格模式。这可以防止发生许多意外的类型和值转换。当您尝试执行MySQL不期望的操作时,您将看到更多错误消息,这有助于您更快地发现问题。

#2

You're not inserting NULL into the table; you're inserting an empty string (which apparently maps to zero as an int). Remember that NULL is a distinct value in SQL; NULL != ''. By specifying any value (other than NULL), you're not inserting NULL. The default only gets used if you don't specify a value; in your example, you specified a string value to an integer column.

你没有在表中插入NULL;你正在插入一个空字符串(显然将其映射为零作为int)。请记住,NULL是SQL中的一个独特值; NULL!=''。通过指定任何值(除NULL之外),您不会插入NULL。如果您未指定值,则仅使用默认值;在您的示例中,您将字符串值指定为整数列。

#3

The way to do this is to not fill the field at all. only fill the ones that actually need to have a value.

这样做的方法是根本不填充场地。只填写实际需要有价值的那些。

#4

Why should it be a NULL? You're providing a value that has an integer representation: empty strings convert to INT 0.

为什么它应该是NULL?您提供的是具有整数表示的值:空字符串转换为INT 0。

Only if you didn't provide any value would the default take over.

只有当您没有提供任何值时,默认才会接管。

#1