MySQL Insert Insert Condition judgment: insert if not present

Absrtact: We often need to make a bulk insert of SQL, which requires that the record does not exist and is inserted, and the presence is not inserted. What if an INSERT statement is used?

Normal Insert into insert:

INSERT INTO card(cardno, cardnum) VALUES(‘1111‘, ‘100‘);INSERT INTO card(cardno, cardnum) VALUES(‘2222‘, ‘200‘);...

For normal insert inserts, if you want to ensure that no duplicate records are inserted, we only have to create a unique constraint implementation for a field (for example, the Cardno card number cannot be duplicated);

Is there a scenario that does not create a unique constraint, only through the INSERT into statement?

Answer: Yes, the specific syntax for INSERT into IF EXISTS is as follows:

INSERT INTO table(field1, field2, fieldn) SELECT ‘field1‘, ‘field2‘, ‘fieldn‘ FROM DUAL WHERE NOT EXISTS(SELECT field FROM table WHERE field = ?)

The DUAL is a temporary table that does not need to be physically created, so it can be used.

The transformation for the card example above is as follows:

INSERTinto card (Cardno, Cardnum)SELECT' 111 ',' 100 'From DUALWHEREnot EXISTS (SELECT Cardno from card WHERE Cardno = ' 111 ');  INSERT into card (Cardno, cardnum) SELECT ' 222 ', ' $ ' from DUAL WHERE not EXISTS (
                          
                           select Cardno from 
                           card WHERE Cardno = ' 222 ');   
                               

Get!

MySQL Insert Insert Condition judgment: insert if not present