PHP/MySQL插入空值 [英] PHP/MySQL Insert null values

问题描述

我正在努力处理一些PHP/MySQL代码.我正在从1个表中读取，更改一些字段，然后写入另一张表，如果我希望在数据库中插入null(该字段允许使用null值)，则如果插入且其中一个数组值是null则什么也不会发生.看起来像这样:

I'm struggling with some PHP/MySQL code. I am reading from 1 table, changing some fields then writing to another table, nothing happens if inserting and one of the array values is null when I would like it to insert null in the database (null values are allowed for the field). It looks a bit like this:

$results = mysql_query("select * from mytable");
while ($row = mysql_fetch_assoc($results) {
    mysql_query("insert into table2 (f1, f2) values ('{$row['string_field']}', {$row['null_field']});
}

并非每一行都具有null值，并且在我的查询中还有更多字段和2列可能为空或不为空

Not every row has a null value and in my query there are more fields and 2 columns which may or may not be null

推荐答案

这是一个使用预先准备好的语句确实为您省去麻烦的示例.

This is one example where using prepared statements really saves you some trouble.

在MySQL中，要插入一个空值，必须在INSERT时间指定它，或将字段保留在需要附加分支的位置:

In MySQL, in order to insert a null value, you must specify it at INSERT time or leave the field out which requires additional branching:

INSERT INTO table2 (f1, f2)
  VALUES ('String Value', NULL);

但是，如果要在该字段中插入一个值，则现在必须分支代码以添加单引号:

However, if you want to insert a value in that field, you must now branch your code to add the single quotes:

INSERT INTO table2 (f1, f2)
  VALUES ('String Value', 'String Value');

准备好的语句会自动为您完成此操作.他们知道string(0) ""和null之间的区别，并适当地编写查询:

Prepared statements automatically do that for you. They know the difference between string(0) "" and null and write your query appropriately:

$stmt = $mysqli->prepare("INSERT INTO table2 (f1, f2) VALUES (?, ?)");
$stmt->bind_param('ss', $field1, $field2);

$field1 = "String Value";
$field2 = null;

$stmt->execute();

它为您转义了您的字段，请确保您不要忘记绑定参数.没有理由继续使用mysql扩展名.使用mysqli，它是准备好的语句.您将为自己节省痛苦的世界.

It escapes your fields for you, makes sure that you don't forget to bind a parameter. There is no reason to stay with the mysql extension. Use mysqli and it's prepared statements instead. You'll save yourself a world of pain.

这篇关于PHP/MySQL插入空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！