数据库防止重复插入相同数据,使用幂等方案(并发插入情况,比如mq接受到消息后插入数据表)
一、错误方案(使用的insert into …where not exists (select …))
insert into pointcloud
(
id ,
mesh ,
tile,
url ,
version,
editor,
create_time,
update_time)
values
(
'1234' ,
'123',
'123',
'url',
'1',
'system',
now(),
now()
)
where not exists (select id from pointcloud where mesh ='123' and tile = '123' )
二、正确方案(使用insert into … select … where not exists (select … from …where …))
INSERT INTO pointcloud
(
id ,
mesh ,
tile,
url ,
version,
editor,
create_time,
update_time
)
SELECT
'1234' ,
'123',
'123',
'url',
'1',
'system',
now(),
now()
WHERE NOT EXISTS(
SELECT 1
FROM pointcloud
WHERE mesh ='123' and tile = '1235'
)