在重复密钥更新时插入到 TBL 中 - 插入一个空白行 - INSERT INTO tbl ON DUUPLICATE KEY UPDATE - a blank row gets inserted?

INSERT INTO tbl ON DUUPLICATE KEY UPDATE - a blank row gets inserted?

这个MySQLi语句有什么问题？

我想在重复键更新时插入

图式

'UID', 'char(17)', 'NO', 'PRI', NULL, ''
'stationID', 'int(11)', 'YES', '', NULL, ''
'temperature', 'float', 'YES', '', NULL, ''
'UV', 'float', 'YES', '', NULL, ''
'temperature_feels', 'float', 'YES', '', NULL, ''
'humidity', 'float', 'YES', '', NULL, ''
'weather_type', 'int(11)', 'YES', '', '-1', ''
'precipitation', 'float', 'YES', '', NULL, ''
'update_station_id', 'tinyint(4)', 'YES', '', '1', ''
'update_due', 'timestamp', 'YES', '', NULL, ''
'weather_status', 'varchar(128)', 'YES', '', NULL, ''

//法典

    $sql = "INSERT INTO weather_data (uv, weather_status, weather_type, temperature, temperature_feels, humidity, precipitation, UID)
            VALUES (uv, weather_status, weather_type, temperature, temperature_feels, humidity, precipitation, UID)
            ON DUPLICATE KEY UPDATE uv = ?, weather_status = ?, weather_type = ?, temperature = ?, temperature_feels = ?, humidity = ?, precipitation = ?, UID = ?";
    $stmt = $dbh->prepare($sql);
    if (!$stmt) {
        throw new 'Exception($dbh->error);
    }
    $stmt->bind_param('ssssssss', $uv, $weather_status, $weather_type, $temperature, $temperature_feels, $humidity, $precipitation, $UID);
    $stmt->execute();
    $stmt->close();

似乎每次都插入一个空行？

看起来有点疯狂，但所有字段的初始值都是字符串（各种）而不是占位符。您需要为每个绑定

$sql = "INSERT INTO weather_data (uv, weather_status, weather_type, temperature, temperature_feels, humidity, precipitation, UID)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        ON DUPLICATE KEY UPDATE uv = ?, weather_status = ?, weather_type = ?, temperature = ?, temperature_feels = ?, humidity = ?, precipitation = ?, UID = ?";
    $stmt->bind_param('ssssssssssssssss', $uv, $weather_status, $weather_type, $temperature,
                           $temperature_feels, $humidity, $precipitation, $UID, 
                           $uv, $weather_status, $weather_type, $temperature, 
                           $temperature_feels, $humidity, $precipitation, $UID);

将查询更新为：

ON DUPLICATE KEY UPDATE 
  uv = VALUES(?),
  weather_status = VALUES(?),
  weather_type = VALUES(?),
  temperature = VALUES(?),
  temperature_feels = VALUES(?),
  humidity = VALUES(?),
  precipitation = VALUES(?),
  UID = VALUES(?)

另一个问题是 param go 与每个? ，因为在插入后您继续使用 ? 您还需要添加这些参数。您可以使用命名参数的PDO来避免这种情况。

$stmt->bind_param('dsidddddidsidddddi', /* first set dsidddddi */ 
  $uv, 
  $weather_status,
  $weather_type,
  $temperature,
  $temperature_feels,
  $humidity,
  $precipitation,
  $UID,
  $uv, // because ? counts further.
  $weather_status,
  $weather_type,
  $temperature,
  $temperature_feels,
  $humidity,
  $precipitation,
  $UID,
);
$stmt->execute();