MYSQL 中 ON DUPLICATE KEY UPDATE / REPLACE 单语句处理重复插入时更新

 mysql  MYSQL 中 ON DUPLICATE KEY UPDATE / REPLACE 单语句处理重复插入时更新已关闭评论
7月 252019
 

来自:https://lobert.iteye.com/blog/1604122 , 讲的很清楚,记录下

 

mysql当插入重复时更新的方法:

第一种方法:

 示例一:插入多条记录

假设有一个主键为 client_id clients 表,可以使用下面的语句:

 

Sql代码  

  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 where clients.client_id=suppliers.supplier_id);  

 

示例一:插入单条记录

Sql代码  

  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 where clients.client_id=10345);  

 

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

第二种方法: 

INSERT ON DUPLICATE KEY UPDATE的使用(本文重点)

如果您指定了ON DUPLICATE KEY UPDATE,并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,则执行旧行UPDATE。例如,如果列a被定义为UNIQUE,并且包含值1,则以下两个语句具有相同的效果:

 

Sql代码  

  1. mysql>INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;  
  2. mysql>UPDATE table SET c=c+1 WHERE a=1;  

 

如果行作为新记录被插入,则受影响行的值为1;如果原有的记录被更新,则受影响行的值为2

注释:如果列b也是唯一列,则INSERT与此UPDATE语句相当:

 

Sql代码  

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

 

如果a=1 OR b=2与多个行向匹配,则只有一个行被更新。通常,您应该尽量避免对带有多个唯一关键字的表使用ON DUPLICATE KEY子句。

您可以在UPDATE子句中使用VALUES(col_name)函数从INSERT…UPDATE语句的INSERT部分引用列值。换句话说,如果没有发生重复关键字冲突,则UPDATE子句中的VALUES(col_name)可以引用被插入的col_name的值。本函数特别适用于多行插入。VALUES()函数只在INSERT…UPDATE语句中有意义,其它时候会返回NULL

示例:

 

Sql代码  

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

 

本语句与以下两个语句作用相同:

 

Sql代码  

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

 

当您使用ON DUPLICATE KEY UPDATE时,DELAYED选项被忽略。

第三种方法:

 

REPLACE语句

  我们在使用数据库时可能会经常遇到这种情况。如果一个表在一个字段上建立了唯一索引,当我们再向这个表中使用已经存在的键值插入一条记录,那将会抛出一个主键冲突的错误。当然,我们可能想用新记录的值来覆盖原来的记录值。如果使用传统的做法,必须先使用DELETE语句删除原先的记录,然后再使用INSERT插入新的记录。而在MySQL中为我们提供了一种新的解决方案,这就是REPLACE语句。使用REPLACE插入一条记录时,如果不重复,REPLACE就和INSERT的功能一样,如果有重复记录,REPLACE就使用新记录的值来替换原来的记录值。

  使用REPLACE的最大好处就是可以将DELETEINSERT合二为一,形成一个原子操作。这样就可以不必考虑在同时使用DELETEINSERT时添加事务等复杂操作了。

  在使用REPLACE时,表中必须有唯一索引,而且这个索引所在的字段不能允许空值,否则REPLACE就和INSERT完全一样的。

  在执行REPLACE后,系统返回了所影响的行数,如果返回1,说明在表中并没有重复的记录,如果返回2,说明有一条重复记录,系统自动先调用了DELETE删除这条记录,然后再记录用INSERT来插入这条记录。如果返回的值大于2,那说明有多个唯一索引,有多条记录被删除和插入。

  REPLACE的语法和INSERT非常的相似,如下面的REPLACE语句是插入或更新一条记录。

  REPLACE INTO users (id,name,age) VALUES(123, ‘赵本山‘, 50);

  

 

  插入多条记录:

  REPLACE INTO users(id, name, age)

