关键字:
容错机制 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; |
参考资料
提供该题目相关内容在产品手册中可以系统学习的位置,例如: