经验 MySQL插入新记录的三种方法：判断记录是否存在，存在则...

2021年9月10日浏览数 77700 最后修改时间 2021年9月12日 赞 (0)

需要一次性修改MySQL里的数据时，我倾向于写SQL实现，而不是写个脚本发布到线上执行。简单的增删改查不在话下，不过有些情况稍微复杂，并且不常见，如果你不知道一条SQL就能实现，那就会花更多的时间去写脚本和走发布流程。以下几种情况你遇到过吗？

一、插入新记录前判断记录是否存在，如果存在就不插入，不存在则插入。

有以下这样一个用户表，现在需要一次性导入1000个用户信息，需求方给到一个Excel文件，要求：如果数据库中原来有相同的用户类型和昵称则不新增。

CREATE TABLE `user` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL DEFAULT '' COMMENT '登录名',
  `type` tinyint(1) NOT NULL DEFAULT '1' COMMENT '用户类型，1普通用户，2管理员',
  `nickname` varchar(20) NOT NULL DEFAULT '昵称',
  `last_active` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '最后活跃时间',
  `create_time` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '注册时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idxUsername` (`username`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户信息表';

这里列举插入一条记录的SQL样例

INSERT INTO `user` (`username`, `type`, `nickname`, `last_active`, `create_time`) 
SELECT * FROM (SELECT 'aaa', '1', 'AAA', '1631149200', '1631149200') AS tmp 
WHERE NOT EXISTS(SELECT id FROM `user` WHERE `type`='1' AND `nickname`='AAA') 
LIMIT 1;

二、判断记录是否存在，存在则更新，不存在则插入。

同样是往上面的 user 数据表插入1000条新数据，这次要求：如果数据库中原来有相同的登录名，则不插入新的记录，只更新原用户的类型和最后活跃时间。

表中的登录名username是唯一索引，这里需求用上它。插入新数据时，遇到唯一索引冲突时做更新操作，没有冲突就是新增。有两个方法可以实现：INSERT INTO 和 REPLACE INTO，他们的区别是REPLACE INTO会先删除旧数据再插入一条新数据，这意味着遇到相同的登录名时，旧数据会被删除，新数据的主键id与旧数据的id不一样。

INSERT INTO `user` (`username`, `type`, `nickname`, `last_active`, `create_time`) 
VALUES ('aaa', '1', 'AAA', '1631149200', '1631149200')
ON DUPLICATE KEY UPDATE type=VALUES(type), last_active=VALUES(last_active);

REPLACE INTO `user` (`username`, `type`, `nickname`, `last_active`, `create_time`) 
VALUES ('aaa', '1', 'AAA', '1631149200', '1631149200');

评论（0）

还没有人评论哦

经验 MySQL插入新记录的三种方法：判断记录是否存在，存在则...

一、插入新记录前判断记录是否存在，如果存在就不插入，不存在则插入。

二、判断记录是否存在，存在则更新，不存在则插入。

我来说说