Mysql If the data does not exist, insert new data, otherwise the update implementation method _mysql

If it does not exist, insert the new data
$sql = "INSERT into {$ecs->table (' Cat_lang ')} (CAT_ID,LANG_ID,CAT_NAME,KEYWORDS,CAT_DESC)
VALUES ({$cat _id},{$k}, ' {$val [' cat_name ']} ', ' {$val [' keywords ']} ', ' {$val [' Cat_desc ']} '} ')
On DUPLICATE KEY UPDATE cat_name= ' {$val [' cat_name ']} ', cat_desc= ' {$val [' Cat_desc ']} ', keywords= ' {$val [' Cat_desc ']} ' ;
Note here that the primary key must be unique to the table. Here, I am using the classification ID and the language ID dual primary key mechanism.

Key is a unique key or primary key

(1) The key exists replaces, does not exist inserts

Replace into T_test | CREATE TABLE ' T_test ' (

' Ikey ' int (one) not NULL default ' 0 ',

' Value ' varchar not NULL default ',

' Icount ' int (one) not NULL default ' 0 ',

PRIMARY KEY (' Ikey ')

) Engine=myisam DEFAULT CHARSET=GBK |


For such a table. When you want to record ikey=1,value= ' a ', icount=0, you don't know if there are any ikey=1 items. We might select first, then see if there are any results, and if so, update with update. If not, Inserts are inserted using the insert.


However, in large concurrency data operations, it may sometimes be a select query with a primary key to use 2s, if the old data does not care, reduce unnecessary operation appears to be still important.

Use the Replace one statement to complete the function of the above two statements, and its syntax is similar to insert. If the above operation can be written as replace into t_test set ikey=1,value= ' A ', icount=0, then ikey in the table is 1 o'clock. Delete the old data first. Then insert the new data. Otherwise insert the data directly.


(2) The key exists is updated, does not exist is inserted

Insert INTO ... on duplicate key update

For operations that need to be based on the original record. If the Icount field in the table is used for counting, and when there is no record, the value inserted is 0, and when there is a record, the value needs to be updated to value+1, and replace cannot complete the function. Use inserts to Its basic syntax is insert INTO ... on duplicate key update ..., as the above statement is

INSERT into T_test set ikey=1,value= ' a ' and value2=1 on duplicate key update value2=value2+1;

If a table has more than one unique index, such as a unique key for the value field. When the table has Ikey and value two unique indexes, replace deletes all the data items with the same unique index values and inserts new records. If there are two records in the table

+------+-------+--------+

| Ikey | Value | icount |

+------+-------+--------+

| 2 | A | 10 |

| 1 | B | 40 |

+------+-------+--------+

The replace into t_test set ikey=1,value= ' A ', icount=0, deletes all two records in the table, and then inserts a new record.

The INSERT into T_test set ikey=1,value= ' A ', and icount=0 on duplicate key update icount=icount+1 update only one record. The effect is equivalent to update t_test set icount=icount+1 where Ikey=1 or value= ' a ' limit 1;