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_id
If 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.