php MySQL的非空列中的空字符串？

Empty string in not-null column in MySQL?

提问by davr

I used to use the standard mysql_connect(), mysql_query(), etc statements for doing MySQL stuff from PHP. Lately I've been switching over to using the wonderful MDB2 class. Along with it, I'm using prepared statements, so I don't have to worry about escaping my input and SQL injection attacks.

我曾经使用标准的 mysql_connect()、mysql_query() 等语句从 PHP 中处理 MySQL 内容。最近，我转而使用出色的 MDB2 类。与此同时，我使用了准备好的语句，所以我不必担心逃避我的输入和 SQL 注入攻击。

However, there's one problem I'm running into. I have a table with a few VARCHAR columns, that are specified as not-null (that is, do not allow NULL values). Using the old MySQL PHP commands, I could do things like this without any problem:

但是，我遇到了一个问题。我有一个包含几个 VARCHAR 列的表，这些列被指定为非空（即，不允许 NULL 值）。使用旧的 MySQL PHP 命令，我可以毫无问题地做这样的事情：

INSERT INTO mytable SET somevarchar = '';

Now, however, if I have a query like:

但是，现在，如果我有以下查询：

INSERT INTO mytable SET somevarchar = ?;

And then in PHP I have:

然后在 PHP 中我有：

$value = "";
$prepared = $db->prepare($query, array('text'));
$result = $prepared->execute($value);

This will throw the error "null value violates not-null constraint"

这将抛出错误“ null value violates not-null constraint”

As a temporary workaround, I check if $valueis empty, and change it to " "(a single space), but that's a horrible hack and might cause other issues.

作为临时解决方法，我检查是否$value为空，并将其更改为" "（单个空格），但这是一个可怕的黑客行为，可能会导致其他问题。

How am I supposed to insert empty strings with prepared statements, without it trying to instead insert a NULL?

我应该如何插入带有准备好的语句的空字符串，而不是尝试插入 NULL？

EDIT:It's too big of a project to go through my entire codebase, find everywhere that uses an empty string "" and change it to use NULL instead. What I need to know is why standard MySQL queries treat "" and NULL as two separate things (as I think is correct), but prepared statements converts "" into NULL.

编辑：项目太大了，无法遍历我的整个代码库，找到使用空字符串 "" 的所有地方并将其更改为使用 NULL。我需要知道的是为什么标准 MySQL 查询将 "" 和 NULL 视为两个独立的东西（我认为是正确的），但准备好的语句将 "" 转换为 NULL。

Note that "" and NULL are notthe same thing. For Example, SELECT NULL = "";returns NULLinstead of 1as you'd expect.

请注意， "" 和 NULL不是一回事。例如，SELECT NULL = "";返回NULL而不是1您所期望的。

采纳答案by hark

This sounds like a problem with the MDB2 API fumbling PHP's duck typing semantics. Because the empty string in PHP is equivalent to NULL, MDB2 is probably mis-treating it as such. The ideal solution would be to find a workaround for it within it's API, but I'm not overly familiar with it.

这听起来像是 MDB2 API 摸索 PHP 的鸭子类型语义的问题。因为 PHP 中的空字符串等价于 NULL，所以 MDB2 可能会误将其视为空字符串。理想的解决方案是在它的 API 中找到解决方法，但我对它并不太熟悉。

One thing that you should consider, though, is that an empty string in SQL is not a NULL value. You can insert them into rows declared 'NOT NULL' just fine:

但是，您应该考虑的一件事是 SQL 中的空字符串不是 NULL 值。您可以将它们插入到声明为“NOT NULL”的行中：

mysql> CREATE TABLE tbl( row CHAR(128) NOT NULL );
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO tbl VALUES( 'not empty' ), ( '' );
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT row, row IS NULL FROM tbl;
+-----------+-------------+
| row       | row IS NULL |
+-----------+-------------+
| not empty |           0 | 
|           |           0 | 
+-----------+-------------+
2 rows in set (0.00 sec)

mysql> INSERT INTO tbl VALUES( NULL );
ERROR 1048 (23000): Column 'row' cannot be null

If you're unable to find (or implement) a workaround in the MDB2 API, one hackish solution (though slightly better than the one you're currently using) might be to define a user variablefor the empty string --

