V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
billgreen1
V2EX  ›  MySQL

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

  •  
  •   billgreen1 · 2015-12-16 11:30:44 +08:00 · 5871 次点击
    这是一个创建于 3247 天前的主题,其中的信息可能已经有所发展或是发生改变。


    ID,date,code,prediction, fact
    ID 自动增长, primary key
    当 date 和 code 确定,能确定这一条记录。

    目的:
    每天更新昨日预测结果和发布次日预测。
    假设今天是 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
        1
    mahone3297  
       2015-12-16 11:34:42 +08:00   ❤️ 1
    replace
    eoo
        2
    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
        3
    snailsir  
       2015-12-16 11:37:05 +08:00
    我想答案你自己已经说出来了
    groot
        4
    groot  
       2015-12-16 11:38:11 +08:00   ❤️ 1
    1.replace
    2.ignore
    3.on duplicate key update
    laoyuan
        5
    laoyuan  
       2015-12-16 12:06:14 +08:00
    你需要一个 unique 字段,值是 md5(date, code) ,然后先 insert ignore 再 update
    这样子稳也不麻烦,相信你的服务器没紧张到要把两条查询压缩成一条半的程度。
    replace 不行, replace 是先删再增, ID 会变
    laoyuan
        6
    laoyuan  
       2015-12-16 12:08:18 +08:00
    看来最佳方案就是添加 md5(date, code) unique 字段加 @groot 提到的 on duplicate key update
    daniellu
        7
    daniellu  
       2015-12-16 12:16:57 +08:00
    @groot +1 正解
    incompatible
        8
    incompatible  
       2015-12-16 12:24:13 +08:00
    @eoo 这代码不怕 sql 注入?
    wawehi
        9
    wawehi  
       2015-12-16 12:25:20 +08:00
    INSERT INTO xxx ON DUPLICATE KEY UPDATE xxx
    xujif
        10
    xujif  
       2015-12-16 12:36:53 +08:00
    @laoyuan 还需要 md5 ?直接 unique[date,code]不行吗
    lyragosa
        11
    lyragosa  
       2015-12-16 12:53:42 +08:00
    on duplicate key 正解
    defunct9
        12
    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
        13
    laoyuan  
       2015-12-16 14:08:35 +08:00
    @xujif 嗯这样更省事,我习惯 md5 了。。其实我不知道 unique key 可以多个字段蛤蛤
    ynztyl10
        14
    ynztyl10  
       2015-12-16 14:11:28 +08:00
    可以先 google 的
    billgreen1
        15
    billgreen1  
    OP
       2015-12-16 14:21:10 +08:00
    多谢以上各位。
    最终方案是:
    alter table mytable
    add constraint date_code_unique
    Unique(date,code);

    然后就可以使用 insert ... on duplicate key update
    eoo
        16
    eoo  
       2015-12-16 15:17:46 +08:00 via Android
    @incompatible 自己写的联通一起沃自动签到程序 代码前端,因为考虑到 一起沃转流量是需要 短信验证码的 而且一起沃的密码是独立性的 没什么危害性 过不过滤都无所谓了。
    Perrie
        17
    Perrie  
       2015-12-16 15:20:17 +08:00
    同比较常用的是 on duplicate key update 以及 replace
    kchum
        18
    kchum  
       2015-12-16 15:21:13 +08:00
    @eoo 有木有考虑开源 17wo 到脚本啊?
    eoo
        19
    eoo  
       2015-12-16 15:47:25 +08:00 via Android
    TaMud
        20
    TaMud  
       2015-12-16 16:01:48 +08:00
    "insert into s_cmd(cmd_str,hosts) select ?,? from dual where not exists(select id from s_cmd where hosts=? and cmd_str=? limit 1) ";
    TaMud
        21
    TaMud  
       2015-12-16 16:02:37 +08:00
    insert into select where not exists()
    TaMud
        22
    TaMud  
       2015-12-16 16:03:29 +08:00
    不要忘了 limit 1
    lixiaoxie
        23
    lixiaoxie  
       2015-12-16 16:06:30 +08:00
    REPLACE INTO t_xxx (date,code,prediction,fact) VALUES (xx,xx,xx,xx);
    Chrics
        24
    Chrics  
       2015-12-16 16:33:07 +08:00
    ORM 框架大部分都内置这个功能的吧?
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   3375 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 30ms · UTC 11:28 · PVG 19:28 · LAX 03:28 · JFK 06:28
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.