LightDB 23.2兼容mysql insert ignore into语法

前言

最近工作中，使用到了insert ignore into语法，感觉这个语法还是挺有用的，就记录下来做个总结。
insert ignore into : 忽略重复的记录，直接插入数据。

包括两种场景：
1、插入的数据是主键冲突时
insert ignore into会给出warnings，show warnings就可以看到提示主键冲突；并且本次插入无效。如果使用insert into语句时，如果主键冲突直接报错。

2、没有主键冲突时，直接插入数据
insert into 与 insert ignore into 都是直接插入数据

兼容时，只语法做了兼容，其功能未实现，也就是俗称的语法糖。

测试

字段不带primary key

create table test ( si smallint,i int,bi bigint,ft float4,dl float,num numeric,txt text,var varchar,bp char(10),c char);
insert ignore into test values('1'::varchar,'2'::varchar,'3'::varchar,'4'::varchar,'5'::varchar,'6'::varchar,7::numeric,8::numeric,9::numeric,5::numeric);
insert ignore into test values('1'::text,'2'::text,'3'::text,'4'::text,'5'::text,'6'::text,7::float8,8::float8,9::float8,5::float8);
insert ignore into test values('1'::char(10),'2'::char(10),'3'::char(10),'4'::char(10),'5'::char(10),'6'::char(10),7::float4,8::float4,9::float4,5::float4);
insert ignore into test values('1'::char,'2'::char,'3'::char,'4'::char,'5'::char,'6'::char,7::int2,8::int2,9::int2,5::int2);
insert ignore into test values('1'::numeric,'2'::numeric,'3'::numeric,'4'::numeric,'5'::numeric,'6'::numeric,7::int4,8::int4,9::int4,5::int4);
insert ignore into test values('1'::float8,'2'::float8,'3'::float8,'4'::float8,'5'::float8,'6'::float8,7::int8,8::int8,9::int8,5::int8);
insert ignore into test values('1'::float4,'2'::float4,'3'::float4,'4'::float4,'5'::float4,'6'::float4,7,8,9,5);
insert ignore into test values('1'::smallint,'2'::smallint,'3'::smallint,'4'::smallint,'5'::smallint,'6'::smallint,7,8,9,5);
insert ignore into test values('1'::bigint,'2'::bigint,'3'::bigint,'4'::bigint,'5'::bigint,'6'::bigint,7,8,9,5);
insert ignore into test values('1'::int4,'2'::int4,'3'::int4,'4'::int4,'5'::int4,'6'::int4,7,8,9,5);
insert ignore into test values('1.1'::varchar,'2.1'::varchar,'3.1'::varchar,'4.1'::varchar,'5.1'::varchar,'6.1'::varchar,7.1,8.1,9.1,5);
insert ignore into test values('1e1'::varchar,'2e1'::varchar,'3e1'::varchar,'4e1'::varchar,'5e1'::varchar,'6e1'::varchar,7.1,8.1,9.1,5);
select * from test;
 si | i  | bi | ft  | dl  | num | txt | var |     bp     | c 
----+----+----+-----+-----+-----+-----+-----+------------+---
  1 |  2 |  3 |   4 |   5 |   6 | 7   | 8   | 9          | 5
  1 |  2 |  3 |   4 |   5 |   6 | 7   | 8   | 9          | 5
  1 |  2 |  3 |   4 |   5 |   6 | 7   | 8   | 9          | 5
  1 |  2 |  3 |   4 |   5 |   6 | 7   | 8   | 9          | 5
  1 |  2 |  3 |   4 |   5 |   6 | 7   | 8   | 9          | 5
  1 |  2 |  3 |   4 |   5 |   6 | 7   | 8   | 9          | 5
  1 |  2 |  3 |   4 |   5 |   6 | 7   | 8   | 9          | 5
  1 |  2 |  3 |   4 |   5 |   6 | 7   | 8   | 9          | 5
  1 |  2 |  3 |   4 |   5 |   6 | 7   | 8   | 9          | 5
  1 |  2 |  3 |   4 |   5 |   6 | 7   | 8   | 9          | 5
  1 |  2 |  3 | 4.1 | 5.1 | 6.1 | 7.1 | 8.1 | 9.1        | 5
 10 | 20 | 30 |  40 |  50 |  60 | 7.1 | 8.1 | 9.1        | 5
(12 rows)

字段带primary key

create table tt(c1 int primary key, c2 varchar(50));
insert into tt values(1, 'aaa'), (2, 'bbb'), (3, 'ccc'); 
insert into tt values(1, 'aaa'), (2, 'bbb'), (3, 'ccc');  --error
ERROR:  duplicate key value violates unique constraint "tt_pkey"
DETAIL:  Key (c1)=(1) already exists.
insert into tt values(1, 'aaa'), (2, 'bbb'), (4, 'ddd');  --error
ERROR:  duplicate key value violates unique constraint "tt_pkey"
DETAIL:  Key (c1)=(1) already exists.
drop table tt;

总结

（1）如insert ignore into使用在存储过程中，表现则会与oracle不同，Oracle能正常执行完整个存储过程，LigtDB则报错。
（2）功能规划到下个版本，进行完善。