MySQL creates a unique index to enable automatic update of repeated inserts and mysql automatic update

MySQL creates a unique index to enable automatic update of repeated inserts and mysql automatic update

Preface

When we insert data into the database, we need to determine whether a field exists. If yes, we need to perform the update operation. If no, we need to perform the insert operation. If each query first checks whether a field exists, it is inconvenient to execute the insert or update operation. The following is a method to help you implement this function. Let's take a look at it.

ON DUPLICATE KEY UPDATE

At this time, you can create a unique index for this field (or several fields) and use the following SQL statement to insert or update the field:

INSERT INTO table (id, user_id, token) VALUES (NULL, '2479031', '232') ON DUPLICATE KEY UPDATE user_id = VALUES(user_id), token = VALUES(token), online = VALUES(online)

MySQL adds a unique index to a field

Add a unique index SQL:

ALTER TABLE table_name ADD UNIQUE (column_list)

Note:If no unique index exists, it cannot be triggered.ON DUPLICATE KEY Such events,DUPLICATE Indicates the meaning of repetition. If a field does not have a unique index, it is not considered that the same value is already inserted. Therefore, a unique index must be created for non-repeated fields.

Instance description

For example, an interface is provided to the IOS end for reporting user id, device, online status, and other information.GET Request parameters.

The interface receives the request, records the information to the database, and determines based on the user id. If the request exists, it is updated. If the request does not exist, it is added.

In this case, the SQL statement useson duplicate key update Statement.

INSERT INTO table (id, user_id, token) VALUES (NULL, '2479031', '232') ON DUPLICATE KEY UPDATE user_id = VALUES(user_id), token = VALUES(token), online = VALUES(online)

Take this SQL statement as an example.user_id Is a unique index field, ifinsert This timeuser_idIf it already exists, the update will be triggered instead of the insert operation, which is equivalent to executing:

update table set user_id = 2479031 token = 232 where user_id = 2479031

This is the unique index andON DUPLICATE KEY UPDATE.

Summary

The above is all about this article. I hope it will help you in your study or work. If you have any questions, please leave a message.