人大金仓数据库KingbaseES DML兼容MySQL ignore

站长

2024年03月19日 21:34 · 阅读数 6

关键字：

容错机制 DML ignore、人大金仓、KingbaseES

1. 什么是DML ignore？

MySQL 的很多语句都可以指定IGNORE 关键字，关键字指定后可以将一些错误降级为warning，从而不会引发整条语句或事务的回滚。对于多行语句，ignore 也会跳过一些错误的行而不是abort 这个语句

2. 支持ignore的场景

能够支持IGNORE 的statement 包含以下几个方面：

Create Table … SELECT , IGNORE 关键字在AS SELECT 语句之前，该语句并不会忽略CREATE 和SELECT 子句的错误，而对于insert into table 部分，如果违反唯一约束的行那么会将其忽略掉。
DELETE 语句会忽略执行过程中产生的错误行。
INSERT 语句，对于唯一约束、主键的重复行，那么会忽略错误并丢弃该行，而对于类型转换的错误，会将其转换为一个最接近的错误代替。
LOAD DATA, LOAD XML 会忽略违反约束的重复行。
UPDATE 语句参考INSERT 的描述。

3. MYSQL模式下可以ignore的错误

约束类，not null、check、主键、唯一键、外键约束。
分区的写入不匹配的错误。
除零、对数为零的表达式求值的错误。
表达式求值的类型转换错误。
表达式求值的数据溢出的错误。
子查询超过一行的错误。
View check 的错误。
所有的行级错误都会被丢弃。
所有的行级错误通过warnings 打印。

4. 不同错误处理原则及示例

4.1 不同约束

4.1.1 Not null 约束

Not null错误转换规则：

数值类型插入或更新的值为null，转换后值为0
字符类型插入或更新值为null ，转换后为 (空字符串)
时间类型插入或更新值为null ，当前时间戳
BLOB,TEXT类型插入或更新值为null ，(空字符串)
枚举类型（ENUM, SET）转换为类型的第一个元素
同时违反多个约束时，预期报多个错误

drop table if exists t_num1;

create table t_num1(c1 smallint not null,c2 integer not null ,c3 bigint not null,c4 float not null,c5 decimal(15,2) not null,c6 number not null,c7 boolean not null);

\d+ t_num1;

insert into t_num1 values(null,null,null,null,null,null,null);

insert ignore into t_num1 values(null,null,null,null,null,null,null);

select * from t_num1;

drop table if exists t_char;

create table t_char (c1 char(1) not null ,c2 varchar(2) not null,c3 character varying(3)not null,c4 character(4)not null);

insert into t_char values(null,null,null,null);

insert ignore into t_char values(null,null,null,null);

select * from t_char;

4.1.2 Check

drop table if exists t_tinyint;

create table t_tinyint(c tinyint not null);

alter table t_tinyint add constraint ck1 check (c<100);

insert ignore into t_tinyint values(101);

select * from t_tinyint;

4.1.3 主键

Drop table if exists t1;

Create table t1(c1 int primary key);

insert into t1 values('10');

insert ignore into t1 values('10');

select * from t1;

4.1.4 唯一键

Drop table if exists t1;

Create table t1(c1 int unique);

insert into t1 values(1);

insert ignore into t1 values(1);

select * from t1;

4.1.5 外键约束

drop table if exists veh_uid;

drop table if exists veh_loan;

--parent表

create table veh_loan(veh varchar(20) primary key);

insert into veh_loan values ('zishu'),('chouniu'),('yinhu'),('maotu');

--child表

create table veh_uid(id int, veh varchar(20) not null references veh_loan(veh) on delete set default);

insert into veh_uid values (1,'zishu'),(2,'chouniu'),(3,'yinhu'),(4,'maotu');

update veh_loan set veh='aaa' where veh='zishu';

update ignore veh_loan set veh='aaa' where veh='zishu';

select * from veh_loan;

select * from veh_uid;

4.2 分区的写入不匹配的错误

drop table if exists vehicles2;

CREATE TABLE vehicles2(

id int ,

category int NOT NULL,

name text check (length(name)<10),-- 车辆名字

color text, -- 车身颜色

weight float, -- 车身重量

area text, -- 产地

madedate date NOT NULL -- 出厂日期

) PARTITION BY LIST(category);

CREATE TABLE vehicles2_unknow PARTITION OF vehicles2

FOR VALUES IN (0);

CREATE TABLE vehicles2_bikes

PARTITION OF vehicles2

FOR VALUES IN (1);

CREATE TABLE vehicles2_cars

PARTITION OF vehicles2

FOR VALUES IN (2);

CREATE TABLE vehicles2_trucks

PARTITION OF vehicles2

FOR VALUES IN (3);

CREATE TABLE vehicles2_ambulances

PARTITION OF vehicles2

FOR VALUES IN (4);

CREATE TABLE vehicles2_others

PARTITION OF vehicles2

FOR VALUES IN (5,6,7);

insert into vehicles2 values (12,8,'A002','WHITE',null,null,'2017-09-30');

insert ignore into vehicles2 values (12,8,'A002','WHITE',null,null,'2017-09-30');

select * from vehicles2;

4.3 表达式相关错误

ERRCODE_INVALID_TEXT_REPRESENTATION 拼写检查错误

1、类型的输入前面为数值，后面为字母或其他字符，预期截断取数值

2、类型前面为字母或其他字符，后边数字 ,插入失败

错误名称

处理原则

备注

RRCODE_INVALID_TEXT_REPRESENTATION 拼写检查错误

数值类型：

1、类型的输入前面为数值，后面为字母或其他字符，预期截断取数值

2、类型前面为字母或其他字符，后边数字 ,插入失败

