系统城装机大师 - 固镇县祥瑞电脑科技销售部宣传站!

当前位置:首页 > 数据库 > MsSql > 详细页面

mysql 中 replace into 与 insert into on duplicate key update 的用法和不同点实例分析

时间:2020-02-18来源:系统城作者:电脑系统城

本文实例讲述了mysql 中 replace into 与 insert into on duplicate key update 的用法和不同点。分享给大家供大家参考,具体如下:

replace into和insert into on duplicate key update都是为了解决我们平时的一个问题

就是如果数据库中存在了该条记录,就更新记录中的数据,没有,则添加记录。

我们创建一个测试表test


 
  1. CREATE TABLE `test` (
  2. `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  3. `name` varchar(32) DEFAULT '' COMMENT '姓名',
  4. `addr` varchar(256) DEFAULT '' COMMENT '地址',
  5. PRIMARY KEY (`id`)
  6. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

向该表中插入一些数据


 
  1. INSERT INTO test
  2. VALUES
  3. (NULL, 'a', 'aaa'),
  4. (NULL, 'b', 'bbb'),
  5. (NULL, 'c', 'ccc'),
  6. (NULL, 'd', 'ddd');

影响行数4,结果如下:

mysql 中 replace into 与 insert into on duplicate key update 的用法和不同点实例分析

我们运行如下语句:


 
  1. REPLACE INTO test VALUES(NULL, 'e', 'eee');

结果显示,影响行数1条,记录被插入成功了

mysql 中 replace into 与 insert into on duplicate key update 的用法和不同点实例分析

注意上面的语句,我们并没有填写主键ID。

然后我们再执行下面的语句:


 
  1. REPLACE INTO test VALUES(1, 'aa', 'aaaa');

结果显示,影响行数2条,ID为1的记录被更新成功了

mysql 中 replace into 与 insert into on duplicate key update 的用法和不同点实例分析

为什么会出现这种情况,原因就是replace into会首先尝试先往表里面插入记录,因为我们的ID是主键,不可重复,显然这条记录是无法插入成功的,然后replace into会把这条已存在的记录删掉,然后再插入,所以会显示影响行数是2。

我们再运行下面这条语句:


 
  1. REPLACE INTO test(id,name) VALUES(1, 'aaa');

这里我们只指定id,name字段,我们来看看replace into后addr字段内容是否还存在

mysql 中 replace into 与 insert into on duplicate key update 的用法和不同点实例分析

显然addr字段内容没有了,跟我们上面的分析是一致的,reaplce into先删除了id为1的记录,然后再插入记录,但我们并没有指定addr的值,所以会如上图所示那样。

 

但是有些时候我们的需求是,如果记录存在则更新指定字段的数据,原有字段数据仍保留,而不是上面所示的,addr字段数据没有了。

这里就需要用到insert into on duplicate key update

执行如下语句:


 
  1. INSERT INTO test (id, name)
  2. VALUES(2, 'bb')
  3. ON DUPLICATE KEY
  4. UPDATE
  5. name = VALUES(name);

VALUES(字段名)表示获取当前语句insert的列值,VALUES(name)表示的就是'bb'

结果显示,影响行数2条

mysql 中 replace into 与 insert into on duplicate key update 的用法和不同点实例分析

如上图所示,addr字段的值被保留了。

insert into on duplicate key update语句的做法是先插入记录,如果不成功,则更新记录,但是为什么影响的行数是2?

我们重新建一张表test2


 
  1. CREATE TABLE `test2` (
  2. `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  3. `sn` varchar(32) DEFAULT '' COMMENT '唯一键',
  4. `name` varchar(32) DEFAULT '' COMMENT '姓名',
  5. `addr` varchar(256) DEFAULT '' COMMENT '地址',
  6. PRIMARY KEY (`id`),
  7. UNIQUE KEY `sn` (`sn`)
  8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

往里面插入点数据


 
  1. INSERT INTO test2
  2. VALUES
  3. (NULL, '01', 'a', 'aaa'),
  4. (NULL, '02', 'b', 'bbb'),
  5. (NULL, '03', 'c', 'ccc'),
  6. (NULL, '04', 'd', 'ddd');

我们运行如下语句:


 
  1. INSERT INTO test2 (sn, name, addr)
  2. VALUES
  3. ('02', 'bb', 'bbbb')
  4. ON DUPLICATE KEY
  5. UPDATE
  6. name = VALUES(name),
  7. addr = VALUES(addr);

结果如下:

mysql 中 replace into 与 insert into on duplicate key update 的用法和不同点实例分析

每运行一次上面的语句,虽然影响行数为0,但表test2的自增字段就加1。

mysql 中 replace into 与 insert into on duplicate key update 的用法和不同点实例分析

显然如果insert into on duplicate key update语句仅仅只是在原记录基础上进行更新操作的话,自增字段是不会自动加1的,说明它也进行了记录删除操作。

先插入记录,如果不成功,则删除原记录,但保留了除update语句后字段的值,然后把保留的值与需要更新的值合并,然后插入一条新记录。

总结:

replace into 与 insert into on duplicate key update都是先尝试插入记录,如果不成功,则删除记录,replace into不保留原记录的值,而insert into on duplicate key update保留。然后插入一条新记录。

更多关于MySQL相关内容感兴趣的读者可查看本站专题:《MySQL查询技巧大全》、《MySQL常用函数大汇总》、《MySQL日志操作技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》及《MySQL数据库锁相关技巧汇总》

希望本文所述对大家MySQL数据库计有所帮助。

分享到:

相关信息

  • SQL Server 数据库中的收缩数据库和文件操作

    收缩数据文件通过将数据页从文件末尾移动到更靠近文件开头的未占用的空间来恢复空间,在文件末尾创建足够的空间后,可取消对文件末尾的数据页的分配并将它们返回给文件系统,本文给大家介绍SQL Server 数据库中的收缩数据...

    2023-10-27

  • dbeaver配置SQL server连接实现

    一、需要java jdk环境,我用的比较新 二、dbeaver新建连接SQL server...

    2023-10-27

系统教程栏目

栏目热门教程

人气教程排行

站长推荐

热门系统下载