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']});
}
并非每一行都有一个空值,在我的查询中有更多的字段和 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屋!