MySQL之插入记录(不同场景下的使用)

MySQL之插入记录

• INSERT INTO
• INSERT IGNORE INTO
• REPLACE INTO
• INSERT ... ON DUPLICATE KEY UPDATE

针对于不同场景，可以使用不同的方法对数据进行插入

INSERT IGNORE INTO 和 INSERT INTO

INSERT IGNORE INTO 和 INSERT INTO是 SQL 中用于插入记录到数据库的两个不同命令,主要区别在于如何处理数据违反约束(例如唯一约束)的情况。

INSERT INTO

INSERT INTO是最常见的插入命令，用于将记录添加到表中。如果插入的数据违反了表的约束（比如试图插入重复的唯一键值），则SQL操作会失败，并返回一个错误。

INSERT IGNORE INTO

INSERT IGNORE INTO这个命令与INSERT INTO类似，用于插入新的记录。但如果数据违反了约束，它会忽略违反约束的那条记录，操作不会插入该记录，但不会返回错误。相反，操作会继续执行，尝试插入其他记录。

INSERT IGNORE INTO有时用于避免由于重复键引起的插入错误，但它不会告诉你哪些记录被忽略了。

REPLACE INTO

REPLACE INTO这个命令首先检查表中是否存在与要插入的记录在唯一索引或主键上有相同值的记录。

如果有，旧纪录会被删除，然后插入新的记录

如果没有，就直接插入新纪录。

REPLACE INTO实际上是一个删除旧纪录再插入新纪录的过程。

用法

-- 先检查主键id为7的记录是否存在
SELECT * FROM student WHERE id =7;

-- 记录存在
id	name			create_time
7	张三			   2024-03-12 09:20:11

-- 使用 replace into 命令插入记录
REPLACE INTO student(id	, `name`, create_time)
VALUES (7,'李四',NOW());

-- 执行成功，再次查询主键id为7的记录
id	name			create_time
7	李四			   2024-03-12 09:22:30

使用场景

• 当你需要删除旧纪录并替换成新纪录时，可以使用REPLACE INTO。它首先尝试插入数据，如果发现数据违反唯一性约束（如主键或唯一索引冲突），它将删除旧纪录，并插入新纪录。
• 适合那些旧数据不重要，只需要保留最新状态的场景，如更新缓存信息，最新状态等
• 但要注意，因为它实际上会删除再插入记录，所以会影响到具有自增主键的表，即主键的值会增加。

INSERT … ON DUPLICATE KEY UPDATE

INSERT … ON DUPLICATE KEY UPDATE这个命令用于在遇到违反唯一约束的情况时，更新旧纪录。如果插入的数据违反了唯一性约束（如主键或唯一索引），那么执行指定的更新操作而不是插入新的记录，这允许用户定义违反约束时如何更新现有记录

用法

-- 先检查主键id为7的记录是否存在
SELECT * FROM student WHERE id =7;

-- 记录存在
id	name			create_time
7	张三			   2024-03-12 09:20:11

-- 使用INSERT ... ON DUPLICATE KEY UPDATE 命令
INSERT INTO student (id	, `name`, create_time)
VALUES (7,'张三',NOW())
ON DUPLICATE KEY UPDATE `name` = '吗喽', create_time = NOW();

-- 执行成功，再次查询主键id为7的记录
id	name			create_time
7	吗喽			   2024-03-12 09:26:33

使用场景

• 当你想要在记录已存在（即触发唯一约束违反）时更新特定字段，而保留其他字段和记录不变时，可以使用INSERT ... ON DUPLICATE KEY UPDATE。
• 适合需要保留已有记录的一部分信息，而只更新部分字段的情况。例如，更新用户的最后登录时间，而保留其他用户信息不变。
• 这种方法不会删除任何行，因此不会影响到自增字段的当前值。这对于保持数据的完整性和历史记录很有帮助。