mysql inserts new data if the data does not exist otherwise updates the implementation method
- 2020-05-12 06:18:47
- OfStack
// if not, insert 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 only be 1. Here, I'm using the classification id and the language id double primary key mechanism.
key is a single key or primary key
(1) if key exists, it will be replaced; if it does not, it will be inserted
replace into t_test | CREATE TABLE `t_test` (
`ikey` int(11) NOT NULL default '0',
`value` varchar(21) NOT NULL default '',
`icount` int(11) NOT NULL default '0',
PRIMARY KEY (`ikey`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk |
For a table like this, when we want to record ikey=1,value='a',icount=0, we don't know if there is an item ikey=1 in the table.
However, in the case of large concurrent data operations, it may sometimes be necessary to use 2s for a single select query with a primary key. If you don't care about the old data, it is especially important to reduce unnecessary operations.
The syntax of the above two statements is similar to that of insert, which can be written as replace into t_test set ikey=1,value='a',icount=0; When ikey is 1 in the table, first delete the old data and then insert the new data. Otherwise, directly insert the data.
(2) if key exists, it will be updated; if it does not, it will be inserted
insert into .... on duplicate key update
If the icount field in the table is used for counting, value inserted is 0 when there is no record,value needs to be updated to value+1 when there is a record, then replace cannot complete this function. on duplicate key update... , as shown in the above statement
insert into t_test set ikey=1,value='a',value2=1 on duplicate key update value2=value2+1;
If there are more than one unique index in the table, unique key is added to the value field. If there are two unique indexes ikey and value in the table,replace will delete all data items with the same index value as ikey and insert a new record, if there are two unique indexes in the table
+------+-------+--------+
| ikey | value | icount |
+------+-------+--------+
| 2 | a | 10 |
| 1 | b | 40 |
+------+-------+--------+
replace into t_test set ikey=1,value='a',icount=0; Both records in the table are deleted and a new record is inserted.
insert into t_test set ikey=1,value='a',icount=0 on update icount update icount icount=icount set icount set icount=icount+1 where ikey=1 or value='a' limit 1;
$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 only be 1. Here, I'm using the classification id and the language id double primary key mechanism.
key is a single key or primary key
(1) if key exists, it will be replaced; if it does not, it will be inserted
replace into t_test | CREATE TABLE `t_test` (
`ikey` int(11) NOT NULL default '0',
`value` varchar(21) NOT NULL default '',
`icount` int(11) NOT NULL default '0',
PRIMARY KEY (`ikey`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk |
For a table like this, when we want to record ikey=1,value='a',icount=0, we don't know if there is an item ikey=1 in the table.
However, in the case of large concurrent data operations, it may sometimes be necessary to use 2s for a single select query with a primary key. If you don't care about the old data, it is especially important to reduce unnecessary operations.
The syntax of the above two statements is similar to that of insert, which can be written as replace into t_test set ikey=1,value='a',icount=0; When ikey is 1 in the table, first delete the old data and then insert the new data. Otherwise, directly insert the data.
(2) if key exists, it will be updated; if it does not, it will be inserted
insert into .... on duplicate key update
If the icount field in the table is used for counting, value inserted is 0 when there is no record,value needs to be updated to value+1 when there is a record, then replace cannot complete this function. on duplicate key update... , as shown in the above statement
insert into t_test set ikey=1,value='a',value2=1 on duplicate key update value2=value2+1;
If there are more than one unique index in the table, unique key is added to the value field. If there are two unique indexes ikey and value in the table,replace will delete all data items with the same index value as ikey and insert a new record, if there are two unique indexes in the table
+------+-------+--------+
| ikey | value | icount |
+------+-------+--------+
| 2 | a | 10 |
| 1 | b | 40 |
+------+-------+--------+
replace into t_test set ikey=1,value='a',icount=0; Both records in the table are deleted and a new record is inserted.
insert into t_test set ikey=1,value='a',icount=0 on update icount update icount icount=icount set icount set icount=icount+1 where ikey=1 or value='a' limit 1;