mysql插入时非主键重复则更新

原创

mob649e8159b30b 2024-09-24 08:35:45 ©著作权

©著作权归作者所有：来自51CTO博客作者mob649e8159b30b的原创作品，请联系作者获取转载授权，否则将追究法律责任

MySQL插入时非主键重复则更新的实现方法

在数据库操作中，常常需要处理插入操作时，遇到非主键重复的情况。在 MySQL 中，我们可以通过使用 INSERT ... ON DUPLICATE KEY UPDATE 语句来实现这一功能。下面，我将带你详细了解整个流程和每一步的代码示例。

整体流程

我们可以将这件事情的流程分为几个步骤：

步骤	描述
1	创建数据库和数据表
2	插入数据
3	若插入失败（因为重复），则更新数据
4	验证数据

接下来我们将通过具体的代码实现这些步骤。

1. 创建数据库和数据表

首先，我们需要创建一个数据库和数据表，下面是相关的 SQL 语句。

-- 创建数据库
CREATE DATABASE IF NOT EXISTS test_db;

-- 选择数据库
USE test_db;

-- 创建数据表
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    age INT
);

代码注释

CREATE DATABASE IF NOT EXISTS test_db;：如果数据库不存在，则创建一个名为 test_db 的数据库。
USE test_db;：选择并使用刚创建的数据库。
CREATE TABLE IF NOT EXISTS users (...);：创建一个 users 表，其中包含一个自增的主键 id，一个唯一的 username，电子邮件 email 和用户年龄 age。

2. 插入数据

接下来，我们希望将数据插入到 users 表中。可以使用如下代码：

INSERT INTO users (username, email, age) 
VALUES ('alice', 'alice@example.com', 30);

代码注释

INSERT INTO users (username, email, age) VALUES ('alice', 'alice@example.com', 30);：向 users 表插入一条新记录，包含用户名、电子邮件和年龄。

3. 若插入失败（因为重复），则更新数据

在进行插入操作时，如果用户名（非主键）重复，我们希望更新该用户的信息。可以使用如下代码：

INSERT INTO users (username, email, age) 
VALUES ('alice', 'alice@newdomain.com', 31) 
ON DUPLICATE KEY UPDATE email = VALUES(email), age = VALUES(age);

代码注释

ON DUPLICATE KEY UPDATE：表示如果插入的记录因重复主键或 UNIQUE 约束而失败，则执行更新操作。
email = VALUES(email) 和 age = VALUES(age)：表示将现有记录的电子邮件和年龄更新为插入值。

4. 验证数据

最后，我们可以查询 users 表验证数据是否插入或更新成功。

SELECT * FROM users;

代码注释

SELECT * FROM users;：查询 users 表中的所有记录，以便验证插入或更新结果。

流程图

flowchart TD
    A[创建数据库和数据表] --> B[插入数据]
    B --> C{检查是否重复}
    C -->|是| D[更新数据]
    C -->|否| E[成功插入]
    D --> F[验证数据]
    E --> F
    F --> G[完成]

完整代码示例

以下是将所有步骤整合在一起的代码示例：

-- 创建数据库
CREATE DATABASE IF NOT EXISTS test_db;

-- 选择数据库
USE test_db;

-- 创建数据表
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    age INT
);

-- 插入新用户
INSERT INTO users (username, email, age) 
VALUES ('alice', 'alice@example.com', 30);

-- 尝试插入重复用户并更新
INSERT INTO users (username, email, age) 
VALUES ('alice', 'alice@newdomain.com', 31) 
ON DUPLICATE KEY UPDATE email = VALUES(email), age = VALUES(age);

-- 查询所有用户
SELECT * FROM users;