数据库表中不建索引，在插入数据时，通过sql语句防止重复添加（转载）

INSERT IGNORE INTO table(aaa,bbb)
SELECT '1111','2222' FROM DUAL
WHERE NOT EXISTS(
  SELECT id FROM table WHERE bbb= '2222'
)

mybaits

<insert id="addItemCharacteristic" parameterType="com.orderalliance.entity.CharacteristicDTO">
        <!--INSERT INTO table (item_id,characteristic) VALUES(#{aaa,jdbcType=BIGINT},#{bbb,jdbcType=VARCHAR})-->
        <!--此sql语句防止数据重复添加-->
        INSERT IGNORE INTO table (aaa,bbb)
        SELECT #{111,jdbcType=BIGINT},#{222,jdbcType=VARCHAR} FROM DUAL
        WHERE NOT EXISTS(
            SELECT id FROM table WHERE bbb= #{bbb,jdbcType=VARCHAR}
        )
</insert>

关键代码

INSERT IGNORE INTO 表名('字段') 
 SELECT '字段需要插入时的数据' FROM dual 
WHERE NOT EXISTS
 (SELECT * FROM '表名' WHERE '需要被限制不重复的字段' = '被限制字段本次插入的数据')


关键词

IGNORE

NOT EXISTS