使用 PHP 将 NULL 插入 MySQL 的最佳实践
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14600650/
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
Best practice to insert NULL to MySQL using PHP
提问by user1725661
function save($gmt, $name, $address, $phone, $remark)
{
$query= "INSERT INTO `user` (`gmt`, `name`, `address`, `phone`, `remark`) VALUES ('$gmt', '$name', '$address', '$phone', '$remark')";
mysql_query($query);
}
Here address, phone and remark can be NULL value. I need it so that when I pass a null parameter into my function it stores an empty value in the database (NULL). I need to know what the best solution for doing this is.
这里的地址、电话和备注可以是 NULL 值。我需要它，以便当我将空参数传递给我的函数时，它会在数据库中存储一个空值 (NULL)。我需要知道这样做的最佳解决方案是什么。
Thanks.
谢谢。
回答by
This is PHP solution, but you have to use mysqli because mysql deprecated, please read more about mysqli. Also, you must consider SQL injection
这是 PHP 解决方案，但您必须使用 mysqli，因为 mysql 已弃用，请阅读有关mysqli 的更多信息。此外，您必须考虑SQL 注入
function save($gmt, $name, $address, $phone, $remark)
{
if(empty($phone)){
$phone = 'NULL';
}else{
$phone = "'".$phone."'";
}
if(empty($remark)){
$remark = 'NULL';
}else{
$remark = "'".$remark."'";
}
$query= "INSERT INTO `user` (`gmt`, `name`, `address`, `phone`, `remark`) VALUES ('$gmt', '$name', '$address', $phone, $remark)";
mysql_query($query);
}
//tests
save("a", "b", "c", "", "")."<br>";
save("a", "b", "c", "d", "")."<br>";
save("a", "b", "c", "d", "e")."<br>";
/*
INSERT INTO `user` (`gmt`, `name`, `address`, `phone`, `remark`) VALUES ('a', 'b', 'c', NULL, NULL)
INSERT INTO `user` (`gmt`, `name`, `address`, `phone`, `remark`) VALUES ('a', 'b', 'c', 'd', NULL)
INSERT INTO `user` (`gmt`, `name`, `address`, `phone`, `remark`) VALUES ('a', 'b', 'c', 'd', 'e')
*/
?>
回答by Gerald Schneider
Try switching to prepared statements (which as a bonus is less prone to SQL injections).
尝试切换到准备好的语句（作为奖励，SQL 注入的可能性较小）。
function save($gmt, $name, $address, $phone, $remark)
{
if(!isset($phone) || empty($phone)) { $phone = null; }
if(!isset($remark) || empty($remark) { $remark = null; }
$db = new PDO(...);
$stmt = $db->prepare("INSERT INTO `user` (`gmt`, `name`, `address`, `phone`, `remark`) VALUES (:gmt, :name, :address, :phone, :remark)");
$stmt->bindValue("gmt", $gmt, PDO::PARAM_STR);
$stmt->bindValue("name", $name, PDO::PARAM_STR);
$stmt->bindValue("address", $address, PDO::PARAM_STR);
$stmt->bindValue("phone", $phone, PDO::PARAM_STR);
$stmt->bindValue("remark", $remark, PDO::PARAM_STR);
$stmt->execute();
}
This will handle the nullvalues correctly in MySQL
这将null正确处理MySQL 中的值
回答by Karoly Horvath
PHP doesn't print NULL - it is just an empty string. So in your example you will try to insert '', which in SQL again is an empty string.
PHP 不打印 NULL - 它只是一个空字符串。因此，在您的示例中，您将尝试 insert ''，它在 SQL 中又是一个空字符串。
You have to use NULL(without quotes).
您必须使用NULL（不带引号）。
And the best practice to achieve that is to use an ORM or a PHP framework with a database abstraction layer which does this for you.
实现这一目标的最佳实践是使用 ORM 或 PHP 框架以及为您执行此操作的数据库抽象层。
回答by SagarPPanchal
Using ternary operator, you can also use this
使用三元运算符，您还可以使用此
$add = ($address == '' ? NULL : $address);
$phn = ($phone == '' ? NULL : $phone);
$rmk = ($remark == '' ? NULL : $remark);
