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

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

$query = "INSERT INTO data (notes, id, filesUploaded, lat, lng, intLat, intLng)
VALUES ('$notes', '$id', TRIM('$imageUploaded'), '$lat', '$long',
'$intLat', '$intLng')";
mysql_query($query);
223410 次浏览
小开
最佳答案

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

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

So, in your code, check if $intLat, $intLng are empty, if they are, use NULL instead of '$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)";
小开

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

But you can just pass the word NULL without quotes.

小开

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

小开

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);
小开

you can do it for example with

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

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

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:

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.

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).

小开

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

$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:

$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.

小开

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

小开

This works just fine for me:

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

(first '' increments id field)

小开

2022 | PHP 7.3 | MySQL 5.7

Accepted answer by Rocket Hazmat gives me "NULL" as a string. So I change it to:

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

提交答案