转载自：http://www.mysqltutorial.org/mysql-replace.aspx
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 standard SQL. The MySQL REPLACEstatement works as follows:
- If the new row already does not exist, the MySQL
REPLACEstatement inserts a new row. - If the new row already exist, the
REPLACEstatement deletes the old row first and then inserts a new row. In some cases, theREPLACEstatement updates the existing row only.
To determine whether the new row already exists in the table, MySQL uses PRIMARY KEY or UNIQUE KEY index. If the table does not have one of these indexes, the REPLACE statement is equivalent to the INSERT statement.
To use the MySQL REPLACE statement, you need to have at least both INSERT and DELETE privileges.
Notice that there is a REPLACE string function which is not the REPLACE statement covered in this tutorial.
MySQL REPLACE statement example
Let’s take a look at an example of using the REPLACE statement to have a better understanding of how it works.
First, create a new table named cities as follows:
|
1
2
3
4
5
|
CREATE TABLE cities (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
population INT NOT NULL
);
|
Next, insert some rows into the cities table:
|
1
2
3
4
|
INSERT INTO cities(name,population)
VALUES('New York',8008278),
('Los Angeles',3694825),
('San Diego',1223405);
|
We query data from the cities table to verify the insert operation.
|
1
2
3
4
|
SELECT
*
FROM
cities;
|
We have three cities in the cities table.
Then, suppose we want to update the population of the New York city to 1008256. We can use the UPDATE statement as follows:
|
1
2
3
4
5
|
UPDATE cities
SET
population = 1008256
WHERE
id = 1;
|
We query the data from the cities table again to verify the update.
|
1
2
3
4
|
SELECT
*
FROM
cities;
|
The UPDATE statement updated the data as expected.
After that, use the REPLACE statement to update the population of the Los Angeles city to 3696820.
|
1
2
|
REPLACE INTO cities(id,population)
VALUES(2,3696820);
|
Finally, query the data of the cities table again to verify the replacement.
|
1
2
3
4
|
SELECT
*
FROM
cities;
|
The name column is NULL now. You may expect that the value of the name column remains intact. However, the REPLACE statement does not behave this way. In this case, the REPLACE statement works as follows:
- The
REPLACEstatement first inserts the new row into thecitiestable with the information provided by the column list. The insertion fails because the row with id 2 already exists in thecitiestable, therefore, MySQL raises a duplicate-key error. - The
REPLACEstatement then updates the row that has the key specified in the value of theidcolumn. In the normal process, it would delete the old row with conflict id first and then inserts a new row.
We know that the REPLACE statement did not delete the old row and inserted the new row because the value of the id column is 2 instead of 4.
MySQL REPLACE and INSERT
The first form of the REPLACE statement is similar to the INSERT statement except the keyword INSERTis replaced by the REPLACE keyword as follows:
|
1
2
|
REPLACE INTO table_name(column_list)
VALUES(value_list);
|
cities table, you use the following query:|
1
2
|
REPLACE INTO cities(name,population)
VALUES('Phoenix',1321523);
|
Notice that the default values of the columns that do not appear in the REPLACE statement will be inserted into the corresponding columns. In case the column that has the NOT NULL attribute and does not have a default value, and you don’t specify the value in the REPLACE statement, MySQL will raise an error. This is a difference between the REPLACE and INSERT statements.
For example, in the following statement, we specify only the value for the name column, not the population column. MySQL raises an error message. Because the population column does not accept a NULL value and we did not specify a default value for it when we defined the cities table.
|
1
2
|
REPLACE INTO cities(name)
VALUES('Houston');
|
This is the error message that MySQL issued:
|
1
|
Error Code: 1364. Field 'population' doesn't have a default value
|
MySQL REPLACE and UPDATE
The second form of REPLACE statement is similar to the UPDATE statement as follows:
|
1
2
3
|
REPLACE INTO table
SET column1 = value1,
column2 = value2;
|
Notice that there is no WHERE clause in the REPLACE statement.
For example, if you want to update the population of the Phoenix city to 1768980, you use the REPLACEstatement as follows:
|
1
2
3
4
|
REPLACE INTO cities
SET id = 4,
name = 'Phoenix',
population = 1768980;
|
Unlike the UPDATE statement, if you don’t specify the value for the column in the SET clause, the REPLACE statement will use the default value of that column.
|
1
2
3
4
|
SELECT
*
FROM
cities;
|
MySQL REPLACE INTO and SELECT
The third form of REPLACE statement is similar to INSERT INTO SELECT statement:
|
1
2
3
4
|
REPLACE INTO table_1(column_list)
SELECT column_list
FROM table_2
WHERE where_condition;
|
Suppose, you want to copy the city with id value 1, you use the REPLACE INTO SELECT statement as the following query:
|
1
2
3
|
REPLACE INTO cities(name,population)
SELECT name,population FROM cities
WHERE id = 1;
|
MySQL REPLACE statement usages
There are several important points you need to know when you use the REPLACE statement:
- If you develop an application that supports not only MySQL database but also other relational database management systems (RDBMS), you should avoid using the
REPLACEstatement because other RDBMS may not support it. Instead, you can use the combination of the DELETE and INSERT statements within a transaction. - If you are using the
REPLACEstatement in the table that has triggers and the deletion of duplicate-key error occurs, the triggers will be fired in the following sequence:BEFORE INSERTBEFORE DELETE,AFTER DELETE,AFTER INSERTin case theREPLACEstatement deletes current row and inserts the new row. In case theREPLACEstatement updates the current row, theBEFORE UPDATEandAFTER UPDATEtriggers are fired.
In this tutorial, you’ve learned different forms of REPLACE statement to insert or update data in tables.