当字段重复时不添加:
判断字段是否重复
INSERT INTO table(field1, field2, fieldn)
SELECT 'field1', 'field2', 'fieldn'
FROM DUAL
WHERE NOT EXISTS(SELECT field FROM table WHERE field = ?)
示例/解释:
INSERT INTO 表A(
//要插入的字段
id,
device_id,
data,
create_by,
create_date,
update_by,
update_date,
remarks,
del_flag
) SELECT
//要插入的值,为空可以用null代替 不能带括号,否则Operand should contain 1 column(s)
#{id},
#{device.id},
#{data},
#{createBy.id},
#{createDate},
#{updateBy.id},
#{updateDate},
#{remarks},
#{delFlag}
FROM DUAL
//dual是为了构建查询语句而存在的表
WHERE NOT EXISTS(SELECT device_id,data FROM 表A WHERE device_id = #{device.id} AND data = #{data} )
//判断device_id,data相同的值是否存在,存在则不添加