Mysql establishes unique index implementation inserts duplicate automatic update _mysql

Objective

When we insert data into the database, we need to determine whether a field exists, if there is an update operation, if it does not exist, the insert operation, if the first query once to determine whether there is, and then perform an INSERT or update operation, it is very inconvenient. Here to share a method to facilitate everyone to achieve this function, let's take a look at it.

On DUPLICATE KEY UPDATE

This is the time to create a unique index for this field (or several fields), while inserting or updating with the following SQL statement:

INSERT into table (IDs, 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

To add a unique index sql:

ALTER TABLE table_name ADD UNIQUE (column_list)

Note: If you do not have a unique index, you cannot trigger ON DUPLICATE KEY such an event, DUPLICATE meaning repetition, such as when a field does not have a unique index added, and you do not think that the same value is duplicated, so you must establish a unique index for the fields that cannot be duplicated.

Example description

such as a requirement is to provide an interface to the IOS side, used to report the user ID, equipment, online status and other information, the interface in the GET form of parameters, the user each open or exit the APP to request the interface.

The interface receives the request, logs the information to the database, makes a judgment based on the user ID, and if it exists, updates it without being added.

This time the SQL statement uses the statement described above on duplicate key update .

INSERT into table (IDs, 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 example, where the user_id unique index field, if insert it user_id already exists, will trigger an update instead of an insert, which is equivalent to executing:

Update table Set user_id = 2479031 token = 232 where user_id = 2479031

This is the only index and ON DUPLICATE KEY UPDATE usage.

Summarize

The above is the entire content of this article, I hope to be able to learn or work to bring certain help, if you have questions you can message exchange.