php - 从 PHP 向 MYSQL 引入 NULL 字段

Question

我在尝试将 NULL 引入数据库字段时遇到问题。我的行由 24 列( double 值)组成，无论我尝试什么，它总是显示 0。

有什么想法吗？我读过插入不带引号的 null 应该可以工作(mysql 的保留字)，但它却插入了我 0。

这是我的代码的简短版本:

安卓:

for(int i = lastID; i < data2.get(SensorType.ACCELEROMETER_X).size(); i++){

    List<NameValuePair> nameValuePairs = new ArrayList<NameValuePair>(data2.keySet().size()+1);
    Set<SensorType> signals = data2.keySet();
    nameValuePairs.add(new BasicNameValuePair("TableName", tableNameSignals));
    for(SensorType s: signals){

        if(data2.get(s).get(i).isNaN()){
            //HERE IS WHERE I WANT THE FIELD TO APPEAR NULL
            nameValuePairs.add(new BasicNameValuePair(s.getAbbreviature(), "null"));
        }
        else{
            nameValuePairs.add(new BasicNameValuePair(s.getAbbreviature(), data2.get(s).get(i).toString()));

        }
    }

    httpPost2.setEntity(new UrlEncodedFormEntity(nameValuePairs));
    HttpResponse httpResponse2 = httpClient.execute(httpPost2);

PHP:

<?php

//Array for Json response
$response = array();

//Include DB class
require_once __DIR__ . '/db_connect_v2.php';

require_once __DIR__ . '/db_config_v2.php';

$mysqli = new mysqli(DB_SERVER, DB_USER, DB_PASSWORD, DB_DATABASE);

if ($mysqli->connect_errno)

    die('Connection Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);

$tableName = $_POST['TableName'];

$sql1 = "CREATE TABLE IF NOT EXISTS " . "$tableName" . "(ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, AccX double, AccY double, AccZ double, MagX double, MagY double, MagZ double, GyrX double, GyrY double,
    GyrZ double, Light double, Pressure double, Proximity double, GravX double, GravY double, GravZ double, LinAccX double, LinAccY double, LinAccZ double, 
    RotVecX double, RotVecY double, RotVecZ double, Humidity double, Temperature double, TimeStamp double);";

$result1 = $mysqli->query($sql1);

if($result1){

    $accX = $_POST['AccX']; $accY = $_POST['AccY']; $accZ = $_POST['AccZ']; $magX = $_POST['MagX'];
    $magY = $_POST['MagY']; $magZ = $_POST['MagZ']; $gyrX = $_POST['GyrX']; $gyrY = $_POST['GyrY'];
    $gyrZ = $_POST['GyrZ']; $light = $_POST['Light']; $pressure = $_POST['Pressure']; $proximity = $_POST['Proximity'];
    $gravX = $_POST['GravX']; $gravY = $_POST['GravY']; $gravZ = $_POST['GravZ']; $linAccX = $_POST['LinAccX'];
    $linAccY = $_POST['LinAccY']; $linAccZ = $_POST['LinAccZ']; $rotVecX = $_POST['RotVecX'];
    $rotVecY = $_POST['RotVecY'];  $rotVecZ = $_POST['RotVecZ'];  $humidity = $_POST['Humidity'];  $temperature = $_POST['Temperature'];
    $timeStamp = $_POST['TimeStamp'];

    $sql2 = "INSERT INTO " . "$tableName" . "(AccX, AccY, AccZ, MagX, MagY, MagZ, GyrX, GyrY, GyrZ, Light, Pressure, Proximity, GravX, GravY, GravZ, 
    LinAccX, LinAccY, LinAccZ, RotVecX, RotVecY, RotVecZ, Humidity, Temperature, TimeStamp) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";

    $stmt = $mysqli->prepare($sql2);
    $stmt->bind_param('dddddddddddddddddddddddd', $accX, $accY, $accZ, $magX, $magY, $magZ, $gyrX, $gyrY, $gyrZ, $light, $pressure,
    $gravX, $gravY, $gravZ, $proximity, $linAccX, $linAccY, $linAccZ, $rotVecX, $rotVecY, $rotVecZ, $humidity, $temperature, $timeStamp);

    $result2 = $stmt->execute();

    $stmt->close();
    $mysqli->close();

    if($result2){

        $response['Success'] = 1;
        $response['Message'] = "Row was uploaded succesfully!";
        echo json_encode($response);
    }

    else{

        $response['Success'] = 0;
        $response['Message'] = "Oops! Couldnt upload data! (Mobile Signals)";
        $response["PhpError"] = mysql_error();
        echo json_encode($response);
    }
}

else{

    $response['Success']=0;
    $response['Message']="Oops! Couldnt create table! (Mobile Signals)";
    $response["PhpError"] = mysql_error();
    echo json_encode($response);
}

?>

解决方案:

问题是在 BasicNameValuePair 中插入 null 时，该字段会作为空字符串发送到 php 脚本。如果在 php 脚本中我这样做:

if($_POST['AccX'] != "") $accX = $_POST['AccX']; else $accX = NULL;

一切正常。

Answer 1

试试这个。

for(SensorType s: signals){

    nameValuePairs.add(new BasicNameValuePair(s.getAbbreviature(), 
        data2.get(s).get(i).isNaN()?null:data2.get(s).get(i).toString();
    ));
}

不要将 "null" 作为字符串发送，而是将其作为 null 作为空发送。 BaseNameValuePair 也接受 null 值。

• BaseNameValuePair | Android Developers