MySQL 当记录不存在时insert

一、使用场景

我们使用MYSQL的时候，为了防止重复的插入，我们就需要先判断是否数据库中时候存在相同的记录。MySQL我们借助于insert into select from的语法进行处理这种问题。

二、示例

1、单条插入：通过dual伪表来判断。

INSERT INTO clients
(client_id, client_name,)
SELECT 1, 'Calvin', 
FROM dual
WHERE not exists (select * from clients
where clients.client_id = 1);

JDBC插入语句示例

INSERT INTO tbl_teahouse_member
 (player_id,house_id ,status) 
 SELECT ?,? ,? FROM dual 
WHERE not exists (select * from tbl_teahouse_member 
WHERE house_id = ? and player_id= ? )

2、多条插入：通过关联表

INSERT INTO clients
(client_id, client_name)
SELECT supplier_id, supplier_name
FROM suppliers
WHERE not exists (select * from clients
where clients.client_id = suppliers.supplier_id);