OceanBase数据库使用 INSERT 语句违反唯一约束冲突解决办法及两者差异分析
当在OceanBase数据库上创建带有唯一性约束的表，在向表中插入唯一性约束的冲突的数据时会提示因违反唯一性约束报错，OceanBase在其官网上提供了两种解决策略，但其官网并未详细说明两种策略的差异，于是早上对两种策略进行一些测试，对比下两者的差异。
>如在测试中哪有错误的地方，还望对此熟悉的大牛多多指正，不胜感激。
一、创建带有唯一性约束表
[admin@obproxy-node ~]$ obclient -h 10.110.3.152 -uroot@sys#obcluster -pob@Passwd -P2883 -c -A oceanbaseWelcome to the OceanBase. Commands end with ; or \g.Your OceanBase connection id is 36Server version: OceanBase_CE 4.0.0.0 (r100000282022112511-dd289d2407609a88b1fcdf2be9e7c384cb8e19d0) (Built Nov 25 2022 11:58:08)Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.obclient [oceanbase]> use test;Database changedobclient [test]> create table customer(-> id number not null primary key,-> name varchar(10) not null,-> salary number,-> create_time datetime not null default current_timestamp-> );Query OK, 0 rows affected (0.132 sec)obclient [test]> desc customer;+-------------+---------------+------+-----+-------------------+-------+| Field | Type | Null | Key | Default | Extra |+-------------+---------------+------+-----+-------------------+-------+| id | decimal(10,0) | NO | PRI | NULL | || name | varchar(10) | NO | | NULL | || salary | decimal(10,0) | YES | | NULL | || create_time | datetime | NO | | CURRENT_TIMESTAMP | |+-------------+---------------+------+-----+-------------------+-------+4 rows in set (0.011 sec)
二、插入重复主键数据
-- 当表上有唯一性约束的时候，插入相同的记录，数据库会报错。提示因有主键冲突，数据未插入obclient [test]> insert into customer(id, name, salary) values (1,'zhangsan', 3000) ,(1,'lisi', 10003);ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'obclient [test]> select * from customer;Empty set (0.004 sec)
三、解决办法
根据OceanBase官网资料，提供两种避免约束冲突的办法，分别是INSERT IGNORE INTO和INSERT INTO ON DUPLICATE KEY UPDATE 两种办法可进行避免。
通过 INSERT IGNORE INTO 避免约束冲突，IGNORE 关键字可以忽略由于约束冲突导致的 INSERT 失败的影响。
通过 INSERT INTO ON DUPLICATE KEY UPDATE 避免约束冲突，可以指定对重复主键或唯一键的后续处理
说明
指定 ON DUPLICATE KEY UPDATE：当要插入的主键或唯一键有重复时，会用配置值替换待插入的值。
不指定 ON DUPLICATE KEY UPDATE：当要插入的主键或唯一键有重复时，插入报错
obclient [test]> insert ignore into customer(id, name, salary) values (1,'zhangsan', 3000) ,(1,'lisi', 10003);Query OK, 1 row affected (0.011 sec)obclient [test]> select * from customer;+----+----------+--------+---------------------+| id | name | salary | create_time |+----+----------+--------+---------------------+| 1 | zhangsan | 3000 | 2022-12-20 10:08:17 |+----+----------+--------+---------------------+1 row in set (0.003 sec)obclient [test]> insert into customer(id, name, salary) values (3,'zhaowu', 10003),(3, 'wangliu', 10004) on duplicate key update name=values(name);Query OK, 3 rows affected (0.011 sec)Records: 2 Duplicates: 1 Warnings: 0obclient [test]> select * from customer;+----+----------+--------+---------------------+| id | name | salary | create_time |+----+----------+--------+---------------------+| 1 | zhangsan | 3000 | 2022-12-20 10:08:17 || 3 | wangliu | 10003 | 2022-12-20 10:08:59 |+----+----------+--------+---------------------+2 rows in set (0.014 sec)obclient [test]> insert into customer(id, name, salary) values (5,'xiaoming', 10003),(6, 'xiaowang', 10004) on duplicate key update name=values(name);Query OK, 2 rows affected (0.012 sec)Records: 2 Duplicates: 0 Warnings: 0obclient [test]> select * from customer;+----+----------+--------+---------------------+| id | name | salary | create_time |+----+----------+--------+---------------------+| 1 | zhangsan | 3000 | 2022-12-20 10:08:17 || 3 | wangliu | 10003 | 2022-12-20 10:08:59 || 5 | xiaoming | 10003 | 2022-12-20 10:10:09 || 6 | xiaowang | 10004 | 2022-12-20 10:10:09 |+----+----------+--------+---------------------+4 rows in set (0.005 sec)obclient [test]> insert into customer(id, name, salary) values (7,'huawei', 10003),(7, 'aiguo', 10004) on duplicate key update name=values(name);Query OK, 3 rows affected (0.006 sec)Records: 2 Duplicates: 1 Warnings: 0obclient [test]> select * from customer;+----+----------+--------+---------------------+| id | name | salary | create_time |+----+----------+--------+---------------------+| 1 | zhangsan | 3000 | 2022-12-20 10:08:17 || 3 | wangliu | 10003 | 2022-12-20 10:08:59 || 5 | xiaoming | 10003 | 2022-12-20 10:10:09 || 6 | xiaowang | 10004 | 2022-12-20 10:10:09 || 7 | aiguo | 10003 | 2022-12-20 10:12:19 |+----+----------+--------+---------------------+5 rows in set (0.001 sec)obclient [test]> insert into customer(id, name, salary) values (8,'xiaoA', 10003),(8, 'xiaoB', 10004),(8, 'xiaoC', 10005) on duplicate key update name=values(name);Query OK, 5 rows affected (0.009 sec)Records: 3 Duplicates: 2 Warnings: 0obclient [test]> select * from customer;+----+----------+--------+---------------------+| id | name | salary | create_time |+----+----------+--------+---------------------+| 1 | zhangsan | 3000 | 2022-12-20 10:08:17 || 3 | wangliu | 10003 | 2022-12-20 10:08:59 || 5 | xiaoming | 10003 | 2022-12-20 10:10:09 || 6 | xiaowang | 10004 | 2022-12-20 10:10:09 || 7 | aiguo | 10003 | 2022-12-20 10:12:19 || 8 | xiaoC | 10003 | 2022-12-20 10:35:46 |+----+----------+--------+---------------------+6 rows in set (0.002 sec)
四、差异分析
通过反复测试，发现INSERT IGNORE INTO和INSERT INTO ON DUPLICATE KEY UPDATE在使用上也有个小小的差异，就是使用INSERT IGNORE INTO插入带有唯一性约束数据时，会插入第一条value后的语句， 而使用INSERT INTO ON DUPLICATE KEY UPDATE插入带有唯一性约束数据时会插入最后一条value的语句。
原文作者：尚雷的驿站；如有侵权，请联系管理员删除。
上一篇：
OGG 两个故障案例处理
请登录后发表评论
登录
全部评论
最新文章
- 记一次 Oracle 19c RAC 因为硬件时钟异常导致 CRS 无法正常启动故障处理记录
- AI万能胶：从挂钩到黏合，一切都靠“智”造
- 跟着博主学习 Halo 数据库带你高薪带你飞
- 【故障处理系列】elasticsearch 索引未正常分片导致集群状态异常如何分析处理
- Elasticsearch max_result_window 含义及参数调整
- Oracle ACE夜话--和首席-总监一起跟大家谈一谈分布式数据库
- openGauss 6.0.0 一主二备集群安装及使用zcbus实现Oracle到openGauss的数据全量和增量同步
- ES检测kibana端口是否宕并自动拉起进程
- clickhouse备份恢复学习分享
- 臭宝，满足您的需求，上大招，今天再分享一个我编写且非常好用处理Elasticsearch集群异常状态的脚本
