Mysql 简明教程
MySQL - REPLACE Query
MySQL REPLACE Statement
通常，如果我们希望向现有表添加记录，我们将使用 MySQL INSERT 语句。同样，我们也可以使用 MySQL REPLACE 语句添加新记录或替换现有记录。replace 语句与 insert 语句类似。
In general, if we want to add records into an existing table, we use the MySQL INSERT statement. Likewise, we can also add new records or replace an existing records using the MySQL REPLACE statement. The replace statement is similar to the insert statement.
唯一的区别是，如果使用 insert 语句插入记录而现有列有 UNIQUE 或 PRIMARY KEY 约束，如果新记录对该列具有相同的值，将生成一个错误。
The only difference is, while inserting a record using the insert statement if a existing column has a UNIQUE or PRIMARY KEY constraint, if the new record has same value for this column an error will be generated.
在 REPLACE 语句的情况下，如果你尝试插入具有 UNIQUE 或 PRIMARY KEY 约束的列的重复值的新列，旧记录将完全由新记录替换。
In the case of the REPLACE statement if you try to insert a new column with duplicate value for the column with UNIQUE or PRIMARY KEY constraints the old record will be completely replaced by the new record.
Syntax
以下是 MySQL REPLACE 语句的语法 −
Following is the syntax of the MySQL REPLACE statement −
REPLACE INTO table_name (column1, column2, column3,...columnN)
VALUES (value1, value2, value3,...valueN);其中，table_name 是你需要向其中插入数据的表的名称，(column1, column2, column3,…columnN) 是列的名称，(value1, value2, value3,…valueN) 是记录中的值。
Where, table_name is the name of the table into which you need to insert data, (column1, column2, column3,…columnN) are the names of the columns and (value1, value2, value3,…valueN) are the values in the record.
Example
让我们从在 MySQL 数据库中使用 ID 列为主键约束创建一个名为 CUSTOMERS 的表开始，如下所示 −
Let us start with creating a table with name CUSTOMERS in MySQL database with primary key constraint on the ID column as shown below −
CREATE TABLE CUSTOMERS (
ID INT NOT NULL,
NAME VARCHAR(20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);以下查询向上述创建的表中添加两条记录 −
The following query adds two records into the above created table −
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 );执行以下查询以显示 CUSTOMERS 表中存在的所有记录：
Execute the following query to display all the records present in the CUSTOMERS table −
select * FROM CUSTOMERS;以下是 CUSTOMERS 表中的记录：
Following are the records in CUSTOMERS table −
现在，让我们尝试插入具有 ID 值 2 的另一条记录 −
Now, let us try to insert another record with ID value 2 −
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
(2, 'Kaushik', 23, 'Kota', 2000.00 );由于 ID 列具有主键约束，因此将生成如下所示的错误 −
Since the ID column has a primary key constraint, an error will be generated as shown below −
ERROR 1062 (23000): Duplicate entry '2' for key 'customers.PRIMARY'现在，使用 REPLACE 语句替换表中现有的记录 −
Now, use the REPLACE statement to replace the existing record in the table −
REPLACE INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
(2, 'Kaushik', 20, 'Kota', 2000.00 );Inserting records using REPLACE statement
当使用 REPLACE 语句插入记录时，如果该记录与表中任何现有的记录不匹配，那么该记录就会作为一个新记录添加进来。
When you use the REPLACE statement to insert a record, if that record doesn’t match any existing records in the table, it will be added as a new record.
Example
以下查询使用了 REPLACE 语句来向 CUSTOMERS 表中添加三条新记录 -
The following query uses REPLACE statement to add three new records into the above CUSTOMERS table −
REPLACE INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
(3, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(4, 'Hardik', 27, 'Bhopal', 8500.00 ),
(5, 'Komal', 22, 'Hyderabad', 4500.00 );Output
执行上述查询将产生以下输出：
Executing the query above will produce the following output −
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0Verification
执行以下查询来验证上面记录是否已插入到 CUSTOMERS 表中 -
Execute the following query to verify whether the above records has been inserted into CUSTOMERS table or not −
SELECT * FROM CUSTOMERS;正如我们从下方的 CUSTOMERS 中所看到的，上面的记录已作为新记录插入到表中。
As we can observe the CUSTOMERS below, the above records are inserted as new records into the table.
Replacing a Record Using a Client Program
除了使用 MySQL 查询替换 MySQL 数据库中表记录外，我们还可以使用客户端程序来执行 REPLACE 操作。
Besides replacing records of a table in a MySQL database with a MySQL query, we can also use a client program to perform the REPLACE operation.