MYSQL中无重复插入更新几种方法

我们在mysql中有时为了防止重复插入记录我们会要利用程序来判断,当然我们也可以直接使用mysql一些语句来实现防止重复插入记录的方法了,希望此方法对各位朋友有帮助.

第一种解决方案:

如果你指定了ON DUPLICATE KEY UPDATE命令语句，那么在唯一索引或者主索引的作用下将不插入与数据库记录重复的内容，但同时会更新数据库中的旧记录。例如，字段a被声明为唯一索引并且里面只包含有值为1的记录，以下两个语句会达到同样的效果,代码如下:

一、INSERT INTO table (a,b,c) VALUES (1,2,3)

ON DUPLICATE KEY UPDATE c=c+1;  

二、UPDATE table SET c=c+1 WHERE a=1;

一、INSERT INTO table (a,b,c) VALUES (1,2,3)

ON DUPLICATE KEY UPDATE c=c+1;

二、UPDATE table SET c=c+1 WHERE a=1;

受影响的是a=1的行，当插入时c的值加1,如果字段b也是唯一的话,这个插入语句将和以下语句的效果一样:

UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;

如果a=1 OR b=2匹配了不止一行,也只是第一行被更新,一般地,如果表中有多个唯一索引的话,你应该避免在使用用ON DUPLICATE KEY子句.

你可以在插入更新语句 INSERT … UPDATE 中使用 VALUES(字段名) 函数去关联某一行记录,也就是说,VALUES(字段名)可以用在UPDATE语句中去更新某字段的值而不会出现重复键,这个函数在多行插入中尤其有用,但是函数 VALUES() 仅当用在 INSERT … UPDATE 语句中才有意义,否则会返回NULL,代码如下:

1. INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)    
2.   ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);  
3. INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6) 
4.   ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b); 

这个语句和下面两个是同效果的,代码如下:

1. INSERT INTO table (a,b,c) VALUES (1,2,3)    
2.   ON DUPLICATE KEY UPDATE c=3;    
3. INSERT INTO table (a,b,c) VALUES (4,5,6)    
4.   ON DUPLICATE KEY UPDATE c=9;  
5. INSERT INTO table (a,b,c) VALUES (1,2,3) 
6.   ON DUPLICATE KEY UPDATE c=3; 
7. INSERT INTO table (a,b,c) VALUES (4,5,6) 
8.   ON DUPLICATE KEY UPDATE c=9; 

如果表中包含有一个自动递增字段AUTO_INCREMENT,并用 INSERT … UPDATE 插入一行，函数 LAST_INSERT_ID()会返回AUTO_INCREMENT的值,如果这个语句更新某一行， LAST_INSERT_ID() 就没有意义了,但是,你可以通过用 LAST_INSERT_ID(expr)使它变得有意义,假如id字段是自动递增栏的话,使 LAST_INSERT_ID() 对更新语句有意义的方法如下:

1. INSERT INTO table (a,b,c) VALUES (1,2,3)    
2.   ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3;  
3. INSERT INTO table (a,b,c) VALUES (1,2,3) 
4.   ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3; 

如果你使用 ON DUPLICATE KEY UPDATE 语句的话,延迟执行选项 DELAYED 将被忽略.

第二种解决方案

这种解决方案比较通用,不过个人感觉性能不是很好,没有测试.

示例一:插入多条记录,假设有一个主键为 client_id 的 clients 表,可以使用下面的语句,代码如下:

1. INSERT INTO clients    
2. (client_id, client_name, client_type)    
3. SELECT supplier_id, supplier_name, 'advertising'   
4. FROM suppliers    
5. WHERE not exists (select * from clients    
6. where clients.client_id = suppliers.supplier_id);  
7. INSERT INTO clients 
8. (client_id, client_name, client_type) 
9. SELECT supplier_id, supplier_name, 'advertising' 
10. FROM suppliers 
11. WHERE not exists (select * from clients 
12. where clients.client_id = suppliers.supplier_id); 

示例二:插入单条记录,代码如下:

1. INSERT INTO clients    
2. (client_id, client_name, client_type)    
3. SELECT 10345, 'IBM', 'advertising'   
4. FROM dual    
5. WHERE not exists (select * from clients    
6. where clients.client_id = 10345);  
7. INSERT INTO clients 
8. (client_id, client_name, client_type) 
9. SELECT 10345, 'IBM', 'advertising' 
10. FROM dual  --Vevb.com 
11. WHERE not exists (select * from clients 
12. where clients.client_id = 10345); 

使用 dual 做表名可以让你在 select 语句后面直接跟上要插入字段的值,即使这些值还不存在当前表中.

第三种解决方案:REPLACE语法

replace的语法格式为:

1. replace into table_name(col_name, …) values(…)

2. replace into table_name(col_name, …) select …

3. replace into table_name set col_name=value, …

算法说明:REPLACE的运行与INSERT很相像,但是如果旧记录与新记录有相同的值,则在新记录被插入之前,旧记录被删除,即:

1. 尝试把新行插入到表中

2. 当因为对于主键或唯一关键字出现重复关键字错误而造成插入失败时:从表中删除含有重复关键字值的冲突行,再次尝试把新行插入到表中,旧记录与新记录有相同的值的判断标准就是,表有一个PRIMARY KEY或UNIQUE索引,否则,使用一个REPLACE语句没有意义.

该语句会与INSERT相同,因为没有索引被用于确定是否新行复制了其它的行.

返回值:REPLACE语句会返回一个数,来指示受影响的行的数目,该数是被删除和被插入的行数的和.

受影响的行数可以容易地确定是否REPLACE只添加了一行,或者是否REPLACE也替换了其它行,检查该数是否为1(添加)或更大(替换).

示例:phone字段为唯一索引,代码如下:

1. replace into table_name(email,phone,user_id) values(‘test569′,’99999′,’123′) 

另外,在 SQL Server 中可以这样处理,代码如下:

1. if not exists (select phone from t where phone= ’1′) 
2. insert into t(phone, update_time) values(’1′, getdate()) 
3. else 
4. update t set update_time = getdate() where phone= ’1′