怎样使得 MySQL 当记录不存在时插入，当记录存在时更新？

目的：
每天更新昨日预测结果和发布次日预测。
假设今天是 2015-12-15 ，记录应该如下：
ID date code prediction fact
1 2015-12-15 000001 1 1
2 2015-12-15 000002 1 -1
3 2015-12-16 000001 1 NULL
4 2015-12-16 000002 1 NULL

2015-12-16 这天的工作就是：
1. 根据今天的 fact 结果，更新本日的记录。
2. 发布 2015-12-17 这天的记录。

目前的做法：删除日期大于等于 2015-12-16 的所有记录，然后插入新的记录。

遇到的问题：
1. 2015-12-16 的 prediction 是 2015-12-15 这日做出的，如果 12-16 这一日的预测模型变了， prediction 也会跟着变。会让人觉得我是在修改昨日预测，这样不好。
2. 有时候我要多次运行程序，先删除再添加会让 ID 显得很奇怪。

希望的做法(这两条 SQL 我都不是太会写，我用的 sqlalchemy)：
1. 直接插入，如果表里有当前(date,code)，则更新这条记录。 OR
2. 直接更新，如果表里没有当前（ date,code)则进行插入。

或者有更好的方法？

谢谢

24 replies • 2015-12-16 16:33:08 +08:00

mahone3297

Dec 16, 2015

replace

eoo

Dec 16, 2015 via Android

$SQL="SELECT `phone` FROM `17wo` WHERE `phone`='{$phone}'";

$mysqli_r=$mysqli->query($SQL);

//如果用户存在则只更新 COOKIE

if($mysqli_r->fetch_array()){

$SQL="UPDATE `17wo` SET `phone`='{$phone}',`password`='{$password}' WHERE `phone`='{$phone}'";

if($mysqli->query($SQL)){

echo $phone.' 帐号更新成功！';

}else{

echo $phone.' 帐号更新失败！';

}

}else{//否则添加用户

$SQL="INSERT INTO `17wo` (`phone`,`password`,`state`,`zctime`) VALUES ('{$phone}','{$password}',0,'{$zctime}')";

if($mysqli->query($SQL)){

echo $phone.' 成功加入自动签到！';

}else{

echo $phone.' 加入自动签到失败！';

}

}

snailsir

Dec 16, 2015

我想答案你自己已经说出来了

bonfirenosedoyou

Dec 16, 2015

1.replace
2.ignore
3.on duplicate key update

laoyuan

Dec 16, 2015

你需要一个 unique 字段，值是 md5(date, code) ，然后先 insert ignore 再 update
这样子稳也不麻烦，相信你的服务器没紧张到要把两条查询压缩成一条半的程度。
replace 不行， replace 是先删再增， ID 会变

laoyuan

Dec 16, 2015

看来最佳方案就是添加 md5(date, code) unique 字段加 @groot 提到的 on duplicate key update

daniellu

Dec 16, 2015

@groot +1 正解

incompatible

Dec 16, 2015

@eoo 这代码不怕 sql 注入？

wawehi

Dec 16, 2015

INSERT INTO xxx ON DUPLICATE KEY UPDATE xxx

xujif

Dec 16, 2015

@laoyuan 还需要 md5 ？直接 unique[date,code]不行吗

lyragosa

Dec 16, 2015

on duplicate key 正解

defunct9

Dec 16, 2015

唉：
statsd 中用的一段，-678 是这个表中永远都不会有的一个值。
表结构：
======================================
CREATE TABLE `gauges_statistics` (
`timestamp` BIGINT NOT NULL ,
`name` VARCHAR(255) NOT NULL ,
`value` INT(11) NOT NULL ,
PRIMARY KEY (`timestamp`,`name`) )
======================================
语句：
insert into `gauges_statistics` select "+time_stamp+", '"+gaugeName+"', "+gaugeValue+" from dual where (select if(max(value),max(value),-678) from `gauges_statistics` where name = '"+gaugeName+"') = -678 OR (select value from `gauges_statistics` where name = '"+gaugeName+"' order by timestamp desc limit 0,1) <> "+gaugeValue+";"

laoyuan

Dec 16, 2015

@xujif 嗯这样更省事，我习惯 md5 了。。其实我不知道 unique key 可以多个字段蛤蛤