MySql replace into ,merge into ,ON DUPLICATEKEY UPDATE 语法

mysql  数据插入问题：

MySQL中常用的三种插入数据的语句: 
insert into表示插入数据，数据库会检查主键，如果出现重复会报错； 
replace into表示插入替换数据，需求表中有PrimaryKey，或者unique索引，如果数据库已经存在数据，则用新数据替换，如果没有数据效果则和insert into一样； 
insert ignore表示，如果表中如果已经存在相同的记录，则忽略当前新数据； 
测试代码如下：

1

2

3

4

5

6

7

8

9

10

11

create table testtb(  

id int not null primary key,  

name varchar(50),  

age int 

);  

insert into testtb(id,name,age)values(1,'bb',13);  

select * from testtb;  

insert ignore into testtb(id,name,age)values(1,'aa',13);  

select * from testtb;  

replace into testtb(id,name,age)values(1,"aa",12);  

select * from testtb;

用INSERT DELAYED解决MySQL堵塞问题】

INSERT DELAYED INTO，是客户端提交数据给MySQL，MySQL返回OK状态给客户端。而这是并不是已经将数据插入表，而是存储在内存里面等待排队。当mysql有空余时，再插入。

　　这样的好处是，提高插入的速度，客户端不需要等待太长时间。坏处是，不能返回自动递增的ID，以及系统崩溃时，MySQL还没有来得及插入数据的话，这些数据将会丢失。

注意，除非表有一个PRIMARY KEY或UNIQUE索引，否则，使用一个REPLACE语句没有意义。该语句会与INSERT相同，因为没有索引被用于确定是否新行复制了其它的行。

[separator] 
所有列的值均取自在REPLACE语句中被指定的值。所有缺失的列被设置为各自的默认值，这和INSERT一样。您不能从当前行中引用值，也不能在新行中使用值。如果您使用一个例如“SET col_name = col_name + 1”的赋值，则对位于右侧的列名称的引用会被作为DEFAULT(col_name)处理。因此，该赋值相当于SET col_name = DEFAULT(col_name) + 1。 

为了能够使用REPLACE，您必须同时拥有表的INSERT和DELETE权限。 
REPLACE语句会返回一个数，来指示受影响的行的数目。该数是被删除和被插入的行数的和。如果对于一个单行REPLACE该数为1，则一行被插入，同时没有行被删除。如果该数大于1，则在新行被插入前，有一个或多个旧行被删除。如果表包含多个唯一索引，并且新行复制了在不同的唯一索引中的不同旧行的值，则有可能是一个单一行替换了多个旧行。 

受影响的行数可以容易地确定是否REPLACE只添加了一行，或者是否REPLACE也替换了其它行：检查该数是否为1（添加）或更大（替换）。 

 下文时算法的详细说明（此算法也用于LOAD DATA…REPLACE）： 

1. 尝试把新行插入到表中 
2. 当因为对于主键或唯一关键字出现重复关键字错误而造成插入失败时： 
a. 从表中删除含有重复关键字值的冲突行 
b. 再次尝试把新行插入到表中 

使用格式如下: 
REPLACE [LOW_PRIORITY | DELAYED] 
[INTO] tbl_name [(col_name,...)] 
VALUES ({expr | DEFAULT},…),(…),… 
或： 
REPLACE [LOW_PRIORITY | DELAYED] 
[INTO] tbl_name 
SET col_name={expr | DEFAULT}, … 
或： 
REPLACE [LOW_PRIORITY | DELAYED] 
[INTO] tbl_name [(col_name,...)] 
SELECT … 

ON DUPLICATE KEY UPDATE 用法

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

INSERT INTO osc_visit_stats(stat_date,type,id,view_count) VALUES (?,?,?,?) ON DUPLICATEKEY UPDATE view_count=view_count+?

--osc_visit_stats 表有复合主键 (stat_date,type,id)

INSERT INTO osc_space_visit_records(space,user,visit_count,ip,visit_time)VALUES(?,?,?,?,?)

ON DUPLICATE KEY UPDATE visit_count=visit_count+1,ip=?,visit_time=NOW()







如果在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 SET c=c+1 WHERE a=1;
 