ERRCODE_DIVISION_BY_ZERO

除零错误

Oracle模式错误，与MYSQL不相关

ERRCODE_INVALID_ARGUMENT_FOR_LOG

无效参数

Oracle模式错误，与MYSQL不相关

ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE

数值类型值域超出数值范围所导致的错误。

越界转换分析原则：

1、小于最小值则返回该目标类型的最小值。

2、大于最大值则返回该目标类型的最大值。

ERRCODE_DATETIME_VALUE_OUT_OF_RANGE

时间日期类型值域检查时越界而产生的错误

格式匹配且均为数值，输入不合理的非法值时，使用ignore后，系统将返回date、datetime、timestamp三种类型对应的系统最小值

4714-11-24 00:00:00 BC

time的默认值 00:00:00

year的隐式默认值为当前年份。

ERRCODE_STRING_DATA_RIGHT_TRUNCATION

字符类型长度超过显示的typmod长度而报错

测试点：

1、非严格模式下（sql_mode取值不包含STRICT_ALL_TABLES），默认场景，直接dml，超长截断插入报warning

2、验证严格模式（sql_mode取值包含STRICT_ALL_TABLES），严格模式下，直接dml超长报warning，dml ignore警告截断处理

4.3.1 除零、对数为零的表达式求值的错误。

Drop table if exists t1;

Create table t1(c1 int);

--该种错误只针对oracle模式，mysql不存在

Insert into t1 select 5/0;

select * from t1;

4.3.2 表达式求值的类型转换错误。

Drop table if exists t1;

Create table t1(c1 int);

Insert into t1 values('123abc');

Insert ignore into t1 values('123abc');

Select * from t1;

4.3.3 表达式求值的数据溢出的错误。

drop table if exists tuint8;

create table tuint8(c1 uint8);

--uint8 + tinyint

insert into tuint8 select 18446744073709551615::uint8 + 127::tinyint;

insert ignore into tuint8 select 18446744073709551615::uint8 + 127::tinyint;

select * from tuint8;

4.3 子查询超过一行的错误。

Drop table if exists emp;

CREATE TABLE emp (

empno int PRIMARY KEY,

ename varchar(10) not null,

hiredate datetime ,

mgr int,

sal decimal(7,2),

comm decimal(7,2),

deptno int

);

INSERT INTO emp VALUES (7782, 'clark','2015-01-02 10:10:10',2233, 2000, 1000, 10);

INSERT INTO emp VALUES (7783, 'king','2020-01-02 10:30:10',7782, 2000, null, 10);

INSERT INTO emp VALUES (7784, 'miller','2021-04-02 20:10:10',2233, 3000, 500, 20);

INSERT INTO emp VALUES (7785, 'smith', '2018-01-02 10:10:10', 7882, 4000, 1000,20);

INSERT INTO emp VALUES (7882, 'lilei', '2022-03-02 10:30:00',2233, 5000, 1000,40);

INSERT INTO emp VALUES (7883, 'lucy','2018-01-02 10:10:10','7985', 7000,'2000', 10);

INSERT INTO emp VALUES (7884, 'mery', '2015-09-01 10:10:10',2233, 4500, 800,20);

INSERT INTO emp VALUES (7885, 'jack', '2016-01-02 10:10:10',7982, 5000, 1000,30);

INSERT INTO emp VALUES (7982, 'zhangsan', '2017-01-02 10:10:10',6500, 2000, null,30);

INSERT INTO emp VALUES (7983, 'lisi', '2018-01-02 10:10:10',2233, 2000, 2000,40);

INSERT INTO emp VALUES (7984, 'wangwu', '2017-03-02 10:10:10',2233, 3000, 500,30);

INSERT INTO emp VALUES (7985, 'hanmeimei', '2020-04-02 10:10:10',4233, 3000, 1000,40);

create table e1 as select * from emp;

create table eo as select * from emp where 1!=1;

alter table eo add column c1 varchar(20);

--子查询返回结果多余1条

SELECT * ,(SELECT ename as c1 FROM e1 where comm is null) FROM EMP; --error

truncate table eo;

insert ignore into eo SELECT * ,(SELECT ename as c1 FROM e1 where comm is null) FROM EMP;

select * from eo;

4.5 View check 的错误。

drop table if exists tb1 cascade;

create table tb1(c1 int unique, c2 text unique);

insert into tb1 values(1,'aaa');

insert into tb1 values(2,'bbb');

insert into tb1 values(3,'ccc');

drop table if exists tba;

create table tba(c1 int,c2 text, c3 VARCHAR, c4 float, c5 int,

constraint p1 primary key(c1), constraint c2 check (c2='a' or c2='b'),constraint p3 unique(c4), constraint fk foreign key(c5) references tb1(c1));

insert into tba values(1,'a','test',4,1);

--创建视图

create view v_t as select * from tba;

--主键约束

insert into v_t values(1,'a','test',4,1);

--唯一约束

insert into v_t values(2,'a',2,4,1);

--check 约束

insert into v_t values(6,'ab',1,5,1);

--unique 约束

insert into v_t values(3,'b',1,4,1);

--外键约束

insert into v_t values(4,'a','test',4,5);

--使用ignore语法

insert ignore into v_t values(1,'a','test',4,1);

insert ignore into v_t values(2,'a',2,4,1);

insert ignore into v_t values(6,'ab',1,5,1);

insert ignore into v_t values(3,'b',1,4,1);

insert ignore into v_t values(4,'a','test',4,5);

select * from v_t;

select * from tba;

参考资料

提供该题目相关内容在产品手册中可以系统学习的位置，例如：

《KingbaseES数据库性能调优指南》