php 如何在 MySQL 的日期时间字段中存储 NULL 值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1691117/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to store NULL values in datetime fields in MySQL?
提问by rhodesjason
I have a "bill_date" field that I want to be blank (NULL) until it's been billed, at which point the date will be entered.
我有一个“bill_date”字段，我想在它被计费之前为空（NULL），此时将输入日期。
I see that MySQL does not like NULL values in datetime fields. Do any of you have a simple way to handle this, or am I forced to use the min date as a "NULL equivalent" and then check for that date?
我看到 MySQL 不喜欢日期时间字段中的 NULL 值。你们中有没有人有一个简单的方法来处理这个问题，或者我是否被迫使用最小日期作为“NULL等价物”然后检查该日期？
Thanks.
谢谢。
EDITED TO ADD:
编辑添加：
Ok I do see that MySQL will accept the NULL value, but it won't accept it as a database update if I'm updating the record using PHP.
好的，我确实看到 MySQL 将接受 NULL 值，但如果我使用 PHP 更新记录，它不会接受它作为数据库更新。
The variable name is $bill_datebut it won't leave the variable as NULL if I update a record without sending a value to $bill_date-- I get this error:
变量名称是，$bill_date但如果我更新记录而不向其发送值，它不会将变量保留为 NULL $bill_date- 我收到此错误：
Database query failed: Incorrect datetime value: '' for column 'bill_date' at row 1
I assume I need to actually send the word NULL, or leave it out of the update query altogether, to avoid this error? Am I right? Thanks!!!
我假设我需要实际发送 NULL 一词，或者将其完全排除在更新查询之外，以避免出现此错误？我对吗？谢谢！！！
回答by Asaph
MySQL doesallow NULLvalues for datetimefields. I just tested it:
MySQL确实允许字段的NULL值datetime。我刚刚测试了一下：
mysql> create table datetimetest (testcolumn datetime null default null);
Query OK, 0 rows affected (0.10 sec)
mysql> insert into datetimetest (testcolumn) values (null);
Query OK, 1 row affected (0.00 sec)
mysql> select * from datetimetest;
+------------+
| testcolumn |
+------------+
| NULL |
+------------+
1 row in set (0.00 sec)
I'm using this version:
我正在使用这个版本：
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.45 |
+-----------+
1 row in set (0.03 sec)
EDIT #1: I see in your edit that the error message you are getting in PHP indicates that you are passing an empty string (i.e. ''), not null. An empty string is different than nulland is not a valid datetimevalue which is why you are getting that error message. You must pass the special sql keyword nullif that's what you mean. Also, don't put any quotes around the word null. See my insertstatement above for an example of how to insert null.
编辑 #1：我在您的编辑中看到您在 PHP 中收到的错误消息表明您正在传递一个空字符串（即''），而不是null. 空字符串不同于null并且不是有效值datetime，这就是您收到该错误消息的原因。null如果这就是您的意思，您必须传递特殊的 sql 关键字。另外，不要在单词周围加上任何引号null。有关insert如何插入null.
EDIT #2: Are you using PDO? If so, when you bind your null param, make sure to use the [PDO::PARAM_NULL][1]type when binding a null. See the answer to this stackoverflow questionon how to properly insert nullusing PDO.
编辑#2：你在使用 PDO 吗？如果是这样，当您绑定 null 参数时，请确保[PDO::PARAM_NULL][1]在绑定 a 时使用该类型null。请参阅有关如何使用 PDO正确插入的stackoverflow 问题的答案null。
回答by roselan
This is a a sensible point.
这是一个明智的观点。
A null date is not a zero date. They may look the same, but they ain't. In mysql, a null date value is null. A zero date value is an empty string ('') and'0000-00-00 00:00:00'
空日期不是零日期。他们可能看起来一样，但他们不是。在 mysql 中，空日期值为空。零日期值是空字符串 ('')和'0000-00-00 00:00:00'
On a null date "... where mydate = ''" will fail.
On an empty/zero date "... where mydate is null" will fail.
在空日期 "... where mydate = ''" 将失败。
在空/零日期“...其中 mydate 为空”将失败。
But now let's get funky. In mysql dates, empty/zero date are strictly the same.
但现在让我们变得时髦。在 mysql 日期中，空/零日期严格相同。
by example
举例
select if(myDate is null, 'null', myDate) as mydate from myTable where myDate = ''; select if(myDate is null, 'null', myDate) as mydate from myTable where myDate = '0000-00-00 00:00:00'
will BOTH output: '0000-00-00 00:00:00'. if you update myDate with '' or '0000-00-00 00:00:00', both selects will still work the same.
两者都会输出：'0000-00-00 00:00:00'。如果您使用 '' 或 '0000-00-00 00:00:00' 更新 myDate，则两个选择仍将相同。
In php, the mysql null dates type will be respected with the standard mysql connector, and be real nulls ($var === null, is_null($var)). Empty dates will always be represented as '0000-00-00 00:00:00'.
在 php 中，标准 mysql 连接器会遵守 mysql 空日期类型，并且是真正的空值 ($var === null, is_null($var))。空日期将始终表示为“0000-00-00 00:00:00”。
I strongly advise to use only null dates, OR only empty dates if you can. (some systems will use "virual" zero dates which are valid Gregorian dates, like 1970-01-01 (linux) or 0001-01-01 (oracle).
我强烈建议只使用空日期，或者如果可以的话，只使用空日期。（某些系统将使用“虚拟”零日期，它们是有效的公历日期，例如 1970-01-01 (linux) 或 0001-01-01 (oracle)。
empty dates are easier in php/mysql. You don't have the "where field is null" to handle. However, you have to "manually" transform the '0000-00-00 00:00:00' date in '' to display empty fields. (to store or search you don't have special case to handle for zero dates, which is nice).
php/mysql 中的空日期更容易。你没有“where field is null”来处理。但是，您必须“手动”转换 '' 中的 '0000-00-00 00:00:00' 日期以显示空字段。（存储或搜索您没有特殊情况来处理零日期，这很好）。
Null dates need better care. you have to be careful when you insert or update to NOT add quotes around null, else a zero date will be inserted instead of null, which causes your standard data havoc. In search forms, you will need to handle cases like "and mydate is not null", and so on.
空日期需要更好的照顾。插入或更新时必须小心，不要在空值周围添加引号，否则将插入零日期而不是空值，这会导致标准数据破坏。在搜索表单中，您需要处理诸如“并且 mydate 不为空”等情况。
Null dates are usually more work. but they much MUCH MUCHfaster than zero dates for queries.
空日期通常是更多的工作。但他们很多很多MUCH快于零个日期查询。
回答by abzarak
I had this problem on windows.
我在 Windows 上遇到了这个问题。
This is the solution:
这是解决方案：
To pass '' for NULL you should disable STRICT_MODE(which is enabled by default on Windows installations)
要将 '' 传递给 NULL，您应该禁用STRICT_MODE（在 Windows 安装中默认启用）
BTW It's funny to pass '' for NULL. I don't know why they let this kind of behavior.
顺便说一句，将 '' 传递给 NULL 很有趣。我不知道为什么他们会放任这种行为。
回答by Andomar
It depends on how you declare your table. NULL would not be allowed in:
这取决于你如何声明你的表。在以下情况下不允许使用 NULL：
create table MyTable (col1 datetime NOT NULL);
But it would be allowed in:
但它会被允许在：
create table MyTable (col1 datetime NULL);
The second is the default, so someone must've actively decided that the column should not be nullable.
第二个是默认值，所以一定有人主动决定该列不应该为空。
回答by Rob
I just tested in MySQL v5.0.6 and the datetime column accepted null without issue.
我刚刚在 MySQL v5.0.6 中进行了测试，并且 datetime 列没有问题地接受了 null。
回答by postmarkis
I just discovered that MySQL will take null provided the default for the field is null and I write specific if statements and leave off the quotes. This works for update as well.
我刚刚发现如果该字段的默认值是 null 并且我编写特定的 if 语句并去掉引号，则 MySQL 将采用 null。这也适用于更新。
if(empty($odate) AND empty($ddate))
{
$query2="UPDATE items SET odate=null, ddate=null, istatus='$istatus' WHERE id='$id' ";
};
回答by Joe M
For what it is worth: I was experiencing a similar issue trying to update a MySQL table via Perl. The update would fail when an empty string value (translated from a null value from a read from another platform) was passed to the date column ('dtcol' in the code sample below). I was finally successful getting the data updated by using an IF statement embedded in my update statement:
值得一提的是：我在尝试通过 Perl 更新 MySQL 表时遇到了类似的问题。当空字符串值（从从另一个平台读取的空值转换而来）传递到日期列（下面代码示例中的“dtcol”）时，更新将失败。通过使用嵌入在我的更新语句中的 IF 语句，我终于成功地更新了数据：
...
my $stmnt='update tbl set colA=?,dtcol=if(?="",null,?) where colC=?';
my $status=$dbh->do($stmt,undef,$iref[1],$iref[2],$iref[2],$ref[0]);
...
回答by Lukey
Specifically relating to the error you're getting, you can't do something like this in PHP for a nullable field in MySQL:
特别是关于你得到的错误，你不能在 PHP 中为 MySQL 中的可为空字段做这样的事情：
$sql = 'INSERT INTO table (col1, col2) VALUES(' . $col1 . ', ' . null . ')';
Because null in PHP will equate to an empty string which is notthe same as a NULL value in MysQL. Instead you want to do this:
因为空在PHP将等同于一个空字符串，它是不一样的MySQL中的NULL值。相反，您想要这样做：
$sql = 'INSERT INTO table (col1, col2) VALUES(' . $col1 . ', ' . (is_null($col2) ? 'NULL' : $col2). ')';
Of course you don't have to use is_null but I figure that it demonstrates the point a little better. Probably safer to use empty() or something like that. And if $col2 happens to be a string which you would enclose in double quotes in the query, don't forget notto include those around the 'NULL' string, otherwise it wont work.
当然，您不必使用 is_null 但我认为它更好地说明了这一点。使用 empty() 或类似的东西可能更安全。如果 $col2 恰好是一个字符串，你会在查询中用双引号括起来，不要忘记不要在 'NULL' 字符串周围包含那些，否则它将不起作用。
Hope that helps!
希望有帮助！