如果行作为新记录被插入，则受影响行的值显示1；如果原有的记录被更新，则受影响行的值显示2。 

这个语法还可以这样用: 
 

如果INSERT多行记录(假设 a 为主键或 a 是一个 UNIQUE索引列): 

1.INSERT INTO TABLE (a,c) VALUES (1,3),(1,7) ON DUPLICATE KEY UPDATE c=c+1;

执行后, c 的值会变为 4 (第二条与第一条重复, c 在原值上+1). 

2.INSERT INTO TABLE (a,c) VALUES (1,3),(1,7) ON DUPLICATE KEY UPDATE c=VALUES(c);

执行后, c 的值会变为 7 (第二条与第一条重复, c 在直接取重复的值7). 
 

注意：ON DUPLICATE KEY UPDATE只是MySQL的特有语法，并不是SQL标准语法！ 

这个语法和适合用在需要 判断记录是否存在,不存在则插入存在则更新的场景. 

可以参考语法: 

http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#insert

 oralce 的此语法要强悍的多啊

/*Merge into 详细介绍
MERGE语句是Oracle9i新增的语法，用来合并UPDATE和INSERT语句。
通过MERGE语句，根据一张表或子查询的连接条件对另外一张表进行查询，
连接条件匹配上的进行UPDATE，无法匹配的执行INSERT。
这个语法仅需要一次全表扫描就完成了全部工作，执行效率要高于INSERT＋UPDATE。 
*/
/*語法：
MERGE [INTO [schema .] table [t_alias] 
USING [schema .] { table | view | subquery } [t_alias] 
ON ( condition ) 
WHEN MATCHED THEN merge_update_clause 
WHEN NOT MATCHED THEN merge_insert_clause;
*/

语法：

MERGE INTO [your table-name] [rename your table here]

USING ( [write your query here] )[rename your query-sql and using just like a table]

ON ([conditional expression here] AND [...]...)

WHEN MATHED THEN [here you can execute some update sql or something else ]

WHEN NOT MATHED THEN [execute something else here ! ]

1. 1，可省略的update 或者insert
2.    MERGE INTO products p   
3.     2 USING newproducts np   
4.     3 ON (p.product_id = np.product_id)   
5.     4 WHEN MATCHED THEN  
6.     5 UPDATE  
7.     6 SET p.product_name = np.product_name,   
8.     7 p.category = np.category;

  使用表newproducts中的product_name 和category字段来更新表products 中相同product_id的product_name 和category.

2，当条件不满足的时候把newproducts表中的数据INSERT 到表products中。

1. MERGE INTO products p   
2.     USING newproducts np   
3.      ON (p.product_id = np.product_id)   
4.      WHEN NOT MATCHED THEN  
5.      INSERT  
6.      VALUES (np.product_id, np.product_name,   
7.      np.category);   

3，带条件的insert 和update

1. MERGE INTO products p   
2.  USING newproducts np   
3.    ON (p.product_id = np.product_id)   
4.    WHEN MATCHED THEN  
5.    UPDATE  
6.    SET p.product_name = np.product_name   
7.    WHERE p.category = np.category;

 insert 和update　都带有where 字句

 

1. MERGE INTO products p   
2.  USING newproducts np   
3.     ON (p.product_id = np.product_id)   
4.     WHEN MATCHED THEN  
5.     UPDATE  
6.      SET p.product_name = np.product_name,   
7.      p.category = np.category   
8.     WHERE p.category = 'DVD'  
9.     WHEN NOT MATCHED THEN  
10.      INSERT  
11.      VALUES (np.product_id, np.product_name, np.category)   
12.     WHERE np.category != 'BOOKS'  

4,无条件的insert

1. MERGE INTO products p   
2.  USING newproducts np   
3.   ON (1=0)   
4.   WHEN NOT MATCHED THEN  
5.    INSERT  
6.    VALUES (np.product_id, np.product_name, np.category)   
7.    WHERE np.category = 'BOOKS'  
   

5，delete 子句

１  merge into products p
  2  using newproducts np
  3  on(p.product_id = np.product_id)
  4  when matched then
  5  update
  6  set p.product_name = np.product_name
  7  delete where category = 'macle1_cate';