Mysql如何实现不存在则插入,存在则更新

目录
  • 数据准备
  • ON DUPLICATE KEY UPDATE
  • REPLACE INTO … VALUES…
  • INSERT IGNORE INTO … VALUES…
  • 使用DUAL虚表和NOT EXISTS

数据准备

ON DUPLICATE KEY UPDATE

insert into test_table(id,username)
VALUES(4,'fukaiit')
ON DUPLICATE KEY UPDATE username='fukaiit'

执行如上sql，该id不存在(此处id不存在，指的是id为X的记录不存在，包括主键、unique索引列等情况)，相当于执行了insert部分，普通插入，受影响的行：1。

insert into test_table(id,username)
VALUES(1,'fukaiit')
ON DUPLICATE KEY UPDATE username='fukaiit'

执行如上sql，该id已存在，相当于执行了update部分，受影响的行：2。

执行结果如下，注意只是更新username，是保留了email的。

REPLACE INTO … VALUES…

REPLACE INTO test_table(id,username)
VALUES(5,'fukaiit');

执行如上sql，该id不存在，相当于执行了insert，普通插入，受影响的行：1。

REPLACE INTO test_table(id,username)
VALUES(1,'fukaiit');

执行如上sql，该id已存在，相当于执行了删除再插入的操作，受影响的行：2。

执行结果如下，注意删除后再插入是没有email值的，所以此时email为null，注意与ON DUPLICATE KEY UPDATE的区别。

无论是insert on duplicate key update还是replace into，在插入时MySQL返回的影响行数就是插入的记录数，但是在更新时返回的影响行数是更新行数*2。

INSERT IGNORE INTO … VALUES…

INSERT IGNORE INTO test_table(id,username) 
values(6,'fukaiit');

执行如上sql，该id不存在，相当于执行了insert，普通插入，受影响的行：1。

INSERT IGNORE INTO test_table(id,username) 
values(1,'fukaiit_new');

执行如上sql，该id已存在，忽略了该插入操作，数据没有变化，受影响的行：0。

使用DUAL虚表和NOT EXISTS

该方法与INSERT IGNORE INTO ... VALUES...方法的效果相同。

INSERT INTO test_table(id,username)
SELECT 7,'fukaiit' from DUAL 
where NOT EXISTS (select * from test_table where id=7);

执行如上sql，该id不存在，select子句返回结果集为空，NOT EXISTS成立，相当于执行了insert，普通插入，受影响的行：1。

INSERT INTO test_table(id,username)
SELECT 2,'fukaiit' from DUAL 
where NOT EXISTS (select * from test_table where id=2);

执行如上sql，该id已存在，select子句返回结果集不为空，NOT EXISTS不成立，则未执行插入操作，数据没有变化，受影响的行：0。

以上为个人经验，希望能给大家一个参考，也希望大家多多支持我们。

赞 (0)

