怎样使得 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 条回复 • 2015-12-16 16:33:08 +08:00

mahone3297

2015-12-16 11:34:42 +08:00

replace

eoo

2015-12-16 11:35:10 +08:00 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

2015-12-16 11:37:05 +08:00

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

groot

2015-12-16 11:38:11 +08:00

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

laoyuan

2015-12-16 12:06:14 +08:00

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

laoyuan

2015-12-16 12:08:18 +08:00

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

daniellu

2015-12-16 12:16:57 +08:00

@groot +1 正解

incompatible

2015-12-16 12:24:13 +08:00

@eoo 这代码不怕 sql 注入？

wawehi

2015-12-16 12:25:20 +08:00

INSERT INTO xxx ON DUPLICATE KEY UPDATE xxx

xujif

2015-12-16 12:36:53 +08:00

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

lyragosa

2015-12-16 12:53:42 +08:00

on duplicate key 正解

defunct9

2015-12-16 13:21:07 +08:00

唉：
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

2015-12-16 14:08:35 +08:00

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