我想插入不重复的记录,mysql中支持这样的写法吗(我这个报错,如何改正)?
INSERT ProcessData_Import1(MachineType,BatchName,UnitType,CreateDate,TimeHour,UpperLimit,LowerLimit,SetPoint,ProcessValue,CO)
SELECT '国产1','B-国产1-20220120','dO2_03','2021/12/29 2:00:00','2021-12-29 02:00:00',0,0,40.000,42.051,0.000
FROM DUAL WHERE NOT EXISTS(SELECT 1 FROM ProcessData_Import1 WHERE BatchName = 'B-国产1-20220120' AND UnitType = 'dO2_03' AND CreateDate = '2021/12/29 2:00:00' LIMIT 1),
'国产1','B-国产1-20220120','dO2_03','2021/12/29 2:00:01','2021-12-29 02:00:01',0,0,40.000,42.013,0.000 WHERE NOT EXISTS
(SELECT 1 FROM ProcessData_Import1 WHERE BatchName = 'B-国产1-20220120' AND UnitType = 'dO2_03' AND CreateDate = '2021/12/29 2:00:01' LIMIT 1)
from DUAL是oracle的,mysql里不需要接这个东西,只需要select 对应的字段即可 。然后"NOT EXISTS"这个写法是标准sql,基本所有的关系型数据库都支持。
另外,存在条件里限制1行,这个完全没必要,数据库会在找到任意一条后马上返回的,不会全表扫
有个replace
REPLACE INTO users (id,name,age) VALUES(123, ‘chao’, 50);