MYSQL on duplicate key update和replace into 详解

曾经终败给现在 2024-03-22 17:05 51阅读 0赞

在MySQL中，`ON DUPLICATE KEY UPDATE`和`REPLACE INTO`都是常用的插入或更新表数据的语句。虽然两者实现的功能类似，但它们有着不同的应用场景和行为特征。本文将对这两种语句进行详细解释，并提供相关测试代码。

### ON DUPLICATE KEY UPDATE ###

`ON DUPLICATE KEY UPDATE`语句是MySQL中用于插入或更新记录的一种方式。它的主要作用是，如果尝试将一条记录插入到已存在的唯一索引（如主键、唯一约束等）中，则会执行更新操作，而不是插入新记录。具体来说，当插入数据时，如果发现指定的索引已经存在，则执行UPDATE操作，将新记录的值更新到该索引对应的原有记录上；否则，执行INSERT操作，插入新记录。其基本语法如下：

INSERT INTO table (col1, col2, ...) VALUES (val1, val2, ...)
    ON DUPLICATE KEY UPDATE col1 = val1, col2 = val2, ...;

其中，`table`是目标表名，`col1`、`col2`等是目标列名，`val1`、`val2`等是需要插入或更新的值。

为了更好地理解`ON DUPLICATE KEY UPDATE`语句的工作原理，我们可以通过以下示例代码进行演示。首先，创建一个名为`students`的表，包含两个列：`id`和`name`。其中，`id`为主键，并设置自增增长。

CREATE TABLE students (
        id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(255) NOT NULL
    );

然后，向该表中插入一些数据：

INSERT INTO students (name) VALUES ('Alice'), ('Bob'), ('Charlie');

这将在`students`表中插入三条记录，id分别为1、2和3。

接下来，我们使用`ON DUPLICATE KEY UPDATE`语句插入一条新记录，并执行更新操作。假设需要插入一条记录，表示学生“Bob”改名为“Bobby”。此时，我们可以使用以下SQL语句：

INSERT INTO students (id, name) VALUES (2, 'Bobby')
    ON DUPLICATE KEY UPDATE name = 'Bobby';

该语句尝试向`students`表中插入一条记录，其id为2，name为“Bobby”。由于id=2已经存在，因此触发了更新操作，将原有记录中name的值从“Bob”更新为“Bobby”。

如果我们再次执行查询操作，可以看到`students`表中的记录如下所示：

+----+--------+
    | id | name   |
    +----+--------+
    | 1  | Alice  |
    | 2  | Bobby  |
    | 3  | Charlie|
    +----+--------+

### REPLACE INTO ###

与`ON DUPLICATE KEY UPDATE`不同，`REPLACE INTO`语句是用于替换或插入表数据的语句。它的工作原理是，当尝试向已经存在的唯一索引中插入新记录时，将删除原有记录并插入新记录。具体来说，如果目标表中已经存在一个与要插入的记录相同的唯一索引，则先删除该记录，再插入新记录；否则，直接插入新记录。其基本语法如下：

REPLACE INTO table (col1, col2, ...) VALUES (val1, val2, ...);

其中，`table`是目标表名，`col1`、`col2`等是目标列名，`val1`、`val2`等是需要插入或更新的值。

为了更好地理解`REPLACE INTO`语句的工作原理，我们可以继续使用上述示例。假设需要插入一条记录，表示学生“Denise”加入了班级。此时，我们可以使用以下SQL语句：

REPLACE INTO students (id, name) VALUES (4, 'Denise');

该语句尝试向`students`表中插入一条记录，其id为4，name为“Denise”。由于id=4不存在，因此直接插入新记录。

如果我们再次执行查询操作，可以看到`students`表中的记录如下所示：

+----+--------+
    | id | name   |
    +----+--------+
    | 1  | Alice  |
    | 2  | Bob    |
    | 3  | Charlie|
    | 4  | Denise |
    +----+--------+

### 测试用例 ###

为了进一步说明`ON DUPLICATE KEY UPDATE`和`REPLACE INTO`的区别和用法，下面提供一个简单的测试用例。

假设我们有一个名为`users`的表，包含三个列：`id`、`name`和`age`。其中，`id`为主键。

首先，我们向`users`表中插入一些数据：

INSERT INTO users (id, name, age) VALUES 
    (1, 'Alice', 20), 
    (2, 'Bob', 25), 
    (3, 'Charlie', 30);

接着，我们分别使用`ON DUPLICATE KEY UPDATE`和`REPLACE INTO`语句向该表插入或更新数据，并执行查询操作，验证结果是否符合预期。

#### 使用ON DUPLICATE KEY UPDATE插入或更新数据 ####

-- 插入一条新记录
    INSERT INTO users (id, name, age) VALUES (4, 'Denise', 35)
    ON DUPLICATE KEY UPDATE name = 'Denise', age = 35;
    
    -- 更新已有记录
    INSERT INTO users (id, name, age) VALUES (2, 'Bobby', 30)
    ON DUPLICATE KEY UPDATE name = 'Bobby', age = 30;
    
    -- 查询数据
    SELECT * FROM users;

执行以上语句后，查询结果为：

+----+--------+-----+
    | id | name   | age |
    +----+--------+-----+
    | 1  | Alice  | 20  |
    | 2  | Bobby  | 30  |
    | 3  | Charlie| 30  |
    | 4  | Denise | 35  |
    +----+--------+-----+

#### 使用REPLACE INTO插入或更新数据 ####

-- 插入一条新记录
    REPLACE INTO users (id, name, age) VALUES (5, 'Emily', 25);
    
    -- 更新已有记录
    REPLACE INTO users (id, name, age) VALUES (3, 'Charles', 40);
    
    -- 查询数据
    SELECT * FROM users;

执行以上语句后，查询结果为：

+----+--------+-----+
    | id | name   | age |
    +----+--------+-----+
    | 1  | Alice  | 20  |
    | 2  | Bob    | 25  |
    | 3  | Charles| 40  |
    | 5  | Emily  | 25  |
    +----+--------+-----+

通过上述测试用例，我们可以看到`ON DUPLICATE KEY UPDATE`和`REPLACE INTO`的不同之处，以及它们在实际应用中的使用场景和注意事项。需要根据具体的业务需求选择合适的语句，以保证数据的正确性和一致性。