Mysql inserts new data if the data does not exist. Otherwise, the update method is used.

Source: Internet
Author: User

// If no data exists, 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 ['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.

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.

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

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.

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

| 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;

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.