MySQL 和 PHP - 插入 NULL 而不是空字符串

MySQL and PHP - insert NULL rather than empty string

提问by user547794

I have a MySQL statement that inserts some variables into the database. I recently added 2 fields which are optional ($intLat, $intLng). Right now, if these values are not entered I pass along an empty string as a value. How do I pass an explicit NULL value to MySQL (if empty)?

我有一个 MySQL 语句，可以将一些变量插入到数据库中。我最近添加了 2 个可选字段（$intLat、$intLng）。现在，如果没有输入这些值，我会传递一个空字符串作为值。如何将显式 NULL 值传递给 MySQL（如果为空）？

$query = "INSERT INTO data (notes, id, filesUploaded, lat, lng, intLat, intLng)
          VALUES ('$notes', '$id', TRIM('$imageUploaded'), '$lat', '$long', 
                  '$intLat', '$intLng')";
mysql_query($query);

回答by Rocket Hazmat

To pass a NULL to MySQL, you do just that.

要将 NULL 传递给 MySQL，您只需这样做。

INSERT INTO table (field,field2) VALUES (NULL,3)

So, in your code, check if $intLat, $intLngare empty, if they are, use NULLinstead of '$intLat'or '$intLng'.

因此，在您的代码中，检查if $intLat, $intLngare empty，如果是，则使用NULL代替'$intLat'or '$intLng'。

$intLat = !empty($intLat) ? "'$intLat'" : "NULL";
$intLng = !empty($intLng) ? "'$intLng'" : "NULL";

$query = "INSERT INTO data (notes, id, filesUploaded, lat, lng, intLat, intLng)
          VALUES ('$notes', '$id', TRIM('$imageUploaded'), '$lat', '$long', 
                  $intLat, $intLng)";

回答by dkretz

If you don't pass values, you'll get nulls for defaults.

如果您不传递值，您将获得默认值的空值。

But you can just pass the word NULL without quotes.

但是你可以不带引号传递 NULL 这个词。

回答by Jesenko Sokoljak

This works just fine for me:

这对我来说很好用：

INSERT INTO table VALUES ('', NULLIF('$date',''))

(first ''increments id field)

（首先''增加 id 字段）

回答by Shahid Sarwar

All you have to do is: $variable =NULL;// and pass it in the insert query. This will store the value as NULL in mysql db

您所要做的就是：$variable =NULL;// 并将其传递到插入查询中。这将在 mysql db 中将该值存储为 NULL

回答by radhoo

Normally, you add regular values to mySQL, from PHP like this:

通常，您将常规值从 PHP 添加到 mySQL，如下所示：

function addValues($val1, $val2) {
    db_open(); // just some code ot open the DB 
    $query = "INSERT INTO uradmonitor (db_value1, db_value2) VALUES ('$val1', '$val2')";
    $result = mysql_query($query);
    db_close(); // just some code to close the DB
}

When your values are empty/null ($val1=="" or $val1==NULL), and you want NULL to be added to SQL and not 0 or empty string, to the following:

当您的值为空/空（$val1=="" 或 $val1==NULL），并且您希望将 NULL 添加到 SQL 而不是 0 或空字符串时，以下内容：

function addValues($val1, $val2) {
    db_open(); // just some code ot open the DB 
    $query = "INSERT INTO uradmonitor (db_value1, db_value2) VALUES (".
        (($val1=='')?"NULL":("'".$val1."'")) . ", ".
        (($val2=='')?"NULL":("'".$val2."'")) . 
        ")";
    $result = mysql_query($query);
    db_close(); // just some code to close the DB
}

Note that null must be added as "NULL" and not as "'NULL'" . The non-null values must be added as "'".$val1."'", etc.

请注意，必须将 null 添加为 "NULL" 而不是 "'NULL'" 。非空值必须添加为 "'".$val1."'" 等。

Hope this helps, I just had to use this for some hardware data loggers, some of them collecting temperature and radiation, others only radiation. For those without the temperature sensor I needed NULL and not 0, for obvious reasons ( 0 is an accepted temperature value also).

希望这会有所帮助，我只需要将它用于一些硬件数据记录器，其中一些收集温度和辐射，另一些只收集辐射。对于那些没有温度传感器的人，我需要 NULL 而不是 0，原因很明显（0 也是可接受的温度值）。

回答by sikas

your query can go as follows:

您的查询可以如下：

$query = "INSERT INTO data (notes, id, filesUploaded, lat, lng, intLat, intLng)
      VALUES ('$notes', '$id', TRIM('$imageUploaded'), '$lat', '$lng', '" . ($lat == '')?NULL:$lat . "', '" . ($long == '')?NULL:$long . "')";
mysql_query($query);

回答by bernte

you can do it for example with

你可以这样做，例如

UPDATE `table` SET `date`='', `newdate`=NULL WHERE id='$id'

回答by profitphp

Check the variables before building the query, if they are empty, change them to the string NULL

在构建查询之前检查变量，如果它们为空，则将它们更改为字符串 NULL

回答by bobv

For some reason, radhoo's solution wouldn't work for me. When I used the following expression:

出于某种原因，radhoo 的解决方案对我不起作用。当我使用以下表达式时：

$query = "INSERT INTO uradmonitor (db_value1, db_value2) VALUES (".
    (($val1=='')?"NULL":("'".$val1."'")) . ", ".
    (($val2=='')?"NULL":("'".$val2."'")) . 
    ")";

'null' (with quotes) was inserted instead of null without quotes, making it a string instead of an integer. So I finally tried:

插入了 'null'（带引号）而不是不带引号的 null，使其成为字符串而不是整数。所以我终于尝试了：

$query = "INSERT INTO uradmonitor (db_value1, db_value2) VALUES (".
    (($val1=='')? :("'".$val1."'")) . ", ".
    (($val2=='')? :("'".$val2."'")) . 
    ")";

The blank resulted in the correct null (unquoted) being inserted into the query.

空白导致将正确的空值（未加引号）插入到查询中。