How to implement insert if none and update if yes in MySql

summary

In some scenarios, there may be such a requirement: insert if there is no record, and update if there is a record. For example, when adding a new user, using the ID number as the unique identifier, if you first check whether the record exists before deciding whether to insert or update, there will inevitably be problems under high concurrency conditions. This article provides three solutions.

Solution 1: Lock

This problem can be solved by using synchronized locks, ReentranLock locks or distributed locks. The disadvantage is that locking will affect performance. Methods 2 and 3 are both database-level solutions, and I personally feel they are better than method 1.

Solution 2: Unique and Replace Into ... SELECT ...

First, add a unique index to the unique field: ALTER TABLE tb_name ADD UNIQUE (col1, col2...). The unique index can ensure the uniqueness of the data.

After adding a unique index, inserting the same data through INSERT INTO will result in an error. In this case, you need to use REPLACE INTO to insert the data. The usage is the same. When inserting data through REPLACE INTO, if the same data exists, the previous record will be deleted and the data will be reinserted. The disadvantage is that there is a process of deleting first and then inserting, and SQL needs to consider all data columns, otherwise the data of some columns will be lost. The disadvantage is that creating a unique index will affect insertion efficiency. The following are specific examples.

# Create index ALTER TABLE user ADD UNIQUE (id_card);
# Assume that the user table has only three fields: id, name, and id_card, and the id field is auto-incremented.
# Now you need to insert a record with name=ly, id_card=142733.
# However, if there is a record with id_card=142733, just change name=ly.
REPLACE INTO user (id,name,id_card) 
SELECT id,'ly',142733 FROM user RIGHT JOIN (SELECT 1) AS tab 
ON user.id_card = 142733;

Through RIGHT JOIN (SELECT 1), if there is a record with id_card=142733, after executing SQL, the original id will be saved in the temporary result set and inserted together with name and id_card. If the record does not exist, null will be inserted as id along with name and id_card. Final Implementation

Solution 3: Use pre-insert statements to determine whether records exist

Use the pre-insert statement to try to insert and determine whether the modified record is greater than 0. If it is greater than 0, it means the insertion is successful. If it is 0, it means the record already exists and needs to be updated.

# Pre-insert INSERT INTO user (name,id_card)
SELECT 'ly',142733 FROM DUAL 
WHERE NOT EXISTE (SELECT id_card FROM user WHERE id_card = 142733);
# If the pre-insert statement is successfully inserted (number of modified records = 1), no subsequent operations are required. Otherwise, perform an update operation.
UPDATE user SET name = 'ly' WHERE id_card = 142733;

Through the NOT EXISTE condition, if there is a record with id_card=142733, the record in the pseudo table DUAL is empty, and the pre-insert statement modifies the record to 0. At this time, an update operation needs to be performed.

If there is no record with id_card=142733, the pseudo table DUAL records one row with the content 'ly',142733. The pre-insert statement modifies the record to 1, and there is no need to execute the update statement.

Mysql batch insert and update performance optimization

For inserting and updating large amounts of data, a lot of time will be consumed due to performance bottlenecks such as io/cpu. The current mainstream optimizations mainly include pre-compilation, inserting multiple data with a single SQL statement, and transaction insertion. The following is a detailed introduction:

Single insert (Mybatis)

INSERT INTO SYS_CITY (CITY_CODE, CITY_NAME, PROVINCE_NAME, ALIAS, ABBRE_PY) VALUES
(${cityCode}, ${cityName}, ${provinceName}, ${alias}, ${abbrePy})

Single precompiled insert (Mybatis)

Precompilation can save the parsing time of MySQL service. Mytatis uses #variable

INSERT INTO SYS_CITY (CITY_CODE, CITY_NAME, PROVINCE_NAME, ALIAS, ABBRE_PY) VALUES
(#{cityCode}, #{cityName}, #{provinceName}, #{alias}, #{abbrePy})

Insert multiple records into a single SQL statement

That is to splice sql, insert multiple or update multiple data in one sql.

INSERT INTO SYS_CITY (CITY_CODE, CITY_NAME, PROVINCE_NAME, ALIAS, ABBRE_PY) VALUES
("cityCode1", "cityName1", "provinceName1" "alias1", "abbrePy1"),("cityCode2", "cityName2", "provinceName2" "alias2", "abbrePy2")

Reasons for fast

1. The amount of logs after merging (MySQL binlog and innodb transaction logs) is reduced, which reduces the amount and frequency of log flushing, thereby improving efficiency;

2. Reduce network transmission IO by merging SQL statements;

3. Reduce the number of SQL statement parsing by merging SQL statements;

Precautions

1. The length of the database SQL is limited. Do not overflow the SQL length, otherwise an error will be reported;

2. When inserting out of order, the speed exceeds the capacity of innodb_buffer. Each index positioning involves more disk read and write operations, and the performance decreases rapidly.

Transaction Insert

Transaction insertion means opening a transaction before insertion, and closing the transaction and committing it after the insertion.

Reasons for fast

1. When performing an INSERT operation, MySQL will create a transaction internally, and the actual insertion processing operation will be performed within the transaction. By using transactions, the cost of creating transactions can be reduced;

Precautions

1. The transaction cannot be too large. MySQL has an innodb_log_buffer_size configuration item. When the transaction exceeds this, the disk will be flushed, resulting in performance degradation.

2. When inserting out of order, the speed exceeds the capacity of innodb_buffer. Each index positioning involves more disk read and write operations, and the performance decreases rapidly.

Test Results

Environment: i5-4200U 1.6GHZ, 12G memory, solid state drive

Summarize

The combination of merged SQL and transaction insertion is the most efficient. When inserting out of order, the speed exceeds the capacity of innodb_buffer. Each index positioning involves more disk read and write operations, and the performance degrades quickly. Try to use non-out of order methods. The above is my personal experience. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.