php 如何在PostgreSQL中将空转换为空？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14035883/
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
How to convert empty to null in PostgreSQL?
提问by D T
I have some columns type int, but value is empty. So I want to convert empty to null when I insert to database. I use code:
我有一些类型为 int 的列，但值为空。所以我想在插入数据库时将空转换为空。我使用代码：
function toDB($string) {
if ($string == '' || $string == "''") {
return 'null';
} else {
return "'$string'";
}
}
//age,month,year is type integer.
$name="Veo ve";
$age='10';
$month='';
$year='';
$query="Insert Into tr_view(name,age,month,year) values ({toDB($name)},{toDB($age)},{toDB($month)},{toDB($year)})
$db->setQuery($query);
$result= $db->query();
But it show error:
但它显示错误：
pg_query(): Query failed: ERROR: syntax error at or near "{" LINE 153: {toDB(10)}, ^ in...
Why?
为什么？
采纳答案by Charles
While Erwin's answer about NULLIFis awesome, it doesn't address your syntax error.
尽管欧文的回答NULLIF是真棒，它没有解决您的语法错误。
Let's take a look at the query:
让我们看一下查询：
$query="Insert Into tr_view(name,age,month,year) values ({toDB($name)},{toDB($age)},{toDB($month)},{toDB($year)})
Earlier you defined a function called toDB. Unfortunately the syntax you are using here is nothow to call a function from within a double-quoted string, so the curlies and toDB(bits are still being passed through. There are two alternatives:
之前您定义了一个名为toDB. 不幸的是，您在这里使用的语法不是如何从双引号字符串中调用函数，因此toDB(仍在传递卷曲和位。有两种选择：
Concatenation using
.:$query='insert Into tr_view(name,age,month,year) values (' . toDB($name) . ',' . toDB($age) . ',' . toDB($month) . ',' . toDB($year) . ')')You can interpolate a callable variableinto a double-quoted string thusly:
$fn = 'toDB'; $query="Insert Into tr_view(name,age,month,year) values ({$fn($name)},{$fn($age)},{$fn($month)},{$fn($year)})";
连接使用
.：$query='insert Into tr_view(name,age,month,year) values (' . toDB($name) . ',' . toDB($age) . ',' . toDB($month) . ',' . toDB($year) . ')')您可以这样将可调用变量插入双引号字符串中：
$fn = 'toDB'; $query="Insert Into tr_view(name,age,month,year) values ({$fn($name)},{$fn($age)},{$fn($month)},{$fn($year)})";
The first is clear and sane, the second is vague to the unfamiliar and downright insane.
第一个清晰而理智，第二个对于陌生和彻头彻尾的疯子来说是模糊的。
However, you still should not be assembling input like this. You still may be vulnerable to SQL injection attacks. You should be using prepared statements with parameterized placeholders.
但是，您仍然不应该像这样组装输入。您仍然可能容易受到SQL 注入攻击。您应该使用带有参数化占位符的准备好的语句。
The Postgres extension uses pg_preparefor this. They have the distinct advantage of, say, allowing you to pass a PHP nullinstead of having to worry about all of that null-detection and quoting.
Postgres 扩展pg_prepare用于此目的。它们具有明显的优势，例如，允许您传递 PHPnull而不必担心所有这些空值检测和引用。
If you insist on keeping toDBas-is, consider adding one of the pg_escape_functions, like pg_escape_string, to the thing that builds quoted strings.
如果您坚持保持toDB原样，请考虑将其中一个pg_escape_函数（例如pg_escape_string）添加到构建带引号的字符串的事物中。
回答by Erwin Brandstetter
There is the NULLIF()function:
有这个NULLIF()功能：
SELECT NULLIF(var, '');
If varholds the value in $2, you get NULLinstead.
In the example I replace the empty string: ''with NULL.
如果var将价值保持在 2 美元，你就会得到NULL。
在这个例子中我替换空字符串：''有NULL。
There is no empty string for the type integer. Just not possible. Since NULLIF()cannot switch the data type, you have to sanitize your input in PHP.
有该类型的整数无空字符串。只是不可能。由于NULLIF()无法切换数据类型，您必须在 PHP 中清理您的输入。
If you did not define a column default, you can also just omit the columnin the INSERTcommand and it will be filled with NULL(which is the default DEFAULT).
如果您没有定义列默认值，您也可以在命令中省略该列，INSERT它将被填充NULL（这是默认值DEFAULT）。
Check if the parameter is empty in PHP and don't include the column in the INSERTcommand if it is.
检查PHP中的参数是否为空，如果是，请不要在INSERT命令中包含该列。
Or use the PHP literal NULLinstead like Quassnoi demonstrates here.
或者像 Quassnoi在这里演示的那样使用PHP 文字 NULL。
The rest only makes sense for string types
其余的只对字符串类型有意义
To make absolutely sure, nobody can enter an empty string add a CHECKconstraint to the table:
为了绝对确定，没有人可以输入空字符串CHECK向表添加约束：
ALTER TABLE tr_view
ADD CONSTRAINT tr_view_age_not_empty CHECK (age <> '');
To avoid exceptionscaused by this, you could add a trigger that fixes input automatically:
为了避免由此引起的异常，您可以添加一个自动修复输入的触发器：
CREATE OR REPLACE FUNCTION trg_tr_view_avoid_empty()
RETURNS trigger AS
$func$
BEGIN
IF NEW.age = '' THEN
NEW.age := NULL;
END IF;
IF NEW.month = '' THEN
NEW.month := NULL;
END IF;
RETURN NEW;
END
$func$ LANGUAGE plpgsql
CREATE TRIGGER tr_view_avoid_empty
BEFORE INSERT OR UPDATE ON tr_view
FOR EACH ROW
WHEN (NEW.age = '' OR NEW.month = '')
EXECUTE PROCEDURE trg_tr_view_avoid_empty();