Sql代码  

  1. VALUES(123, 赵本山, 50), (134,‘Mary’,15);  
  2.   
  3. REPLACE也可以使用SET语句  
  4.   
  5. REPLACE INTO users SET id = 123, name = 赵本山, age = 50;  

 

  上面曾提到REPLACE可能影响3条以上的记录,这是因为在表中有超过一个的唯一索引。在这种情况下,REPLACE将考虑每一个唯一索引,并对每一个索引对应的重复记录都删除,然后插入这条新记录。假设有一个table1表,有3个字段a, b, c。它们都有一个唯一索引。

  CREATE TABLE table1(a INT NOT NULL UNIQUE,b INT NOT NULL UNIQUE,c INT NOT NULL UNIQUE);

 

  假设table1中已经有了3条记录

  a b c

  1 1 1

  2 2 2

  3 3 3

  下面我们使用REPLACE语句向table1中插入一条记录。

  REPLACE INTO table1(a, b, c) VALUES(1,2,3);

 

  返回的结果如下

  Query OK, 4 rows affected (0.00 sec)

  在table1中的记录如下

  a b c

  1 2 3

 

详解JS正则replace的使用方法

 javascript  详解JS正则replace的使用方法已关闭评论
2月 092018
 

想不到一个replace还有这么多奥妙:http://www.jb51.net/article/80544.htm 

在讲replace的高级应用之前,我们先简单梳理一下JS正则中的几个重要的知识点,以帮助你对基础知识的回顾,然后再讲解JS正则表达式在replace中的使用,以及常见的几个经典案例。 

一、正则表达式的创建

JS正则的创建有两种方式: new RegExp() 和 直接字面量。

?
1
2
3
4
5
//使用RegExp对象创建
varregObj =newRegExp(“(^\s+)|(\s+$)”,”g”);
 
//使用直接字面量创建
varregStr = /(^\s+)|(\s+$)/g;

其中 g 表示全文匹配,与之相关的还有 i 和m,i 表示匹配时忽略大小写,m 表示多行匹配,如果多个条件同时使用时,则写成:gmi 

二、()、[]、{} 的区别

() 的作用是提取匹配的字符串。表达式中有几个()就会得到几个相应的匹配字符串。比如 (\s+) 表示连续空格的字符串。

[]是定义匹配的字符范围。比如 [a-zA-Z0-9] 表示字符文本要匹配英文字符和数字。

{}一般用来表示匹配的长度,比如 \d{3} 表示匹配三个空格,\d[1,3]表示匹配1~3个空格。

三、^ 和 $

^ 匹配一个字符串的开头,比如 (^a) 就是匹配以字母a开头的字符串

$ 匹配一个字符串的结尾,比如 (b$) 就是匹配以字母b结尾的字符串

^ 还有另个一个作用就是取反,比如[^xyz] 表示匹配的字符串不包含xyz

需要注意的是:如果^出现在[]中一般表示取反,而出现在其他地方则是匹配字符串的开头

四、\d  \s  \w  .

\d 匹配一个非负整数, 等价于 [0-9]

\s 匹配一个空白字符

\w 匹配一个英文字母或数字,等价于[0-9a-zA-Z]

.   匹配除换行符以外的任意字符,等价于[^\n]

五、* + ?

* 表示匹配前面元素0次或多次,比如 (\s*) 就是匹配0个或多个空格

+ 表示匹配前面元素1次或多次,比如 (\d+) 就是匹配由至少1个整数组成的字符串

? 表示匹配前面元素0次或1次,相当于{0,1} ,比如(\w?) 就是匹配最多由1个字母或数字组成的字符串

六、test 、match

前面的大都是JS正则表达式的语法,而test则是用来检测字符串是否匹配某一个正则表达式,如果匹配就会返回true,反之则返回false

?
1
2
3
/\d+/.test(“123”) ;//true
 
/\d+/.test(“abc”) ;//false

match是获取正则匹配到的结果,以数组的形式返回

“186a619b28”.match(/\d+/g); // [“186″,”619″,”28”] 

以上基本上是我经常用到的基础知识,不是很全面,不常用的就没有列出来,因为列出来也只是摆设,反而混淆主次!

七、replace

replace 本身是JavaScript字符串对象的一个方法,它允许接收两个参数:

replace([RegExp|String],[String|Function])
第1个参数可以是一个普通的字符串或是一个正则表达式

第2个参数可以是一个普通的字符串或是一个回调函数

如果第1个参数是RegExp, JS会先提取RegExp匹配出的结果,然后用第2个参数逐一替换匹配出的结果

如果第2个参数是回调函数,每匹配到一个结果就回调一次,每次回调都会传递以下参数:

