在本教程中,您将学习如何使用MySQL REPLACE
语句向数据库表插入或更新数据。
MySQL REPLACE语句简介
MySQL REPLACE
语句是标准SQL的MySQL扩展。 MySQL REPLACE
语句的工作原理如下:
MySQL使用PRIMARY KEY或UNIQUE KEY
索引来要确定表中是否存在新行。如果表没有这些索引,则REPLACE
语句等同于INSERT语句。
要使用MySQL REPLACE
语句,至少需要具有INSERT
和DELETE
权限。
请注意,有一个REPLACE字符串函数,它不是本教程中说述的
REPLACE
语句。
MySQL REPLACE语句示例
我们来看一下使用REPLACE
语句来更好地了解它的工作原理的例子。
首先,创建一个名为cities
的新表,如下所示:
USE testdb;
CREATE TABLE cities (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
population INT NOT NULL
);
接下来,在cities
表中插入一些行记录:
INSERT INTO cities(name,population)
VALUES('New York',8008278),
('Los Angeles',3694825),
('Shanghai',1923400);
下面查询cities
表中的数据,以验证插入操作 -
mysql> SELECT * FROM cities;
+----+-------------+------------+
| id | name | population |
+----+-------------+------------+
| 1 | New York | 8008278 |
| 2 | Los Angeles | 3694825 |
| 3 | Shanghai | 1923400 |
+----+-------------+------------+
3 rows in set
现在,在cities
表中有三个城市数据。
那么,假设我们要将纽约市的人口更新为1008256
,可以使用UPDATE语句如下:
UPDATE cities
SET
population = 1008256
WHERE
id = 1;
再次查询cities
表中的数据来验证更新结果 -
mysql> UPDATE cities
SET
population = 1008256
WHERE
id = 1;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM cities;
+----+-------------+------------+
| id | name | population |
+----+-------------+------------+
| 1 | New York | 1008256 |
| 2 | Los Angeles | 3694825 |
| 3 | Shanghai | 1923400 |
+----+-------------+------------+
3 rows in set
UPDATE
语句的确按照预期更新了数据。
之后,使用REPLACE
声明将洛杉矶市的人口更新为3696820
。
REPLACE INTO cities(id,population) VALUES(2,3696820);
Query OK, 2 rows affected
上面执行返回结果中提示:2 rows affected,说明有两行数据受影响。
最后,再次查询城市表的数据来验证替换的结果。
mysql> SELECT * FROM cities;
+----+----------+------------+
| id | name | population |
+----+----------+------------+
| 1 | New York | 1008256 |
| 2 | NULL | 3696820 |
| 3 | Shanghai | 1923400 |
+----+----------+------------+
3 rows in set
现在name
列为NULL
。 您可能期望name
列的值保持不变。但是,REPLACE
语句不这样做。 在这种情况下,REPLACE
语句的工作原理如下:
REPLACE
语句首先使用列列表提供的信息将新行插入到cities
表中。但是插入失败,因为在cities
表中已经存在ID
为2
的行记录,因此,MySQL引发了重复键错误。然后,
REPLACE
语句更新具有id
列值为2
指定的行记录。在正常进程中,它将先删除具有冲突id
为2
的旧行,然后插入一个新行。
MySQL REPLACE和INSERT
REPLACE
语句的第一种形式类似于INSERT
语句,除了INSERT
关键字换成REPLACE
关键字以外,如下所示:
REPLACE INTO table_name(column_list)
VALUES(value_list);
例如,如果要在cities
表中插入新行,请使用以下查询:
REPLACE INTO cities(name,population)
VALUES('Phoenix',1321523);
请注意,没有出现在REPLACE
语句中的列将使用默认值插入相应的列。 如果列具有NOT NULL
属性并且没有默认值,并且您如果没有在REPLACE
语句中指定该值,则MySQL将引发错误。这是REPLACE
和INSERT
语句之间的区别。
例如,在以下语句中,仅指定name
列的值,而没有指定population
列。MySQL引发错误消息。 因为population
列不接受NULL
值,而我们定义cities
表时,也没有指定population
列的默认值。
REPLACE INTO cities(name)
VALUES('Houston');
执行上面语句后,MySQL发出如下的错误消息:
Error Code: 1364. Field 'population' doesn't have a default value
MySQL REPLACE和UPDATE
REPLACE
语句的第二种形式类似于UPDATE
语句,如下所示:
REPLACE INTO table
SET column1 = value1,
column2 = value2;
请注意,REPLACE
语句中没有WHERE子句。
例如,如果要将Phoenix
城市的人口更新为1768980
,请使用REPLACE
语句,如下所示:
REPLACE INTO cities
SET id = 4,
name = 'Phoenix',
population = 1768980;
与UPDATE
语句不同,如果不在SET
子句中指定列的值,则REPLACE
语句将使用该列的默认值。
SELECT * FROM cities;
MySQL REPLACE INTO和SELECT
REPLACE
语句的第三种形式类似于INSERT INTO SELECT语句:
REPLACE INTO table_1(column_list)
SELECT column_list
FROM table_2
WHERE where_condition;
假设要复制ID
为1
的城市行记录,可以使用REPLACE INTO SELECT
语句,如下查询示例:
REPLACE INTO cities(name,population)
SELECT name,population FROM cities
WHERE id = 1;
MySQL REPLACE语句用法
使用REPLACE
语句时需要知道几个重点:
如果您开发的应用程序不仅支持MySQL数据库,而且还支持其他关系数据库管理系统(RDBMS),则应避免使用
REPLACE
语句,因为其他RDBMS可能不支持。代替的作法是在事务中使用DELETE和INSERT语句的组合。如果在具有触发器的表中使用了
REPLACE
语句,并且发生了重复键错误的删除,则触发器将按以下顺序触发:在删除前删除,删除之后,删除后,如果REPLACE
语句删除当前 行并插入新行。 如果REPLACE
语句更新当前行,则触发BEFORE UPDATE
和AFTER UPDATE
触发器。
在本教程中,您学习了不同形式的REPLACE
语句来插入或更新表中的数据。