当前位置：首页 > article >正文

OceanBase数据库使用 INSERT 语句违反唯一约束冲突解决办法及两者差异分析

article 2025/1/20 3:39:25

当在OceanBase数据库上创建带有唯一性约束的表，在向表中插入唯一性约束的冲突的数据时会提示因违反唯一性约束报错，OceanBase在其官网上提供了两种解决策略，但其官网并未详细说明两种策略的差异，于是早上对两种策略进行一些测试，对比下两者的差异。
>如在测试中哪有错误的地方，还望对此熟悉的大牛多多指正，不胜感激。

一、创建带有唯一性约束表

[admin@obproxy-node ~]$ obclient -h 10.110.3.152 -uroot@sys#obcluster -pob@Passwd -P2883 -c -A oceanbase
Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 36
Server 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 changed
obclient [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: 0
 
obclient [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: 0
 
obclient [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: 0
 
obclient [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: 0
 
obclient [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的语句。