MySQL Replace语句-原创手记-慕课网

Summary: in this tutorial, you will learn how to use the MySQL REPLACE statement to insert or update data in database tables.

Introduction to MySQL REPLACE statement

The MySQL REPLACE statement is a MySQL extension to the SQL standard. The MySQL REPLACE statement works like the INSERT statement with the additional rules:

If the record which you want to insert does not exist, the MySQL REPLACE inserts a new record.
If the record which you want to insert already exists, MySQL REPLACE deletes the old record first and then insert a new record.

In order to use MySQL REPLACE statement, you need to have at least both INSERT and DELETE privileges.

Please don’t confuse the REPLACE statement with the REPLACE string function.

MySQL REPLACE statements

MySQL REPLACE INTO statement

The first form of the REPLACE statement 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 the offices table, you use the following query:

REPLACE INTO offices(officecode,city) VALUES(8,'San Jose')

Notice that the default values of of the columns that does not appear in the REPLACE statement will be inserted to the corresponding columns.

If you want to update the office that we have inserted with the new city San Mateo, you can use the REPLACEstatement as follows:

REPLACE INTO offices(officecode,city) VALUES(8,'San Mateo')

Two rows affected by the query above because the existing record was deleted and the new one was inserted.

MySQL REPLACE acts like UPDATE statement

The second form of MySQL REPLACE like the UPDATE statement as follows:

REPLACE INTO table_name SET column_name1 = value1 AND     column2 = value2

Notice that there is no WHERE clause in the REPLACE statement. For example, if you want to update the office in San Mateocity with officecode value 8, you use the REPLACE statement as follows:

REPLACE INTO offices SET officecode = 8 and     city = 'Santa Cruz'

MySQL REPLACE INTO with SELECT statement

The third form of REPLACE is similar to INSERT INTO SELECT statement:

REPLACE INTO table_name1(column_name1,column_name2,…) SELECT column_name1, column_name2… FROM table_name2 WHERE where_condition

Suppose if you want to copy the office with officecode value 1, you use the REPLACE INTO SELECT statement as the following query:

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

MySQL REPLACE usages

There are several important points you need to know when you use the REPLACE statement:

If you are developing an application that potentially supports not only MySQL database, try to avoid using the REPLACE statement because other database management systems may not support the REPLACE statement. Instead, you can use the combination of the INSERT and DELETE statements.
If you are using the REPLACE statement in the table that has triggers and if the deletion of duplicate key happens, the triggers will be fired in the following sequence: BEFORE INSERT, BEFORE DELETE, AFTER DELETE, AFTER INSERT.
You should use the UPDATE statement in case you want to update data because it performs faster than the REPLACE statement.

In this tutorial, you’ve learned different forms of MySQL REPLACE statement to insert or update data in database tables.