Insert a data SQL statement if the data exists and the update does not exist

In mysql website production, we will encounter the need to simplify database operations to save a little bit of resources. If data exists, an SQL statement will be inserted if the update does not exist, next we will use the insert on duplicate key update instance,

Insert a data SQL statement if the data exists and the update does not exist
/*
In the creation of the mysql tutorial website, we will encounter the need to simplify the database tutorial operations to save a little bit of resources. If the data exists, the SQL statement will be inserted if the update does not exist, next we will use the insert on duplicate key update instance,
Syntax:

Insert [low_priority | delayed | high_priority] [ignore]
[Into] tbl_name [(col_name,...)]
Values ({expr | default },...),(...),...
[On duplicate key update col_name = expr,...]

Next we will create an instance based on the preceding statement.

Create a data table ips tutorial tats

Create table ipstats (
Ip varchar (15) not null unique,
Clicks smallint (5) unsigned not null default '0'
);

*/

$ Cn = mysql_connect ('2017. 0.0.1 ', 'root', 'root ');
Mysql_select_db ('abc', $ cn );
$ SQL = "insert into ipstats values ('192. 168.0.1 ', 1) on duplicate key update clicks = clicks + 1 ";
Mysql_query ($ SQL) or die (mysql_error ('data insertion failed '));
Echo 'Operation successful ';

// We used the insert into on duplicate key update operation above, so let's take a look at the methods I used previously

$ SQL = "select * from ipstats where ip = '192. 168.0.1 '";
$ Query = mysql_query ($ SQL );
If (mysql_num_rows ($ query ))
{
Mysql_query ("update ipstats set clicks = clicks + 1 where ip = '192. 168.0.1 '");
}
Else
{
Mysql_query ("insert into ipstats values ('192. 168.0.1 ', 1 )");
}

// From this point of view, the operation is much more complicated. Of course, there is also a way to directly execute SQL, but three steps of code are required:

$ SQL = "if (select * from ipstats where ip = '2017. 168.0.1 '){
Update ipstats set clicks = clicks + 1 where ip = '192. 168.0.1 ';
} Else {
Insert into ipstats (ip, clicks) values ('192. 168.0.1 ', 1 );
}";
Mysql_query ($ SQL );

/*
Summary:
After the above three mysql data exists, the SQL statement instance code is inserted if the update does not exist. It can be concluded that insert into on duplicate key update is the most convenient and fast, in web development, we try to select the most suitable method for the current application to solve the problem. The above three methods can be used to insert instance data and update if there is, but the first method is the best.
This site original tutorial, reprinted with the source http://www.bKjia. c0m/database/database.html
*/