Mysql saves data, otherwise it saves data

I will share an article about how to update data when saving data in mysql. Otherwise, I will save the data. It is very practical. If you need it, please refer to it.

// If no data exists, insert new data

The Code is as follows:	Copy code
$ SQL = "INSERT INTO {$ ecs-> table ('cat _ lang ')} (cat_id, lang_id, cat_name, keywords, cat_desc) VALUES ({$ cat_id}, {$ k}, '{$ val ['cat _ name']}', '{$ val ['keyword']}', '{$ val ['cat _ desc']}') On duplicate key update cat_name = '{$ val ['cat _ name']}', cat_desc = '{$ val ['cat _ desc']}', keywords = '{$ val ['cat _ desc']}' ";

Note that the table primary key must be unique. Here, I use the double primary key mechanism of classification id and language id.

The key is a unique or primary key.

(1) If the key exists, replace it. If the key does not exist, insert it.

The Code is as follows:	Copy code
Replace into t_test | create table 't_ test '( 'Ikey' int (11) not null default '0 ', 'Value' varchar (21) not null default '', 'Count' int (11) not null default '0 ', Primary key ('ikey ') ) ENGINE = MyISAM default charset = gbk |

For such a table. if you want to record ikey = 1, value = 'A', icount = 0, you do not know if there are items in the table with ikey = 1. we may first select and check whether there are any results. If so, update the data using update. if not, insert is used.

However, when a large number of concurrent data operations are performed, a select query with a primary key may use 2 s. If you do not care about the old data, it is important to reduce unnecessary operations.

The replace statement can be used to complete the functions of the preceding two statements. Its syntax is similar to insert. the preceding operations can be written as replace into t_test set ikey = 1, value = 'A', and icount = 0. If the table has an ikey of 1, the old data is deleted first. insert new data. otherwise, insert data directly.

(2) If the key exists, update it. If the key does not exist, insert it.

The Code is as follows:	Copy code
Insert into... on duplicate key update

For operations based on the original record. for example, the icount field in the table is used for counting. If no record exists, the inserted value is 0. If a record exists, the value must be updated to value + 1. In this case, replace cannot complete this function. you can use insert. The basic syntax is insert... on duplicate key update ..., if the preceding statement is

The Code is as follows:	Copy code
Insert into t_test set ikey = 1, value = 'A', value2 = 1 on duplicate key update value2 = value2 + 1;

If the table has multiple unique indexes, such as adding a unique key to the value field. in this case, the table has two unique indexes, ikey And value. replace deletes all data items with the same unique index value and inserts a new record. for example, the table has two records.

The Code is as follows:

Copy code

+ ------ + ------- + -------- + | Ikey | value | icount | + ------ + ------- + -------- + | 2 | a | 10 | | 1 | B | 40 | + ------ + ------- + -------- + Replace into t_test set ikey = 1, value = 'A', icount = 0; delete both records in the table and insert a new record. Insert into t_test set ikey = 1, value = 'A', icount = 0 on duplicate key update icount = icount + 1 then only one record is updated. the effect is equivalent to update t_test set icount = icount + 1 where ikey = 1 or value = 'A' limit 1;