MySQL,如何插入空日期 [英] MySQL, how to insert null dates
问题描述
这是插入查询:
$ query ='INSERT INTO表(column_s1,column_s2,column_d1,column_d2)
VALUES('。$ string1。,'。$ string2。','。$ date1。','$ date2。 ;列bs和s2取字符串值,d1和d2取日期,
当我仅使用字符串字段运行此查询时,没有问题。
日期值可以设置或为空,因此我没有将引号括在该查询,而是将它们稍后添加到变量中。这是我用来设置日期值的PHP代码:
if(empty($ date1)){
$ date1 = NULL;
}
else {
$ date1part = explode(/,$ date1);
$ date1 =''$ date1part [2]。'/'。$ date1part [1]。'/'。$ date1part [0]
}
当日期值全部设置时,记录被正确插入。但是,当任一日期为空时,不会插入任何内容。
为什么我不能像这样将空值插入MySQL?
您应该将null变量首先转换为NULL字符串
像这样:
if(is_null($ date1)){
$ date1 ='NULL';
}
如果您使用MySQL日期列,还必须指定它应该在创建它时保持为null,如下所示:
CREATE TABLE`table`(
id INT NOT NULL AUTO_INCREMENT,
date DATE NULL DEFAULT NULL,
PRIMARY KEY(id)
)
使用绑定参数执行查询也很重要,例如使用pdo
- http://www.php.net/manual/en/pdo.construct.php
- http://php.net/manual/en/ pdo.prepared-statements.php
- 如何使用PDO插入NULL值?
$ query ='INSERT IN TO表(column_s1,column_s2,column_d1,column_d2)
VALUES(?,?,?,?)';
$ stmt = $ db-> prepare($ query);
$ stmt-> execute(array($ string1,$ string2,$ date1,$ date2));
I am having trouble inserting null values into date fields into a MySQL table.
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.
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:
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?
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:
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
- 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?
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));
这篇关于MySQL,如何插入空日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!