php 如何在mysql日期类型字段中插入一个空值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1137655/
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 insert an empty value in mysql date type field?
提问by TigerTiger
How to insert a NULL or empty value in a mysql date type field (NULL = yes).
如何在 mysql 日期类型字段中插入 NULL 或空值（NULL = yes）。
If I try to insert an empty value it inserts 0000-00-00 but I want to keep it empty or NULL.
如果我尝试插入一个空值，它会插入 0000-00-00 但我想将其保留为空或 NULL。
Thanks for help.
感谢帮助。
UPDATE
更新
Please see that I have set default value as NULL
请参阅我已将默认值设置为 NULL
`payment_due_on` date DEFAULT NULL,
Ok OKies
好的OKies
I have got it working now
我现在已经开始工作了
function get_mysqlDate($date, $delimiter='/') {
if(check_empty($date)) {
return 'NULL';
}
list($d, $m, $y) = explode($delimiter, $date);
//$datetime = strtotime($date);
return "'".date('Y-m-d', mktime(0,0,0,$m, $d, $y))."'";
}
"... SET mydate = ".get_mysqldate($_POST['mydate'])." ...."
Cheers
干杯
回答by chaos
If that's happening, it's because the column has been defined with NOT NULLor with a default '0000-00-00'. You need to change the column definition to allow NULLs and have no default.
如果发生这种情况，那是因为已使用NOT NULL或定义了该列default '0000-00-00'。您需要更改列定义以允许NULLs 并且没有默认值。
Your insert should be specifying NULL, as well, not '', if that's what you mean by "empty value".
您的插入也应该指定 NULL，而不是 NULL，''如果这就是您所说的“空值”。
回答by Vicer
phew.. was struggling with this null thing a bit.
呼.. 有点为这个空的东西苦苦挣扎。
I had a php script that copies, formats and stores data from one database A to database B. there was a date field in database A with NULL values. but when I copied it it became 0000-00-00 in my database B.
我有一个 php 脚本，可以将数据从一个数据库 A 复制、格式化和存储到数据库 B。数据库 A 中有一个日期字段为 NULL 值。但是当我复制它时，它在我的数据库 B 中变成了 0000-00-00。
finally figure out it was because I was putting single quotes around NULL like ('NULL').
终于弄清楚这是因为我在 NULL 周围加上了单引号，例如 ('NULL')。
$started_date = mysql_result($databaseA['result'],$i,"start_date");
if (empty($published_at_date)){
$insert_sql .= "NULL, ";
}
else
{
$insert_sql .= " '" . mysql_real_escape_string($started_date) ."', ";
}
This fixed it.
这修复了它。
回答by Michal Gorecki
Just omit the field when your doing your insert.
插入时只需省略该字段。
Say your table is like this:
说你的表是这样的：
CREATE TABLE mytable(
`name` VARCHAR(30) NOT NULL,
`payment_due_on` date DEFAULT NULL);
If you want to insert something without the date, use:
如果要插入不带日期的内容，请使用：
INSERT INTO mytable (name) VALUES ('name here');
