PHP/MySQL 插入空值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5329542/
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
PHP/MySQL Insert null values
提问by MattP
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:
我正在努力处理一些 PHP/MySQL 代码。我正在从 1 个表中读取，更改一些字段然后写入另一个表，如果插入并且数组值之一为 null，当我希望它在数据库中插入 null（该字段允许使用 null 值）时不会发生任何事情。它看起来有点像这样：
$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']});
}
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
并非每一行都有一个空值，在我的查询中有更多的字段和 2 列可能为也可能不为空
回答by Andrew Moore
This is one example where using prepared statementsreally saves you some trouble.
这是使用准备好的语句确实为您节省一些麻烦的一个示例。
In MySQL, in order to insert a null value, you must specify it at INSERTtime or leave the field out which requires additional branching:
在 MySQL 中，为了插入空值，您必须在INSERTtime 中指定它或将需要额外分支的字段省略：
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');
Prepared statements automatically do that for you. They know the difference between string(0) ""and nulland write your query appropriately:
准备好的语句会自动为您完成。他们知道 和 之间的区别，string(0) ""并null适当地编写您的查询：
$stmt = $mysqli->prepare("INSERT INTO table2 (f1, f2) VALUES (?, ?)");
$stmt->bind_param('ss', $field1, $field2);
$field1 = "String Value";
$field2 = null;
$stmt->execute();
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 mysqlextension. Use mysqliand it's prepared statementsinstead. You'll save yourself a world of pain.
它会为您转义您的字段，确保您不会忘记绑定参数。没有理由继续mysql延期。改用mysqli它是准备好的语句。你会拯救自己一个痛苦的世界。
回答by mooreds
I think you need quotes around your {$row['null_field']}, so '{$row['null_field']}'
我认为你需要引用你的{$row['null_field']}, 所以'{$row['null_field']}'
If you don't have the quotes, you'll occasionally end up with an insert statement that looks like this: insert into table2 (f1, f2) values ('val1',)which is a syntax error.
如果您没有引号，您有时会得到如下所示的插入语句：insert into table2 (f1, f2) values ('val1',)这是一个语法错误。
If that is a numeric field, you will have to do some testing above it, and if there is no value in null_field, explicitly set it to null..
如果这是一个数字字段，则必须在其上方进行一些测试，如果 null_field 中没有值，则将其显式设置为 null..
回答by Micah Fagre
For fields where NULLis acceptable, you could use var_export($var, true)to output the string, integer, or NULLliteral. Note that you would not surround the output with quotes because they will be automatically added or omitted.
对于那些领域NULL是可以接受的，您可以使用var_export($var, true)输出string，integer或NULL文字。请注意，您不会用引号将输出括起来，因为它们会被自动添加或省略。
For example:
例如：
mysql_query("insert into table2 (f1, f2) values ('{$row['string_field']}', ".var_export($row['null_field'], true).")");