如果您无法在 MDB2 API 中找到（或实施）一种变通方法，一个骇人听闻的解决方案（尽管比您目前使用的略好）可能是为空字符串定义一个用户变量——

SET @EMPTY_STRING = "";
UPDATE tbl SET row=@EMPTY_STRING;

Finally, if you need to use the empty string in an INSERT statement but find yourself unable to, the default value for string types in MySQL is an empty string. So you could simply omit the column from INSERT statement and it would automatically get set to the empty string (provided the column has a NOT NULL constraint).

最后，如果您需要在 INSERT 语句中使用空字符串但发现自己无法使用，那么 MySQL 中字符串类型的默认值为空字符串。因此，您可以简单地从 INSERT 语句中省略该列，它会自动设置为空字符串（前提是该列具有 NOT NULL 约束）。

回答by davr

Thanks to some of the answers, I realized that the problem may be in the MDB2 API, and not in the PHP or MYSQL commands themselves. Sure enough, I found this in the MDB2 FAQ:

感谢一些答案，我意识到问题可能出在 MDB2 API 中，而不是出在 PHP 或 MYSQL 命令本身。果然，我在 MDB2 FAQ 中找到了这个：

• Why do empty strings end up as NULL in the database? Why do I get an NULL not allowed in NOT NULL text fields eventhough the default value is ""?
  • The problem is that for some RDBMS (most noteably Oracle) an empty string is NULL. Therefore MDB2 provides a portability option to enforce the same behaviour on all RDBMS.
  • Since all portability options are enabled by default you will have to disable the feature if you dont want to have this behaviour: $mdb2->setOption('portability', MDB2_PORTABILITY_ALL ^ MDB2_PORTABILITY_EMPTY_TO_NULL);

• 为什么空字符串在数据库中以 NULL 结尾？为什么我在 NOT NULL 文本字段中得到不允许的 NULL，即使默认值为“”？
  • 问题是对于某些 RDBMS（最值得注意的是 Oracle），空字符串是 NULL。因此，MDB2 提供了一个可移植性选项来在所有 RDBMS 上强制执行相同的行为。
  • 由于默认情况下启用所有可移植性选项，如果您不想有此行为，则必须禁用该功能： $mdb2->setOption('portability', MDB2_PORTABILITY_ALL ^ MDB2_PORTABILITY_EMPTY_TO_NULL);

Thanks to everyone who provided thoughtful answers.

感谢所有提供深思熟虑的答案的人。

回答by Anthony

I realize this question is pretty much answered and retired, but I found it while looking for answers to a similar situation and I can't resist throwing my hat in the ring.

我意识到这个问题几乎得到了回答并已退休，但我在寻找类似情况的答案时发现了它，我忍不住把我的帽子扔到了戒指上。

Without knowing what the NULL/"" column relates to, I can't know how the true significance of an empty string. Does empty string mean something unto itself (like, if I convinced a judge to let me change my name to simply nothing, I would be really irritated if my name showed up on my Driver's License as NULL. My name would be !

在不知道 NULL/"" 列与什么相关的情况下，我无法知道空字符串的真正意义。空字符串本身是否意味着什么（例如，如果我说服法官让我将我的名字改成什么都不做，如果我的名字在我的驾照上显示为 NULL，我会非常恼火。我的名字是 !

However, the empty string (or blank, or the nothingness that is SOMETHING, not simply the lack of anything (like NULL)) could also simply just mean "NOT NULL" or "Nothing, but still not Null". You could even go the other direction and suggest that the absence of the value NULL makes it even LESS something than Null, cuz at least Null has a name you can say aloud!

然而，空字符串（或空白，或者是某种东西的虚无，不仅仅是缺少任何东西（如 NULL））也可能仅仅意味着“NOT NULL”或“Nothing, but still not Null”。你甚至可以反其道而行之，并建议 NULL 值的缺失使它比 Null 更少，因为至少 Null 有一个你可以大声说出的名字！

