php MySQL，如何插入空日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17784390/
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
MySQL, how to insert null dates
提问by apkdsmith
I am having trouble inserting null values into date fields into a MySQL table.
我在将空值插入到 MySQL 表的日期字段中时遇到问题。
Here is the insert query:
这是插入查询：
$query = 'INSERT INTO table (column_s1, column_s2, column_d1, column_d2)
VALUES ("'.$string1.'", "'.$string2.'", '.$date1.', '.$date2.')';
Columns s1 and s2 take string values and d1 and d2 take dates. When I run this query with only the string fields, there is no problem.
列 s1 和 s2 采用字符串值，而 d1 和 d2 采用日期。当我仅使用字符串字段运行此查询时，没有问题。
The date values can be either set or null, so I have not included the quotation marks in the query, but have instead added them to the variable earlier on. This is the php code I am using to set the date values:
日期值可以是 set 或 null，所以我没有在查询中包含引号，而是将它们添加到之前的变量中。这是我用来设置日期值的 php 代码：
if (empty($date1)){
$date1 = NULL;
}
else{
$date1part = explode("/",$date1);
$date1 = '"'.$date1part[2].'/'.$date1part[1].'/'.$date1part[0].'"';
}
When the date values are all set, the record is inserted correctly. However, when either of the dates is null, nothing is inserted.
当日期值都设置好后，记录就被正确插入了。但是，当任一日期为空时，不会插入任何内容。
Why can't I just insert null values into MySQL like this?
为什么我不能像这样在 MySQL 中插入空值？
回答by Wh1T3h4Ck5
Try this:
尝试这个：
$query = "INSERT INTO table (column_s1, column_s2, column_d1, column_d2)
VALUES ('$string1', '$string2', " . ($date1==NULL ? "NULL" : "'$date1'") . ", " . ($date2==NULL ? "NULL" : "'$date2'") . ");";
so for example if you put this into query:
例如，如果您将其放入查询中：
$string1 = "s1";
$string2 = "s2";
$date1 = NULL;
$date2 = NULL;
result should be:
结果应该是：
INSERT INTO table (column_s1, column_s2, column_d1, column_d2) VALUES ('s1', 's2', NULL, NULL);
回答by Timo Huovinen
You should convert the null variable into a NULL string first Like this:
您应该先将空变量转换为空字符串，如下所示：
if(is_null($date1)){
$date1 = 'NULL';
}
If you are using a MySQL date column, you must also specify that it should hold null when creating it, like this:
如果您使用的是 MySQL 日期列，则还必须指定它在创建时应为 null，如下所示：
CREATE TABLE `table` (
id INT NOT NULL AUTO_INCREMENT,
date DATE NULL DEFAULT NULL,
PRIMARY KEY(id)
)
It is also very important that you perform the query with bound parameters, for example using pdo
使用绑定参数执行查询也非常重要，例如使用 pdo
- http://www.php.net/manual/en/pdo.construct.php
- http://php.net/manual/en/pdo.prepared-statements.php
- How do I insert NULL values using PDO?
- http://www.php.net/manual/en/pdo.construct.php
- http://php.net/manual/en/pdo.prepared-statements.php
- 如何使用 PDO 插入 NULL 值？
Something like this:
像这样的东西：
$query = 'INSERT INTO table (column_s1, column_s2, column_d1, column_d2)
VALUES (?, ?, ?, ?)';
$stmt = $db->prepare($query);
$stmt->execute(array($string1,$string2,$date1,$date2));
回答by innovative kundan
Backslash N is another way to express NULL in MySQL.
反斜杠 N 是另一种在 MySQL 中表示 NULL 的方法。
Try putting the value (backslash N): \Ninto one of the parameters like this:
尝试将值 (反斜杠 N):\N放入如下参数之一：
$data1 = "\N";
$sql="insert into tablename set column_s1='" . $data1 .
"', column_s2='" . data2 .
"', column_s3='" . $data3 . "'";
Reference: http://dev.mysql.com/doc/refman/5.1/en/load-data.html
回答by Luis Daniel Dorta
In Derby, If you want to insert values except the ones you have declared Null (column_d1, column_d2), sql:
在 Derby 中，如果要插入除已声明为 Null (column_d1, column_d2) 之外的值，sql：
INSERT INTO DB.table (column_s1, column_s2) VALUES ('s1', 's2');
回答by Grokking
Probably answer is unneeded at this moment, but I found solution exactly I have been searching. Use an Expressionto pass NULLlike this:
目前可能不需要答案，但我找到了我一直在寻找的解决方案。使用 an像这样Expression传递NULL：
['some_date_to_update' => new Expression('NULL')]
Hence, MySQL will understand what you want, and save (NULL)in DB instead of storing 0-dates. Hope this will help somebody.
因此，MySQL 会理解你想要什么，并保存(NULL)在数据库中而不是存储 0 日期。希望这会帮助某人。
回答by Optimaz ID
if NULL no work just pass date as "0000-00-00"
如果 NULL 没有工作，只需将日期传递为“0000-00-00”
$chequeDate = "0000-00-00";
回答by Siva
In Mysql DATEdata type Default NULLmeans
在 MysqlDATE数据类型中默认NULL是指
Some version set as 0000-00-00
某些版本设置为 0000-00-00
Some version set as 1970-01-01
某些版本设置为 1970-01-01
