MySQL: ON DUPLICATE KEY UPDATE 用法

2013-04-02  来源:本站原创  分类:java/linux/mysql  人气:7 

使用该语法可在插入记录的时候先判断记录是否存在,如果不存在则插入,否则更新,很方便,无需执行两条SQL

[代码] [SQL]代码

1 INSERT INTO osc_visit_stats(stat_date,type,id,view_count) VALUES (?,?,?,?) ON DUPLICATEKEY UPDATE view_count=view_count+?
2
3 -- osc_visit_stats 表有复合主键 (stat_date,type,id)

[代码] 多字段更新

1 INSERT INTO osc_space_visit_records(space,user,visit_count,ip,visit_time)VALUES(?,?,?,?,?)
2 ON DUPLICATE KEY UPDATE visit_count=visit_count+1,ip=?,visit_time=NOW()
相关文章
  • MySQL: ON DUPLICATE KEY UPDATE 用法 2013-04-02

    使用该语法可在插入记录的时候先判断记录是否存在,如果不存在则插入,否则更新,很方便,无需执行两条SQL [代码] [SQL]代码 1 INSERT INTO osc_visit_stats(stat_date,type,id,view_count) VALUES (?,?,?,?) ON DUPLICATEKEY UPDATE view_count=view_count+? 2 3 -- osc_visit_stats 表有复合主键 (stat_date,type,id) [代码] 多字段更新

  • mysql ON DUPLICATE KEY UPDATE语句示例 2014-01-25

    本文介绍一下关于mysql中INSERT INTO- ON DUPLICATE KEY UPDATE用法 MySQL 自4.1版以后开始支持INSERT - ON DUPLICATE KEY UPDATE语法,使得原本需要执行3条SQL语句(SELECT,INSERT,UPDATE),缩减为1条语句即可完成. 例如ipstats表结构如下: CREATE TABLE ipstats ( ip VARCHAR(15) NOT NULL UNIQUE, clicks SMALLINT(5) UNSI

  • MySQL, on duplicate key update is implemented in the PostgesSQL 2010-11-09

    MySQL, on duplicate key update username = VALUES (username), maxdate = VALUES (maxdate), How to achieve it in PostgresSQL? MySQL in a statement: insert into db select distinct username, mindate, maxdate from test_table on duplicate key update usernam

  • mysql "ON DUPLICATE KEY UPDATE" 语法 2012-04-16

    如果在INSERT语句末尾指定了ON DUPLICATE KEY UPDATE,并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,则在出现重复值的行执行UPDATE:如果不会导致唯一值列重复的问题,则插入新行. 例如,如果列 a 为 主键 或 拥有UNIQUE索引,并且包含值1,则以下两个语句具有相同的效果: INSERT INTO TABLE (a,c) VALUES (1,3) ON DUPLICATE KEY UPDATE c=c+1; UPDATE TABLE

  • MySQL ON DUPLICATE KEY UPDATE 傻瓜应用教程 2012-11-07

    背景:最近有一个算数据的脚本几天都没出数据,而且手动跑起来的时候服务器立马卡机.目标是优化! [work(2)@dm02 19:02:21 ~]$ nohup python twitter_click_stat.py 2012-11-02 1 >/tmp/click.log 2>&1 & [3] 25862 [work(3)@dm02 19:05:07 ~/GALAXY_RELEASE/release]$ kill 25862 3分钟吃力7G内存 脚本流程: 1.load my

  • On duplicate key update in MySQL's implementation in PostgesSQL 2010-11-09

    MySQL, on duplicate key update username = VALUES (username), maxdate = VALUES (maxdate), In PostgresSQL how to achieve it? MySQL in a statement: insert into db select distinct username, mindate, maxdate from test_table on duplicate key update usernam

  • Some transfer mysql insert operation (DELAYED. IGNORE.ON DUPLICATE KEY UPDATE) 2010-05-12

    Some mysql insert operation (DELAYED, IGNORE, ON DUPLICATE KEY UPDATE) INSERT syntax INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [ IGNORE ] [INTO] tbl_name [(col_name,...)] VALUES ({expr | DEFAULT},...),(...),.. . [ ON DUPLICATE KEY UPDATE col_na

  • mysql insert into select ON DUPLICATE KEY UPDATE updates the value of the problem 2010-05-12

    I have one thing to reach INSERT INTO table1 (count) select count (b.c1) count from table2 b where date (b.date) =... group by b.page_hash ON DUPLICATE KEY UPDATE count = count + b.count ON DUPLICATE KEY UPDATE count = count + values (count) Today, w

  • Some mysql insert operation (DELAYED. IGNORE.ON DUPLICATE KEY UPDATE) 2010-06-29

    Some mysql insert operation (DELAYED, IGNORE, ON DUPLICATE KEY UPDATE) INSERT syntax INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name ,...)] VALUES ((expr | DEFAULT), ...), (...), ... [ON DUPLICATE KEY UPDATEcol_nam

  • Mysql "insert ... on duplicate key update" and "replace into" 2011-06-21

    1. INSERT ... ON DUPLICATE KEY UPDATE http://dev.mysql.com/doc/refman/5.6/en/insert-on-duplicate.html insert into tableName values (1, "testvalue", now()) on duplicate key update value=values(value), gmt_modify=values(gmt_modify); Pre-conditions

  • MySQL的Replace into 与Insert into on duplicate key update真正的不同之处 2014-02-09

    今天听同事介绍oracle到mysql的数据migration,他用了Insert into ..... on duplicate key update ...,我当时就想怎么不用Replace呢,于是回来就仔细查了下,它们果然还是有区别的 看下面的例子吧: 1 Replace into ... 1.1 录入原始数据 mysql> use test; Database changed mysql> mysql> CREATE TABLE t1 SELECT 1 AS a, 'c3' AS

  • mysql insert的几点操作(DELAYED,IGNORE,ON DUPLICATE KEY UPDATE ) 2014-09-05

    DELAYED 做为快速插入,并不是很关心失效性,提高插入性能. INSERT语法 INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)] VALUES ({expr | DEFAULT},...),(...),... [ ON DUPLICATE KEY UPDATE col_name=expr, ... ] 或: INSERT [LOW_PRIORITY | DELAY

  • mysql之replace和ON DUPLICATE KEY UPDATE的区别 2015-03-18

    1 Replace into ... 1.1 录入原始数据 mysql> use test; Database changed mysql> mysql> CREATE TABLE t1 SELECT 1 AS a, 'c3' AS b, 'c2' AS c; ALTER TABLE t1 CHANGE a a INT PRIMARY KEY AUTO_INCREMENT ; Query OK, 1 row affected (0.03 sec) Records: 1 Duplicate

  • ON DUPLICATE KEY UPDATE 2010-12-19

    Since the start after MySQL 4.1 support INSERT ... ON DUPLICATE KEY UPDATE syntax, making the need to perform three original SQL statement (SELECT, INSERT, UPDATE), reduced to a statement to complete. For example ipstats table structure is as follows

  • INSERT - ON DUPLICATE KEY UPDATE (rpm) 2011-07-06

    MySQL has supported since the 4.1 version after INSERT ... ON DUPLICATE KEY UPDATE syntax, making the need to perform three original SQL statement (SELECT, INSERT, UPDATE), reduced to a statement to complete. INSERT ... ON DUPLICATE KEY UPDATE, when

  • insert into - on duplicate key update / replace into 多行数据介绍 2015-04-12

    当我插入一条数据时,我要判断(k1,k2)是否已经存在(1条selete),若存在就update,不存在就insert 场景是这样的,我有KV型的表,建表语句如下: CREATE TABLE `dkv` ( `k1` int(11) NOT NULL DEFAULT '0', `k2` int(11) NOT NULL DEFAULT '0', `val` varchar(30) DEFAULT NULL, PRIMARY KEY (`k1`,`k2`) ) ENGINE=InnoDB DEFA

  • INSERT INTO .. ON DUPLICATE KEY更新多行记录 2014-01-14

    本文详细的介绍了关于INSERT INTO .. ON DUPLICATE KEY更新多行记录,有需要了解的同学可参考一下 如果在INSERT语句末尾指定了ON DUPLICATE KEY UPDATE,并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,则执行旧行UPDATE:如果不会导致唯一值列重复的问题,则插入新行.例如,如果列a被定义为UNIQUE,并且包含值1,则以下两个语句具有相同的效果: INSERT INTO TABLE (a,b,c) VALUES (

  • Mysql delete duplicate records 2010-12-31

    Mysql delete duplicate records on the method, I read many articles, many of them are copied, and my own method of online experiments by a bit, not a sql statement can be a solution, do not know if I have a master to make moves. My test process is as

  • MYSQL delete duplicate records (where there are positive solutions) 2011-05-06

    Mysql delete duplicate records on the method, I read many articles, many of them are copied, and my own method of online experiments by a bit, not a sql statement can be a solution, do not know if I have a master to make moves. My test process is as

  • mysql query duplicate field 2010-03-19

    mysql query duplicate field July 11, 2007 | Posted by admin There is a big table in the database, need to find the name of which there are duplicate records id, for comparison purposes. If only to find the database name does not duplicate the field,