My point is, that if the empty string is a direct representation of some data (like a name, or what I prefer be inserted between the numbers in my phone number, etc), then your options are either to argue until you're sore for the legitimate use of empty string or to use something that represents an empty string that isn't NULL (Like an ASCII control character or some unicode equivalent, a regex value of some kind, or, even worse, an arbitrary yet totally unused token, like: ?

我的观点是，如果空字符串是某些数据的直接表示（例如姓名，或者我更喜欢在电话号码中插入的数字等），那么您的选择要么是争论，直到您感到疼痛用于空字符串的合法使用或使用表示非 NULL 的空字符串的内容（例如 ASCII 控制字符或某些 unicode 等效字符、某种正则表达式值，或者更糟的是，任意但完全未使用的标记， 喜欢： ？

If the empty cell really just means NOT NULL, then you could think of some other way of expressing it. One silly and obvious way is the phrase "Not NULL". But I have a hunch that NULL means something like "Not part of this group at all" while the empty string means something like "this guy is cool, he just hasn't gotten his gang tattoos yet". In which case I would come up with a term/name/idea for this situation, like "default" or "rookie" or "Pending".

如果空单元格真的只是意味着 NOT NULL，那么您可以考虑其他一些表达方式。一种愚蠢而明显的方式是短语“Not NULL”。但我有一种预感，NULL 的意思是“根本不属于这个组”，而空字符串的意思是“这个人很酷，他只是还没有得到他的帮派纹身”。在这种情况下，我会为这种情况想出一个术语/名称/想法，例如“默认”或“新秀”或“待定”。

Now, if by some crazy chance you actually want empty string to represent that which is not even worthy of NULL, again, come up with a more significant symbol for that, such as "-1" or "SUPERNULL" or "UGLIES".

现在，如果有一些疯狂的机会，你真的想要空字符串来表示甚至不值得 NULL 的东西，再一次，想出一个更重要的符号，比如“-1”或“SUPERNULL”或“丑陋”。

In the Indian Caste System, the lowest Caste are Shudra: Farmers and Laborers. Beneath this caste are the Dalit: "The Untouchables". They are not considered a lower caste, because setting them as the lowest caste would be considered a contamination of the entire system.

在印度种姓制度中，最低的种姓是首陀罗：农民和劳工。在这个种姓之下是达利特人：“贱民”。他们不被视为低种姓，因为将他们设置为最低种姓将被视为对整个系统的污染。

So don't call me crazy for thinking empty strings may be WORSE than NULL!

所以不要因为我认为空字符串可能比 NULL 更糟糕而说我疯了！

'Til next time.

'直到下次。

回答by ahhon

I found the solution!

我找到了解决方案！

MDB2 converts empty strings to NULL because portability option MDB2_PORTABILITY_EMPTY_TO_NULL is on by default (thanks to Oracle which considers empty strings to be null).

MDB2 将空字符串转换为 NULL，因为默认情况下可移植性选项 MDB2_PORTABILITY_EMPTY_TO_NULL 处于启用状态（感谢 Oracle 将空字符串视为 null）。

Switch this options off when you connect to the database:

连接到数据库时关闭此选项：

$options = array(
    'portability' => MDB2_PORTABILITY_ALL ^ MDB2_PORTABILITY_EMPTY_TO_NULL
);
$res= & MDB2::connect("mysql://user:password@server/dbase", $options);

回答by Lisa Simpson

While 0 and empty strings are variables NULL is the absence of data. And trust me, it's a lot easier to write a query to

虽然 0 和空字符串是变量，但 NULL 是没有数据。相信我，写一个查询要容易得多

SELECT * from table where mything IS NULLthan to try to query for empty strings :/

SELECT * from table where mything IS NULL比尝试查询空字符串：/

回答by warren

Doesn't an empty set of quotes, ""do that?

不是空引号集，""这样做吗？

回答by Powerlord

I'm confused. It looks like you're using mysqli OO (from the tags and style), but the syntax is different than the manualon php.net, which says to do this instead:

我糊涂了。看起来您正在使用 mysqli OO（来自标签和样式），但语法与php.net 上的手册不同，它说要这样做：

$query = "INSERT INTO mytable SET somevarchar = ?";
$value = "";
$prepared = $db->prepare($query);
$prepared->bind_param("s", $value);
$result = $prepared->execute();