【MySQL数据添加操作详解】

---

一、创建测试表

在进行数据添加之前，先创建一张测试表 user，用于演示各种插入操作。

CREATE DATABASE IF NOT EXISTS test_db;

USE test_db;

CREATE TABLE IF NOT EXISTS user (
    id INT PRIMARY KEY AUTO_INCREMENT,       -- 自增主键
    username VARCHAR(50) NOT NULL,            -- 用户名
    email VARCHAR(100) UNIQUE,                -- 邮箱（唯一）
    age INT                                   -- 年龄
);

插入几条初始数据：

INSERT INTO user (username, email, age) VALUES
('Alice', 'alice@example.com', 25),
('Bob', 'bob@example.com', 30),
('Charlie', 'charlie@example.com', 28);

---

二、基本的 INSERT 语法

INSERT 语句的基本语法如下：

INSERT INTO 表名 (列1, 列2, ...) VALUES (值1, 值2, ...);

✅ 示例：向 user 表中插入一条新记录

INSERT INTO user (username, email, age) VALUES ('David', 'david@example.com', 35);

📌 注意事项：

• 字符串类型的值需要用单引号 ' ' 包裹。
• 数字类型可以直接写数值。
• 如果表中存在自增字段，如 id，可以省略它，数据库会自动生成。

✅ 省略列名插入
当插入的数据与表中列的顺序和数量一致时，可以省略列名：

INSERT INTO user VALUES (NULL, 'Emma', 'emma@example.com', 29);

• NULL 表示使用自增字段自动生成主键。
• 不推荐省略列名，容易出现字段错位问题。

---

三、一次插入多条记录

在 MySQL 中，可以使用 一条 INSERT 语句插入多条记录，提高性能。

✅ 语法：

INSERT INTO 表名 (列1, 列2, ...) VALUES
(值1, 值2, ...),
(值1, 值2, ...),
(值1, 值2, ...);

✅ 示例：插入多条记录

INSERT INTO user (username, email, age) VALUES
('Frank', 'frank@example.com', 26),
('Grace', 'grace@example.com', 27),
('Helen', 'helen@example.com', 32);

📌 优势：

• 多条记录合并成一条 SQL 语句插入，效率更高。
• 减少了多次连接数据库的开销。

---

四、使用 INSERT INTO ... SELECT 插入

MySQL 支持通过 SELECT 查询结果插入到另一张表中。

✅ 语法：

INSERT INTO 表名 (列1, 列2, ...) 
SELECT 列1, 列2, ... FROM 其他表 
WHERE 条件;

✅ 示例：将 user 表中的数据复制到 user_backup 表中

CREATE TABLE IF NOT EXISTS user_backup LIKE user;

INSERT INTO user_backup (username, email, age)
SELECT username, email, age FROM user;

📌 应用场景：

• 数据备份或转存。
• 从其他表批量插入数据。

---

五、ON DUPLICATE KEY UPDATE 插入或更新

当插入数据时，如果表中存在主键或唯一索引冲突，可以使用 ON DUPLICATE KEY UPDATE 实现：

• 如果记录存在，则更新。
• 如果记录不存在，则插入。

✅ 语法：

INSERT INTO 表名 (列1, 列2, ...) 
VALUES (值1, 值2, ...) 
ON DUPLICATE KEY UPDATE 列1=新值1, 列2=新值2, ...;

✅ 示例：如果邮箱已存在，则更新年龄

INSERT INTO user (username, email, age) VALUES
('Alice', 'alice@example.com', 29)
ON DUPLICATE KEY UPDATE age = VALUES(age);

📌 解释：

• VALUES(age) 表示插入时使用的 age 值。
• 如果 email 是唯一键且存在，则更新 age。
• 如果不存在，则插入新记录。

---

六、REPLACE INTO 插入或替换

REPLACE INTO 类似于 INSERT，但具有以下特性：

• 如果主键或唯一索引冲突，先删除原记录，再插入新记录。
• 如果没有冲突，则直接插入。

✅ 语法：

REPLACE INTO 表名 (列1, 列2, ...) VALUES (值1, 值2, ...);

✅ 示例：替换数据

REPLACE INTO user (id, username, email, age)
VALUES (1, 'Alice', 'alice_new@example.com', 26);

📌 注意：

• REPLACE 是删除后再插入，因此会生成新的自增 ID。
• 不适合有外键约束的表，容易引发问题。

---

七、自增字段插入

在有自增字段的表中：

• 如果不指定自增列，数据库自动生成。
• 可以使用 LAST_INSERT_ID() 获取最近插入的自增 ID。

✅ 示例：获取自增 ID

INSERT INTO user (username, email, age) VALUES ('Ivy', 'ivy@example.com', 31);

SELECT LAST_INSERT_ID();  -- 获取最后插入的自增ID