相关推荐

  • MySql 如何实现无则插入有则更新

    摘要 某些场景会有这样的需求:无记录则插入,有记录则更新.例如:新增用户,以身份证号码作为唯一身份标识,插入时若先查询是否存在记录再决定插入还是更新,在高并发情况下必然存在问题.本文提供三种解决方案. 方案一 加锁 无论通过synchronized锁.ReentranLock锁还是分布式锁,都可以解决该问题.缺点是,加锁会影响性能.方法二和三都是数据库层面解决方案,个人感觉比方法一好一些. 方案二 Unique和Replace Into - SELECT - 首先对唯一性的字段添加唯一索引ALT

  • mysql 记录不存在时插入 记录存在则更新的实现方法

    mysql 记录不存在时插入在 MySQL 中,插入(insert)一条记录很简单,但是一些特殊应用,在插入记录前,需要检查这条记录是否已经存在,只有当记录不存在时才执行插入操作,本文介绍的就是这个问题的解决方案. 问题:我创建了一个表来存放客户信息,我知道可以用 insert 语句插入信息到表中,但是怎么样才能保证不会插入重复的记录呢? 答案:可以通过使用 EXISTS 条件句防止插入重复记录. 示例一:插入多条记录 假设有一个主键为 client_id 的 clients 表,可以使用下面的

  • mysql 存在该记录则更新，不存在则插入记录的sql

    复制代码 代码如下: INSERT table (auto_id, auto_name) values (1, 'yourname') ON DUPLICATE KEY UPDATE auto_name='yourname' ON DUPLICATE KEY UPDATE的使用 如果您指定了ON DUPLICATE KEY UPDATE,并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,则执行旧行UPDATE.例如,如果列a被定义为UNIQUE,并且包含值1,则以下两

  • Mysql如何实现不存在则插入,存在则更新

    Mysql如何实现不存在则插入,存在则更新

    目录 数据准备 ON DUPLICATE KEY UPDATE REPLACE INTO … VALUES… INSERT IGNORE INTO … VALUES… 使用DUAL虚表和NOT EXISTS 数据准备 ON DUPLICATE KEY UPDATE insert into test_table(id,username) VALUES(4,'fukaiit') ON DUPLICATE KEY UPDATE username='fukaiit' 执行如上sql,该id不存在(此处i

  • MySQL下将一个表的数据插入到另外一个表的实现语句

    如果2张表的字段一致,并且希望插入全部数据,可以用这种方法: Code: 复制代码 代码如下: INSERT INTO 目标表 SELECT * FROM 来源表; 比如要将 articles 表插入到 newArticles 表中,则是: 复制代码 代码如下: INSERT INTO newArticles SELECT * FROM articles; 如果只希望导入指定字段,可以用这种方法: 复制代码 代码如下: INSERT INTO 目标表 (字段1, 字段2, ...) SELECT

  • MySQL查询结果复制到新表的方法(更新、插入)

    MySQL查询结果复制到新表的方法(更新、插入)

    MySQL中可以将查询结果复制到另外的一张表中,复制的话通常有两种情况,一种是更新已有的数据,另一种是插入一条新记录.下面通过例子来说明.首先构建两个测试表. 表t1: 表t2: 1.如果t2表中存在score值,将score更新到t1表中.方法如下: UPDATE t1,t2 SET t1.score = t2.score WHERE t1.id = t2.id AND t2.score IS NOT NULL 这就是将查询结果作为条件更新另一张表,当然,t2也可以是更为复杂的一个查询结果而不

  • 详解MySQL主键唯一键重复插入解决方法

    目录 解决方案: 1. IGNORE 2. REPLACE 3. ON DUPLICATE KEY UPDATE 我们插入数据的时候,有可能碰到重复数据插入的问题,但是这些数据又是不被允许有重复值: CREATE TABLE stuInfo ( id INT NOT NULL COMMENT '序号', name VARCHAR(20) NOT NULL DEFAULT '' COMMENT '姓名', age INT NOT NULL DEFAULT 0 COMMENT '年龄', PRIMA

  • mysql ON DUPLICATE KEY UPDATE重复插入时更新方式

    mysql ON DUPLICATE KEY UPDATE重复插入时更新方式

    目录 mysql当插入重复时更新的方法 第一种方法 第二种方法 第三种方法 Mysql on duplicate key update 解决插入重复数据时更新值的问题以及其存在的问题 一.使用 二.存在问题 mysql当插入重复时更新的方法 第一种方法 示例一:插入多条记录 假设有一个主键为 client_id 的 clients 表,可以使用下面的语句: INSERT INTO clients (client_id,client_name,client_type) SELECT supplie

  • spring data jpa开启批量插入、批量更新的问题解析

    spring data jpa开启批量插入、批量更新的问题解析

    最近准备上spring全家桶写一下个人项目,该学的都学学,其中ORM框架,最早我用的是jdbcTemplate,后来用了Mybatis,唯独没有用过JPA(Hibernate)系的,过去觉得Hibernate太重量级了,后来随着springboot和spring data jpa出来之后,让我觉得好像还不错,再加上谷歌趋势... 只有中日韩在大规模用Mybatis(我严重怀疑是中国的外包),所以就很奇怪,虽然说中国的IT技术在慢慢抬头了,但是这社会IT发展的主导目前看来还是美国.欧洲,这里JPA

  • PostgreSQL数据库事务插入删除及更新操作示例

    PostgreSQL数据库事务插入删除及更新操作示例

    目录 INSERT DELETE UPDATE 事务 INSERT 使用INSERT语句可以向表中插入数据. 创建一个表: CREATE TABLE ProductIns (product_id CHAR(4) NOT NULL, product_name VARCHAR(100) NOT NULL, product_type VARCHAR(32) NOT NULL, sale_price INTEGER DEFAULT 0, purchase_price INTEGER , regist_d

  • java操作mysql入门代码实例（含插入、更新和查询）

    复制代码 代码如下: import java.sql.*; public class mysql {    public static String url = "jdbc:mysql://localhost:3306/test";//characterEncoding=GBK    public static String username = "root";    public static String password = "root";

  • mysql 一次向表中插入多条数据实例讲解

    mysql一次插入多条数据: INSERT INTO hk_test(username, passwd) VALUES ('qmf2', 'qmf2'),('qmf3', 'qmf3'),('qmf4', 'qmf4'),('qmf5', 'qmf5') GO 我们先来创建一种表Authors: CREATE TABLE Authors( AuthID SMALLINT NOT NULL PRIMARY KEY, AuthFN VARCHAR(20), AuthMN VARCHAR(20), A

  • MYSQL开发性能研究之批量插入数据的优化方法

    MYSQL开发性能研究之批量插入数据的优化方法

    一.我们遇到了什么问题 在标准SQL里面,我们通常会写下如下的SQL insert语句. INSERT INTO TBL_TEST (id) VALUES(1); 很显然,在MYSQL中,这样的方式也是可行的.但是当我们需要批量插入数据的时候,这样的语句却会出现性能问题.例如说,如果有需要插入100000条数据,那么就需要有100000条insert语句,每一句都需要提交到关系引擎那里去解析,优化,然后才能够到达存储引擎做真的插入工作. 正是由于性能的瓶颈问题,MYSQL官方文档也就提到了使用批

随机推荐