mysql - 在重复键更新错误时插入到 tbl

Question

我有以下查询，如果它不存在，我想将其修改为 INSERT into tbl，如果存在，我想将其修改为 UPDATE。

$sql = "UPDATE tbldata temperature = ?, temperature_feels = ?, humidity = ?, precipitation = ? WHERE id = ?";

我正在尝试以下操作，但到目前为止没有任何乐趣 - 我的以下查询有什么问题？

$sql = "INSERT INTO tbldata (temperature, temperature_feels, humidity, precipitation) ON DUPLICATE KEY UPDATE id = ?, temperature = ?, temperature_feels = ?, humidity = ?, precipitation = ?";

-- 更新--这不起作用...我做错了什么？

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

-- 更新--我的模式

'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, ''

Answer 1

您忘记在insert query 中写入values

$sql = "INSERT INTO tbldata (temperature, temperature_feels, humidity,precipitation)  
VALUES (?,?,?,?) 
ON DUPLICATE KEY UPDATE 
id = ?, 
temperature = ?,
temperature_feels = ?, 
humidity = ?, 
precipitation = ?";

更多信息链接 http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html