MySQL数据库replace into table where set select的使用方法

MySQL replace into 有三种形式
1. replace into tbl_name(col_name, …) values(…)
2. replace into tbl_name(col_name, …) select …
3. replace into tbl_name set col_name=value, …

feihu it-web为了让更多对MYSQL REPLACE不明真相的同学了解它的用法 把这篇文章摘录入下 也发表今后查阅
飞狐itweb特别提醒的是使用第一种方法 ()values() 时,没有列出的列将被默认值覆盖,如变为NULL,0或者其他等等

MySQL REPLACE statement is a MySQL extension to SQL standard. MySQL REPLACE works like INSERT statements with the following rules:
If the record being inserting does not exist, MySQL REPLACE will insert a new record.
If the record being inserting exists, MySQL REPLACE will delete the old record first and then insert a new record with new values.
In order to use MySQL REPLACE you need to have at least both INSERT and DELETE privileges for the table.
The first form of MySQL REPLACE is similar to the INSERT statement except the keyword INSERT is replaced by the
REPLACE keyword as follows:
REPLACE INTO table_name(column_name1,column_name2,…)VALUES(value1,value2,…)

For example if you want to insert a new office into offices table, you use the following query:
REPLACE INTO offices(officecode,city)VALUES(8,'San Jose')

Note that all values of missing columns in the REPLACE statement will be set to default value.
Now if you want to update the inserted office with San Mateo city, we can do it as follows:
REPLACE INTO offices(officecode,city)VALUES(8,'San Mateo')

You see that two rows affected by the query above because the existing record is deleted and the new record is inserted.
The second form of MySQL REPLACE like UPDATE statement as follows:
REPLACE INTO table_name
 SET column_name1 = value1 AND column2 = value2

Note that there is no WHERE clause is specified in the REPLACE statement. For example, if you want to update office in San Mateo with officecode 8 you can do it as follows:
REPLACE INTO offices SET officecode = 8 and city = ‘Santa Cruz’

The third form of MySQL REPLACE is similar to SQL INSERT INTO SELECT statement as follows:
REPLACE INTO table_name1(column_name1,column_name2,…)
SELECT column_name1, column_name2…
FROM table_name2
WHERE where_condition

Let’s say if we want to copy the office with officecode 1, we can do it as follows:
REPLACE INTO offices(officecode,
 city,
 phone,
 addressline1,
 addressline2,
 state,
 country,
 postalcode,
 territory)
SELECT (SELECT MAX(officecode) + 1 FROM offices),
 city,
 phone,
 addressline1,
 addressline2,
 state,
 country,
 postalcode,
 territory
FROM offices
WHERE officecode = 1

REPLACE是仅MYSQL才有的语法 使用注意触发器的顺序
There are several points you need to know before using MySQL REPLACE statement:
If you are developing an application that potentially supports not only MySQL database, try to avoid using MySQL REPLACE because other databases may not support REPLACE statement. You can use the combination of INSERT and DELETE statement instead.
If you are using MySQL REPLACE in the table which has triggers associate with it and if delete of duplicate key happens, the triggers are fired in the following orders:
Before insert
Before delete
After delete
After insert
It is preferred using MySQL UPDATE statement to using MySQL REPLACE in case you just want to update data. Because MySQL UPDATE is a much faster performer than MySQL REPLACE.