使用 PDO 插入 NULL 而不是空字符串 [英] Insert NULL instead of empty string with PDO

问题描述

我有一个表，它有一些可以为空的字段，当用户在 HTML 表单字段中不输入任何内容时，我想在该字段中插入 NULL，而不是一个空字符串(这很重要，因为我稍后会在这些表上进行一些 SELECT)使用 WHERE x IS NOT NULL 等条件.

I have a table which has some nullable fields and when the user enters nothing into the HTML form field, I want to insert NULL into that field, not an empty string (this is important as some of my SELECTs on these tables later use conditions such as WHERE x IS NOT NULL).

但是，这个版本的 bindParam 代码在可空字段中插入了一个空字符串而不是 NULL.

However, this version of bindParam code inserts an empty string into the nullable field instead of NULL.

$stmt2->bindParam(':title', $title, PDO::PARAM_STR);

我读了很多书，发现这是在字段中插入 null 的答案:

I've been reading quite a bit and figured out that this is the answer to insert null into the field instead:

$stmt2->bindParam(':title', $title, PDO::PARAM_NULL);

但这意味着我需要预先检查所有传递给可空字段的参数以确定它们是否为空，如果是，则传递 PDO::PARAM_NULL 而不是 PDO::PARAM_STR.我当然可以这样做，但希望可能有一个设置，它会告诉 PDO 如果遇到空字符串，请插入 null.

But this means I need to pre-check all parameters that are being passed to nullable fields to determine if they are empty, and if so pass the PDO::PARAM_NULL instead of PDO::PARAM_STR. I can of course do this, but was hoping there might be a setting which would just tell PDO if it encounters an empty string, insert null instead.

我偶然发现了这个

$this->dbh->setAttribute(PDO::ATTR_ORACLE_NULLS, PDO::NULL_EMPTY_STRING);

但它没有影响，随着进一步研究，我认为这只会影响出路的记录，而不是入路的记录.

But it has no effect and with further research I'm thinking this only affects record on the way out, not on the way in.

除了预先检查变量之外还有其他选择吗?

Any options other than pre-checking the variables?

推荐答案

如果你想要 null，只需插入 null 而不是空字符串.

If you want null, just insert null instead of empty string.

$stmt2->bindParam(':title', $title === '' ? null : $title, PDO::PARAM_STR);

这篇关于使用 PDO 插入 NULL 而不是空字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！