How to check if data exists before inserting in mysql

Business scenario: The visitor's visit status needs to be recorded, but it cannot be recorded repeatedly

Before inserting, you can determine whether the data to be inserted exists. The business code is as follows:

INSERT INTO t_topic_vistor(user_id,topic_code) SELECT '218', 
'XQ33019920170811142528217' FROM DUAL WHERE NOT EXISTS(SELECT * 
FROM t_topic_vistor WHERE user_id = 218 and 
topic_code='XQ33019920170811142528217')

The syntax is as follows:

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

Additional knowledge: MySql inserts if it does not exist, updates or ignores if it exists

Preface

When inserting data, you may need to ignore or replace duplicate data (based on a certain field). This can be handled at the application layer, or you can use complex SQL statements to handle it (if you only know some simple SQL syntax). Of course, you can also use some simple SQL syntax, but it is not universal for all database types.

All the following examples are only for MySQL and cannot be used for other databases at will.

Examples

Table name: student

Table fields:

Initial table data:

Note: All the following examples require a UNIQUE index or PRIMARY KEY field to be present in the data to be inserted. The primary key id of the table is introduced here and set to auto-increment. You can see its changes later.

1. Insert if not present, update if present

1.1 on duplicate key update

If the inserted data will cause a conflict/duplicate in the UNIQUE index or PRIMARY KEY, execute an UPDATE statement, for example:

INSERT INTO `student`(`name`, `age`) VALUES('Jack', 19)
 ON DUPLICATE KEY 
 UPDATE `age`=19; -- If conflict will happen, the update statement is executed

-- 2 row(s) affected

The number of rows affected here is 2, because there is data with name='Jack' in the database. If this data does not exist, the number of rows affected is 1.

The latest table data is as follows:

1.2 replace into

If the inserted data will cause a conflict/duplicate in the UNIQUE index or PRIMARY KEY, delete the old data first and then insert the latest data, for example:

REPLACE INTO `student`(`name`, `age`) VALUES('Jack', 18);

-- 2 row(s) affected

The number of rows affected here is 2, because there is data with name='Jack' in the database, and the value of id will become 2, because it deletes the old data first and then inserts the data. The latest table data is as follows:

2. Avoid duplicate insertion

Keyword/sentence: insert ignore into, if the inserted data will cause a conflict/duplicate in the UNIQUE index or PRIMARY KEY, the operation will be ignored/no data will be inserted, for example:

INSERT IGNORE INTO `student`(`name`, `age`) VALUES('Jack', 18);

-- 0 row(s) affected

There is already data with name='Jack' here, so the newly inserted data will be ignored, the number of affected rows is 0, and the table data remains unchanged.

The above operation of judging whether data exists before inserting into MySQL is all the content that the editor shares with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.