现在的位置: 首页 > 数据库 > 正文

MYSQL无重复插入数据更新语法&sql一句话使insert时若主键重

2020年05月02日 数据库 ⁄ 共 2349字 ⁄ 字号 评论关闭

MYSQL无重复插入数据更新语法&sql一句话使insert时若主键重复则更新
 
如果你指定了ON DUPLICATE KEY UPDATE命令语句,那么在唯一索引或者主索引的作用下将不插入与记录重复的内容,但同时会更新数据库中的旧记录。例如,字段a被声明为唯一索引并且里面只包含有值为1的记录,以下两个语句会达到同样的效果:
  www.2cto.com  
一、 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。例如:
 
INSERT INTO table ( a , b , c ) VALUES ( 1 , 2 , 3 ) , ( 4 , 5 , 6 ) 
ON DUPLICATE KEY UPDATE c = VALUES ( a ) + VALUES ( b ) ;
这个语句和下面两个是同效果的:
  www.2cto.com  
INSERT INTO table ( a , b , c ) VALUES ( 1 , 2 , 3 ) 
ON DUPLICATE KEY UPDATE c = 3 ;
INSERT INTO table ( a , b , c ) VALUES ( 4 , 5 , 6 ) 
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() 对更新语句有意义的方法如下:
 
INSERT INTO table ( a , b , c ) VALUES ( 1 , 2 , 3 ) 
ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID ( id ) , c = 3 ;
如果你使用 ON DUPLICATE KEY UPDATE 语句的话,延迟执行选项 DELAYED 将被忽略。
 
英文原文地址:http://dev..com/doc/refman/5.0/en/insert-on-duplicate.html
 
其它声音:
  www.2cto.com  
插入记录
 
Insert into table_name (field_name1, field_name2, …) values (value1, value2, …);
 
Insert into values (value1, value2, …);   //这种形式只能应用于值的顺序与字段顺序匹配的情况(可以通过调用describe命令来决定字段顺序)。
包含引用标志的值需要再引号’前面加上反斜线                   //转义字符
注意:into 关键字是可选的
 
也可以同时插入多个值,用逗号分开
Eg:insert table_name values (value1, value2, …), (value3, value4, …);
还可以不使用insert… values格式,而使用类似update语句,它使用set子句分别为每一列设置值。
Insert into table_name set field_name1 = value1, field_name2 = value2,…;
使用default值
Create table table_name (field_name1 type default def_value not null,…);
Insert table_name values (default,…);   //default关键字只在4.0.3以及更高版本才支持
 
使用AUTOINCREMENT字段
自动产生下一个序号,该字段必须被设置为主键
使用UNIQUE字段
使用IGNORE关键字或者ON DUPLICATE KEY UPDATE子句跳过INSERT、中断操作或者更新旧记录为新值。  www.2cto.com  
INSERT IGNORE INTO TABLE_NAME (UNIQUE_FIELD, …) VALUES (REPEAT, …);
如果重复记录,则不会报告错误,也不会更新记录,数据库中的数据保持不变。
ON DUPLICATE KEY UPDATE和REPLACE区别:前者只把已命名的字段更新为新值,后者删除了旧记录,然后用新值完全替换。
 
Eg:insert into Menu value (null, ‘MySQL’, ‘www.mysql.com’) on duplicate key update label=’MS SQL’, url=’www.microsoft.com’;
在这种情况下,如果MySQL发现表中已经包含具有相同唯一键的记录,则会自动更新旧的记录为ON DUPLICATE KEY UPDATE从句中指定的新值。
当有很多insert语句需要被顺序执行时,IGNORE关键字就使操作变得很方便。这可以保证不管拿一个INSERT包含了重复值,MySQL都会跳过而不放弃全部操作。

抱歉!评论已关闭.