When Mysql inserts a record, checks if the record already exists, is updated, does not exist, inserts a record SQL

In the process of developing database-related logic, we often check to see if such a record exists in the table, if there is an update or no action, and if there is no record, a new record is inserted.

This logic can be done with two SQL statements.

SELECT COUNT (*) from xxx WHERE id=xxx;

if (x = = 0)

INSERT into XXX VALUES;

Else

UPDATE xxx SET;

But there is a loss of performance in this operation, and the code structure feels a bit ugly.

In fact, MySQL provides support for the above logic to be done in an SQL statement.

Official documents are as follows:

MySQL provides many extentions to SQL which help performance on many common use scenarios. Among these is INSERT ... SELECT, INSERT ... On DUPLICATE KEY UPDATE, and REPLACE.

I rarely hesitate to use the above since they is so convenient and provide real performance benefits in many situations. MySQL has other keywords which is more dangerous, however, and should is used sparingly. These includeinsert DELAYED, which tells MySQL that it isn't important to INSERT the data immediately (say, e.g. , in a logging situation). The problem with the under high load situations the insert might be delayed indefinitely, causing the insert Queu E to Baloon. You can also give MySQL index hints on which indices to use. MySQL gets it right most of the time and the time it doesn ' t it's usually because of a bad scheme or poorly written query.

The important thing is the above mentioned:

INSERT ... SELECT

INSERT ... On DUPLICATE KEY UPDATE

INSERT ... On DUPLICATE REPLACE

For example, if you want to insert a piece of data into a table, if the table does not have that data inserted, if the data already exists, it will not be inserted.

First, when you create a table, you set the fields that do not need to be duplicated to unique, and then, when you insert, you use the Insert Ignore statement.

For example: (the database is MYSQL5)
Create a table to store users:
CREATE TABLE User_info
(
UID Mediumint (Ten) unsigned not NULL auto_increment primary key,
Last_Name char () NOT NULL,
First_Name char () NOT NULL,
Unique (last_name, first_name)
);
ALTER TABLE Anser add UNIQUE (last_name,first_name)
Insert data:
Insert Ignore into user_info (last_name,first_name) VALUES (' x ', ' Y ');
This way, if the table already exists last_name= ' x ' and first_name= ' y ' data, it will not be inserted, and if not, a new data will be inserted.

The above is a usage, you can also use the INSERT .... SELECT to implement.

http://blog.csdn.net/langeldep/article/details/6241155

Ask the Blogger to refer to the Replace statement
Http://dev.mysql.com/doc/refman/5.0/en/replace.html

When Mysql inserts a record, checks if the record already exists, is updated, does not exist, inserts a record SQL