?
1
2
3
4
5
6
7
result: 本次匹配到的结果
 
$1,…$9: 正则表达式中有几个(),就会传递几个参数,$1~$9分别代表本次匹配中每个()提取的结果,最多9个
 
offset:记录本次匹配的开始位置
 
source:接受匹配的原始字符串

以下是replace和JS正则搭配使用的几个常见经典案例: 

(1)实现字符串的trim函数,去除字符串两边的空格

?
1
2
3
4
5
6
7
8
9
10
String.prototype.trim =function(){
 
  //方式一:将匹配到的每一个结果都用””替换
  returnthis.replace(/(^\s+)|(\s+$)/g,function(){
    return””;
  });
 
  //方式二:和方式一的原理相同
  returnthis.replace(/(^\s+)|(\s+$)/g,”);
};

^\s+ 表示以空格开头的连续空白字符,\s+$ 表示以空格结尾的连续空白字符,加上() 就是将匹配到的结果提取出来,由于是 | 的关系,因此这个表达式最多会match到两个结果集,然后执行两次替换:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
String.prototype.trim =function(){
  /**
   * @param rs:匹配结果
   * @param $1:第1个()提取结果
   * @param $2:第2个()提取结果
   * @param offset:匹配开始位置
   * @param source:原始字符串
   */
  this.replace(/(^\s+)|(\s+$)/g,function(rs,$1,$2,offset,source){
    //arguments中的每个元素对应一个参数
    console.log(arguments);
  });
};
 
” abcd “.trim();
 
输出结果:
 
[” “,” “, undefined, 0,” abcd “]//第1次匹配结果
[” “, undefined,” “, 5,” abcd “]//第2次匹配结果

(2)提取浏览器url中的参数名和参数值,生成一个key/value的对象

?
1
2
3
4
5
6
7
8
9
10
11
functiongetUrlParamObj(){
  varobj = {};
  //获取url的参数部分
  varparams = window.location.search.substr(1);
  //[^&=]+ 表示不含&或=的连续字符,加上()就是提取对应字符串
  params.replace(/([^&=]+)=([^&=]*)/gi,function(rs,$1,$2){
    obj[$1] = $2;
  });
 
  returnobj;
}

/([^&=]+)=([^&=]*)/gi 每次匹配到的都是一个完整key/value,形如 xxxx=xxx, 每当匹配到一个这样的结果时就执行回调,并传递匹配到的key和value,对应到$1和$2

(3)在字符串指定位置插入新字符串

?
1
2
3
4
5
6
7
8
9
10
String.prototype.insetAt =function(str,offset){
 
  //使用RegExp()构造函数创建正则表达式
  varregx =newRegExp(“(.{“+offset+”})”);
 
  returnthis.replace(regx,”$1″+str);
};
 
“abcd”.insetAt(‘xyz’,2);//在b和c之间插入xyz
>>”abxyzcd”

当offset=2时,正则表达式为:(^.{2})  .表示除\n之外的任意字符,后面加{2} 就是匹配以数字或字母组成的前两个连续字符,加()就会将匹配到的结果提取出来,然后通过replace将匹配到的结果替换为新的字符串,形如:结果=结果+str

(4) 将手机号12988886666转化成129 8888 6666

?
1
2
3
4
5
6
7
8
9
10
11
12
functiontelFormat(tel){
 
  tel = String(tel);
 
  //方式一
  returntel.replace(/(\d{3})(\d{4})(\d{4})/,function(rs,$1,$2,$3){
    return$1+” “+$2+” “+$3
  });
 
  //方式二
  returntel.replace(/(\d{3})(\d{4})(\d{4})/,”$1 $2 $3″);
}

(\d{3}\d{4}\d{4}) 可以匹配完整的手机号,并分别提取前3位、4-7位和8-11位,”$1 $2 $3″ 是在三个结果集中间加空格组成新的字符串,然后替换完整的手机号。

 (5) 实现函数escapeHtml,将<, >, &, ” 进行转义

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
functionescapeHtml(str) {
  //匹配< > ” &
  returnstr.replace(/[<>”&]/g,function(rs) {
    switch(rs) {
      case”<“:
        return”<“;
      case”>”:
        return”>”;
      case”&”:
        return”&”;
      case”\””:
        return”””;
    }
  });
}