MySQL 如果数据存在则不插入

MySQL 如果数据存在则不插入

INSERT INTO table_name(field1,field2,....)
SELECT  field1,field2,.... FROM DUAL WHERE NOT EXISTS (SELECT * FROM table_name where field = ?)

在MySQL中有SELECT必定存在FROM,要不然会报语法错误,DUAL表是纯粹为了满足 SELECT * FROM 习惯而存在的表,MySQL会自动忽略对该表的引用。

INSERT INTO t_cashaccess_detail 
(deviceNo, deviceIp, paymentTarget, targetNum, num, cashType, oType, inTime, deviceType, accessTime, creatTime) 

SELECT 'AU0000103', '10.1.6.103', '1', 'JJ84846', '10', '1', '1', '2018-08-28 18:00:30', '6', '2018/8/28 18:35:30', NOW() 

FROM dual WHERE NOT EXISTS (select * from t_cashaccess_detail where t_cashaccess_detail.deviceNo = 'AU0000103');

数据库中不存在AU0000103，插入成功 数据库中存在AU0000103，插入失败